2

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)?

  • I would consolidate databases unless you have some that need their own instance. – paparazzo Apr 28 '16 at 17:15
2

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

1

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.

  • Thanks for the responses. I think I'm on the right track with what is being said here but good to confirm. I realize there is not likely one single answer to all scenarios but the hope is to reduce the chances of thing falling through the cracks. Some databases would make more sense to be on their own while many would be just fine and happy living as a community of databases... – Robert May 2 '16 at 17:32
  • To avoid them falling through the cracks, I would make a list of Registered Servers using SQL Server Management Studio, and start to divide them into groups. This way, you can keep similar instances in the same Registered Servers folder, which will help you. There's also tools and Powershell scripts online to help you make this list, they can read all SQL Servers listening on your network. – Arthur D May 3 '16 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.