Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

July, 2013

  • Installing SQL Server in a Virtual World

    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 (windows\system32\sysprep\sysprep.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 = []::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 contoso\administrator } -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.

  • Bi Product

    I have just read Paul Gregory’s guest post for the TechNet Flash, and the two things that caught my eye was to be bi-lingual and to keep your skills up to date.  I put these two themes together and came up with the title for this post which essentially means being skilled in more than one technology.  As we move into a world where some of the nuts and bolts are automated or outsources away from us then having a set of skills that can bridge technologies is going to be more valuable. In you own case I think of two ways this works:

    • I am pretty good on SQL Server especially BI, such that I could still have that on my cv with some confidence, however more recently I have been focusing on Windows Server and System Center and my SQL background has really helped with this. For example in my Evaluate This series I used a SQL Server workload to show Hyper-V Live Migrations, SQL Server running on a Storage Space and running SQL in Windows Server core.
    • I have a pretty good knowledge of VMware (I am a lowly VCP5) and  I have MCSE Private Cloud,  This means I understand enough to be able to speak VMware and articulate the world of Hyper-V to VMware experts. 

    This ability to cross technologies comes into play with integration if you know how to get product X to integrate with solution Y, and with migration I want to move from vendor 1 to vendor 2.  Those kinds of projects are always going on and have a number of advantages over other kinds of IT work:

    • You are respected as the expert and you can’t buy respect you can only earn it
    • The work is more challenging so having the skills isn’t enough you need to also (to Paul Gregory's post) relate to users and other technical teams. 
    • The day rates are higher, because the combination those two skills are rare.

    So while it’s quiet over the summer holidays (unless you are in education IT in which case you have my complete respect!) start having a look at some new stuff be it Windows Server 2012 R2, SQL Server 2014 or have another look at the Microsoft Virtual Academy (MVA)

  • Virtualizing Tier 1 SQL Server

    A while back I published a couple of posts on virtualizing SQL Server, and in the light of developments in both the virtualization platform out there and SQL Server itself I feel the need to do a complete rewrite. 

    The traditional approach to implementing high availability (HA) in SQL Server has been to create a cluster and for this to be a more resilient HA means three nodes or more are needed to maintain HA with two nodes while one node is offline for planned maintenance, for example to either patch the OS or the SQL Server node itself.   What does this mean for virtualization? If you are using Hyper-V  it doesn’t really matter; the VM’s comprising this cluster (aka a guest cluster) are kept on separate physical nodes on a (physical) cluster you can patch the hosts the guest OS, and SQL Server all using Cluster Aware Updating (CAU) in Windows Server 2012.  However it’s not quite so easy in VMware, you‘ll have use VMware Update Manager to patch the hosts and then use CAU to patch the guest OS and SQL Server. Moreover as far as I know you can only have a two node guest cluster in VSphere so while you are patching SQL Server you are down to one node.   So what if you have to use VMware and you want more in the way of HA like you have on Hyper-V? 

    One option would be to use Availability Groups in SQL Server 2012 Enterprise edition. This combines the best of mirroring/ log shipping with Clustering:

    • There’s no shared storage so I don’t see why you would be limited to a three node guest Windows Cluster.
    • Failover is very quick as there’s no shared storage each node has it’s own copy of the database being protected
    • Unlike mirroring and log shipping you are protecting a group of databases as though they were one and you can use the secondaries for reporting and as a source for backups (only full backups though). Plus you can have multiple secondaries for example a synchronous secondary in your local data centre with an asynchronous copy at another location , so a bit like replication in Hyper-V & VMware but at the database rather than VM level.  That’s an important point you should use this techniques over VM replication as all you are synching is the actual SQL Server

    Your next consideration is going to be making sure you get a predictable level of performance or your users might be phoning if there’s issues with speed as well.  Tuning in a physical world has occupied many a mind and there’s tons of advice out there from MVPs, TechNet etc.   Things get tricky in a virtual world as resources are shared.  However if you are running a tier 1 on database then best practice would be:

    • CPU Don’t over commit and use all the NUMA capabilities in your hypervisor to pass through maximum performance to the database.  Bear in mind that for HA you might well want this capacity reserved on other nodes.
    • RAM can’t be over committed in Hyper-V, but shouldn’t be over committed on VMware as performance suffers.
    • IO use the latest SR-IOV cards which can recieve and mange virtual network traffic straight fro the VMs if you can.  However you can’t team SR-IOV cards so you might want to pass through multiple SR-IOV NICs to a VMand then team inside the VM (while you can do this in Hyper-V but I am not sure if you can do that on VMware where the guest OS is Windows Server 2012).  If not use NIC teaming at the host level and the appropriate  teaming policies for access to the database (VMware advice on this is under networking policies here).
    • Storage Access questions usually revolve around whether to use RDMA/pass through disks so that the database itself is stored directly on a LUN referenced by the VMs. There’s actually very little difference these days and in both platforms you could use a share if you have a Windows Server 2012 fileserver running storage spaces. 

    The definitive white paper for virtualizing SQL Server 2012 is here. However the latest version of a best practices guide for running SQL Server on VMware I could find is here but it’s three years old and so applies to older versions of SQL Server (typically 2005/2008) and Windows Server 2008.  Hopefully this will change as Windows Server 2012  & SQL Server 2012 are now supported and of course there’s going to be even more new stuff with SQL Server 2014 running on Windows Server 2012 R2. Whatever you decide to do you’ll want your HA design to be supported and the definitive word on that check KB956893.

    Finally if you are a DBA reading this, one way to get to know your data centre admins is to help them with their SQL Server, as whether they are using System Center or VSphere it’s likely that the database underpinning these is SQL Server and it could probably do with a but of TLC, and a general discussion about protecting those databases too as they are vital components of your data centre.


    • My definition of tier 1 isn’t necessarily big, it’s more about the impact of a tier 1 service not being there. If it isn’t there you can’t operate trade, function etc.  Of course systems like this tend to be heavily used and predictable performance is also important too.
    • I haven’t mentioned VMware fault tolerance here because it has so many limitations that render it impractical for all but the smallest databases and I generally find that if it’s tier 1 it’s generally very big and used by lots of people and so only having one CPU doesn’t really work.
  • Virtualisation and Interoperability

    I often think that Microsoft is a bit like the English language, a lot of people speak it, a lot of people don’t and for many people it’s a second or third language they need for work e.g. air traffic controllers.  In technology few of us run a totally Microsoft environment, a few more will have nothing to do with Microsoft for almost religious reasons but the majority have some of this and some of that and are working hard every day to get everything to work.

    So I am pleased that there are three really important announcements to make support issues for the majority of us a little easier:

    • The Server Virtualisation Validation Platform (SVVP) has been updated and Microsoft in conjunction with VMware will now support Windows Server 2012 running on VSphere 5.0 update 1 and 5.1.  That means you can phone Microsoft support when a VM running Windows Server 2012 doesn’t work properly on either of those versions of VSphere.  For example you might want to see how reverting to snapshots of virtual your domain controllers  works properly when the domain controller is running at the Server 2012 domain functional level and you are running that on top of VSphere 5.0 update 2 + ESXi 5.0 update 2  or later, both of which exchange information via a Virtual machine GenID
    • A couple of weeks ago at TechEd it was announced Oracle will be supported to run either on Hyper-V or on Azure, that means the database, WebLogic servers, Oracle Linux and support for Java.
    • Open Management Infrastructure (OMI). A couple of months ago I interviewed the lead architect for Windows Server, Jeffrey Snover (the man behind PowerShell) as part of TechDays Online and he was talking about the work we are doing on OMI,  a standards based framework to enable cross platform management of all devices in the same way as WMI is used to manage Windows.  While tools like System Center already do a pretty good job at managing multiple platforms, from switches to phones to hypervisors and application software, OMI will make this possible without agents and enable management tools to work to or from the open source world.

    Combining these three development means you can get proper support, and effectively manage the  heterogeneous hell that can arise in your data centre, as a result of acquisitions, migrations or because your policy is to have best of breed solutions for your business needs.   What this means  for us IT professionals is that those of us who have multiple disciplines will be in more demand and so being skilled in say Hyper-V and VMware or Oracle and SQL Server will only be good for you.

  • Server 4024 part 3 - Networking

    Databases are typically bound either by networking or IO and actually this can be two sides of the same coin if you are using remote storage.  So what’s new in Windows Server to improve networking both to improve access to shared storage and access to database workloads from another tier in a service or directly to your users and their applications?

    So what is there in networking in Windows Server 2012 to help?

    The answer is a lot and the most tangible thing is NIC teaming which is now built into the operating system this can be used both for load balancing and to provide failover, and can either use LACP (Link aggregation control protocol was 802.3ad & 802.1ax) on switches that support that or use switch independent mode.  This has several advantages over the drivers that come with NICs to provide NIC teaming:

    • It’s easy to set up and use either form server manager or PowerShell (My post on using it is here
    • You can team NICs from disparate providers  
    • You can create NIC teams inside a VM if the VM has more than one NIC.   but whatever your hypervisor you might want to do this if you have several of the new SR-IOV NICs in your hosts to provide failover as this newer NIC can’t be teamed as the network virtualisation is done on the card itself.

    Note: In Hyper-V there is a per VM setting to declare which NICs will comprise a team in the VM and although I haven’t tried this on VMware but it should be fine.

    If you are using SQL Server inside a VM then support for those SR-IOV NICs will improve performance, but what’s more important in my opinion is the ability to regulate network bandwidth like you can regulate CPU and Memory in SQL server with Resource Governor. Network Quality of Service (QoS) can be set on a per VM basis through Hyper-V, System Center Virtual Machine Manager or PowerShell

    Then there are numerous improvements designed to improve  low latency performance to allow better remote storage access via SMB and better data centre bridging support.

    Finally managing networks gets a lot easier with a comprehensive IP Address Management (IPAM) role which uses a SQL server database to manage and monitor all your subnets, DHCP scopes and IP address usage. You also get DHCP guard and Router guard option in the Hyper-V virtual switch to stop conflict occurring between applications that might actually have the same ip addresses etc. such as in a multi tenancy environment.


    So that’s a quick look at SQL Server 2012 running on Windows Server 2012, but while I have been away Windows Server 2012 R2 has been announced as has SQL Server 2014. There are public betas you can download but as ever a word of caution on those – You can’t upgrade from the betas to the final products so take snapshots and backups if you want to look at those now.