1

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.

3

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 \\myfileserver\myfileshare) and then SQL02 will always be able to find them there.

  • Thanks Brent, that has helped, in doing that I have uncovered the actual error that I'm getting. After setting up Backup,Copy,Restore on SQL01A, all works okay. I failover and set up on SQL01B, SQL01B copies/restores work but SQL01A jobs error with "The specified agent_id or agent_type 1 do not form a valid pair for log shipping monitoring processing" understandable as I have overwritten the log shipping configuration. Unfortunately we are only licensed for 1 active 1 passive server, so can only really backup from the Primary, meaning our only option is manual setup in the event of failover? – Ryan Nov 11 '16 at 15:03
  • Uh oh - if you're only licensed for 1 active and 1 passive server, you're already in trouble - you have 2 passive servers (SQL01B and SQL02). – Brent Ozar Nov 11 '16 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.