December 2010 - Posts

How are people using virtualisation with SQL Server? Part 2
20 December 10 09:48 PM | GavinPayneUK | 1 comment(s)

This is part two of an article reviewing the results of a virtualisation with SQL Server survey I performed.  Part one can be found here.

How do you size a new virtual server?
When deploying a new virtual server you want to size it according to its predicted workload knowing that additional resource can be allocated as required. Unlike physical servers giving your virtual server more resource than it actually needs can actually be a bad thing, if nothing else if you’ve got resource you’re not using your virtualisation admin is going to spot that and take it back. It was no surprise then that most people started off with a medium sized virtual server (2 vCPUs, 6GB memory) and scaling up or down as required. This was also a good test to see what spec of virtual server people were deploying. Most people started with mid-sized as mentioned previously, however 7 started with just 1 vCPU and 4GB of memory, while only 2 started at a high spec of 4 vCPU and 16GB.

Good vs. Bad Contention

One of the benefits of virtualisation which businesses like is “good contention”, the assumption that a physical server is never 100% CPU utilised so you can consolidate multiple physical servers onto a single one. But did anyone stop to consider the memory and storage availability vs. utilisation? Low workload servers are great candidates for virtualisation; however a production SQL Server probably doesn’t fit that category. Ensuring the benefits of virtualisation are experienced while making sure SQL Server has the resources and performance it requires is an art, which is often over-looked. What I call “bad contention” is when the sum of a resource allocated to all the virtual servers exceeds the amount the host server actually has. For example, I can allocate 8GB of memory to 3 virtual servers running on a host which only actually has 16GB. If all 3 servers really do need 8GB at once then we have bad contention occurring, the missing 8GB has to be found using clever tricks the hypervisor knows about which might have good intentions but might cause you to gulp if you weren’t expecting them Bad contention doesn’t just affect memory, it can affect every resource a virtual server requires, CPU, memory, storage I/O and network I/O.


Attitudes towards contending resources/over-allocation

Now we understand how “bad contention” can be a bad design practice it’s interesting to see people’s attitudes towards it.




A few people are happy to contend CPU and memory, I suspect that ties in with the heavy use of virtualisation for dev/test environments where performance isn’t key. What’s interesting is that most people said they deliberately don’t contend any resources. Wow! To me that means one of three things: people start off with good intentions but reality changes that, people are deploying virtualisation in really high end solutions or what I suspect is the case, people don’t realise how virtualisation contends resource – for example do people really have 2 HBAs in the host server for every VM which requires SAN? This is an area I’ll go into in future articles.


The final of this article, part three, is here.

How are people using virtualisation with SQL Server? Part 1
20 December 10 09:48 PM | GavinPayneUK | 1 comment(s)

Allow me to share with you the results of a survey I did about how people are using virtualisation with SQL Server. This is a three part series of articles sharing the results.

In December 2010 I did a 10 question survey to see how people were using virtualisation with SQL Server and if they were changing their deployment and management processes as a result. 21 people responded from at least Europe, the US and Asia Pac. It’s not a massive number but it was large enough to see some trends and some differences of opinion as we’ll see.

This survey followed on from some thoughts Brent Ozar’s (web|twitter) presentation at SQL Bits gave me about virtualisation needing to change behaviours. He also helped promote my survey so thank you Mr Ozar.

Who answered?

Those who answered had a variety of interactions with SQL Server although the majority were SQL Server DBAs who also interact with the operating system, server hardware and storage. The dev users weren’t missed out representing approximately 40% of the participants. Most interesting was that only 9 out of 21 had any involvement with the hypervisor, arguably the most important component in a virtualisation stack.


What are people using virtualisation for?

I asked where in the application lifecycle people were using virtualisation and the graph below shows the answers best.


Dev and test are obviously biggest areas, no doubt because of the ability to spin-up, spin-down, clone and snapshot makes virtualisation a good platform for these areas. A lower adoption rate in production environments opens up a topic I cover later on: do people trust virtualisation yet for production, or are there features needed which virtualisation can’t deliver? Not everyone has a build, staging or DR environment so I wouldn’t suggest lower figures here are saying bad things about virtualisation.

Do people trust virtualisation used with SQL Server?

There’s a big perception still based upon nothing specific that virtualisation is still in its infancy and people generally don’t trust it. My survey couldn’t answer that question as it was for people who are already using virtualisation with SQL Server. However, what I did ask was how people who do use both together feel about it.
The pie chart below speaks for itself. Everyone is happy using virtualisation, they may not have had a choice even though they didn’t trust it, but what’s interesting is that a large share of people still don’t trust it for production environments.



What influence do people have on the hypervisor?

The results here were as I expected, the majority of people who answered had either full control over the hypervisor or could influence the sizing and configuration of their virtual servers. This is good as it means the sizing of servers still lies with the SQL Server teams, however do SQL Server teams know how to size their virtual servers is the next question?


Part two of this article is available here.

How are people using virtualisation with SQL Server? Part 3
20 December 10 09:47 PM | GavinPayneUK | with no comments


After CPU, memory and storage, monitoring is the fourth thing which changes massively according to Brent Ozar’s list when you move to virtualisation. Some of the performance counters we used to organise our lives around become meaningless and the performance of the host server is often over-looked when looking for problems.




What’s encouraging here is that the majority of people are already looking beyond the performance of virtual server and at the performance of the host server. This is what matters equally as much as looking at the virtual server alone. What’s sad and shows the challenges some DBAs have is that they can’t access performance data of the underlying host servers. This isn’t to be un-expected. In a managed service, IaaS or PaaS world however you just can’t access the host server, that’s part of the magic you buy into. A compromise will need to be found.


Choice of hypervisor

