Automated Configuration Management: Deploying SQL Server via PowerShell DSC

Desired State Configuration (DSC) is a new PowerShell feature that manages Windows infrastructure (servers and applications), both on premises and in the cloud. It’s built into PowerShell 4.0 and is part of the Windows Management Framework. PowerShell DSC comes shipped with Windows 2012 R2 but is available to download for Windows 2008 R2 and Windows 2012.

DSC is declarative. DSC scripts have no logic on how to perform a particular install or uninstall. Instead they define how a particular server or application should be set up; it is then the responsibility of the runtime to “make it so”.

Imagine getting a house built, with walls and doors and windows. You wouldn’t worry about the composition of the walls, the number of hinges required to mount a door, or even how to install a window. That is the job of your builder – after all he is the expert. All you would do is describe how the house should look and then rely on your builder to make it so.

DSC works in the same way. The specification is contained in a configuration and the DSC runtime acts as the builder, making sure that resources on the server are set up just as we want them to be.

DSC is a relative newcomer to the scene. Other configuration management tools like Chef and Puppet have been around for many years and manage both Windows and non-Windows systems. However using them requires learning another language. DSC scripts use new extensions to PowerShell – most Windows administrators understand PowerShell (and if not, why not!?) so will find getting started a breeze.

What’s so good about DSC?

  • Standardisation. Create scripts containing the definition of different service types in your organisation (e.g. IIS, database, file server) then use these for all new deployments. You can be sure that all servers will be set up in an identical fashion – no more regional configuration problems or development servers that are different to those in production.
  • Speed up Deployment. It is quick and easy to apply a DSC configuration to a server via the PowerShell runtime
  • Detect configuration drift. DSC provides a way to determine whether a live server’s configuration matches that specified in the scripts and can report on or automatically correct the discrepancies.
  • Simplicity. As DSC is PowerShell based then anything you do in DSC can also be done using pure PowerShell. But with DSC you focus on what rather than how. Scripts have no logic or error handling and are easy to read.
  • Idempotency. You can apply the same DSC configuration over and over again and nothing will break. And if the configuration is updated, only the settings that differ will be changed.

DSC Components

  • WinRM (Windows Remote Management) – the Microsoft implementation of the industry-standard WS-Management protocol for managing servers using SOAP.
  • CIM (Common Information Model). A standard for describing the structure and behaviour of managed resources (such as storage, network, software components). WMI is the Windows implementation of CIM.
  • MOF files (Managed Object Format). Contains the configuration applied to the target node.
  • Resources These are the building blocks for a DSC configuration. DSC comes with a number of built in resources, such as File and Windows Feature, and you can also create your own.

Anatomy of a Resource

Every DSC resource exposes three functions.

  1. Test-TargetResource. This is the first method called when a DSC configuration is applied. It returns true/false based on whether the resource is in the correct state. If true then there’s no need for the DSC runtime to do anything else.
  2. Set-TargetResource. Called when Test-TargetResource returns false. Responsible for ensuring that the resource is set up following the specification stored in the configuration.
  3. Get-TargetResource. Returns all properties of the resource. Not used during the configuration step but used for reporting

A Basic DSC Configuration

The configuration below specifies that a folder named DSC_Demo should exist in c:\temp.

When the above PowerShell script is executed a MOF file named localhost.mof is created by the DSC runtime in the CreateFolderDemo folder. If you open the file it’ll look like this:

MOF Example

To apply the configuration – and “make it so” – use the Start-DscConfiguration cmdlet:

folder example
The directory didn’t exist before the script was run, so the Test-TargetResource function would return false. Next the Set-TargetResource function would be called and the directory created.

To show idempotency in action, if the script is run a second time then Test-TargetResource will return true and no further configuration takes place. Note that there are no errors and no attempt is made to create the folder a second time:

folder example try again

Something more meaty: Installing SQL Server using DSC

Using the xSqlPs PowerShell module you can perform a SQL Server installation via DSC.

First download the module then extract to the $env:ProgramFiles\WindowsPowerShell\Module directory. Open PowerShell and verify the modules are present when you run the Get-DSCResource command:


Next create the configuration. In my example I am installing a SQL 2014 instance called DSCInstance onto the local machine:

Running the script will generate the localhost.mof in the InstallSQLDemo folder:

dir sqlinstall

Deploy the configuration with the command:

This will run SQL Server setup creating the new DSCInstance database engine instance.

deploy example

Like with our FileDemo above if we try to run Start-DscConfiguration a second time then it’ll complete without errors:


Note: the xSqlServerResource is designed for SQL 2012 and other versions will report an installation failure even if it was successful. This is because the resource looks for the SQL Setup log file in the ‘110’ directory but other versions of SQL Server keep the log file elsewhere. As a quick fix edit ‘C:\Program Files\WindowsPowerShell\Modules\xSqlPs\DSCResources\MSFT_xSqlServerInstall\MSFT_xSqlServerInstall.psm1’ and change the directory on line 154 to the relevant one for your SQL version (100 for 2008, 120 for 2014).


DSC is a great technology and I think it will make managing and deploying servers much easier in the future. However right now it is still in its infancy and support for anything other than basic actions is very limited. The SQL Server module doesn’t expose many of the command line switches available in SQL Setup and all of the large enterprise environments I have worked on have customisations not available in the current version. I’m sure things will improve and of course it’s possible to write your own resources to implement any custom features.

There are also several tasks which should be able to benefit from the power of DSC but right now there appears to be no solution. A good example is the deployment of Service Packs or Cumulative Updates to a SQL Server instance. A SQL Server DSC configuration should contain the version of SQL to deploy. Then when a new Service Pack is released all a DBA has to do is to update some configuration files and re-deploy, and the SQL Server resource will detect that only a patch needs to be installed rather than an entire deployment.

This entry was posted in Automation, Deployment. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *