November, 2014:

Last post I gave the rundown of Desired State Configuration and how it worked, but I skipped over the “secret sauce” around SQL Server. Of course, that was the tease to get you to come back for this week’s post.  Let’s dig in to the process and see how we can use DSC to install SQL Server without ever having to log in to the box.

Installing the Resource

The first thing to understand is that your custom resource will be contained within a Powershell module.  This means it lives in your WindowsPowershell\Modules directory.  The structure of the resource is specific and requires a couple pieces:

• <Folder – Your Resource Name>
  • Your Resource Name.psd1 (Powershell Data file describing the module)
  • Folder – DSCResources (Contains all resources in the module
    • Folder – Your Resource Name (folder containing your specific custom resource)
      • Your Resource Name schema file (descibes the resource)
      • Your Resource Name script

Now, if you’re using the Resource Designer toolkit, these will all get created for you.  I highly recommend doing that, because you miss one piece and you’ll be bashing your head against this for a week or so.  Ask me how I know.  :)

Another thing to setup is your execution policy.  As this is a custom script, the local server needs to it is trustworthy.  I set the execution policy to RemoteSigned for this (Set-ExecutionPolicy RemoteSigned).  If you don’t do this, you’ll get an invisible failure, where your configuration will fail but you will have no feedback on the reason.  Again, ask me how I know!

Custom Resource

When you actually create the resource script, you need three functions:

• Get-TargetResource
• Test-TargetResource
• Set-TargetResource

Get-TargetResource is the function that will return the resource you’re checking for.  It returns a hash table to represent the key values of the resource.  Test-TargetResource is a boolean check, returning true if the resource exists, false if it does not.  Set-TargetResource does all the work, as it is the function that is called if the resource needs to be created.  You can have other internal functions if you want to further modularize your process, but these three must exist for DSC to work.  The internal operations must be written by you, but as long as the names and outputs are consistent you are set.

The other key piece is not in the resource itself, but up in the data file describing your module.  Within that file you need to have a GUID set for the module so that the DSC engine can reference it when it is installed on other machines.  I tripped up on this many times, so be careful.

cSqlInstall

So let’s talk about the resource I constructed.  As I said in my previous post, I was frustrated with the limitations of the Microsoft resource.  The biggest limitation was the inability to use a config file, which is pretty much how I do SQL installs now.  So that’s how I approached writing mine, leveraging the .ini file for most of the installation detail.

 The resource accepts the following parameters:

• InstanceName – (required) The name of the instance you plan to install, MSSQLSERVER if you want the default.
• InstallPath – (required) The UNC path for where the setup files are.
• ConfigPath – (required) The UNC path for the config.ini file.
• UpdateEnabled – Boolean, defaults to false.  If true, the setup will check for and attempt to install available SPs and CUs.
• UpdatePath – If UpdateEnabled is true, this is the path where the update files reside.
• MixedMode – Boolean, defaults to false.  If set to true, the install will set authentication to mixed mode and create ‘sa’ with a randomly generated password.

I’ve tried to keep the parameters to a minimum and allow for customization to happen within the the config file.  The biggest gap right now is that the service accounts and their passwords will be contained in plain text in that file.  Ideally, you’d use managed service accounts to get around this, but I still am working on setting those up.

We then look at the functions within the resource.  Get-TargetResource should return a hash table, so what will return from this resource is the InstanceName, InstallPath, and ConfigPath.  This is because these are the required parameters for the function, but really we only care about the InstanceName.   To get that, it’s a simple check of the services on the target machine to find a service with the desired InstanceName.  If we find it, it returns that name.  If we don’t, it returns NULL.

Test-TargetResource is a lot simpler.  Since we want a boolean, all we do is use Get-TargetResource to get the resource hash table.  Then we check the hash table’s InstanceName with the desired InstanceName.  If they match, we return true and indicate the resource exists.  If they don’t match, the resource doesn’t exist and we return false.

