This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
What is the benefit of having multiple databases in the same Availability Group vs single AG per database
Question
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?
asked 2016-08-08 by Vadim K.
Answer
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.
answered 2016-08-08 by Andy Mallon