Dedicated SQL instance vs multiple database same server instance

Question

Looking for best practice advice for a sprawling network. I inherited a bunch of SQL servers. The practice has been to just stand up a new VM instance any time a database was needed or add a new SQL instance for every database. The administration challenge is huge so was hoping to hear some feedback on approach. I guess the simple question is, would it be better to run multiple instances per machine or 1 instance with multiple databases assuming licensing is not an issue, security and compatibility can be easily grouped and managed (very small user base, very small lightweight databases)?

Answer 1

assuming licensing is not an issue, security and compatibility can be easily grouped and managed (very small user base, very small lightweight databases)

You already have addressed the concerns.

IMHO, depending on the VM capacity (memory, CPU and disk space), you should consolidate databases to one instance.

This gives you advantage of managing one instance wherein you can have different rules for different databases - e.g. maintenance (index rebuilds, reorgs, stats update, backup schedules) etc depending on their criticality.

When your database size and usage increases, thats the time to move out the heavy used ones out of the instance on a different VM.

Make sure you have proper Disaster recovery inplace e.g. use logshipping/AlwaysON, etc so if the VM crashes or fails, you can failover the databases to secondary server.

Also, refer to my answer for

Which is better: one database per application, or just one database?.

Answer 2

To answer your question simply, if you have 1 VM with several instances, you have the possibility of resource contention between the instances on that VM. In that scenario, I would consolidate databases onto a singular instance.

But if you're talking about multiple virtual servers with one instance per virtual machine, I'm going to go against the grain here and say that you should not consolidate databases, unless necessary. In my opinion, I would consolidate databases onto an instance that perform similar functions or rely on each other.

You retain a lot more control over the instance when they are separate, and being separated by instance will allow you to upgrade versions of SQL server on different schedules.

There's also the issue of vendor-owned databases. When you have a vendor owned database, such as Sharepoint, you should follow as many recommendations in the documentation as possible, especially for instance-level settings such as Degree of Parallelism, or Cost Threshold for Parallelism.

If you are going to make a new database, and you know it could fit into an existing instance, then that is fine.