5

We are planning a migration from current mirroring architecture to Availability Groups for several databases. For every Database we have defined the primary/secondary replica configuration.

The question that I'm unsure is: do we need to group the databases with the same primary/secondary configuration into the same AG, or use one AG per Database. What are pros&cons of having individual AGs vs AG with multiple databases? Is there a best practice to determine if a database needs its own AG or can be combined with others? Any performance or failover advantages?

Improve this question

1 Answer 1

Reset to default
5

An Availability Group is a unit of failover. Therefore, when considering how to group databases into an AG, keep in mind that all of these DBs will fail over together.

If there are multiple databases that must failover together, then I would recommend putting those databases into a single AG.

Similarly, you may have databases that are completely independent, where you want to put them in separate AGs so that a failover of one does not affect the other.

In some cases, you may wish to put databases into AGs based on logical groups that fail over together, even if they technically can fail over independently.

Having too many AGs or too many DBs per AG can both be management headaches. For example:

  • If you have 50 DB, each in it's own AG: you'll have 50 unique connection strings to manage, and 50 failovers to execute for a planned failover.
  • If you have 50 DBs in a single AG: If there is a problem with one database that necessitates a failover, then the other 49 databases need to be failed over as well, with all 50 databases incurring a short outage during the failover.
Improve this answer
5
  • It's an excellent answer. @Jk_xp Just remember that AMtwo is talking about a logical group. AGs don't support cross-database transactions (though I suspect they're heavily used everywhere). Some databases will start failing over before others so your use case has to be able to handle that.
    – Cody Konior
    Aug 9, 2016 at 0:44
  • @cody-konior, correct me if I'm wrong, but read-only cross-database queries are supported even if a client is connected to AG listener, assuming that replicas on the server are primary or read-enabled secondary.
    – Vadim K.
    Aug 9, 2016 at 15:54
  • It works but I don't know if I've ever seen Microsoft define their use of the term. The problem case being a failover at a bad time kills your databases, permanently.
    – Cody Konior
    Aug 10, 2016 at 3:42
  • 1
    Please note that that what I am about to say is neither condoned nor supported. If it truly is a read only query, it should be fine. Additionally you could take the policy that any in-doubt distributed transactions should be rolled back. This can be set via sp_configure or a trace flag. 2016 has much more support for cross database transactions.
    – Sean Gallardy
    Aug 11, 2016 at 1:17
  • 1
    Thanks for sharing Sean, that's cool information. I wish we could test it though. (Here's the source for the in-doubt xact resolution sp_configure option: msdn.microsoft.com/en-us/library/ms179586.aspx)
    – Cody Konior
    Aug 11, 2016 at 3:50

Your Answer

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

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