March 2011 - Posts

Mixing SQL Server and Virtualisation
27 March 11 03:20 PM | GavinPayneUK | 1 comment(s)

This article isn’t mean to be an advert for my forthcoming session at SQLBits or the Kent SQL Server user group, both in the next couple of weeks but I suspect it’ll probably turn into one.

Almost everyone has now realised that SQL Server when virtualised works, in fact they can work so well together that we can get flexibility and service levels which are far better than what we had on dedicated physical servers.  Yet despite the knowledge the two can work well together I still hear of people who have heard horror stories about virtualised SQL Servers, see environments which suffer from performance issues and meet IT decision makers reluctant to mix the two.

This is no surprise when you think that SQL Server is probably the only piece of software which out of the box can happily use all your server’s resources once a few small databases, small queries and housekeeping jobs have been installed.  Traditionally we saw people virtualising their “low footprint” servers, domain controllers, file servers, application servers etc and yet SQL Server is now routinely being virtualised with expected workloads that make it far from being a low footprint application.

In my view hosting your production database servers on a virtualised platform is only something you should do once you have:

  • Sized the server’s workload and made sure its a good candidate for virtualisation
  • Understood how to defend your server against the memory tricks hypervisors use
  • Confirmed, if not configured yourself, the hypervisor to work for you, not against you
  • Deployed a DAS/SAN/NAS storage infrastructure which supports SQL Server’s parallel I/O behaviours

In small businesses the person who deploys the virtualisation platform is often also the DBA and if their only previous experience of deploying SQL Server has been on isolated dedicated physical servers then the workload driven contention which virtualisation brings will be a new challenge for them.

In medium sized businesses we may see larger virtualisation platforms deployed but with resource contention and prioritisation introduced to try and make every penny spent count.  While they maybe able to afford the SANs, CPUs and hypervisors smaller businesses can’t we need to make sure these expensive resources are configured optimally for our workloads, i.e. we know SQL Server loves taking and keeping memory, it loves parallel I/O work but doesn’t always need lots of CPU cores.

In large businesses we’re almost certainly going to see a different team managing the virtualisation platform to SQL Server, if the virtualisation platform isn’t outsourced to a separate private cloud provider.  Are the virtualisation administrators going to know how SQL Server works as well as you do or how your individual deployment of SQL Server is scheduled to work?  Almost certainly the answer is no.  In those situations the SQL Server person needs to be informed and prepared for dialogue with the virtualisation team, not so they can argue why they must be right, but to explain their point using a common virtualisation terminology.

The points above should give you some insight into what I’ll be covering and why it might be of benefit to you.  I’ll post my presentation once its completed and presented, however for those who might be interested in broadening their SQL Server related infrastructure skills it might be the session for you.

SQLBits session, Friday 8th 4pm, link here.

Replication Presentation at the Southampton User Group
10 March 11 09:24 PM | GavinPayneUK | with no comments

Last night I delivered a presentation about SQL Server’s replication services to Mark Pryce-Maher’s user group in Southampton.

As those who were there saw this is a massive topic and to deliver anything but a high level overview in 45 minutes does an injustice to the subject.  Therefore, what I gave the Wednesday night audience was a deliberately high level introduction through my slides with an accompanying detailed commentary as well as answering questions as we went along.

The great thing about the breadth of SQL Server’s replication services is their breadth.  The chances are if you need to regularly distribute data between several places then there’s a SQL Server replication feature for you, whether its snapshot, transactional, peer-to-peer or merge replication.  The challenge comes though from knowing what each of those features are designed to do, and which of them could meet your business requirement while sitting within your technical constraints.  On top of that my presentation hopefully gave people the confidence to open books online for the first time and not get put off by the whole new vocabulary replication uses if they hadn’t read about it before.

We also discussed some of the challenges choosing to use replication can bring.  The replication services which affect our database designs the least can have the biggest impact on our database platform’s disk space and performance - transaction logs can grow very quickly if things aren’t sized correctly for example, while other replication services like merge replication can be so prescriptive about how they work to deliver their benefits that we almost have to design our application around them.

The good news is, those who were in Southampton will now be able to at least make their first, second or thousandth step having some new knowledge or new light shed on existing knowledge.

My presentation can be downloaded from here.