Virtualising SQL Server

Is virtualising SQL Server a good idea or utter madness?

Consolidation is the hot topic in data centres everywhere as space, cooling and power constraints have frustrated other routes to expansion. The key technology used to achieve this is server virtualisation, the ability to run multiple workloads each with a separate operating system and applications on one physical server in such a way that each of these workloads behaves as though it was a server in its own right.

Why Virtualise?

It’s important to understand what your organisation's objectives are in adopting virtualisation, and why it appears that your infrastructure colleagues are almost fanatical about it:

· Consolidation. Given that many servers are running at less than 20% capacity while occupying the same space and power as if they were running flat out there is clearly a lot of advantage in running a set of virtual machines on one physical box.
 
· Development and Test. Essentially a virtual machine is a small set of metadata files plus one physical file for each hard disk in the virtual machine. These can be quickly copied and saved allowing something to be tested and then if problems arise simply revert back to the backup copy.

· Manageability. It is a simple matter to shut down a virtual machine and copy it to another physical server. This is obviously far easier then re-installing software and restoring backups to move an environment from one physical machine to another. This means that virtual workloads can be easily distributed across a set of physical servers to meet users' demands.

A lot of this applies to SQL Server as well:
· Consolidation. SQL Server sprawl is a well known problem and some DBAs I know don't even know how many servers they've got. Traditionally the key weapon in consolidating SQL Server workloads has been the use of multiple instances. These allow the DBA to assign CPU and memory to a given workload and also allow applications to make use of sysadmin and sa permissions just for a particular third party application without providing this access to other databases.

Instances also allow individual instances to run different versions, service packs and cumulative updates of SQL Server all on one physical server. However, some third party applications mandate the installation of other software and settings on a server which might conflict with another application and there can also be restrictions on which operating system is supported.

· Development Life Cycle. Applying changes to SQL Server can be time consuming because it's essential to have a rollback position if something gets broken. This applies to cumulative updates, schema and script. Backing out of changes can also be time consuming. For example uninstalling a cumulative update or recovering from a failed upgrade might mean a complete reinstall of SQL Server.

Provisioning test environments can also take up a lot of time and it might not be possible to justify a dedicated server for this so developments often sit alongside test which is not always ideal.

· Manageability. SQL Server occasionally gets installed in branch offices, possibly as part of a replication topology to provide local reporting and applications. There might also be domain controller such as the read only domain controller introduced in Windows server 2008. Virtualisation would allow you to run both of these on the same physical box but in different virtual machines.

Why Not Virtualise?

Really large and heavily utilised databases running at near the capacity of the server they reside on are already pretty efficient and so consolidation is not a factor, so why would anyone virtualise these? The answer is you don’t and can’t. One of the limitations of virtualisation at the moment is the number of processors that can be allocated to a virtual machine is four.

Other types of SQL Server workloads like SharePoint can place heavy demands on a server and these should be last on your list to virtualise. This also applies to Analysis Services So rather than being a fanatical advocate or opponent of virtualisation it's important to understand where best to apply this as part of a consolidation check list:

· Can I move the database to an existing instance of SQL Server (which might be on a virtual machine) without affecting the performance of the existing databases in the instance or the database to be moved?

If this is possible you have no extra installations of SQ:L Server to do which will save the processing overhead and possible licence implications .

· Can I create a new instance on a machine (physical or virtual) without adversely affecting the other workloads on that machine or the database or instance I am moving?

In this case you’ll need to install SQL Server but you won’t need to create another Windows installation with its associated overhead and possible license implications.

· If the answer is no to these questions then you need to create a new machine (physical or virtual), install Windows and SQL Server, accept the processing overhead and license both the operating system and SQL Server (more on that later).

Next page: Configuring the virtual environment