SQL Server Always-On: Centralizing Backup History Across Replicas

Context

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’ve 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.

Because the msdb 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?”.

Before we dive deeper, a quick disclaimer for the lucky ones: If you are running SQL Server 2022 and have implemented Contained Availability Groups 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.

For the others, in this post, we’re going to explore how to stop chasing metadata across your cluster. We’ll look at the limitations of the local msdb, compare different ways to consolidate a unified backup view using Linked Servers, OPENDATASOURCE, and PowerShell, and see how to build a monitoring query that finally tells the whole truth, regardless of where the backup actually ran.

The traditional approach

The traditional approach is therefore to query the msdb 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.

SELECT
bs.database_name,
MAX(bs.backup_finish_date) AS LastBackup
FROM msdb.dbo.backupset bs
WHERE bs.database_name = 'StackOverflow2010'
GROUP BY bs.database_name;

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 will contain incomplete data because the primary msdb will be the one on SQLAGVM2.

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.

The good old Linked Servers

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, and union the results with your local data.

SELECT
@@SERVERNAME AS [PrimaryServer],
bs.database_name,
MAX(bs.backup_finish_date) AS LastBackup
FROM msdb.dbo.backupset bs
WHERE bs.database_name = 'StackOverflow2010'
GROUP BY bs.database_name

UNION ALL

SELECT
'REMOTE_MSDB' AS [ReportingServer],
bs.database_name,
MAX(bs.backup_finish_date) AS LastBackup
FROM [REMOTE_MSDB].msdb.dbo.backupset bs
WHERE bs.database_name = 'StackOverflow2010'
GROUP BY bs.database_name;

On paper, the best way to secure a Linked Server is to use the Self option, ensuring that your own permissions are carried over to the next node. It’s the most transparent approach for auditing and security. However, this is where we often hit a silent wall: the Double-Hop. Unless Kerberos delegation is perfectly configured in your domain, NTLM will prevent your identity from traveling to the second replica. You’ll 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.

SELECT auth_scheme 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;

To bypass this hurdle, it is common to see Fixed Logins 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 if one instance is ever compromised, a major concern in modern Zero Trust 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.

The “On-the-Fly” Connection: OPENDATASOURCE

To address the frustrations of Linked Servers, another T-SQL path often explored is the use of ad hoc queries via OPENDATASOURCE.
The concept is tempting: instead of building a permanent bridge (Linked Server), you use a temporary ladder ; OPENDATASOURCE 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.

In theory, you would use it like this to pull backup history from your secondary node:

SELECT 
    @@SERVERNAME AS [Source Server],
    bs.database_name, 
    MAX(bs.backup_finish_date) AS LastBackup
FROM OPENDATASOURCE('MSOLEDBSQL', 'Data Source=SQLAGVM1,1432;Integrated Security=SSPI').msdb.dbo.backupset bs
WHERE bs.database_name = 'StackOverflow2010'
GROUP BY bs.database_name;

However, if the environment is not properly configured, an error will occur immediately.

Msg 15281, Level 16, State 1, Line 1
SQL 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. 

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. This requires tweaking the advanced configuration of the instance.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Once these options have been reconfigured, access to the remote msdb is finally possible.

While we successfully made OPENDATASOURCE work, the trade-offs are significant. We have ended up with a solution that is brittle, difficult to maintain, and a potential liability:

• Hardcoding & Maintenance: Every replica requires a manually adapted connection string. If a server is renamed, a port is migrated, or a password expires, the entire monitoring logic collapses.
• Security & Shadow IT Risk: Enabling Ad Hoc Distributed Queries opens a permanent hole in your instance. You aren’t just allowing your script to run; you are allowing any sysadmin to connect to any external server, creating a ghost feature that can be easily misused.

In short, we are fighting the SQL engine’s security defaults just to get a simple timestamp. For a truly robust and scalable solution, it is time to look beyond T-SQL.

Here comes the superhero Powershell

PowerShell steps in as the ultimate lifesaver, delivering high-level automation and pure execution simplicity. It allows you to centralize and control your scripts from an external management server, piloting your entire fleet remotely without cluttering your SQL instances.

By leveraging the power of dbatools, we shatter the limitations of traditional T-SQL. We gain dynamic flexibility and enhanced security by bypassing risky configurations, all while maintaining total control over how we manipulate the retrieved data. The security gain does not come from PowerShell itself, but from removing risky SQL Server surface area features and centralizing access control on a hardened management host. This works because PowerShell establishes direct connections from your management host to each replica. This bypasses the NTLM double-hop issue entirely, as your identity is never passed between servers, removing any need for complex Kerberos delegation or risky fixed logins.

Here is how to put this into practice. By using the Availability Group Listener as your unique gateway, you can dynamically discover the cluster topology and query all member nodes.

$Listener = 'SQLLISTENER,1432'
$TargetDB = 'StackOverflow2010'

try {
    $Nodes = Invoke-DbaQuery -SqlInstance $Listener -Database 'master' -Query "SELECT replica_server_name FROM sys.dm_hadr_availability_replica_cluster_states" 
    $BackupQuery = "SELECT SERVERPROPERTY('ServerName') as [InstanceName], MAX(backup_finish_date) as [LastBackup] FROM msdb.dbo.backupset WHERE database_name = '$TargetDB' GROUP BY database_name"
    write-output $Nodes
    $Results = foreach ($Node in $Nodes.replica_server_name) {
        write-output $Node
        Invoke-DbaQuery -SqlInstance $Node -Database 'msdb' -Query $BackupQuery -ErrorAction SilentlyContinue
    }

    $Results | Where-Object { $_.LastBackup } | Sort-Object LastBackup -Descending | Select-Object -First 1 | Format-Table -AutoSize
}
catch {
    Write-Error "Error : $($_.Exception.Message)"
}

This script is just a first step, but it lays the foundation for truly scalable infrastructure management. By shifting the logic to PowerShell instead of overloading our SQL instances, we achieve a robust and extensible method capable of handling large Always-On ecosystems without additional manual effort.

In this example, the listener name is hardcoded for the sake of clarity. However, the true strength of this approach lies in its ability to work behind the scenes with a dynamic inventory. In a Production environment, you would typically query a CMDB or a centralized configuration file to automatically populate the list of instances. This transforms a simple check into a silent, reliable automation that adapts seamlessly as your SQL environment evolves.

While we wrote the T-SQL manually in this example for the sake of clarity, it is worth noting that dbatools offers an even more streamlined approach with the Get-DbaBackupHistory command. This native function eliminates the need for manual queries entirely, returning rich metadata objects that are ready to be filtered and aggregated across your entire fleet.

$Nodes.replica_server_name | Get-DbaBackupHistory -Database 'StackOverflow2010' | Sort-Object End -Descending | Select-Object -First 1

Final Thoughts: Taking Control of the Always-On Fleet

To wrap up, remember that technical skills are only as good as the management strategy behind them. Transitioning away from legacy methods toward a PowerShell-driven approach is about gaining control over your environment. Here is what you should keep in mind:

• Beyond T-SQL Boundaries: While Linked Servers or OPENDATASOURCE might work for quick fixes, they quickly become bottlenecks and security risks in hardened infrastructures.
• Object-Oriented Efficiency: By using PowerShell and dbatools, you stop managing raw text and start handling objects. This allows you to effortlessly filter, sort, and aggregate data from multiple Always-On nodes to extract a single, reliable source of truth.
• Smarter Security: Running queries externally via dedicated management shells ensures you maintain a high security posture without needing to enable high-risk surface area features on your SQL instances.

The real game-changer is the Central Management Server. By centralizing your logic on a dedicated administration machine, you stop scattering scripts across every instance. This server becomes your orchestrator: it pulls from your inventory (CMDB, central tables), broadcasts tasks across your entire fleet, and consolidates the results. This is exactly the approach we take at dbi services to manage the extensive SQL Server environments under our care. We leverage PowerShell and the dbatools module as the backbone of our scripting architecture. This allows us to collect data in the most comprehensive and optimized way possible, ensuring we deliver top-tier service to our clients.

This is how you move from artisanal, server-by-server management to an industrial-grade automation capable of piloting hundreds of instances with the same simplicity as a single one.