How many database can be added in one Always On Availability Group??

Article
08/09/2017

Question

_{Wednesday, August 9, 2017 6:26 AM}

Hi,

We SQL Server 2016 Enterprise Edition Active - Passive Clustering (SQL_INS_DC) at DC and same (SQL_INS_DR) at DR.

110 database are created at DC instance SQL_INS_DC. We want to configure Always On AG between DC & DR. We have good hardware configuration.

Please let me know how many databases can be added in one SQL Server Availability Group suppose AG1.

Regards,

Nikhil. P . Desai

All replies (6)

_{Wednesday, August 9, 2017 6:34 AM ✅Answered}

Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)
http://msdn.microsoft.com/en-us/library/ff878487.aspx

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 AlwaysOn 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.

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Wednesday, August 9, 2017 8:14 AM ✅Answered}

Please let me know how many databases can be added in one SQL Server Availability Group suppose AG1.

Regards,

Nikhil. P . Desai

Theoretically as much as your hardware can support. But you must note that more the number of databases more would be thread required and if you add way more databases you might start facing worker thread starvation so you can start with 30 databases in an AG I would say test it and keep adding but do not go more than 90-100.

Make sure your hardware is up to mark

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

_{Tuesday, March 27, 2018 8:39 PM}

I would greatly appreciate if anyone had hands-on work experience with a high number of databases (5,000) or more using SQL server “Always On Available Group”.

Please share your experience.

See below.

Here are requirements:

We have about 6,000 databases ranging from 100Mb to under 100Gb.

We would like to use SQL Server 2016 "Always On Available Group" for high available(HA) and disaster recovery(DR).

Two SQL 2016 database servers on the same location(site) for HA (sync).

One SQL 2016 database server on the different location(site) for DR(asynchronous).

We understand the need to adjust "max worker threads" and other SQL server configurations

and based on the published document, we also understand "Microsoft has extensively tested with 10 AGs and 100 DBs per physical machine..."

Number of max worker threads= 512 + ((logical CPU’s - 4) * 16)

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability

Thank you.

--John

Johnc

_{Wednesday, March 28, 2018 5:12 AM}

John,

Please raise a new question or discussion( discussion would be good) piggybacking on old thread will hardly give you any replies.

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

_{Wednesday, March 28, 2018 5:29 AM}

Hi John

The only way to get full instance level protection built into SQL Server is to use a geographically dispersed FCI. That means replication at the storage level either via the hardware or using something like Storage Replica in Windows Server 2016.

That said, FCI + AG is a great HA and D/R combo. FCI would provide local HA, and D/R would be achieved with the AG connecting to your other data center.

That said, you must to know your workloads and test to see if it would work. With 6000 databases in a single instance, you would not only need to ensure that you had enough system resources but would you have huge number of AGs? Or would you have smaller subsets of AGs, each of which would have multiple DBs? Can you get 6000 DBs with AGs working? Possibly, but I think you might be hard pressed. Size of DB does not necessarily correlate to actual usage.

Remember that all nodes participating in an AG or a geographically dispersed FCI must be part of the same WSFC so that also means ensuring AD is right on both sides, etc.

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

_{Sunday, March 17, 2019 3:19 AM}

ok John. Know this is an old thread & interested in knowing what solution U decided to implement.

What we have done for our 10k+ DBs in 1000+ SS is to break it down to this formula: Plus we moved to AZ.

*separate set of DBs to AGs.

*AGs to Cluster Availability Sets.

*Availability Sets to Resource Group

*Resource Group to Regions

Hierarchy is Application -> DBs.

Regards

Share via

How many database can be added in one Always On Availability Group??

Question

All replies (6)

Additional resources