Robert's SQL Blog

My thoughts on SQL Server, PowerShell and Microsoft products in general

August, 2012

  • From 0 to SQL Demo lab in a few easy steps – Part 2

    This post is part of a series of posts about buidling a lab environment using some of the new PowerShell cmdlets and features in Windows 8.

    In the previous post I explained how to create a virtual disk, apply a standard Windows Server 2008 image to it and attach the disk to a new VM. If you followed my steps you should have noticed that it was not a fully automated experience. During the installation I was asked to provide the regional settings, set or skip a license key, accept the EULA and provide an administrator password. And when I finally was logged in I still had to do a lot of configuration like setting an ip-address and renaming the machine. In this post we will get these steps out of the way as well.

    The first step is to do some primary settings. In my case I enable PowerShell and Remote Desktop. This is also a good time to download updates and .NET frameworks you might need later. Try to keep things as basic as possible. Only set the ip-address if that's needed. We will change it later on.

    Once the machine is to your liking it's time to sysprep it. Sysprep is located in the sysprep folder inside the system32 folder. Sysprep removes all personal information from the machines and makes it possible to create an image for redeployment. Start sysprep and choose OOBE Experience, Shutdown and make sure the box at Generalize is checked or better run it from the commandline: sysprep /generalize /oobe /shutdown. Make sure you specify the generalize option. This will create a new machine identifier for every machine. If you miss that one you could get into weird problems.

    After sysprep is done it shuts down the machine. Now it's time to create an image that we can use as a base for deployement. You only have to do this once for every base image you want to create. I'm using a Windows 2008 and Windows 2008 Core template. Using DISM you can create an image (WIM) file. First you need to mount the image using Mount-VHD <pathtovhd>. Now capture the image with: DISM.exe /Capture-Image /ImageFile:D:\vm\base.wim /CaptureDir:G:\ /name:Base-Image. Replace the paths and name with your own. After some time a file is created. Make sure to dismount the vhd using Dismount-VHD. This concludes step 1 of this task. Next up is to automate the installation. There is an easy way and a hard way. The easy way is to use the Windows Automation Installation Kit. This tool has an editor to create unattended files. Another option is to create one with notepad. Of course you can also copy from others. Here is an example of the unattend.xml I'm using to create a domain controller. Read through it, I will explain the values in a moment.

    <?xml version="1.0" encoding="utf-8"?>
    <unattend xmlns="urn:schemas-microsoft-com:unattend">
    <settings pass="specialize">
    <component name="Microsoft-Windows-Shell-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">\
    <ComputerName>DC-01</ComputerName>
    <TimeZone>UTC</TimeZone>
    <RegisteredOrganization>Contoso Corp.</RegisteredOrganization>
    <RegisteredOwner>Contoso Corp.</RegisteredOwner>
    <ProductKey>7P8GH-FV2FF-8FDCR-YK49D-D7P97</ProductKey>
    </component>
    <component name="Microsoft-Windows-DNS-Client" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="NonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Interfaces>
    <Interface wcm:action="add">
    <DNSServerSearchOrder>
    <IpAddress wcm:action="add" wcm:keyValue="1">127.0.0.1</IpAddress>
    </DNSServerSearchOrder>
    <Identifier>Local Area Connection</Identifier>
    </Interface>
    </Interfaces>
    </component>
    <component name="Microsoft-Windows-TCPIP" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Interfaces>
    <Interface wcm:action="add">
    <Ipv4Settings>
    <DhcpEnabled>false</DhcpEnabled>
    <Metric>10</Metric>
    <RouterDiscoveryEnabled>false</RouterDiscoveryEnabled>
    </Ipv4Settings>
    <Identifier>Local Area Connection</Identifier>
    <UnicastIpAddresses>
    <IpAddress wcm:action="add" wcm:keyValue="1">192.168.200.1/24</IpAddress>
    </UnicastIpAddresses>
    <Routes>
    <Route wcm:action="add">
    <Identifier>1</Identifier>
    <Metric>10</Metric>
    <NextHopAddress>192.168.200.1</NextHopAddress>
    <Prefix>0.0.0.0/0</Prefix>
    </Route>
    </Routes>
    </Interface>
    </Interfaces>
    </component>
    </settings>
    <settings pass="oobeSystem">
    <component name="Microsoft-Windows-International-Core" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <InputLocale>132105</InputLocale>
    <SystemLocale>en-US</SystemLocale>
    <UILanguage>en-US</UILanguage>
    <UserLocale>en-US</UserLocale>
    </component>
    <component name="Microsoft-Windows-Shell-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <OOBE>
    <HideEULAPage>true</HideEULAPage>
    </OOBE>
    <UserAccounts>
    <AdministratorPassword>
    <Value>p@ssw0rd</Value>
    <PlainText>true</PlainText>
    </AdministratorPassword>
    </UserAccounts>
    </component>
    </settings>
    <settings pass="windowsPE">
    <component name="Microsoft-Windows-Setup" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <UserData>
    <AcceptEula>true</AcceptEula>
    </UserData>
    </component>
    </settings>
    </unattend>

    There are several sections in the xml. One section is the pass phase. This tells the setup routing at what point to use the settings. Some settings are only valid in one phase while other can be in multiple. The Windows Automation Kit knows and will only choose the right ones.

    In the first part of the file the computer name is set along with the time zone and other details. The product key is not a real key but a temporary one. It's actually the same key that's entered when you click skip during the setup process. You can find this key inside a file in the Windows installation dvd. So, it's not a real key, just one that will get the setup process automated.

    In the second part the network is set. In this example the ip 192.168.200.1 is used with netmask 255.255.255.0. The DNS is set to 127.0.0.1 because this machine will take the role of domain controller with DNS installed. The gateway is set to the 192.168.200.1 as well.

    The last part is where the locale settings are set. The values can be retrieved from this table. Also the EULA is accepted and the administrator password is set. If you use the Windows Automation Kit the password will be encrypted. As a best practice you should not include the password in plain text.

    So far for preparation, now it's show time. The trick is to get the unattend.xml we created located at the rootdrive of the machine. Using PowerShell and the commands from the previous post this task is easy. As you might recall I mounted the disk to apply the image and set the bootrecords. Run through the steps from the first post up until the point where the drive is dismounted. With the disk still mounted you can copy whatever file you want to the drive, including an unattend. If you add this PowerShell one-liner before you dismount the disk the file will be copied: Copy-Item source destination –force. This will copy the file from A to B. Now dismount the disk and you are done.

    Once the autounattend is copied you can start the VM. After a few minutes and with no user interaction you should be able to log into the machine.

    This brings us one step closer to a complete unattended installation of a lab environment. In the next post I will explain how to perform post install actions like installing and configuring AD and setting up SQL Server.

  • From 0 to SQL Demo lab in a few easy steps – Part 1

    As a Premier Field Engineer I get into a different challenge every week. These challenges can be anything; performance issues, architecture review or migration scenarios. If I'm at a customer site it is not always possible (or recommended) to test my solution on the production systems. And if it's a possible bug I want to reproduce it so it can be investigated. For this you will need a lab environment. But building a lab environment can be a time consuming task. Most of the times I need different SQL versions, clustering, AlwaysOn or another Microsoft product relying on SQL server. And when I'm ready to present and prove my solution I don't want any data from other customers to be visible. For these reasons I always build new and fresh environments. If it's a performance issue I can get away with just a single VM but if it is a more complex scenario I might have to install a domain controller and a series of other machines as well.

    I used to run Windows Server 2008 R2 on my laptop as my primary OS. Basically because I needed Hyper-V. But with Windows 8 I can run Hyper-V on a client OS and as a bonus I get a lot more PowerShell cmdlets to automate recurring tasks. Do you see where I'm going? There is some great software available to automate the deployment of VM's. You can use Windows Deployment Services or System Center Virtual Machine Manager. But I wanted an out-of-the-box solution that didn't require any additional tooling other than Windows 8 with Hyper-V enabled. And there is.

    This post will be part of a series of posts because getting a complete AlwaysOn cluster running is a lot of preparation and understanding all the steps will make it easier to customize this to your purpose. Basically I will break it down in a few steps:

    • Part 1: Installing Windows on a virtual disk and boot it in Hyper-V
    • Part 2: Automating and customizing the installation using sysprep and unattend
    • Part 3: Running post deployment tasks like AD configuration or SQL Server installation
    • Part 4: Putting it together

    Installing Windows on a virtual disk and boot it in Hyper-V

    Before you can start to automate anything with PowerShell you will need the steps to do it. Imagine you would create a new VM? What steps do you need to take?

    1. Create the VM
    2. Insert the DVD
    3. Install Windows

    If we are going to do this in PowerShell you will need to reorder some of the items. In PowerShell we will create the disk first, install Windows on it and create the VM in the final step.

    Create a vhd(x) with PowerShell

    There are several ways to create a virtual disk. You can use DISKPART but that can be a bit of a drag and is harder to automate, or you can use PowerShell. In Windows 8 you can use some new and cool cmdlets to create a virtual disk. The next script will create a vhd and format it. I will explain them after the script.

    $vhdpath = "D:\vm\dc-01.vhdx"
    $vhd = New-VHD -Path $vhdpath -SizeBytes 30GB

    Mount-DiskImage -ImagePath $vhdpath
    $disk = Get-DiskImage -ImagePath $vhdpath | Get-Disk

    Initialize-Disk -Number $disk.Number -PartitionStyle MBR
    $volume = New-Partition -DiskNumber $disk.Number -UseMaximumSize -AssignDriveLetter -IsActive | Format-Volume -Confirm:$false
    $drive = [string]$volume.DriveLetter + ":\"

    We start with setting a variable to make things easier later on. The variable $vhdpath hold the path to our virtual disk. The first step is to create the disk using New-VHD with the specified size, in my case 30GB is enough. The disk will be dynamically by default but you can create a fixed or differencing disk as well. Refer to the documentation for more options: http://technet.microsoft.com/en-us/library/hh848503.

    The following step is to mount the image using Mount-DiskImage specifying the path. When the disk is mounted you can format it using PowerShell instead of DISKPART. Formatting is a three-step action; first initialize the disk, partition it and finally format it. This is not different from DISKPART. Once it's formatted we can get a handle to the drive which we will need later on.

    With a freshly formatted drive we are ready to install Windows. For those of you that are not familiar with deployment or images; we can deploy Windows directly to a disk. No need to boot into a DVD. When you install Windows an image file (*.wim) is applied to the destination. The proper boot records are written and you are off to go. Prior to Windows 8 you would need a tool called ImageX to capture and apply images to a destination. Being part of the Windows Automation Deployment Toolkit this was another download and dependency and does not fit in my 'only use what's default' policy. But with Windows 8 this is not a problem. The command line tool DISM is extended to capture and apply images as well. Being part of the operating system it can be used for my purpose. Check out the next piece of PowerShell.

    Install the DVD

    $image = "F:\sources\install.wim"
    $dismArgs = @("/Apply-Image", "/ImageFile:$image", "/index:4", "/ApplyDir:$drive")
    Start-Process -FilePath "DISM.exe" -ArgumentList $dismArgs -NoNewWindow -Wait -PassThru

    There is no DISM cmdlet so we will resort to invoking the process. The source is set to my DVD drive with the Windows Server 2008 R2 DVD loaded. The index is set to 4 because I'm installing the Enterprise Core edition. You need to supply an index if the image contains multiple installations. I will not elaborate much on this because in the next post I will show another method. If you want to find out the index number of the installation use DISM /Get-WimInfo /wimfile:<pathtowimfile>. Finally we apply this to the drive we obtained after the formatting.

    When that's finished the proper boot records need to be written to the disk. The tool is bcdboot and we pass the drive and boot type as parameter.

    $bcdargs = @((Join-Path $drive "Windows"), "/s $drive", "/f BIOS")
    Start-Process -FilePath "bcdboot.exe" -ArgumentList $bcdargs -NoNewWindow -Wait -PassThru

     

    Create the VM

    And basically that's it. All that is left is to create the VM, start it and open it. And as you might have guessed, Windows 8 comes loaded with all you need cmdlets.

    $vmname = "DEMO-01"
    Dismount-DiskImage -ImagePath $vhdpath

    $vm = New-VM -name $vmname -MemoryStartupBytes 512MB -SwitchName "Hyper-V Internal Network" -VHDPath $vhdpath
    Set-VMMemory $vm -DynamicMemoryEnabled $true -MinimumBytes 512MB -MaximumBytes 1GB
    Start-VM $vm
    vmconnect localhost -G $vm.VMId

     

    First another variable is loaded with the name of the VM. Then the disk is dismounted and a new VM is created. It's connected to my Hyper-V switch named "Hyper-V Internal Network". You will need to adjust this to your environment. In the second step dynamic memory is configured. I use this a lot. Using dynamic memory enables me to have 6 VM's running with 8GB internal memory and still not run into any issues. The final two steps are starting the vm and connecting to it using vmconnect. The latter can be discarded of course. Also notice I'm using the identifier to connect to the VM. During my testing I found out it is possible to create multiple VM's with the same name. Connecting to the identifier makes sure you connect to the right one.

    After a few minutes you should have a shiny Windows installation awaiting you to create a new administrator password. In the next post I will go into customizing the installation, sysprepping it and automating the installation process.