Installing SQL Server in a Virtual World

  • Article

I do get occasional feedback that SQL Server is hard to install, all those screens all those checkboxes etc. etc.  This is simply a reflection that there is so much in SQL Server aside form the database engine, however if you know what you want and you are doing this regularly then script it, if you have less experienced staff you want to delegate the task to then script it and if you want to reduce patching then er … script it.

On my recent VMware course it was obvious that the rest of the delegates while generally keen on scripting didn’t do this when deploying SQL Server on VMware so here’s my advice (which also works on Hyper-V BTW)

What you can’t do in a virtual world is simply copy/clone a SQL Server virtual machine because you’ll end up with two VMs with the same Active Directory SID, and SQL server doesn’t like to the server name to change once it’s installed. So this is how I do this as I often need to build a quick SQL VM

  • Setup a VM with your guest OS of choice e.g. Windows Server 2012 Datacenter edition.
  • Install the prerequisites for SQ Server for example the .Net Framework 3.5. sp1
  • Use Image Prepare to partially install SQL Server
  • SysPrep the machine (windowssystem32sysprepsysprep.exe) to anonymise it
  • Create an unattend.xml to be consumed when the vm comes out of SysPrep and save this to the SysPrep folder above. Typically this answer fie will join your new VM to your domain, setup the local admin account, input locale, date/time etc. and  this TechNet library article will walk you through that.

Note: passwords in the answer file are stored in clear so plan around that.

  • Save this off as a template.  That typically means saving the VHD on a share for later use

 

To use the template

  • Create a new VM using a PowerShell / PowerCLI script. For example here’s the sort of thing we used earlier in the year at our camps to create a server on Hyper-V..

New-VM -Name $VMName -NoVHD -MemoryStartupBytes 1Gb -bootdevice IDE -SwitchName $VMSwitch -Path $VMLocation
Set-VM -Name $VMName -ProcessorCount 2 -DynamicMemory
Add-VMHardDiskDrive -VMName $VMName -Path $VHDPath

start-VM $VMName

which creates a simple VM with 2 processors 1gb dynamic memory based on a given VHS , $VHD Path 

  • Rename the machine. We have a clunky script Simon and I use in our camps to do this..

#find the ip address of the new V and look it up in DNS

$vmip = Get-VMNetworkAdapter $VMToRename |where switchname -eq "CorpNet" | `
select -expandproperty "IPAddresses" | where {$_ -match "^(?:[0-9]{1,3}.){3}[0-9]{1,3}$"}

$vmGuestName = [system.net.dns]::GetHostEntry($vmip)
$vmGuestName = $vmGuestName.HostName

#now execute a remote powershell command to rename it

Invoke-Command -ComputerName $vmHostName -ScriptBlock {
rename-Computer -NewName $args[0] -DomainCredential contosoadministrator } -ArgumentList $NewVMName

Restart-Computer –ComputerName $vmGuestName –Wait –For Powershell

Hopefully you’ll write something better for production

Doing this from the installer UI and server manager is tedious and prone to mistakes, but there is another reason to do this all from the command line, and that is because you should be installing SQL Server onto an installation of Windows Server that has little or no UI. It’s called called Server Core and is the default method for installing Windows Server 2012.  It cuts patching in half, and there’s no browser to secure, because it’s designed to be managed remotely. New in Windows Server 2012 is the ability to turn the user interface on and off (where in 2008R2 this was an install choice) and there’s a new halfway house installation called MinShell and my post on it here.

Any VMware expert is going to read this and laugh because VCentre has a built in template capability so you don’t have to do all the PowerShell hand cranking to clone a sysprepped  VM and then domain join it.  Any Hyper-V expert shouldn’t be doing this either as System Centre is how this is done in production as you can create, not just templates of individual VMs, but architect services and setup self service so users can ask for templated VMs via your service desk or directly from a portal.  However my point here is that under the covers this is the sort of thing you’ll need to do to run lots of SQL Server at scale for tier 1 applications where downtime is critical. Having said that this sort of thing might be useful for labs and for setting up evaluations of SQL Server 2014 running on Windows Server 2012R2.