1

We are planning to create multiple BAGs for SQL Server 2016 Standard Edition. My question is, should I need to same listener for all BAG?

Let's say we have 3-4 AG's i.e AG1, AG2, AG3 and so on.

If one of our AG1 failed, I need to failover all AGs to other node not just one. How can i achieve the same using script or job?

2
  • 3
    I wouldn't use Basic AGs if the databases need to failover together. I'd use an FCI.
    – Tara Kizer
    Dec 13, 2016 at 16:48
  • I don't really see the point of it. If your databases have a dependency between each other, they should belong to the same AG. Having multiple AGs is good if you want to fail then separately or do some load balance between the nodes.
    – Evandro
    Oct 23, 2018 at 15:27

1 Answer 1

3

This is a difficult scenario to truly handle. I would probably start with running a job on your secondary replica that checks the state of all the databases, and should it become primary for one of them then performs the ALTER AVAILABILITY GROUP [AG] FAILOVER; command for all of the others.

The biggest challenge with this is also running jobs on the primary, and taking care to not accidentally try to fail one of those databases back. You could manage this by checking the AlwaysOn extended event session and looking for failovers there, and do not do work if the database was failed away (as the most recent captured event), this way you don't end up bouncing the databases around all over the place.

You can use sys.fn_hadr_is_primary_replica to check and see if any particular database is the primary on your system.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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