Microsoft SQL Server consolidation strategy

Reliability and performance are the two main concerns for database administrators, especially those working with large databases that are under continuous heavy load. Virtualisation can play a big role in improving reliability for your datacenter. Have you considered database server consolidation?

Consolidation
SQL server consolidation is something that many SQL server support DBAs and SQL Server consultants shy away from because of the perception that it is too difficult. There was a time when this was true, but modern SQL server installations offer much better consolidation options, and there are many benefits to consolidation.

Consolidation involves exactly what the name suggests - consolidating your servers. Instead of having separate physical servers for each project - with their own hardware, databases and software, and their own individual support and maintenance overheads, you merge your solutions onto fewer machines.

The separate solutions approach leads to "islands" in your datacenter. You may think this is a good thing - if one server goes down, others won't go down with it. However, if your servers are interdependent then you'll still suffer if one server fails. In addition, having multiple machines, each needing OS, server and software patches, security monitoring, backup solutions, and other maintenance work, is a waste of resources. Consolidating your servers reduces the maintenance overhead massively. It could also save you a lot of money in "per seat" licensing fees.

Why now is a good time to consolidate
Now is a good time to start looking at SQL server consolidation. Microsoft will be ending SQL server support for Microsoft SQL Server 2008 R2 in Q2 2014. If you are running that version, or an older and currently unsupported version of SQL server, you should seriously consider upgrading soon.

Newer versions of Microsoft SQL Server offer many performance improvements, even on relatively old hardware. With consolidation, you can enjoy:

Better use of your existing hardware - no more servers sitting at only 10% CPU utilization for most of the day.

Improved scalability - it's easier to move a VM from one machine to another than it is to completely rebuild a physical computer.

Easier backup and recovery - take snapshots of your VMs periodically, and you can re-load them easily to get up and running instantly. Reinstalling a traditional OS (or even re-imaging a drive) takes far longer by comparison.

Rapid provisioning - you can deploy new virtual servers on demand.

Consolidation isn't a magical fix
If you are considering consolidating your SQL servers, you should bear the following in mind:

Performance can be bottle-necked by the speed of your drives in environments with heavy I/O. Consider using SSDs to get around this issue.

The virtual environment you use may limit performance.

The technology is still immature - test your virtual servers thoroughly for unexpected driver/performance issues before putting them into a production environment.

Consolidating your servers is a good way to reduce waste and improve performance and reliability if you have several standalone servers which generally experience only light or medium load. Tread carefully when considering consolidating servers that are heavily utilised, and make sure that you do not overload the physical machine that is hosting the VMs.