I have an AlwaysOn Availability Group which is running with Synchronous Commit & Automatic Failover. For example, connected to as SQL01 with nodes SQL01A or SQL01B.
I want to make this data available for Warehousing on another server SQL02, due to high numbers of changes and high numbers of reads we cannot use AAG Asynchronous Commit as it gets behind and causes high waits.
Because of this I have decided to logship to SQL02 once daily. However with the AAG failing over between the nodes SQL01A & SQL01B I am not sure on best practices around this.
I had set up LSBackup,Copy,Restores from SQL01A. Then failed over to SQL01B and set up. However then my jobs error on the copy and restore, so I can add an additional step onto each job to check if it is the primary copy or restore job and exit out if not, but this just seems messy and I feel I am missing something?
Any advice would be greatly appreciated.
Are you doing your backups to a neutral independent server like a file share? If you’re doing them locally (like to a drive on SQL01A or SQL01B) then log shipping won’t work since the file path would change whenever there’s a failover.
Instead, on SQL01A and SQL01B, write your backups to a UNC path (like \myfileservermyfileshare) and then SQL02 will always be able to find them there.