다음을 통해 공유

Virtualisation for the DBA part 1 - Are you Bovvered?

  • 아티클

image

 

I have been at a couple of trade shows over the last couple of weeks and a consistent theme has emerged, the reluctance of DBA’s to virtualise SQL Server, whether or not that is Hyper-V.  If you fall into this camp please read-on…

Hopefully we can agree that many organisations are worried about SQL server databases popping up on lots of servers and that this borne out of the need to:

  • optimise the licenses you need to buy
  • manage all of this
  • control access to this data  and possibly audit it

The traditional method of consolidation for SQL Server has been to a two tiered approach:

  1. Try and co-locate databases on one installation of SQL Server.
  2. If this isn’t possible then run multiple instances of SQL Server on one machine.This allows you to:
  • Assign CPU and memory to each instance.
  • Further manage memory and CPU with windows resource manager (introduced in Windows Server 2003)
  • Isolate sysadmin privileges.
  • Run different versions of SQL Server alongside each other albeit on the same operating system.

Virtualisation is simply an extension of this approach. It isolates the whole environment at the operating system level not only from other environments on the physical machine but also from the physical hardware itself.   This not only allows many lightly loaded servers to be combined onto one lump of tin, but also allows the movement of these to any server running virtualisation without changing them as required to balance load of for maintenance purposes.

Typically this is initially done in the dev and test servers allowing complete production environments to be quickly created. However in most of the community events I go to 20-30% of the audience now have SQL Severs running in virtual machines in production.

The primary reason many DBA’s object to virtualisation is the loss in performance they will suffer.  However many servers are only under 10-20% load so combing 3-6 of these onto one server is often possible.  Of course the virtualisation process (known as the hypervisor) must use some resource but this is typically only about 10%.

Research and advice on how to get the most out of SQL server on Microsoft’s Hyper-V virtualisation platform is here which is basically:

  • Not to over commit CPU or memory. Below are some simple rules around processors in Hyper-V in Windows Server 2008 R2

image

 

  • Use fixed or pass through hard disks i.e. NOT dynamically expanding disks. Pass through disks are simply a logical pointer to a LUN on a SAN or other storage so the database itself is still on a physical disk.
  • Remember to provision enough network bandwidth when you are consolidating physical machines

BTW there is similar advice and guidance for this from Vmware here.

So basically the flexibility and power of virtualisation comes at small (10%-ish ) cost which for pretty everyone else in the IT world is a price worth paying.  Of course you still  have to manage support and tune in this new world and I’ll cover that in my next post.

Technorati Tags: SQL Server,Virtualisation

Comments

  • Anonymous
    October 19, 2009
    Hi Andy , I may have been one of the people you've been chatting too on this. One of the idea's we've considered is a specific low density virtualisation layer for Virtualising SQL - possibly going as low as 1-1 ( reason being some workloads that are not supported in a MSCS environment - and having Virtual Machine layer clustering is better than nothing )

  • Anonymous
    October 21, 2009
    Chris thanks and do feel free to ping me directly if you need anything more on this

  • Anonymous
    November 26, 2009
    great article really enjoyed it, i've been looking into ways in which my business could implement a virtualisation strategy and i'm very much looking forward to where it will go in the future, this is a good article which i came accross http://bit.ly/futurevirtualisation looking forward to reading more of your articles in the future.