I’ve seen servers with thousands of databases on a single SQL Server, and it works. Sure, opening the databases list in SQL Server Management Studio is painful, and a lot of third party monitoring tools fall over, but it’s not so bad once you know how to work around these issues.
But there’s two issues you can’t work around: our old archenemies, RPO and RTO. A quick reminder:
- Recovery Point Objective (RPO) – if the server went down right now, where’s the point at which you could recover the data? It’s measured in time, like 1 second of data loss, 1 minute, 1 hour, etc.
- Recovery Time Objective (RTO) – how long will it take you to get the server back online and functional? It’s also measured in time.
Say you’ve got a single stand-alone SQL Server. You’re doing full backups once a day, and those backups take six hours to complete.
The business comes along and says:
- You can’t lose more than 15 minutes of data (RPO)
- The server can’t be down for more than 1 hour (RTO)
In that case, knowing that your backups take six hours, that means your restores are likely going to take at least six hours. If you lose this server (due to hardware failure, storage corruption, Windows patch gone bad, etc), you’re not going to be able to get the databases restored in time.
You have three options: tune your backups and restores to make them go faster, put less data on each SQL Server, or implement a standby server that’s ready to go when disaster strikes.
The real answer to how many databases (and how much data) you can put into SQL Server isn’t a software limitation: it’s a business limitation. Get the RPO and RTO from the business first, put it into our HA/DR planning worksheet, and that will help you figure out if you’ll be able to recover in time.
Kendra says: If you’re asking this question, that’s your first sign that you may have a problem.
22 Comments. Leave new
That is great article, The “The Cost of Choices” table is very useful to me.
Thanks Brent.
Great point of view. I like how you answer the questions that should have been asked.
If Swart’s ten percent rule applies, then over 3,276 databases in a single instance would mean you’re doing it wrong.
Excellent point, Michael. I’m going to use 3276 as my new benchmark. :p
Nice article .. in the same vein and actually one of the key downside of hosting multiple databaes to one instance is balanceing performance among all databases one query out of controle can make all you other system in bad shape….
In my experience strange things start to happen after about 2000 databases in one instance, among those are that suddenly deleting old backups with xp_delete_file take exponentially longer time, at least until you reboot the server and everythings all honky-dory for a couple of days, bug exists on early versions of 2008R2. MS released a fix in CU11.
And don’t get me started on trying to mirror 2000 databases….. 🙂
The biggest problem with having all these databases on one SQL server is Scheduling and coordinating Maintenace. Say you just received a CU that fixes a big security hole! Having to go through all Application owners, Management approuval and business needs becomes a nightmare!
So you’re saying this might be difficult? http://dba.stackexchange.com/questions/27792/sql-server-express-with-20000-databases-wont-start
The guy must be trolling, no way that can be a serious question?
Nice article Brent,
I´ve made some tests to reach the total number of single databases a SQL Server instances Stands for.
I got the number 32766, that I think it due the smallint data type limitation for database_id
You can´t create the 32767ª database, because of the hidden mssqlresources databases.
Of course nobody with a minimum sense would create so many databases in a single SQL Server intance, but that´s the SQL Server limit.
When you try the create the 32767ª database, a error raises:
Msg 1835, Level 16, State 1
Unable to create / attach any new database because the number of existing databases has reached the maximum number allowed: 32766
regards,
Edvaldo Castro
http://edvaldocastro.com
I have a payroll processing client I managed to get up to just over 7400 databases on one SQL 2005 cluster. I had to hand-craft a log-shipping system to get all those databases over to a reporting/hot-standby box. Worked fine, lasted a long time – once a LOT of I’s there dotted and T’s crossed. We have spread the load around a bit, but the pair of clusters still run over 4000 databases and it is still on SQL 2005!
And Brent is spot-on about RPO/RTO. If the fit-hit-the-shan late in the day on a payroll close or tax reporting close date there is no way success could be achieved without the log-shipped box.
Hi Brent. Thanks for the insightful article as always.
It is true that Consolidation Level (number of databases mutualized on a single host) and RPO/RTO business reqs have to be mapped at some point in time in architecture. But in the end, it almost always comes down to the following 4 variables:
1> The level of IOPS and throughput a consolidated IO subsystem can produce in an automated restore process. The faster the subsystem,the higher the consolidation level with same RTO/RPO. A good case for DAS AlwaysOn systems.
2> The cost of consolidated RTO/RPO based on the topology used (use of standby servers) . LIcense Costs are the key element here
3> The purchase policy into using commodity hardware or appliances vs using expensive brands. This will strongly impact topology choices
4> The ability of the DB Architect to think *out of the box* and take responsibility into designing topologies which will meet budget AND RTO/RPO.
In the end, the idea of using multiple highly consolidated platforms with a single *super* consolidated standby server comes interesting emerges slowly as an economic solution. High RTO/RPO should of course remain on dedicated platforms.
My two cents.
I forgot also to mention
5> The total size of data. The bigger the lower the consolidation level obviously.
A summary :
1> IOPS/Throughput of consolidated system.
2> License Cost impact on topology choices
3> Hardware Purchase Policy
4> DB Architect’s guts and talent
5> Total Size of Data
Regards…
I have found the single largest limiting factor to be uptime.
We have a lot of DB’s that fall in the >99.8% availability category but budgets don’t stretch to Enterprise, given the other business demands with monthly upgrades etc. keeping everything in flight can be a major challenge and many smaller VMS and instances is an answer.
The second largest is the efficiency of the code being thrown at it.
The increased complexity of bus apps makes for different calculations based more on throughput than availability.
So much comes back to “it depends”, the charts above do a very good job of making your choices clear, if not to you then certainly to the committee with the chequebook.
Let me see, I’ve done this course, I know the answer….it depends?!
Brent, this is an awesome topic that we don’t really think about often enough in DB land. To compound on your example, which was a great illustration of an RTO deficiency, when we’re those backups taken. What is the backup strategy over a 24 hour period? You could get the backups down to 1 hour, but if they occur at Midnight and there’s nothing in between, you could still be facing RPO deficiencies. RPO = (Point of Failure – Most recent point of restore) + RTO. 🙂
It can be a very complex workflow to map and test, but thinking through all the steps, regardless the number of DB on a single server, can make reasonable RPO/ RTO attainable.
Great article and I wholeheartedly agree with Kendra.
We’re currently having this debate within our DB group about consolidating everything onto one server. Which I’m currently against because if that one server goes down, then ALL our products go down.
I think that concern is supposed to be mitigated with a proper High Availability strategy, right? 😀
Redundancy/distribution at EVERY level, religiously, is the rather simple trick to lowest downtime.
– From not one Data Centre but two. Make sure also two different companies! Geographically sufficiently separated, but interconnected by dark fibre. Connected to different power grids! Reciprocal nightly full and hourly log backup shipping over dark fibre.
– All the way down to hardware and network redundancy at EVERY available level, CPU, PSU, NIC teaming, Load balancers, switches, firewalls, VPN vs. Rac access…
Whenever there is only ONE of something, that is bad and a recipe for disaster. Simple.
Since 2002, for fifteen years, we typically have over 70.000 Sql Server databases online, distributed over 12-20 Sql Servers standard edition, and in all those years only twice a 1/15th downtime slightly greater than 12 hrs and once a 24 hour powergrid 1/2 downtime. Not bad IMHO.
Nick – interesting, so 70,000 databases / 20 servers = 3,500 databases apiece. What HA and DR mechanisms do you use to protect those as new databases come online?
Wat a excellent article for optimizing server.
Bit late, but better late than never.
Yes that’s about right 3,500 – 4.000 on every server.
As I wrote, distributed over two data centers, with daily full backups and hourly transaction log backups, both copied over to the other datacenter by scheduled robocopy jobs. So in the event of one DC down we have scripts in place which can easily and quickly restore the backups from that DC. That would temporarily give 7,000 db’s per brick (as we call them), but even that is no problem for Sql Server Standard Edition. Then in every DC we also have spare empty brick online.
No clusters (thankfully! ugh!) or HA configuration other than mirroring a couple of handfuls of central databases. And there again, we mirror synchronously from one DC to the other. The distribution of the db’s over the bricks is done by modulo our db key over number of bricks. So we do have a ‘dance’ when we put a new server online, with every brick pulling ‘it’s’ db’s to itself, but that simply takes it’s time in the background (a few days). I’m not sure that’s very future scalable…
This has worked like a charm for us over a decade.
Does this answer your question?
Ah yes, and we do just love Redgate Sql Backup 🙂