When using availability groups it is hard to get backup history in situation of failover to secondary node. It would good to have msdb backup set data to be in-sync with all the servers involved in availability groups. can i create a procedure or script to have the backup history information for all the backups from different msdb databases to a central database ? What is the best way to do this ?
We made a SSIS package that collect msdb history from each instance and centralize it on a “DBA” database. We then “purge” the local msdb from each instance.
That way, we can query the centralize DB for any backup history and it prevent every msdb from getting to big.