Desired State Configuration for SQL Server - Configurations

By: Shawn Melton   |   Comments (3)   |   Related: More > Database Administration

Problem

After reading previous tips on Desired State Configuration (DSC), here and here, I’m ready to actually start writing my own configurations. Starting off with a small example I would like to ensure the SQL Servers in my environment have the PowerShell module dbatools “installed”. How can I use DSC to validate and ensure this module is in place?

Solution

In this tip I thought it would be best to take you through a real-world configuration, something that would be beneficial to use, but also simple enough to get the feel of how configurations work. I find that this area of DSC tends to be where folks can get stuck, or just give up from frustration. In the first tip I wrote on DSC I provided the basic structure of what a configuration looks like, so in this tip we will expand that and cover more of the details around them.

Definitions

As in other features that came out with PowerShell for DSC a few keywords and commands were created that help you develop your server configurations with PowerShell code. I want to touch on a few of the main items in a configuration that will help you understand a bit more of the flow.

Configuration

This keyword is treated like function or workflow is in PowerShell 4.0 and above. If you are writing a basic function in your script you usually specify “function Verb-Noun”, with DSC it is a similar format where you specify “configuration DeployModules”. It does not necessarily have to follow the same format of “verb-noun” but you want it to be a meaningful name. Just like functions you can also parameterize the configurations if you desire, something you would commonly do if you share your code to the public.

One thing to note is just like in a function a configuration does support comment based help. So, you can (and should always) write help for your configurations. This will help your team, or the public understand how to use your code and what it does. They will be able to just run Get-Help DeployModules to retrieve the comment-based help.

Node

This refers to the server that the configuration will be applied against. This can be a single machine or multiple, based on how you build your configuration. In this tip we will focus on applying a configuration to multiple nodes in a dynamic fashion.

MOF File

Managed Object File are used to describe CIM classes. They are basically your configuration converted into a format that CIM will understand when the LCM applies it to the given node.

Import-DSCResource

This one is named like a cmdlet, but is referred to as a keyword in DSC. This keyword is used to import the resource(s) you are referencing in your configuration. It can only be used within the context of a configuration.

ConfigurationData

This is a built-in parameter of your configuration and is a hash table type. This parameter allows you to pass in a hash table of values that can be applied to a specific set of nodes, or all of them.

A basic example:

@{
   AllNodes = @( 
      @{ 
         NodeName = '*' 
         Features = 'Net-Framework-Core' 
      }, 
      @{ 
         NodeName = 'VM-2'
         Features = 'Failover-Clustering'
      } 
   ) 
} 

In the above example the first node name has an asterisk (“*”) value, this declares that this applies to all the nodes targeted. So, all nodes would have the feature “Net-Framework-Core”. The second item you can see has a name hard coded for the node name, “VM-2”. If a target’s host name matches this node name, both features “Net-Framework-Core” and “Failover-Clustering” are applied.

The Features key is simply one I made up, and you can name them what you want and have as many keys as you need to get the job done. The NodeName is the only required key that must be declared.

$AllNodes

This is a special variable that refers to the entire collection of nodes (AllNodes) in ConfigurationData. You can use filter methods introduced in PowerShell 4.0 against this object: Foreach() or Where().

$Node

Another special variable that refers to the entry in the AllNodes collection. You can use the same filter methods noted above on this object as well. The hash table you build for a node must contain a key named NodeName and after that can be any key/value pair you want. In my example above the "Features" key is one I made up.

Non-Node Data

You can include any data you may need for your configuration. If you have an external file that has data you need, you can pull that into your hash table and then have access to that for your configuration data.

There is one caveat to this in that your configuration data can be included in the same configuration script, or you can save the configuration data in a psd1 file. Use of the psd1 file requires that data be hard coded, it cannot be dynamic or have references to external data in it. Once you move to the file format the values have to be hard coded.

Local Configuration Manager (LCM)

The LCM is the engine of DSC, it runs on every target node and is responsible for getting the work done. When you build a configuration, and deploy it to a node, the LCM takes that MOF file and applies it to the target.

One special note to make on DSC and the LCM is that execution of your configuration is done in the context of the system account on a given target node. There are options to execute a configuration under another credential, but it takes some special considerations that are beyond what I can discuss directly in this tip.

Built-In Parameters

If you just create an empty configuration you can see from Get-Help what built-in parameters are included.

InstanceName

This is used to uniquely identify the resource ID for each resource in the configuration. Not one you must worry about in the current versions of DSC; a default value is generated so you do not have to utilize this parameter.

