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

  • Article

In part 1 showed how you could install Windows on a virtual disk and use that disk to create a new virtual machine. We improved on part 2 to show how to set the administrator password or the computer name during installation to minimize user input.

In part 3 we will take another step. It's nice to have a clean Windows installation with the desired network configuration or computer name but what if I need a domain controller setup with some default settings or have a SQL server joined to an existing domain.

The magic is in the unattend file as well. Besides setting the computer name or network we can also autologon for a specified number of times. We can also specify commands we want to run when the logon takes place.

First the autologon. To get this going add the following XML right before the UserAccounts segment but after the OOBE.

 <AutoLogon>
 <Password>
 <Value>p@ssw0rd</Value>
 <PlainText>true</PlainText>
 </Password>
 <Username>Administrator</Username>
 <LogonCount>1</LogonCount>
 <Enabled>true</Enabled>
 <Domain>CONTOSO</Domain>
 </AutoLogon>

Again, not the best method for a production environment but it fits my purpose. If you use WAIK to generate the XML file the password will be encrypted.

We can now specify some commands we want to execute during the logon process. It is important to know how to install what you want using commands only. For active directory this is easy. You run DCPROMO and specify an unattend file. In this file you specify the domain name, level and dns settings. Some examples of the unattend file are on technet.

To copy it over I add another Copy-Item to my PowerShell script to copy my ad installation file to the destination system. Finally you must add the command to execute:

 <FirstLogonCommands>
 <SynchronousCommand wcm:action="add">
 <CommandLine>dcpromo.exe /unattend:c:\ad.txt</CommandLine>
 <Description>Installation of AD</Description>
 <Order>1</Order>
 </SynchronousCommand>
 </FirstLogonCommands>

If all goes well you will have a shiny new domain controller. Now for step two. Adding a member server to the domain. There are several ways to add a machine to the domain. I have tried a few but my knowledge of active directory is not at the same level as SQL so I ran in some issues using the autojoin. Eventually what worked for me was just specifying the user to join the domain. Again, using the autojoin is much safer than putting passwords in text files. This is what I added after the network settings in the unattend file.

 <component name="Microsoft-Windows-UnattendedJoin" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="https://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">
 <Identification>
 <UnsecureJoin>false</UnsecureJoin>
 <Credentials>
 <Domain>CONTOSO</Domain>
 <Username>bob</Username>
 <Password>p@ssw0rd</Password>
 </Credentials>
 <JoinDomain>contoso.com</JoinDomain>
 <MachineObjectOU>OU=Servers,DC=contoso,DC=com</MachineObjectOU>
 <DebugJoin>true</DebugJoin>
 </Identification>
 </component>

The next thing was installing SQL server. There are several ways to do this. One is to use the sysprep feature and include SQL in your base image and then finalize the installation. This proved to be limited for me. This only works for some components of SQL and also not for all versions of SQL. I wanted a solution that would work regardless of the version. My solution was to add the DVD during the VM creation and run a cmdline setup of SQL Server. This gives me much more control and flexibility. To install SQL server I created a cmd file with a very basic setup:

 d:\Setup.exe /q /ACTION=Install /FEATURES=SQLEngine,ADV_SSMS /INSTANCEID=SQL2012 /SQLSVCACCOUNT="NT Service\MSSQL$SQL2012" /ADDCURRENTUSERASSQLADMIN="True" /AGTSVCACCOUNT="NT Service\SQLAgent$SQL2012" /AGTSVCSTARTUPTYPE=Automatic /BROWSERSVCSTARTUPTYPE=Automatic /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS /UPDATEENABLED=0

And using the logon command I showed earlier I started this installation after the first logon. I also added a LOGOFF command to the cmd file just to return to the logon screen.

This works perfectly. I did notice the installation of SQL took a long time because of some .NET requirements. I have included these in my base image and things got a lot faster afterwards. Check your installation logs to see where most time is spend and see if you can speed things up by including these in the base image.

In the final part I will provide you with the definite PowerShell scripts and command files to recreate what I showed here.