0

I have an AG (say AG1) setup with 2 node wsfc (svr1/svr2) and it has got 20 dbs.

I am being asked to provide 3 of these as a readable copy on another sql server which is located in a separate wsfc (svr3/svr4 - this also has its own dbs in an AG).

I'm exploring DAG as a solution. So I will have to create a new AG (say AG2) on both the servers, move the 3 dbs into 1st server's AG2. And setup a DAG between this AG2 and the new empty AG2 on the 2nd server.

My concern is that - on server 1/2, and server 3/4, I now have 2 AGs, and I want to prevent a situation where either AG can failover to the other server [within the wsfc] (because there will be cross dependencies between dbs in both the AGs).

What are the settings to ensure that AGs (on respective server) failover together (or don't failover at all)?

For example:

  • Flexible failover policy (set to level 1)
  • Db level health detection (set to off)

Anything else?

1 Answer 1

2

What are the settings to ensure that AGs (on respective server) failover together (or don't failover at all)?

There is no built in option or configuration in SQL Server. Since AGs are logical containers of databases that should all be together to serve a common set of goals (or, more often, application(s)) all of the databases that are needed to be together should be in the same AG. There are no dependency options or configurations in SQL Server as all AGs are viewed and treated as independent of each other.

Having said this, there are times, such as you've stated in your question, where only 1 or 2 databases from an entire set need to be in another environment. Generally, this is where something such as Replication or Log Shipping would work better in a broader sense. This allows all of the databases to be in an AG, providing (hopefully) high availability and potentially some disaster recovery or business continuity. Generally, the entire databases is rarely needed for these types of situations which is also why targeted Replication is a good fit (for most, but I not all situations).

If you can't/won't/don't want to go the replication/log shipping/etc. option, it is possible, if utilizing WSFC based AGs to set resource group affinity. This is not 100% and will most likely lead to some... undesirable scenarios, however if you're dead set on having a switch or button rather than a change in architecture or infrastructure, this could provide some modicum of help... possibly.

6
  • How about setting the flexible failover policy to 1 - that way the AG won't fail over for any reason other than server down.
    – variable
    Jan 7, 2023 at 16:58
  • That won't help them fail over together, so no it won't help with your question as I stated in my answer. Jan 7, 2023 at 19:32
  • So my question was What are the settings to ensure that AGs failover together (or dont failover at all)? - agree that setting to 1 cannot ensure AG failover together. But it does ensure they don't failover independently. So it's in my control to make a manual failover within 5 seconds (by right clicking and failing over on each AG). Yes?
    – variable
    Jan 8, 2023 at 5:35
  • The only way to stop automatic failover is to not setup automatic failover. It still doesn't mean that an AG doesn't fail and also doesn't mean should something occur the AGs go together somewhere. Thus, again, setting 1 won't change the outcome. Jan 8, 2023 at 14:37
  • Ok and if I stop automatic failover, then does it matter what the flexible failover level is?
    – variable
    Jan 8, 2023 at 16:49

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.