DependsOn

If I have a configuration that I need to copy some files from a UNC path to a node, I first need to ensure the destination folder exist. The configuration you put in does not guarantee those operations will be performed. In this situation I would declare that the file copy DependsOn the folder creation, so that folder is created before I try to copy anything into it. You will declare this within your configuration code so use of this parameter is rarely needed.

PsDscRunAsCredential

As noted previously that the LCM runs under the context of the system account on a server, this parameter allows you to pass in a specific credential.

OutputPath

This will be the path the MOF files are generated in. If a path is not provided the MOF files will be created under the current script path.

ConfigurationData

The hash table of data you have defined to utilize within your configuration.

DSC Functions

The following functions are the main ones that you will need to get familiar with to test and deploy your configurations. These are functions within the PSDesiredStateConfiguration module. You can utilize Get-Help or Get-Command to get more details on the functions available and help for each one.

• Start-DscConfiguration – This function applies the configuration to each specified node, pushes the MOF file in our scenario to the targeted servers.
• Test-DscConfiguration – This function lets you test a given MOF file or configuration against a targeted node to find out if the given server matches your desired configuration or not.

Now, there are other functions included in the module, but the two above are the most commonly used when you are working with your configurations.

Common Errors

PowerShell Version Matching

One issue that can come up often in DSC are errors, they occur for various reason and they don’t always just scream to you the exact issue. One such issue you can have with configurations is on the PowerShell version they were generated under. If your development machine, or deployment server possibly, is running PowerShell 5.1 and you hit a target node that is running PowerShell 4.0…you will get some funny errors. The most common one I have seen is the following:

This error will, generally, mean you generated the MOF file on a machine that had a higher version of PowerShell than the target machine. I would recommend if your System Administrators have not already, upgrading PowerShell on your servers to version 5.1. It will save you many nights of troubleshooting issues with DSC, and overall your PowerShell scripting will run more efficiently.

Access Permissions

Remember that the LCM runs as the local system, so when you are working with domain resources or external objects they need to have been granted access to the server accounts. (You will see this specifically in the scenario example next.) You can use the test function noted earlier and if permissions are an issue on the paths in your configuration you will see an error like this:

Deploying dbatools

One of the big things to come out of the community in the past year or so has been the PowerShell module dbatools. This module can be used to manage and troubleshoot SQL Server in your environment. It is common practice that production servers have Internet access blocked (or at least should) so you need a method to deploy the module to your servers.

A few things we need to have our configuration do for us:

• Check the target node for the existence of the module.
• If not found, pull the module from a central location to the target node.

Resources

The DSC resource we will use for this task will be the file resource. The benefit of this one is that resource already exist within the PSDesiredStateConfiguration module, built-in to PowerShell 4.0 and above. When you want to utilize a resource that is not built-in you have to factor in that it will have to be copied to each target node. [Any resource that is not built-in has to exist on every target node.]

My Environment

I’m working from an Active Directory (AD) environment built within Azure. I have three servers in use:

• UTILITY-01 – This is the server I generate the configurations (MOF files) from and acts as my file server. I have a “Scripts” share configured on this machine where my MOF files and the modules I want to deploy are kept.
• SQL2012-01 – A basic Windows Server 2012 R2 virtual machine, joined to the domain.
• SQL2014-01 – A basic Windows Server 2012 R2 virtual machine, joined to the domain.

The SQL2012-01 and SQL2014-01 are the two servers I need to deploy the dbatools module, they will act as my target nodes for the configuration.

One special note on the Scripts share that is hosted on UTILITY-01 is that as noted earlier the servers need to have access to this share. In my environment I solved this by just giving all Domain Computers read access to the share.

Your environment and security requirements for production may differ so you will need to work with your AD and Security administrators to get the required access. One option may be to grant permissions by specific computer, or add those computers to an AD group.

To save the module to this share I simply ran the following command on my UTILITY-01 server:

Save-Module dbatools -Path F:\Scripts\Modules
   

This provides the following directory for the module:

Configuration

  
Configuration DeployModule {
    param (
        [string[]]$NodeName,
        [string]$SourceModulePath,
        [string]$DestModulePath
    )
    Import-DscResource -ModuleName PSDesiredStateConfiguration
    Node $NodeName {
        File DirectoryCopy {
            Ensure = "Present"
            Type = "Directory"
            Recurse = $true
            SourcePath = $SourceModulePath
            DestinationPath = $DestModulePath
        }
    }
}