Set-TargetResource is fairly straightforward.  Using these arguments along with some default ones, the resource will build out a call to setup.exe.  Once this string is built, the resource will invoke the setup.exe call just like any other unattended install.  After the setup run is complete, the script finds the most recent Summary.txt file and checks it to see if the installation was successful.  If the install was successful, it restarts the server and we’re done.  Otherwise, it will report an error.

Work To Be Done

There’s still some other gaps in the code that I will be working on.  First, it assumes the local machine account that the Local Configuration Manager runs under has permissions to the file shares for the SQL install.  If your environment is more locked down, this could be a problem.  There’s also the issue of logging that still needs to be addressed, because the current logging is not useful.  All of these items (and others that come up) will be addressed eventually.

I also plan to add other resources to this.  I’d like to get one to handle service pack and cumulative updates outside of the SQL install so that you can use it to keep everything to the correct version as well.  I’ve posted the code to my GitHub repository  As with the other scripts there, it is a work in progress and can use a fair amount of improvement.  If you have any suggestions or recommendations for this code, I’d love to hear of them.

As a DBA, I’m always concerned with consistency in my environments.  From maintenance to code deploy, I want things to be done the same way every single time.  Usually this is a process question, making sure that you harness the power of check lists and repeatable steps.  You then make it better by wrapping your process in scripts and leveraging other tools.

When I go into a new shop, one of the first things I usually have to do is bolt down the server build process.  Most environments will manually build servers, where an engineer goes in and manually installs the appropriate packages and features.  Then a DBA will install SQL Server, adding maintenance jobs and deploying admin databases.  Even with building scripts and sketching out steps, there will be a lot of spots where configurations can be missed and the process can breakdown.  Fortunately, we now have a tool in Powershell that helps us solve these problems.

You might have heard of Chef or Puppet.  Well, now Powershell has its own answer for managing server configurations:  Deired State Configuration or DSC.  DSC is an engine included in Windows Management Framework 4 that allows folks like us to create declarative configurations for our servers which can then be acted on by our servers.  This brings the next level of automation, infrastructure as code, to our servers.

How does it work?

The process for DSC relies on two things:  Your configuration and your resources.  You create a configuration, which specifies the resources you want (or don’t want).  You then use DSC via Powershell to deploy the configuration to a target server, which then runs the configuration through a Local Configuration Manager (LCM) to check those resources.  If those resources are present, the LCM takes note and moves on.  If the resources is not present as declared in the configuration, the LCM will then attempt to install or configure the resource as specified in the configuration file.

Simple, eh?  It’s meant to be that way.  The idea is to have an intuitive way to deploy servers in a standardized method, whether it’s 1 or 100 (or 1000 for that matter).  It’s also meant to be flexible.  DSC ships with some basic resources (things like File, Windows Feature, and Registry Key), but because it’s built in Powershell you have the ability to create your own resources.  This means the sky’s the limit with what you can do.

What do I need?

Let’s dig a little more into the detail of how DSC works.  First off, if we want to use DSC, we need a couple things:

• Windows Management Framework 4.0 on all machines involved
• Permissions to run custom scripts on the targets (if we’re using custom resources, more on this later)
• Custom resources need to be deployed to the target ahead of deploying the configuration

Note I call out some requirements around custom resources.  These are scripts you write yourself.  We’ll talk about those in a bit, just file these bits away for later reference.

Now, the configuration.  This is a configuration I created for deploying a base installation of SQL Server, so the resources are designed around that.  Here’s the script:

Configuration SQLServer{
   param([string[]] $ComputerName)

   Import-DscResource -Module cSQLResources

   Node $ComputerName {

       File DataDir{
           DestinationPath = 'C:\DBFiles\Data'
           Type = 'Directory'
           Ensure = 'Present'
       }

       File LogDir{
           DestinationPath = 'C:\DBFiles\Log'
           Type = 'Directory'
           Ensure = 'Present'
       }

       File TempDBDir{
           DestinationPath = 'C:\DBFiles\TempDB'
           Type = 'Directory'
           Ensure = 'Present'
       }

       WindowsFeature NETCore{
           Name = 'NET-Framework-Core'
           Ensure = 'Present'
           IncludeAllSubFeature = $true
           Source = 'D:\sources\sxs'
       }

       cSQLInstall SQLInstall{
           InstanceName = 'MSSQLSERVER'
           InstallPath = '\\HIKARU\InstallFiles\SQL2014'
           ConfigPath = '\\HIKARU\InstallFiles\SQL2014\SQL2014_Core_DSC.ini'
           UpdateEnabled = $true
           UpdatePath = '\\HIKARU\InstallFiles\SQL2014\Updates'
           DependsOn = @("[File]DataDir","[File]LogDir","[File]TempDBDir","[WindowsFeature]NETCore")
       }
   }
}

