Cross-registering backups in SQL Server

Question

My organization is implementing an interesting replication app to slurp data from multiple SQL Server databases and throw it into an Azure data lake. Some of the databases are in availability group clusters. Our AGs are configured to run backups on the secondary replica.

Because of this, my team was told that the app will need backup metadata to be synchronized across the AG members. In other words:

• server A takes a transaction log backup of database foo every 15 minutes
• server B takes transaction log backups of database bar every 15 minutes
• server A, after its backups finish, pulls transaction log backup metadata from B and adds it to msdb
• server B, after its backups finish, pulls transaction log backup metadata from A and adds it to msdb

This way, so the vendor says, whichever replica a database is on, their app will be able to reach transaction log backups and fill in any gaps it may have if it can’t keep up with change data capture. The script they supplied uses RESTORE VERIFYONLY...WITH LOADHISTORY to load the backup metadata into msdb. Fortunately, it’s only the most recent t-log backup for one database on any given AG. At least for now.

Our AG clusters do not have shared storage for backups, so the backups from other replicas would show up with UNC paths. From my experience, people rarely back up using UNC paths and I’m assuming there’s a good reason why.

1. How concerned should I be about this?
2. How much resource contention could this create? I did a test verify on a t-log backup for one of our terabyte-sized databases and it completed in a couple seconds, but I feel like I should be paranoid about that.
3. If we need to run a restore, will the database server actually try to touch the backup files on the other replicas?
4. If the answer to #3 is yes, is there a way I can mark the record so the app can use it but SQL Server will ignore it? We usually don’t keep backups for more than 72 hours (they get siphoned off by NetBackup) so this hopefully isn’t an issue?
5. Is there a better way than RESTORE VERIFYONLY...WITH LOADHISTORY to load the metadata?

Answer 1

From my experience, people rarely back up using UNC paths

With backup-from-secondary it's highly encouraged to have all the replicas drop their backups in a shared network location. So UNC paths is what I would normally expect to use (except in Azure where you might use backup to URL).

If we need to run a restore, will the database server actually try to touch the backup files on the other replicas?

That's why you put the backup files on a network share: so restores can run without knowing which replica took the backup, or connecting directly to the replicas.

As for the rest, that's what 'RESTORE VERIFYONLY...WITH LOADHISTORY' is for.