Deployment–Getting SQL “right” with PDT

Article
03/05/2013

So far I’ve introduced PDT, explained the downloader and VM creator utilities of PDT, and given an overview of how to troubleshoot PDT. So how do you actually use this thing to do something useful? It’s time to start digging into how to customize a PDT deployment.

The goal of PDT is to be relatively simple to use, whilst also allowing for any type of deployment customization. In this post, I will explain how to customize the PDT deployment of SQL Server. There are several reasons that I start with SQL Server – it is the first item you need to get installed as part of a System Center deployment, and it is also one of the most critical items to get “right”.

But what is “right”, I hear you ask? I like to think of this less in terms of “right” versus “wrong”, and more in terms of “working” versus “not working”. “Working” means you get acceptable performance from your System Center deployment, and the most critical factor there is SQL performance. There are three key pivots to SQL performance – processor, memory, and disk IO. Processor and memory, especially in a virtualized environment, can easily be changed post-installation. Disk IO is not so easy to correct. Most SQL experts will recommend that you separate database files, log files, and tempdb files. Even in a virtualized environment where all of these files actually reside on a single volume, if you use separate virtual volumes for SQL installation it is relatively easy to move them for optimum SQL performance post-installation.

A default SQL installation puts everything on the system volume. That is bad, bad, bad, bad, bad. If you use the sample Variable.xml in the PDT package, that’s what you get. But PDT allows you to specify paths for all files, just as an attended SQL installation would.

So here’s the trick… and this is going to apply over and over for everything related to customizing a PDT deployment. In my introduction post, I said that Workflow.xml is a static file and should not be modified. Workflow.xml specifies the default values for all deployment variables, and any one of those variables can be copied to the appropriate location in Variable.xml and customized as needed.

If you look at Workflow.xml, you will see a <SQL> section – below is a snippet of that section showing just the variables we care about right now:

These values correspond to the default values when installing SQL Server manually.

Now, take a look at Variable.xml, and you will see a section per instance of SQL being installed:

To change the location of SQL data files, just copy the variable entries from Workflow.xml to the SQL instance sections of Variable.xml, and change their values. For example, if you are installing SQL with just database services and want to change the location of database files, log files, and tempdb, your SQL instance section in Variable.xml might look like:

This allows you to fully customize the SQL installation, just as though you were installing it manually. Of course, you still need to know where the optimum locations for those files are based on your infrastructure – and that will be different for everyone – but once you have that information, PDT will deploy the way you ask it to.

Any variable in Workflow.xml can be customized in Variable.xml – how to use this for further customization of your deployment will be explored in more detail in future blog posts.

Deployment–Getting SQL “right” with PDT

Additional resources