The breakdown of this configuration:

1. Configuration name is called DeployModule.
2. The File resource created as DirectoryCopy.
3. The type of File I’m working with is a “Directory”.
4. I want to ensure it is “Present” on the give target node.
5. I want to also ensure all subdirectories are included, so I must set Recurse to true. Which this property is only valid when you have a type of Directory in use, otherwise it is ignored.
6. I I provide the source path via a parameter of the configuration, which in this scenario will be the script share path for dbatools module.
7. I also provide the destination path via a parameter of the configuration, which will be the PSModule path “C:\Program Files\WindowsPowerShell\Modules\dbatools”.

Current setup for the dbatools module on each server:

As you can see from the screenshot SQL2012-01 already has a version of the module installed, while SQL2014-01 does not. However, let say we are not sure which one already has it, and which one does not. We can utilize the Test-DscConfiguration to check which server is missing the module.

Test the Configuration

This process will look like this:

Breaking this down for you:

1. I dot-source the script so my configuration DeployModule is loaded into my session. (. .\DeployModule.ps1)
2. I then run the DeployModule against each node.
   1. Note the SourceModulePath is a path that is “visible” from the remote machines, and not just the local server I am on.
   2. Also, the DestModulePath is the full path, including the folder name “dbatools”. 
3. DSC will generate the MOF file for each node.
   1. Once it does that the configuration will output a FileSystem object.
   2. You can save a few keyboard strokes by setting your configuration call to a variable if you wanted and then simply pass in the first directory name (e.g. $files.DirectoryName[0]) to the test command.
4. I then run Test-DscConfiguration
   1. The ComputerName can be just one of the servers or both.
   2. The path is simply the root folder where the MOF files are saved.
   3. This particular DSC command will look for a MOF file that matches the computer name you passed in, if an exact match is not found it will error stating the MOF file does not exist in the directory.

If everything aligns properly you will see an indicator of the test running:

The output is simple to understand:

You can see the “InDesiredState” shows that SQL2014-01 is “false”, meaning it does not find the dbatools modules under that destination directory. So, it is not installed, but on SQL2012-01 that property shows to be “true” so the directory found on that target matches up to the module path I have on the central share.

Apply the Configuration

You simply run the Start-DscConfiguration with the same parameters you used for the test command. Only thing with the start command is that you can have it run as a job, or it has the Wait parameter that causes the command to not return your prompt until it has completed.

Start-DscConfiguration -ComputerName SQL2012-01,SQL2014-01 -Path F:\Scripts\DSC\MOF -Wait
   

The other thing is this command has no output if everything goes as planned. You can use the test command to double check that everything was done properly:

Module Versions and Homework

I want to be clear that the File resource we are using is doing a comparison from the top-level folder under “\\UTILITY-01\Scripts\Modules\dbatools” to what is on a given server at “C:\Program Files\WindowsPowerShell\Modules\dbatools”. Since our configuration has Recursive set to true it will go down into that directory as well. So if you noted the module on the UTILITY-01, when I saved it, shows as “..\dbatools\0.9.130”.

What happens when I do another “Save-Module”? The latest version of dbatools will be pulled down from the PowerShell Gallery and saved in that same dbatools directory. That “..\dbatools” will now contain two child directories. At that point if we generate a new configuration file, and then run our test again what do you think the “InDesiredState” will show? It will show false for both servers. Do you know why? At this point the only version that was installed on SQL2012-01 (previously showing true for desired state) was the 0.9.130 version. Since the central share for dbatools now has two child directories, the dbatools folder no longer matches on the server.

All that is to point out that you can maintain multiple versions of the module and have all of them deployed to each server if you need. If you want to only maintain the latest version, then you can simply provide the path to the module version you want (e.g. “\\UTILITY-01\Scripts\Modules\dbatools\0.9.130”). The only caveat is if you want only that version to exist, you will need to contend with cleaning up older versions of the module on the target servers. You can do the cleanup with DSC as well, but I will leave that as a homework assignment for you. I will give you a hint that you can use the File resource, but it would likely be more efficient to use the Script resource.

Summary

Configurations with DSC can be simple or extremely complex. It is best to keep it as simply as you can, but there are scenarios that simply require some complexity. Either way DSC is a tool that can save you a lot of time with configuring your servers or ensuring the PowerShell modules you need to do your job are in place.

Next Steps

• More details on MOF files.
• Understanding Configuration keyword.
• Creating the ConfigurationData hash table.

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