For me this was the most interesting result of the whole survey. I asked what hypervisor people primarily use. I’m sure there are all sorts within an organisation but which is used for those mission critical platforms?



What’s Missing From The Virtual Toolbox?

My final question covered the reasons people aren’t deploying all of their SQL Server installs with virtualisation. I worded the answers expecting features to be the primary reasons and they were certainly popular reasons but not the biggest reason. Lack of high CPU count virtual servers and clustering support were the features people were missing the most, but the majority of reasons were all around knowledge and comfort of virtualisation itself. Either companies were still dictating physical servers had to be used or they just didn’t have enough confidence in their support staff to run mission critical servers on a relatively new piece of technology. Mass adoption of virtualisation is still only 4 years old at the most, using physical servers is decades old. I suspect in 18 months’ time we’ll look back and wonder why we never trusted it for production.



I only had 21 responses, not enough to make the virtualisation marketplace re-consider its strategies, but what I now know is what some people are doing, not doing, or think they’re doing. The biggest observations for me were the dominance of VMware and the fact people really hate the idea of contending resources.

I hope this survey was of interest to you, many thanks for reading.

New UK SQL Server community event
19 December 10 10:43 PM | GavinPayneUK | with no comments

I’m pleased to announce that with the support of VMware I will be holding a new UK SQL Server community event in January 2011.

Wednesday January 19th 2011 6.45-9.00pm
Free registration required, free parking on-site
Registration link here

SQL Server in the Evening, hosted at VMware’s UK headquarters in Frimley in Surrey, will cover contemporary technology topics for those using SQL Server in 2011, as well as providing a chance to make and meet with SQL Server community friends.

The event will have three sessions, with questions and answers encouraged throughout, covering:

  • Getting the most for SQL Server from VMware
    Delivered by a VMware Engineer
  • SQL Server Transparent Data Encryption
    Gavin Payne, Solution Architect, Attenda
  • Understanding where cloud services really fit within your 2011 data centre
    Matt Mould, Advisory Practice Consultant, EMC Consulting

There will be the usual short break mid-evening with pizza and soft drinks.

VMware UK Limited
Theta Building
Lyon Way
GU16 7ER

Wednesday January 19th 2011 6.45-9.00pm
Free registration required, free parking on-site
Registration link here

Any question and queries to myself, Gavin Payne
The role of the Infrastructure DBA
18 December 10 06:02 PM | GavinPayneUK | 2 comment(s)

Do you have someone performing an Infrastructure DBA role within your organisation? Do you realise why today you now might need one?

When I first started working with SQL Server there were three distinct roles in the SQL Server virtual team: developer, DBA and sysadmin.


In my simple terms, the developer looked after the “code”: the schema, stored procedures, and any ETL to get data in, out or updated within the database. They could talk in business entity terms about Customer numbers, Product codes and Contract line items. Whatever else happened in the SQL Server stack didn’t really bother them as long as their code worked.

The DBA knew how SQL Server worked, they’d install the software, create databases, manage indexes and SQL Server Agent jobs, do backups and restores. They’d sometimes write ad-hoc reports so knew which columns held key data but how every bit of database logic worked wasn’t of interest to them. DBAs did however care about the design of the database and the server hardware. Making sure the server hardware was of the right spec and configuration was also another interest for DBAs, along with doing everything to avoid getting called out at 2am.

The sysadmin could never do anything right. He always wanted downtime for Windows patches or firmware upgrades, however, as long as everything got built right and worked we never had to bother him, until a SAN got installed. Then we were never quite sure if he actually knew what he was doing and were pretty sure it wasn’t configured properly.

Ok, there maybe some exaggeration there but with dedicated physical servers those three roles were fairly common throughout the 2000s. Nothing but SQL Server could affect the performance of our database platform, and more importantly with the sysadmin at our side everything was in our control. Equally as important was the fact that our monitoring results were true, no one else’s workloads affected the results.

Role forward to 2010

By the end of 2010 virtualisation and SANs are regularly deployed to host both production and non-production SQL Server environments. The dependency between an instance of SQL Server and physical hardware and disks has been broken, we can create new servers and allocate them storage and resources on demand. However, as the diagram below shows this agility has come from the practice of pooling then sharing our physical resources.

New_stack_1What we must now cater for in our SQL Server is that although hardware resources might be allocated to us, the platforms they come from aren’t dedicated to us. The SAN our data is stored on might also store the Exchange mail server’s data, the virtualisation host server our database server runs on might also be running a web server.

The Infrastructure DBA

This new world brings new responsibilities to a SQL Server administrator, in fact it brings a new role to the SQL Server virtual team. To be able to confidently ensure that SQL Server delivers the performance your applications need in a virtualised world you now need someone who understands:

  • the database’s demands of SQL Server
  • SQL Server’s demands of the server
  • SQL Server’s demands of the storage system
  • How the hypervisor is configured, shared and works
  • How the SAN is configured, shared and works  

Is this because we don’t trust the virtualisation or SAN admins, well I’d hope the answer is no.

However, if we’re to understand how SQL Server is performing, or needs to be configured to perform, when it’s using shared components then we need to know as much about our SAN storage layout as we do the logical TempDB layout, if not more so. Configuration changes can be made which we have no control or influence over which can bring SQL Server to its knees.

As well as defending SQL Server against threats to its performance the Infrastructure DBA can bring new features to the SQL Server toolkit. Infrastructure services such as virtual server snapshotting, SAN snapshotting and SAN-to-SAN replication could be invaluable in some environments. By knowing what the infrastructure you’re using is capable of these feature become options to you.


We will always need application code developers, SSIS junkies, production DBAs, sysadmins, virtualisation admins and SAN admins, but now there’s a place at the table for someone who knows enough about each of these to keep the SQL Server ship sailing, at the right direction and at the right speed.

Welcome to the Infrastructure DBA.