question

sakuraime avatar image
0 Votes"
sakuraime asked ManojVISHWAKARMA-0585 published

Max number of Databases in a AG group

Are there any limit of Max number of Databases in a AG group? Putting many databases in a Single AG group has any consideration ?
like many log send queue/ redo threads ???

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

There is as such no limit defined. Microsoft has tested only upto 10 AG and 100 Databases. If you want to create more AGS, You will have to do so and test the workload you have to see that you do not land into performance issues for same.

0 Votes 0 ·
MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered MiaMiao-MSFT edited

Hi @sakuraime,

Are there any limit of Max number of Databases in a AG group?

Suggestion: Better no more than 100 in one AG.

Quote from this case: availability-groups-limits
There is as such no limit defined. Microsoft has tested only upto 10 AG and 100 Databases. If you want to create more AGS, You will have to do so and test the workload you have to see that you do not land into performance issues for same.

Maximum number of availability groups and availability databases per computer: The actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there is no enforced limit. Microsoft has extensively tested with 10 AGs and 100 DBs per physical machine. Signs of overloaded systems can include, but are not limited to, worker thread exhaustion, slow response times for availability group system views and DMVs, and/or stalled dispatcher system dumps. Please make sure to thoroughly test your environment with a production-like workload to ensure it can handle peak workload capacity within your application SLAs. When considering SLAs be sure to consider load under failure conditions as well as expected response times.

More information: sql-server-alwayson-number-of-databases-and-the-hadr-worker-pool

Putting many databases in a Single AG group has any consideration ?>like many log send queue/ redo threads ???

Yes. You are right. You need to consider more about its threads.

Quote from this doc.: prereqs-restrictions-recommendations-always-on-availability
...
A SQL Server instance uses up to 100 threads for parallel redo for secondary replicas. Each database uses up to one-half of the total number of CPU cores, but not more than 16 threads per database. If the total number of required threads for a single instance exceeds 100, SQL Server uses a single redo thread for every remaining database. Serial Redo threads are released after ~15 seconds of inactivity.
...

BR,
Mia


If the answer is helpful, please click "Accept Answer" and upvote it.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered sakuraime edited

Hi @sakuraime,

Is the reply helpful?

BR,
Mia


If the answer is helpful, please click "Accept Answer" and upvote it.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hihi. what's the formular of calculating the worker thread required for number of AG db and number of AGs , on primary and secondary ?

suppose 2AG Group , 2 replica , 10 DBs in each AG Group.

SQL Server 2016 to SQL Server 2019

0 Votes 0 ·