Installing SQL Server Using Desired State Configuration

(Update: I’ve since discovered that SqlServerDsc has replaced xSQLServer.)

One of my growing passions is using PowerShell Desired State Configuration (DSC) to automate all the things. I started out with simple configurations for testing but wanted to dive into more complex\useful situations for my day-to-day DBA life. To be honest, I was intimidated by the idea of doing a SQL installation. Configuring simple parameters or creating a directory are easy enough to wrap my head around, but something as complex as a DBMS installation gave me pause. I’m here to tell you that my worries were unfounded, and that you should have none as well.

The blessing and curse of DSC is that it’s so new. It is without doubt a very powerful tool, but as of yet there isn’t a lot of documentation around the individual resources. Or worse yet, the pace of improvement moves so quickly that information from two years ago is now out of date. I plan on doing a separate post for how to approach DSC given these realities. With this post, however, I wanted to fill one of those documentation gaps. Specifically, how to install and configure an instance of SQL server. I based my file off of an example one provided by Microsoft in the “Examples” folder of the xSQLServer module named “SQLPush_SingleServer.ps1”. Pro tip: always look for example folders in the modules you want to work with. It should be noted that you can address much more complicated scenarios, such as setting up clusters or Availability Groups, but for simplicity this configuration will be creating a single instance on one node.

If you have experience with DSC or simply don’t want to listen to me drone on about the details, the full configuration is at the bottom. For those interested in the play by play, or just bored and looking for something to do, I’ll address each piece individually.

The script starts out with compulsory documentation on the script and what it does. Kidding aside, get into the habit of doing small sections like this. Your coworkers (and you years from now when you’ve forgotten what you did) will thank you.

Next, we hard-code a couple of items specific to your individual run of the script. List the computer(s) that you want to deploy to as well as a local path for the configuration file that DSC will create.

Following that, we will set how the Local Configuration Manager on the target nodes is to behave. We’re specifying that the configuration is being pushed to it, that it should automatically check every so often for compliance to this configuration and auto-correct anything that’s not aligned, that modules on the node can be overwritten, and that it can reboot if needed.

Following that is the actual configuration details, where all the fun is defined. Mine is named “SQLSA”, but it really doesn’t matter what you name it. This is like defining a function; so as long as you call it by that same name later, little else is relevant. You’ll see at the top of this section there are three “Import-DscResource” lines. This tells the configuration which DSC modules will be needed to perform the actions we’re requesting.

The WindowsFeature item is one of the most handy in DSC. This allows us, as you might guess, to install Windows Features (in this case the .NET Framework).

Next I’ve created a firewall rule to make sure our instance’s port will be open (this is defined later under xSQLServerNetwork). It’s worth noting that there is a resource built into xSQLServer that allows you to configure firewall rules for SQL. However, I did not like the behavior of it and found that xFirewall from the module xNetworking provided a lot more flexibility.

Up next is the actual meat of installing SQL Server. The if($Node.Features) block is something I picked up from the example file. I’d say it’s redundant to check for whether you’re installing SQL when you came here to install SQL, but hey, it works well so I left it.

One way I’ve altered this section from the original is to parameterize everything. If you look further down there is a $ConfigurationData section. Having all of our customizable fields there allows us to easily change them for each deployment (dev, test, prod) without having to search through the code. You and your team will know exactly where to go and what to change for each situation.

I’ve also included some examples of basic SQL Server tasks like creating a database, disabling the sa account, disabling a feature like xp_cmdshell, and configuring the network port (referenced earlier). The naming on these items looks odd but makes sense. By adding in the node name we can ensure that they are unique should we deploy to more than one target node. And adding a friendly name to the configuration item, like “sa”, makes it easy to tell DSC which item depends on which. Speaking of which, note that each of the configurations depends on the base installation. That way DSC will not run those if there is nothing to actually configure.

After the configuration definition we have the $ConfigurationData mentioned earlier. It’s a great idea to get in the habit of using sections like this. It will make your transition between various environments much easier.

The next section details what we’d like the instance name to be as well as what features should be installed. It’s very picky about the feature names, and they don’t line up exactly with a standard command line install. So be careful with what you place here. It won’t install anything incorrectly, just simply cause the configuration not to run and you to lose your mind.

Also in this section, we’re copying over the modules that each node will need in order to perform this configuration. This isn’t necessary when using DSC in pull mode, but that’s a story for a different post.

I know you thought it’d never come, but at last it’s time to actually do something with all of this. We call our “SQLSA” configuration, passing in the $ConfigurationData and specifying to place the resulting .mof file in $OutputPath. After that, configuration is started on each node using Start-DscConfiguration and calling the .mof that was just created. Lastly, the node is tested to make sure it’s not out of compliance.

If all goes well, your output will lack red and eventually will end in a message stating that the configuration tests as “True”.

 

And that’s all there is to it! Not so scary after all. I deployed my first DSC SQL Server while making tea and wondered why I’d been doing it any other way…

 

 

One Reply to “Installing SQL Server Using Desired State Configuration”

Leave a Reply

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