Andrew Fryer's Blog

SQL Server MVPs

Andrew_Fryer — Wed, 18 Nov 2009 19:34:00 GMT

The best thing about working with SQL Server is working with the other people that use it. The SQL server experts who do most for their colleagues in the community are recognised as MVPs, so not only do they know their stuff they are willing to share their knowledge.

Of course you could argue that all this furthers their careers and/or businesses and while this might be true up to a point I would counter this by suggesting that you buy SQL Server MVP Deep Dives

For two simple reasons, it’s an excellent example of what MVP’s are capable of producing, and because they have donated all their royalties to the WarChild Charity

Thanks to Kimberley , Paul R, Paul N , Rob, Kalen, Greg and Adam for this. and thanks to Richard Siddaway in the UK for the PowerShell chapter and for letting me know the background.

Technorati Tags: MVP,SQL Server,War Child

A small problem with Managed Service Accounts

Andrew_Fryer — Tue, 17 Nov 2009 07:51:46 GMT

I am very fortunate to always have the latest versions of Microsoft stuff to play with such as SQL Server 2008 R2, but occasionally something doesn’t work, and so I need to tell the relevant product team what the problem is.

In this case I was trying to get the SQL Server 2008 R2 November CTP to run with the new Managed Service Accounts in Windows Server 2008 R2 Active Directory. They are a bit like the NTAUTHORITY/LocalService account in that they don’t have passwords that you need to specify to the service, however they are a totally different beast in that they are domain accounts designed to be used for services, like IIS 7.5. Essentially the passwords are managed internally by active directory and are designed for use with Kerberos. For more on this have a look at this article on TechNet

However they are so new there is no way you can create one without using PowerShell (with the active directory module loaded)..

New-ADServiceAccount SQLService

Then you tell AD where you want to use the account on the server SQL08R2Demo..

Add-ADComputerServiceAccount -Identity SQL08R2DEMO -ServiceAccount SQLService

finally you deploy it to the computer that will use it

Install-ADServiceAccount -Identity SQLService

Now you can see this account in Active Directory under the new managed service account folder..

I can now use an account like this to run IIS by going to services and running IIS with the new account (note you don’t supply a password for this account).

However when Iu try and use it to run a SQL Server service, it doesn’t show up in the SQL Server configuration tool as it doesn’t see this kind of account at all even though you can search the managed service accounts folder. Of course if I go into services proper I can change SQL Server services to use it…

but that is not supported, as it will foul up the permissions needed to do things like backup. BTW I didn’t supply the dollar sign suffix

