Will AlwaysOn work with hundreds of databases in 1 group/multiple groups?

Question

I have inherited a SQL 2017 system that had 114 databases that were synched to a second instance via an Always-On AG, with another 13 databases needing to be added. It was supposed to be providing High Availability.

Long story short, it ended in tears as I started to add in the additional ones.

I know that Microsoft has tested Always-On with up to 100 databases. What I have not been certain about is whether that limit of 100 is a SQL instance limit or per AG.

What I would like to know is if anyone has had experience/success with Always-On with a large number of databases, say 2 or 300? If so was this in a single or multiple groups.

This installation failed due to insufficient memory - I found out the secondary has half the memory of the primary so it is no surprise it came a cropper. There were HADR waits indicating throttling of requests being sent from the primary.

There is a great blog article by Bob Dorr about Always-On and worker pool usage ( https://learn.microsoft.com/en-gb/archive/blogs/psssql/alwayson-hadron-learning-series-worker-pool-usage-for-hadron-enabled-databases )

If the secondary server is beefed up memory-wise, and possibly the primary, is it worth attempting to get this to work using the info from Bob Dorr's article? Or do I cut my losses and go with a SQL cluster, and then log shipping when management realise the costs involved?

Thanks for any thoughts

Simon

Answer 1

Increase the hardware capacity or look for Cloud Solutions, if possible. This is from official doc:

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 tested up to 10 AGs and 100 DBs per physical machine, however this is not a binding limit. Depending on the hardware specification on the server and the workload, you can put a higher number of databases and availability groups on an instance of SQL Server. 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.

I had working experience with huge databases (size), not large in numbers( Availability Groups and databases)

From my experience and based on the research, there is no limit to number of Availability Groups and number of databases by SQL instance or AG. If you have sufficient hardware capacity, you can host any number of databases. If there is not enough memory, even in SQL cluster you will face the issue.