Hi, In this post I want to discuss database placement and recovery within a SQL Server Availability Group.

I've seen posts in the past offering a wide range of recommendations and solutions to placement and recovery
and it's important to clarify the ideal practice.

As we know, there is no current maximum for the number of databases in an AG, although, Microsoft have
tested various configurations up to 100 databases.

But first, what's the point of an AG, why use one? If we understand this we have a guide for our maximum number.

First let's recall what happens during the AG failover:

All databases in an AG go through a recovery process whereby the committed log records are replayed into the database,
uncommitted records are rolled back. The thread that performs this operation is now multi threaded since SQL Server 2016,
but it must be kept in mind that the instance only has a finite number of worker threads available.
You can track the recovery process inside the SQL Server error log, a sample is shown below:

Once all of the databases have been recovered, the group will be brought online and the databases are accessible,
this is the important part to realise. If you have, 5 or even 10 databases this could be fairly quick. If you have 100 or more
databases this could be a lengthy task. Databases with a long outstanding redo queue will affect the group recovery time too.
Very large databases could have a lengthy redo queue even with multiple threads active.

Second, what's the point of an AG?

A SQL server Availability Group is designed to ensure that logically grouped sets of databases failover together as a unit in the event
of an issue. You may have an application that has 3 or more logically grouped databases and they're all required to be online for the
application to function correctly. Over the years i have seen many configurations, one in particular I encountered was where the AG
had just about every database on the instance ( around 130 databases ), none of which were logically linked and the group recovery
time took over 15 minutes.

Also, with separate AGs and listeners per application you can expose dedicated endpoints into the databases.

So what is the best\ideal configuration to use?

Understanding the application architecture is an important point, knowing how the application databases are utilised will give a clear
picture on how the highly available database placements can be configured. Engaging with the architect team to highlight preferences
for application design\topology is important to ensure that scenarios have been considered that they may have overlooked.
The takeaway here is, don't just put all of your databases into one AG and hope for the best.
Plan based on application requirements and find the best configuration.