so I have found something wrong and now I need to tell someone to fix it and the place where you do that for Microsoft stuff , irrespective of whether or not you work for them is on Connect (http://www.connect.microsoft.com).

You register suggestions and bugs by first searching to see if it’s there already and then you can create your own..

Here’s that bug, and notice that no one’s voted on it yet. Votes are primarily there to influence feature requests, but bear in mind that what you or I consider to be a bug, maybe there by design and so could in fact really be a feature request. Anyway the more votes there are coupled with the severity of the problem the more likely it will be fixed

So if you think something needs to be changed then use the site as the product teams are pretty good but are not known for their telepathic abilities.

In the meantime I hope that this gets fixed for the release of SQL Server 2008 R2.

Technorati Tags: Connect,Microsoft bug reporting,managed service accounts,active directory,windows server 2008 R2,SQL Server 2008 R2

SQL Bits Friday session

Andrew_Fryer — Mon, 16 Nov 2009 14:50:15 GMT

I promise this is the last time I mention SQL Bits this week, but I wanted to let you know I have got 25 free tickets for the paid for event this Friday which would normally cost £149 to attend.

The venue is the Celtic Manor in Newport…

and is probably the next best thing to going to TechEd. There are four tracks;

Data warehousing,
Database,
BI,
Development,

specifically on SQL Server 2008 and R2, with an all star cast headed by Donald Farmer, and Thomas Kejser, the details of which are here.

If you can make the date and want to come please e-mail me (afryer@microsoft.com) with SQLBits in the title and I’ll get the tickets to the first 25 to respond.

Technorati Tags: SQL Bits,SQL Server 2008 R2,SQL Server 2008

TechEd 09 Ask the Experts

Andrew_Fryer — Mon, 16 Nov 2009 10:53:00 GMT

I have been at TechEd Berlin last week and when I wasn’t speaking I spent most of the time in the Learning Centre (TLC) or ask the experts. I must confess I felt a bit of a non-expert this year for two reasons;

I have been spending time getting up to speed on the new Windows releases
There were some real experts on the stand..

In the snap below we have Bob Beachemin from SQL Skills, Gopal Ashok and Torsten Grabs from the SQL product team in Redmond. Gopal is mr Replication and HA, while Torsten is mr StreamInsight.

Joanne is also from Redmond while Roger is from Microsoft in Switzerland. On other days we had Omri Bahat (DBEngine), Michael Rys (XML and Spatial) and Donald Farmer (BI and specifically PowerPivot).

AJF 17/11/2009 I forgot to mention the other product superstar on the stand, Chris Baldwin who is Reporting Services.

So going to TechEd isn’t just about going to sessions it’s an opportunity to share experiences with the guys who develop the product as well as having time to do a few labs..

However it’s a tough world out there and so getting your manager to agree to letting you go was hard (however 7,300 managed it this year), so the next best thing if you’re interested in SQL Server is SQL Bits this Thursday – Saturday.

Donald farmer is presenting at this event on the Friday, and in addition there will be a whole crop of MVP’s there and actually these guys have more influence with the product team than I do.

The Saturday community day is free, while the Friday is a paid for event focusing on SQL Server 2008 and R2. Either of these would be my top tip if you need to be looking for your next career move, as the contacts you meet like the MVP’s are often aware of vacancies through the projects they are engaged on.

So I’ll hopefully see you in Cardiff!

Technorati Tags: TechEd Europe 2009,SQLBits,SQL Server 2008

Wir fahr'n fahr'n fahr'n auf der Autobahn

Andrew_Fryer — Sat, 07 Nov 2009 15:27:40 GMT

James and I are on our way to TechEd Berlin, but we’re driving because we’re both a little bit eccentric. Not only that we have a bit of stuff to take..

four laptops, a shuttle, a hub assorted drives, cables so that we can demo a bunch of interesting stuff in our session.

We’ll be showing as many of the following as we can cram in 75 minutes:

Our SQL Server on Server Core (but not supported) cookbook
Using the advanced installation options in SQL Server 2008 R2 to prepare a virtual machine which can be quickly bought on line
Live Migration in Hyper-V2 to move a SQL Server virtual machine from Wallace-R2 to Grommit-R2, the 2 laptops in the picture above.
Managing these virtual machines in the latest version of System Center Virtual Machine Manager (SCVMM).
Using the enterprise policy management framework from codeplex to ensure all the SQL Servers above are configured the way we want them.

All of this will be done from the command line / powershell, and the only thing we can’t share with you is our internal iSCSI target software which so you’ll need something like StarWind or iSCSI Cake.

If you’re going to be there then the session is DAT01-IS on Monday afternoon, but if not then James and I will be making a series of 5-10 minute videos from each demo, when we get back as IS sessions aren’t recorded.

Technorati Tags: SQL Server 2008R2,Windows Server 2008R2,sysprep,scvmm

An Interesting VDI case, Holmes.

Andrew_Fryer — Fri, 06 Nov 2009 10:39:51 GMT

Last June I didi a series of interviews with Alun Rodgers of Risual about the cool stuff in Windows Server 2008. In one of these..

Alun is telling me about how Terminal Services (now known as Remote Desktop Services, RDS, in Windows Server 2008 R2) could be used to provide a secure desktop for police forces and the like.

This would mean that rather than having 2 separate systems on an officer’s desk, one for the classified network e.g. for Holmes (Home Office Large Major Enquiry System) and one for normal administrative duties, everything could be done over one network from one desktop. The upshot of this is that it saves a large pile of infrastructure, maintenance and running costs without compromising the integrity of any of the secure systems.

I did call this out again at the session that Jamie Burgess and I did on RDS at the Wembley tri-launch on 6th October. What I didn’t realise was that Alun had actually bought along Lancashire Police (the customer) to this event to talk about exactly this situation in the closing keynote session at the same event..

Ward Ralston with Colin Fizsimons & Michael Wood from Lancashire Police

If you couldn’t get there on the day, the videos and decks from that event can be found from these links..

There is also a case study that has been published about the work that Risual has done for Lancashire Constabulary.

Footnote: Alun is the AL bit of Risual, the others being RIchard Proud & SUe Denton ;they are a small but influential system integration partner.

Technorati Tags: Windows Server 2008 R2,VDI,Remote desktop Services,Terminal Services,Risual

IIS 7.5 and Windows Server Core

Andrew_Fryer — Wed, 04 Nov 2009 10:29:23 GMT

There is so much stuff on Windows7 & 2008 R2 features out there, but not so much on IIS 7.5. The other reason it slipped off my radar was that reporting services in SQL Server 2008 directly uses http.sys for its portal and there is no longer a need to install IIS at all. Anyway it’s now time for me to look at this in more detail as part of my series on server core.

Firstly IIS 7.5 like every other new bit of the Microsoft platform has embedded PowerShell support, meaning you can now directly manage IIS 7.5. Also you turn on IIS7.5 features using DISM rather than the complexity of the setup in the original server core..

where a basic install would have been

start /w pkgmgr /iu:IIS-WebServerRole;WAS-WindowsActivationService;WAS-ProcessModel

using DISM the equivalent command is

DISM –online /enable-feature /featurename:IIS-WebServerRole

From there you can then add the extra individual features you need, as from IIS 7 it is a very modular solution. For example to turn on asp.net you would use to turn on three features..

DISM –online /enable-feature /featurename:IIS-ISAPI Filter

DISM –online /enable-feature /featurename:IIS-NetFxExtensibility

DISM –online /enable-feature /featurename:IIS-ASPNET

Having done all of this the best approach would then be to remotely manage it from another machine. Form there you can start use some of the new stuff in IIS 7.5

My top feature would be the best practices analyser. This is primarily concerned with making sure your configuration is as secure as possible, e.g.

Hiding custom errors from displaying remotely, the more the attacker knows about your site the quickest and easier it is to attack, for example one of mates always rubs his hands with glee when he says the site is talking to Oracle as opposed to SQL (he wouldn’t tell me why though).
Ensuring ssl certificates are up to date
Ensuring you are using ssl if you have basic authentication set.

For more on this refer to this article in the Windows Server TechCenter

Other new stuff includes

Application pre-load, so your application is already running when the user access it. To do this you need to add the following to the applicationHost.config file:

<application path="/myapp" preloadEnabled="true">
<virtualDirectory path="/" physicalPath="c:\inetpub\myapp"/>
</application>

and this to the web.config file

<preload>
<add value="default.aspx"/>
</preload>

There’s an article here to get you started with this

Configuration Log Tracking. This pulls in all the changes you make and logs them. BTW this is off by default and you need to turn it by going to “Application and Service Logs->Microsoft->Windows->IIS-Configuration” in event viewer and enable tracing. BTW IIS generates 4 kinds of events. These are administrative, operational, analytic and debug. Right click on areas in event viewer and select “enable log” for categories you want to enable tracing.
The ability to work with multiple versions of the .net framework. While one application might need .net 2 another might need 3.5
Built in FTP and WebDAV support.

I could go on and on here, but I would suggest you might want to check up on all the new stuff in IIS 7.5 here, and hopefully try it out by installing the role on a full or server core installation of windows server 2088 R2. It’s also an opportunity to confuse the web developers you might be working with by understanding their world a little better, and offering to help them deploy their applications more quickly.

Technorati Tags: IIS 7.5,Windows Server 2008 R2,server core,PowerShell

Server Core in Windows Server 2008 R2 – part 3

Andrew_Fryer — Tue, 03 Nov 2009 08:05:00 GMT

If I have convinced you to look at server core there some things you need to know to get started. There’s a simple step by step guide on the initial release of server core here, most of which still applies to server core in R2.

First and foremost as part of Microsoft’s 3D security philosophy Windows Server 2008 R2 (and therefore core) is secure by default i.e. locked down so you can’t remotely manage it initially.

netsh advfirewall set currentprofile settings remotemanagement enable

Once you have done this you can then remotely manage the firewall to allow other traffic in, so you can then remotely manage the server. The step by step guide has all this detail and command lines for joining the a domain setting up dhcp and so on.

Enable Powershell. The reason you want to do this is called James O’Neill. He’s taken the pain out of configuring server core with his configurator on codeplex. But before you can use this you need to turn on PowerShell using the DISM (Deployment Image Servicing and Management) tool :

dism /online /enable-feature /featurename:MicrosoftWindowsPowerShell

You’ll also need to allow PowerShell to run your scripts (more secure by default stuff)

set execution-policy remotesigned

James’ stuff will now allow you to do pretty much anything in that step by step guide but more easily. You might also want to install the .Net framework..

dism /online /enable-feature /featurename:NetFx3-ServerCore

If 32-bit support is needed:

dism /online /enable-feature /featurename:ServerCore-WOW64

and then

dism /online /enable-feature /featurename:NetFx3-ServerCore-WOW64

Note DISM is CASE SenSitiVE

You’ll need to do all this if you want to install SQL Server 2008 or R2 and then do a command line install along the lines of ..

Setup.exe /q /ACTION=Install /FEATURES=SQL /INSTANCENAME=MSSQLSERVER

/SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>"

/SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="NT AUTHORITY\Network Service"

The details of this are on Books OnLine here, but as I have said before this is NOT supported.

Next post is how to setup and manage IIS 7.5 on Server Core.

Technorati Tags: SQL Server 2008,DISM,Deployment Image Servicing and Management,Powershell,codeplex

Server Core in Windows Server 2008 R2 – part 2

Andrew_Fryer — Thu, 29 Oct 2009 17:21:00 GMT

Server Core is lean and mean but what can it be used for aside from sitting behind your infrastructure? The biggest new thing is IIS, specifically server core can now support asp.net websites. I think this is really useful as web servers need to be secure and increasingly highly available.

Server core and IIS 7.5 (the version that ships with Windows Server 2008 R2) do this by having a very small surface area, which needs to be patched left often, and provides less to attack. I did notice that there isn’t too much out there on what’s new in IIS7.5 but I will leave that for another post.

However I do have a video showing IIS 7.5 running on core ..

BTW the SQL Server 2008 database engine will also run on server core as does the current beta (CTP2) of R2, but this is not going to be supported, which I think is a great shame as it would get the same benefits and possibly run even faster.

Technorati Tags: IIS,SQL Server,Server core,windows server 2008 r2

Think U Know

Andrew_Fryer — Wed, 28 Oct 2009 18:19:46 GMT

Reading today’s Sun, you might well be worried if your kids are on FaceBook, but taking their laptop away is probably not going to be the correct response, as they’ll simply find another way to get online which is less under your control than what they are doing now e.g. using their phone, a mates laptop or an internet cafe.

I can say this with some confidence despite having no children of my own, as I have just spent the day learning how to be a volunteer trainer for the Child Exploitation and Online Protection (CEOP) Centre.

If you can spare a minute at this point watch this CEOP authorised video..

Although CEOP is a government body (affiliated to the Serious Organised Crime Agency – SOCA) it also has an industry partnership involving Visa, O2 and Microsoft among others. We get to help in a number of ways including training as many children as possible to be able to be safe on line.

This is all about making sure children can have fun using the internet, have control of their online personas, and know what to do to report any abuse such as clicking on this logo..

e.g. Windows Live Messenger.

and Facebook, and many other sites.

There is a public web site where you can find out more, ThinkUKnow which you can point your kids to and learn a little more yourself.

The other thing you can do is ensure your kids get this training at school by asking the school to lay this on. They will have a CEOPS nominated teacher who can arrange this, and then a volunteer like me comes in to do this. BTW we all have to go through the whole criminal record check process just as anyone does who works with children.

Separately Microsoft also has volunteers to run similar sessions for parents which also encompasses the issues around cyber-bullying and if that’s of interest to you please get back to me.

To wrap up the internet is a force for good, it should be fun for you and your children, which IMO means that what you do on line should be the same as what you do off line.

Technorati Tags: CEOP,child exploitation,child abuse,internet safety,thinkuknow

Windows 7 Press

Andrew_Fryer — Mon, 26 Oct 2009 12:20:07 GMT

I love reading real books and I love radio 4, but I don’t do newspapers or television, this is isn’t baby boomer bias, I am big into social media or you wouldn’t be reading this.

If you saw windows on the BBC last week the journalist couldn’t wait to show you something else and the Metro and Telegraph advised you to wait.

So who’s right? well If the BBC is correct and so is the press then it’s all your fault, and let me explain why. You know all those annoying send feedback and comments messages you see from Microsoft well it goes somewhere, so those people you see in the TV adverts on Windows 7 those were just a few of the people whose suggestions got built into Windows 7.

Also the amazing buzz around windows 7 isn’t some massive bot program, or Microsoft paying people to queue up and buy windows 7, it was you. For example the twitter sphere and even the Register were massively of the “installed it , “liked it” etc. sort of thing and the odd reactionary was usually flamed instantaneously by his peers.

So who’s right you, or the press and TV? – only time and having used Windows 7 for a year now I would say it would be you.

Finally if you have been using the Windows 7 beta (RC) , I would say there are a couple of issues with it , for example I noticed that HomeGroups wasn’t completely implemented, and so you should move away from it to the proper (RTM) version.

Technorati Tags: BBC,Daily Telegraph,Metro,Windows 7

Windows 7 – it’s here

Andrew_Fryer — Thu, 22 Oct 2009 09:37:30 GMT

Windows 7 is released today, but I feel a bit detached from the buzz in the social cloud about this, having played with for it nearly a year.

However a couple of things happened recently that made me remember what life was like before windows 7. I was in a well known burger bar and my windows 7 laptop saw the free wi-fi and advised me that I would need to enter additional information and gave me an option to go to that page straight from the wifi icon on the taskbar. So I am on-line, unlike the charming Norwegian girl sitting next to me struggling away in XP.

I naturally offered to help and finally got round the problem by using the native connectivity rather than the tool that came with her laptop, but it was a struggle (even allowing for the Norwegian dialog boxes).

My second epiphany came when I need to print an airline ticket to go to Edinburgh earlier in the week. My home printer is attached to my gaming rig which is also running windows 7 and has a HomeGroup setup on it. HomeGroups in windows 7 allow simple sharing of resources based on one password. The clever bit is that my domain joined office laptop can see the HomeGroup resources but my home computers can’t see the office laptop.

BTW If you want to see this in action I have made a 4 minute video of it..

as part of my YinYang series on Windows 7 and Windows Server 2008 R2.

What I also like about this little feature is that not many of my colleagues know about it which usually leads to a “yeah that’s good, but have you seen this..” conversation and so we all learn from each other.

I could go on all day about this stuff, but my advice to you is to have a play with windows 7 from your TechNet subscription, if you haven’t already. There is also 90 day time-bombed copy here if you don’t have a TechNet subscription, but read the “read me” before you use it and I would recommend using a virtual machine if you’re using that.

Technorati Tags: Windows 7,HomeGroup,wifi,TechNet

Virtualisation for the DBA part 4 – Licensing and Support

Andrew_Fryer — Wed, 21 Oct 2009 07:02:00 GMT

To wrap up this mini-series on virtualisation, I wanted to clarify the support and licensing stuff you need to know if you want to Virtualise.

The support is really simple, Microsoft support virtual machines just as though they are real environments. The interesting bit is that this isn’t specific to Hyper-V, it also applies to various versions of Vmware ESX and vSphere, Citrix Xen plus various other products listed in the Server Virtualisation Validation Program (SVVP).

One thing to bear in mind here is that SQL Server 2000 and other products are in extended support or not supported now and the virtualisation doesn’t change that, so don’t expect too much help if you’re planning to run SQL Server 1.1. on OS/2 (although it works!).

The licensing is also remarkably straightforward. If you are using any edition except standard edition then you license the virtual machine as though it were a physical machine where virtual processors count as processors. However if you you enterprise edition then you simply license the physical machine and then you are good to run as many virtual machines or instances on it as you wish each with a copy of SQL Server enterprise on it. Two things to note:

This applies to SQL server 2005 sp2 and later
It doesn’t matter what virtualisation technology you’re using

To get the definitive word on licensing in a virtual world form whihc you can be quoted on go here.

Hopefully you know understand the pressure being put on you to virtualise and have the resources to make this as painless as possible or push back if it isn’t going to work for certain workloads under your control. If not you have my contact details!

Technorati Tags: SQL Server,virtualisation,Xen,Vmware,vSphere,licensing,support

Virtualisation for the DBA part 3 – SQL Server Performance

Andrew_Fryer — Tue, 20 Oct 2009 07:27:00 GMT

As I have said already in this series the major concern most DBA’s have when virtualising SQL Server is performance. I think this is actually a bit of myth for a number reasons:

Virtualisation just keeps getting better. This is down to a partnership between the hardware and software industries. We have had a couple of generations of CPU from AMD & intel with specific support for virtualisation and Microsoft’s Hyper-v virtualisation offering will only work on hardware with these CPU’s in.
Most Servers aren’t that busy. Typically an average server in a data centre is only under 10-20% load most of the time, even if it is running SQL Server.
Performance is not properly measured. In order for you to understand how much performance you are loosing in a virtual world you need to measure how well the system is performing now. In older versions of SQL Server this either means you have invested money to buy 3rd party tools to do this or you have invested your time to write your own.

There are several things in SQL Server 2008 to help with these issues:

Data Collection aka Performance Warehouse, allow you to quickly setup a pre-defined data warehouse on a designated SQL Server instance and then collect telemetry from other servers/instances at periodic intervals to see how they are performing via a suite of custom reports ..

The clever thing about these reports is that they are all linked allowing you to drill down to a particular query that might be running slowly or blocking other queries. The really clever thing is that it only take about a dozen mouse click to set all this up and leave it running. The only potential downside is that this only works and monitors on SQL Server 2008 instances.

Data Compression. Given that the first thing that slows down a database is IO, compression can be useful in getting more data of the disk per read for a little bit of CPU overhead. There are 2 parts to this one compresses the fixed width fields e.g. decimal, and the date and time data types and stores them so that they only take up the space used i.e. they behave like var(char) as opposed to char. You can then elect to further compress each page in a table filegroup etc. and this works by identifying recurring values and storing that in the header of the page and replacing it with a token for each value in the column. One thing to note is that is an enterprise edition feature.

As I have mentioned before how you use resources when virtualisation is also important, and the most important of these is disk usage…

Dynamic disks should only be used for dev work and for testing where performance is not being measured. Fixed disks perform nearly as well as pass-through disks (i.e. where the database itself is kept on a physical disk or LUN in a SAN).

Although it might seem that Microsoft has vast resources at its disposal it is not immune to the current recession and nor did it get where it is today by wasting money, and so it has an ongoing project to virtualise SQL Server as part of its Green IT strategy (details here).

So hopefully we can discount performance as a reason not to virtualise unless your demands exceed the capability of your virtual platform to support what you need. In the case of Hyper-V this would be 4 virtual processors (think of that as a four core cpu) and 64 Gb of memory (in Windows Server 2008 R2).

For further reading on SQL Server performance on Hyper-V see this whitepaper from the SQL Customer Advisory Team (SQLCAT).

Technorati Tags: SQL Server 2008,Performance,virtualisation,Hyper-V

Virtualisation for the DBA part 1 - Are you Bovvered?

Andrew_Fryer — Fri, 16 Oct 2009 12:19:54 GMT

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:

Try and co-locate databases on one installation of SQL Server.
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

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