0

I have my WSFC setup with three nodes in my Availability Group. One for DR and one primary and the other active secondary. the secondary will be used to failover should my primary server fail. My question is can I also use the secondary to do backups and reporting on it as well?

If so, if there is an on going backup happening on my secondary node, and suddenly the primary crashes. What happens in this case? Does the backup stop and the failover begins to the secondary node, or does it wait till the backup finishes?

I'm using SQL Server 2016

hashguard
  • 403
  • 4
  • 22

1 Answers1

1

Reporting off the secondary, read only copy shouldn't be a problem. Just make sure you include ApplicationIntent=ReadOnly in the connection string of of your shared data source.

Jason A. Long
  • 4,382
  • 1
  • 12
  • 17
  • thank you. What about backups though? Could I use the secondary replica for failover, reporting and backups all together? – hashguard Oct 02 '17 at 07:02
  • 1
    Sure... We haven't had any problems (knock on wood), just monitor the and make sure that the reporting load doesn't interfere with log synchronization. If you see synchronization related wait stats increase on the primary, you can always pull the plug... Also keep in mind that the ApplicationIntent=ReadOnly in the connection string will cut off reporting in the event you fail over to the secondary, **if** there isn't another replica to fill the read only role.. – Jason A. Long Oct 02 '17 at 07:33
  • 1
    Understood. Thank you so much for the clarification. – hashguard Oct 02 '17 at 13:15