{"id":42169,"date":"2026-01-08T18:19:10","date_gmt":"2026-01-08T17:19:10","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=42169"},"modified":"2026-01-08T18:22:24","modified_gmt":"2026-01-08T17:22:24","slug":"sql-server-always-on-centralizing-backup-history-across-replicas","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-always-on-centralizing-backup-history-across-replicas\/","title":{"rendered":"SQL Server Always-On: Centralizing Backup History Across Replicas"},"content":{"rendered":"\n

Context<\/h2>\n\n\n\n

Monitoring backups in a SQL Server Always On Availability Group can often feel like a game of hide and seek where the rules change every time you flip a failover switch. On paper, your backup strategy is solid ; you\u2019ve configured your replica priorities and your jobs are running like clockwork. But when you query the backup history to ensure everything is under control, you realize the truth is fragmented.<\/p>\n\n\n\n

Because the msdb<\/strong><\/code> database is local to each instance, each replica only knows about the backups it performed itself. If your backups happen on the secondary today and the primary tomorrow, no single node holds the complete story. This leads to inconsistent monitoring reports, “false positive” alerts, and a lot of manual jumping between instances just to answer a simple question: “Are we actually protected with consistent backups?”<\/em>.<\/p>\n\n\n\n

Before we dive deeper, a quick disclaimer for the lucky ones: If you are running SQL Server 2022 and have implemented Contained Availability Groups<\/a><\/strong><\/em> you can stop reading here, you are safe. In a Contained AG, the system databases are replicated alongside your data, meaning the backup history is finally unified and follows the group. <\/p>\n\n\n\n

For the others, in this post, we\u2019re going to explore how to stop chasing metadata across your cluster. We\u2019ll look at the limitations of the local msdb<\/strong><\/code>, compare different ways to consolidate a unified backup view using Linked Servers<\/em>, OPENDATASOURCE<\/em>, and PowerShell, and see how to build a monitoring query that finally tells the whole truth, regardless of where the backup actually ran.<\/p>\n\n\n\n

The traditional approach<\/h2>\n\n\n\n

The traditional approach is therefore to query the msdb<\/code> through the AG listener, pointing to the primary replica. Using a simple TSQL query to find the most recent backup, we get the following results.<\/p>\n\n\n\n

SELECT\nbs.database_name,\nMAX(bs.backup_finish_date) AS LastBackup\nFROM msdb.dbo.backupset bs\nWHERE bs.database_name = 'StackOverflow2010'\nGROUP BY bs.database_name;<\/code><\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
As we can see, the two backup dates differ (14:39 being the time the database was created in the AG and 15:15 being the time of the last backup). As a result, if a failover occurs, the main msdb<\/code> will contain incomplete data because the primary msdb<\/code> will be the one on SQLAGVM2<\/em>.<\/p>\n\n\n\n
This traditional approach is perfectly acceptable for standalone instances because there is only one source of truth, but in the case of Always-On, we need a more robust solution. Let’s examine the different possibilities.<\/p>\n\n\n\n
The good old Linked Servers<\/h2>\n\n\n\n
If you want to keep everything within the SQL Engine, Linked Servers are your go-to tool. The idea is simple: from your Primary replica, you reach out to the Secondary, query its msdb<\/code>, and union the results with your local data. <\/p>\n\n\n\n
SELECT\n@@SERVERNAME AS [PrimaryServer],\nbs.database_name,\nMAX(bs.backup_finish_date) AS LastBackup\nFROM msdb.dbo.backupset bs\nWHERE bs.database_name = 'StackOverflow2010'\nGROUP BY bs.database_name\n\nUNION ALL\n\nSELECT\n'REMOTE_MSDB'<\/strong> AS [ReportingServer],\nbs.database_name,\nMAX(bs.backup_finish_date) AS LastBackup\nFROM [REMOTE_MSDB]<\/strong>.msdb.dbo.backupset bs\nWHERE bs.database_name = 'StackOverflow2010'\nGROUP BY bs.database_name;<\/code><\/pre>\n\n\n\n
On paper, the best way to secure a Linked Server is to use the Self <\/a><\/em><\/strong>option, ensuring that your own permissions are carried over to the next node. It\u2019s the most transparent approach for auditing and security. However, this is where we often hit a silent wall: the Double-Hop<\/a><\/strong>. Unless Kerberos delegation is perfectly configured in your domain, NTLM will prevent your identity from traveling to the second replica. You\u2019ll end up with a connection error, not because of a lack of permissions, but because your identity simply couldn’t make the trip. To determine the type of authentication protocol you are using, use the following query.<\/p>\n\n\n\n
SELECT auth_scheme \nFROM sys.dm_exec_connections \nWHERE session_id = @@SPID;<\/code><\/pre>\n\n\n\n
To bypass this hurdle, it is common to see Fixed Logins<\/em><\/strong> being used as a pragmatic workaround. But by hardcoding credentials to make the bridge work, we create a permanent, pre-authenticated tunnel. From a security standpoint, this can facilitate lateral movement<\/a> if one instance is ever compromised, a major concern in modern Zero Trust<\/strong> architectures. Furthermore, it obscures your audit logs, as the remote server only sees the service account instead of the actual user. These hidden complexities and security risks are precisely why many DBAs are now moving toward more decoupled, scalable alternatives.<\/p>\n\n\n\n
The “On-the-Fly” Connection: OPENDATASOURCE<\/h2>\n\n\n\n
To address the frustrations of Linked Servers, another T-SQL path often explored is the use of ad hoc <\/em>queries via OPENDATASOURCE<\/a><\/em><\/strong>.
The concept is tempting: instead of building a permanent bridge (Linked Server), you use a temporary ladder ; OPENDATASOURCE<\/code> allows you to define a connection string directly inside your T-SQL statement, reaching out to a remote replica only for the duration of that specific query. It feels lightweight and dynamic because it requires no pre-configured server objects.<\/p>\n\n\n\n
In theory, you would use it like this to pull backup history from your secondary node:<\/p>\n\n\n\n
SELECT \n    @@SERVERNAME AS [Source Server],\n    bs.database_name, \n    MAX(bs.backup_finish_date) AS LastBackup\nFROM OPENDATASOURCE('MSOLEDBSQL', 'Data Source=SQLAGVM1,1432;Integrated Security=SSPI').msdb.dbo.backupset bs\nWHERE bs.database_name = 'StackOverflow2010'\nGROUP BY bs.database_name;<\/code><\/pre>\n\n\n\n
However, if the environment is not properly configured, an error will occur immediately.<\/p>\n\n\n\n
Msg 15281, Level 16, State 1, Line 1\nSQL Server blocked access to STATEMENT 'OpenRowset\/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. <\/code><\/pre>\n\n\n\n
By default, SQL Server locks this door. It is a protective measure to prevent users from using the SQL instance as a jumping point to query any other server on the network. To get past this error, a sysadmin must explicitly enable Ad Hoc Distributed Queries<\/strong>. This requires tweaking the advanced configuration of the instance.<\/p>\n\n\n\n
EXEC sp_configure 'show advanced options', 1;\nRECONFIGURE;\nGO\nEXEC sp_configure 'Ad Hoc Distributed Queries', 1;\nRECONFIGURE;\nGO<\/code><\/pre>\n\n\n\n
Once these options have been reconfigured, access to the remote msdb<\/code> is finally possible.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
While we successfully made OPENDATASOURCE<\/code> work, the trade-offs are significant. We have ended up with a solution that is brittle, difficult to maintain, and a potential liability:<\/p>\n\n\n\n