This post is about the (lack) of usage of Microsoft SQL Instances or SQL instance consolidation.

In the wild you see a lot networks where there are several (small) SQL servers. And if you want to save costs our keyword should be consolidation.

When should you think about consolidation?

• You have more then 2 SQL Servers in your environment? Oh yes, those nasty SQL Express editions delivered with several software packages. I mean you too.

Try to cram as much of SQL databases on one server as you can. This will save you costs in several areas:

• Hardware:
  For every SQL server you install there is a hardware overhead for the operating system. Best practices mention you should always reserve 2-4GB RAM for you OS. Memory isn’t  our most expensive resources anymore. But picture yourself what could be done with all those VCPU resources coming available in your virtualisation platform. Consolidating will also save you on disk space. All those ‘c-drives’ suddenly aren’t needed anymore, as will the overhead on all your SQL disks.
• Licensing:
  Did you know that once you’ve paid your Microsoft SQL license you can install up to 50 instances on one server? Or 25 when it concerns a failover cluster.
• Maintenance (read as work hours):
  You will save some time on maintenance:
  • All instances of the same version can be patched at the same time.
  • Only one server to install Windows updates.

However, before you start installing your brand new SQL server, you should think and check your current configuration. You can start by asking yourself the following questions:

• Do I want the same amount of SQL instances as I have now? In other words, do I want to consolidate instance level as well?
• Are there issues among the different versions in use now? Please check the Microsoft documentation on this topic (https://msdn.microsoft.com/en-us/library/ms143694(v=sql.110).aspx).
• Are there versions nearing end of life cycle with Microsoft. Perhaps this is the time to replace them with newer versions. This doesn’t necessarily mean that your application needs to be replaced as well. ‘Compatibility level’ is a setting on database level which offers you great flexibility. In general, I would recommend phasing out all 2005 and 2008 versions.

Some SQL instance settings you should think about:

• Collation: Not all applications require the same collation. Having different database collations on one instance isn’t really an issue. However, if you use constants in queries, they will always have the collation of the instance. In short, different collations in one instance isn’t impossible but certainly not recommended.
• Max DOP: Defines the number of CPU cores used to calculate the result of one query. Also here there some applications (for example Sharepoint) that require MAX dop settings that deviate from the standard practices (https://support.microsoft.com/en-us/kb/2806535).

After thinking all of this through you should end up with one or two SQL servers in your network, which probably is going to be a lot less then you have now. If you end up with more. Try doing the same exercise again.

Do I want virtual or go physical? Most servers will have enough with 4 CPU cores. This is something every virtualisation platform should be able to deliver without to much issues.
Memory is a whole other case. There isn’t really a best practice on how much RAM you should foresee in your server. But if the RAM requirements are greater then 1/2 of the memory in one of your virtualisation hosts you should think about expanding them or go physical.
To prevent adding a single point of failure, when going physical, think about implementing Microsoft Failover Cluster (most cost effective for storage). You could run your passive node on your virtualisation environment to cut costs.

Sources:

• https://msdn.microsoft.com/en-us/library/ms143432.aspx
• https://msdn.microsoft.com/en-us/library/ms143694(v=sql.110).aspx
• https://support.microsoft.com/en-us/lifecycle/search?sort=PN&alpha=SQL
• https://support.microsoft.com/en-us/kb/2806535

Should you have any questions or remarks, feel free to comment on this post or contact me via Linkedin.