SQLServer -ComputerName MISA

 This looks like (and is) a Powershell function, just a special one using the ‘Configuration’ key word.  When you call the function, it will create a special file for the Node(s) declared within the configuration, a Managed Object Format (.mof) file.  This is a standardized file type that defines our configuration.  Note, this file is not Windows specific, as the design for DSC is to ultimately be used with non-Windows OS machines and hardware.  It’s the .mof that will be deployed to our target machines and acted upon by the LCM.

 Next up in the file, the Node.  This is the target for our configuration.  You’ll see that in the script, it is parameterized as an array, meaning we can run the script for as many different targets as we want, we just have to pass the array of machine names we want.  We could also have multiple nodes within the configuration script, if we wanted this configuration to have different types of targets.

 Within the Node section, we have our resource declarations.  This is the meat, the pieces of code that identify what needs to be configured.  The nice thing is reading them is fairly intuitive.  Let’s look first at the File resource.  These three declarations are for my default directories.  I have to declare specify that it is a directory (the Type), the path (the DestinationPath), and that is must exist (Ensure = Present).  As an aside, I could get a lot more involved with the File resource, copying things from a net share down to the target if I wanted, things like config files or web source directories.

 The Windows Feature resource is also easy to understand.  Using this, I can make sure certain Windows features exist on the target.  Since I’m using this particular configuration to install SQL Server, I’ve declared that I want the Net-Framework-Core feature installed.  This is a prerequisite for installing SQL Server.

 Finally, I have the cSQLInstall resource.  I’m going to save the explanation of this detailed custom resource for the next blog post, but I want to call attention to the DependsOn argument.  With this, we can declare prerequisites for our resources.  This give us some additional resilience for our install, allowing us to create some precedence within the configuration.

Where do I run it from?

Once you have the configuration written, you simply need to run it.  It will create a directory named after your configuration and contain a .mof file for each node within it.  The next step is to use the Start-DscConfiguration cmdlet and call the path where all our files live.  After that, a Powershell background job kicks off and DSC handles the rest.

Any not cool parts?

So not everything is unicorns and rainbows with DSC.  First off, it’s a stone cold female canine to debug, because it’s difficult to capture any logging around it.  Many times I would try and run something and it would fail before it even entered my script.  Most of this was around my custom stuff, but it was extremely frustrating. 

As for the custom scripts, DSC is still fairly immature.  Microsoft has some resource kits out there, but most of the resources in there are pretty weak.  The SQL resource provided has several gaps, such as being designed specifically for SQL 2012 and not being able to use .ini files.  So you’re probably going to write resources on your own, which has a LOT of gotchas.  You’re going to want to start with the Resource Designer Tool, which will solve most of the gotchas for you.

 Also, since it’s immature, there’s not a lot written about it.  Especially for SQL Server.  Powershell.org and MSDN are going to be your best bets for help, but this is a brave new world.  Be prepared to walk a fair amount of the way on your own.  If you want to start, go with this book from Don Jones.

 Infrastructure as code is the future.  DSC gives us intuitive tools with a straightforward interface (once your resources are written, of course) to make our environments more consistent and easier to manage.  I’m super excited about this feature and will be blogging more about it in the weeks to come.  Definitely stay tuned for next week, where I’ll walk through my custom SQL Server resource (and tell you where you can get the code yourself).  Also, hit me up if you’ve got questions or any experiences of your own around DSC, I’d love to hear about them.