Art of the DBA Rotating Header Image

DSC – The Custom SQL Resource

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

VPn1q6NSo 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.

Leave a Reply

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