Desired State Configuration and the SQL Server DBA

By: Shawn Melton | Comments | Related: More > Database Administration

Problem

Automation in our jobs as a DBA is becoming more and more pivotal as time goes. Taking a few days to get a basic SQL Server instance installed and configured is something that just shouldn't happen anymore. We have other things to get done, like learning all the new things coming out with SQL Server 2017. Are there any options for a DBA that will allow us to install and configure SQL Server in an automated manner?

Solution

Desired State Configuration (DSC) can be your saving grace to giving you so much more free time. Got a few SQL Servers that need to be installed and configured for a project next week, fire off a few commands and you're done. Leave the office early on Friday, or take the time to catch up on those webinar recordings you missed the other day because someone changed a setting in the OS that affected SQL Server. Oh, forgot to mention that DSC can help you to ensure your configuration stays in place and does not drift over time.

Desired State Configuration Basic Definition

Desired State Configuration (DSC) is a management platform in PowerShell that enables you to manage your IT and development infrastructure with configuration as code.

DSC offers the ability to get your configuration transferred into code using a not-so-hard-to-understand language, PowerShell. It allows you to declare what configuration either a server or group of servers should be set. You can do this across all your servers or have variations of those configurations based on the role a server plays.

In most of your PowerShell scripts (to-date) when you have used cmdlets or SQL Management Objects (SMO) to get SQL Server installed and configured, you have told PowerShell how to do something so it is configured in the way you want. The beauty of DSC is you simply tell it what you want it to be and it takes care of making that happen.

A configuration would follow, in a simplified form, the syntax below:

Configuration Identifier {
    Node TargetServer {
        ResourceName UniqueIdentifier {
            < defined properties and values >
        }
    }
}

The “Node” is where you define either the specific targeted machine for the configuration and there are options that will allow you to make this more dynamic. PowerShell rules still follow with DSC so you can parameterize this just like you do a normal function. As well, you can code this in a manner that allows you to have roles if you wanted (e.g. database server, file server, web server, etc.).

The “ResourceName” will be the actual resource you use, whether it is a built in one like “WindowsFeature” or an experimental resource like xSqlServerSetup.

The defined properties and values are going to be based on the resource you use and what properties have been added to that resource. There will be some more details on this below.

Requirements

Does not do you any good to find out after you spent time building it that it won’t work in your environment. I want to explain what is required to use DSC. These requirements must be in place for both the client machine that generates the configuration and then the server(s) it will be applied on.

PowerShell WMF 4.0 or higher.
- 5.0 or higher should be preferred, latest version means more features and better performance.
PowerShell Remoting enabled
.NET 4.5 framework (required by PowerShell 4)
Window Server 2008 R2 SP1 or higher (minimum OS that runs WMF 4.0)

If your shop happens to already be all on Window Server 2012 R2 then all the above requirements are covered, they are all baked into the OS out of the box.

Something to also be aware of is that DSC runs by default under the system account on a given target server. As we get more into using DSC there are ways to pass in credentials to handle any special access that could be required, but it is just something to keep in the back of your mind.

Desired State Configuration Terms

While DSC is part of PowerShell there are a few things that knowing up front can save you time. It will greatly help to get a good understanding of these areas so you know what limitations you may have, if any. Now that DSC has been out for some time there are numerous books and online courses available to help you get more understanding that what I could provide in this tip. I will provide links to where you can find some of these resources at the end of this tip.

The following items will give you a starting point of the main items you deal with using DSC.

Configuration

You saw this term used above in the example code. This is the meat of it all. A configuration is used to declare what you want DSC to apply to one server or a hundred. It is a keyword in DSC that acts like creating a custom function. You will basically create a script that defines your desired configuration for a given server, or group of them.

Resources

A configuration being what you desire, a resource is the part that helps get it done. Resources are grouped into modules to keep them organized. There are many resources that are "built-in" and others that are referred to as "experimental" or “community” resources. An experimental resource will be prefixed with an “x” while the community resources are prefixed with a “c” to avoid confusion.

The experimental classification is to suggest that it is experimental and not 100% tested or validated. The resources that are built-in or included with PowerShell will quickly be limiting on what you may need done, so your only option is to use the experimental or community resources. Just make sure you test and test and test before using them in production.

The main set of experimental resources I use come from and are maintained by the PowerShell team along with some contributions from the community. You can find them all under the PowerShell's team GitHub account. The PowerShell team package these up as a "DSC Resource Kit" release, generally every month or two months.

One thing to note on the DSC Resource Kit is to make sure you read the release notes, and/or the blog post. They make a point to let you know if the new version of a resource has breaking changes to any specific area.

Local Configuration Manager (LCM)

Just like SQL Server has a database engine, DSC has the LCM as the engine that is the liaison between the resources and configurations. It will go through and parse your configuration and based on the resource utilized act upon that to put it in place. This exist on each target server, or node.

There are two modes the LCM can be configured to run in: Push or Pull. I most commonly used push because I use DSC in more of an ad-hoc manner. The pull mode is more for setting up a central configuration server of sorts, and then you would configure all your servers to go and pull their configuration on a set interval. The push configuration is what LCM will be set to by default.

Desired State Configuration Modules

The PS Gallery hosts a large portion of the DSC Resources, but first I want to show you how you can find the commands available in the built-in module. These will include some of the main commands you will use most often while working with DSC. The module is called PSDesiredStateConfiguration. On my local machine I have PowerShell 5.1 (Windows 10 Pro), and the following figure shows what is available:

Figure 1 - Commands for PSDesiredStateConfiguration Module

As you can see in figure 1 we have both functions and cmdlets in this module. You can see that keyword “Configuration” shows up as a function. In a future tip I will go over some of these in more detail but you can reviewing the PowerShell documentation online for each command to get familiar now.

As I mentioned before that resources are grouped into modules and available in the PS Gallery. You are likely familiar with using “Find-Module” to locate modules in the PS Gallery. You can find the modules that have resources in them using -Includes “DscResource” parameter with Find-Module. You can also search for the modules using Find-DscResource and the -ModuleName parameter. As an example, to find a resource around SQL Server we can just do a pattern search and get the results in figure 2, as of this writing.

Figure 2 - Find Resources with SQL in the name

Then in comparison if we use the Find-DscResource command:

Figure 3 - Find Resources using Find-DscResource

You can see there is a pretty significant difference in how you can search for resources. I tend to stick with using Find-DscResource for this reason.

Summary

Look for future tips where we will dig into using DSC more. DSC can look like a complicated topic but once you get an understanding of some things noted above you will have that moment where it will all click. See the “Next Steps” section below to find some useful resources to expanding your learning.

Next Steps

About the author

Shawn Melton started his IT career in 2002 and has experience working with SQL Server 2000, 2005, 2008, and 2008 R2.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips