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

  • Article

I have not been able to follow up my post for a long time. Work has to be done as well. The good thing is that it gives me a lot of new ideas for blog posts and as a (late) resolution for the new year I will write more blog posts

But back to part 4 of building a SQL Demo lab. There are a few things you have to consider building an entire lab. Some are easy and some require creativity. First let me list the steps you would take to create an AlwaysOn demo manually.

  • Build a Domain Controller
  • Provision some users to the domain
  • Install 2 or more member servers
  • Install SQL Server 2012
  • Configure SQL Server
  • Build a cluster on top of the member servers
  • Enable AlwaysOn

And that should leave you with a ready-to-go demo machine.

In the previous part we have done all the individual steps but the complexity lies in the sequence. We cannot add a member server if there is no domain controller and we cannot enable AlwaysOn if there is no Windows Cluster build on top of the member servers. And what would be the use of automating things if we have to execute three or four scripts manually.

Key Value Pairs to the rescue

Hyper-V virtual machines have this concept of exchanging data through a dedicated registry portion. There is some good information out there but the best I found so far was this blog post by Taylor Brown; https://blogs.msdn.com/b/taylorb/archive/2012/12/05/customizing-the-key-value-pair-kvp-integration-component.aspx. By leveraging this concept I can write some PowerShell code that will read this data in a loop and exit when a certain value has been saved to the registry key. This will enable me to create one script that will call different scripts to install the various servers but in the proper sequence. In my VM I will set a status. Using a function on the host I read the value and depending on the status I will proceed to the next step.

There had to be challenges

Of course there had to be. What I wanted was to alter the unattend.xml files. I could have taken the easy road and use placeholders like @computernamehere@ and just do a textual replace. But I choose to use XML to navigate through the unattend.xml and change the proper value. To give you an example of some funny PowerShell behavior. Imagine this piece of XML:

<root>
<data type="database">
<name>master</name>
</data>
<data type="job">
<id>1</id>
</data>
</root>

If you navigate through this piece of XML in PowerShell, for example $xml.root.data.name it will return master. Great! A new world of possibilities arises. Things get ugly if you want to change the value. You get an error telling you the value cannot be set. It took some time but it turns out to be related to the fact the data element is there twice but the name element only once. When you navigate through the XML PowerShell uses late binding to figure this out but when you write to it, it wants to know exact. I solved this in my script by using Xpath queries to get to the correct element.

On my SkyDrive you can find the scripts I used: https://sdrv.ms/Y0oUfv

The main script I used is Create-Lab which will create a Domain Controller and configure it. Installs 1 management member server and 3 SQL Servers. When the installation of the third server is finished a cluster is build and Availability Groups are enabled. The instances are restarted using WMI. This proved to be the most reliable method. If you want a shorter example of what the script does check out Create-Lab-Demo. This creates a lab with SQL 2008 R2 and SQL 2012 installed on it.

Important! In the Create… scripts there are names and paths I used on my machine. Evaluate each one and set this to your environment. Also in my script I refer to two *.wim files for the base installation. Due to legal and other reasons I cannot provide you these files but you can use a *.wim file from the Windows setup media or create your one using the method I described in one of my earlier posts. Also the iso files I used for SQL Server are of course not included and you need to provide your one. Finally, the keys included are setup keys the installer uses when you would choose Skip Installation during the Windows Setup process. Check one of my earlier posts to see where to retrieve these keys.

Feel free to contact me for any further information or improvements. And remember, do not use these scripts in production environments. They were built to create quick demo labs.