Following on from previous posts on a similar theme, I wanted to make a quick note of how SQL 2008 R2 is installed on a 2008 R2 Server Core VM.
SQL 2008 R2 has a number of feature dependencies that must be present before an installation can take place. Assuming that you’ve deployed your VM, given it some network settings and joined it to your domain (if you want) then there are three further things you need to address to get SQL installed.
The first is adding some disk space to the VM. If, like me, you deploy Windows server VMs with a reasonable 40Gb of C: drive space you’ll need to add something to have enough room for SQL and some databases. I like to keep databases and log files on separate drives to the main OS so I tend to add extra disks to the VM. I’ve covered that off in a previous post.
Next up – those pesky dependencies. You could do this remotely with Server Manager running on another machine somewhere or you could just use the command line. Hint: if you’ve got the commands written down somewhere then the command line is so much quicker. Just RDP to your 2008 R2 Server Core VM and paste the following into the command prompt:
[text]dism /online /enable-feature /featurename:NetFx2-ServerCore
dism /online /enable-feature /featurename:NetFx3-ServerCore
dism /online /enable-feature /featurename:NetFx2-ServerCore-WOW64
dism /online /enable-feature /featurename:NetFx3-ServerCore-WOW64
dism /online /enable-feature /featurename:IIS-WebServerRole
dism /online /enable-feature /featurename:IIS-ISAPIFilter
dism /online /enable-feature /featurename:IIS-ISAPIExtensions
dism /online /enable-feature /featurename:IIS-NetFxExtensibility
dism /online /enable-feature /featurename:IIS-ASPNET[/text]
The final stage is installing SQL itself. Simply mount the DVD ISO and run setup! Note though that I think SQL 2008 may not be supported on Server 2008 Core versions. SQL 2012 seems to be but I haven’t played with that yet.
Some people still aren’t convinced by virtualisation and while it’s true that there are some situations that it’s not especially suited for they are relatively few in my experience. I know a few people who are yet to be convinced completely. One’s a SQL DBA and there are times when she has a point. I thought that this might be one of them until I started poking around.
Initially I was asked about what was causing a SQL VM to respond slowly and use 100% CPU. I had a look in vCenter and while it looked slightly busy it didn’t seem over worked. As the graph to the right shows, it was using only about half of the available 3Ghz CPU it had access to. Perhaps I should explain further at this point that my client’s practice when it comes to VMs is to provision them with a single vCPU and add more if they are required. It seems that if this was normal load for the VM that 1 vCPU should be enough.
Looking back through the VM’s performance history I could see nothing particularly wrong either. Occasional CPU spikes in the past possibly indicating reboots or overnight processing. Oddly though there were random plateaus of activity for several hours at a time. Mostly overnight the VM would idle along using practically no CPU resource but during the day there were long periods where it looked a lot like the activity above. Time to look at the guest OS.
The picture inside Windows is slightly different. Opening up task manager shows frequent bursts of 100% CPU usage (see below). Actually you could call them regular. And, more worringly, it transpires that the server is not yet in production – it’s still being configured.
The offending process is services.exe so it’s not immediately obvious what the issue is. Purely by coincidence I asked the DBA if she could log off for a while so that I could look into what was going on. When she did, the strangest thing happenned:
See how the CPU usage dropped back down to idle and stayed there. That begged the question “What were you running?”.
It turns out that the culprit was none other than SQL Management Studio. When opened and connected it polls the server’s status every 10 seconds. Strangely though, instead of polling just the SQL services it polls all services on the server (this can be seen using Process Monitor) which seems a bit excessive to me. Due to the way that hypervisors share resources, what would be a small blip on a physical host is magnified within the VM somewhat. Microsoft have acknowledged that this happens but to my knowledge haven’t done much about it. There is a registry key that can be modified to adjust Management Studio’s behaviour. For SQL 2005 SP1 onwards (it’s not available before that) it is:
HKLMSoftwareMicrosoftMicrosoft SQL Server90ToolsShellPollingInterval
Setting it to 600 will reduce the frequency of polls to once a minute. Alternatively, just don’t leave SQL Management Studio open longer than you have to and wait for Microsoft to fix it.