Hosting Multiple Data Bases

Question

Hi, we have 5 databases and we want to host it to production Server (Windows Server 2012). what is the recommended solution to host Multiple databases i.e, Hosting All 5 Databases on Seperate VM is Better or All 5 DB Should be hosted in Seperate Physical Servers? Pls. Advise. I am not much into DBA

Madan Shekar · Answer

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. RPO and RTO Worksheet RPO and RTO Worksheet 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. If it helps you means please accept the answer