Number of databases limit for Database Mirroring within X64 environment?
We have had a request from one of the solutions company stating they have to mirror 15 SQL Servers that are dispersed across the European locations to a central location. For this they have deployed the X64 environment for operating system and SQL Server application too. So now the question is will there be any limit on the number of databases to involve in this type of setup. Each database will not grow beyond 25GB in next 3 years and few of them are 24/7 type of applications within minimum database maintenance window.
As usual having number of databases within a Database Mirroring session depends on the database size and their usage, storage size and features to use those databases for reporting purpose. The general rule of thumb by Microsoft set as 10, but this is more or less applicable for a 32 bit environment due to the amount of memory and resources availability. Based on the resource (memory and threads) utilization by database mirroring, it is a best practices recommendation to mirror not more than 10 databases in one instance. The recommended limit of 10 is only approximate and not an exact number. The limit can vary depending upon your application and workload, this is where it differs on the X64 environment. Also within this DB Mirroring session ensure to have a setup of same kind of operating system including the SQL Server version in addition to the service packs & hotfixes.
Within my experience there are few additional considerations in this regard:
-
What kind of high availability option you need to have for the database that are spread across the geographical locations.
-
Check the recommendations of the product that owns these databases. If you are using any third party tools other than in-house developed applications, in case to support failover of servers in case of any issues.
-
If there is any dependancy to few databases or interrelated databases, be aware that database mirroring works at the database level and does not ensure that a group of related databases will all failover at the same time.
-
When working with multiple related databases you probably want to be in control of the failover, failing over all databases at the same time in some manual (or at least not the built-in automatic) way. Ensuring transactional consistency across those databases across failovers is beyond database mirroring's built-in guarantees.
-
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.