Freigeben über

Desired State Configuration(DSC) for SQL Server

  • Artikel

 

Many people are not familiar with DSC yet so lets first start with what is DSC and why we as DBA’s should care.  DSC is a management framework in PowerShell.  DSC allows us to deploy and manage configuration data.

Some examples of practical applications would be the following:

  • Enabling/Disabling server roles and features
  • Managing registry settings
  • Managing files and directories
  • Starting/Stopping/Managing processes and services
  • Managing groups and user accounts
  • Deploying software
  • Fixing a configuration which has drifted from its intended configuration

So from a SQL perspective how can we as DBA’s leverage this framework?  There are several ways and many are already available for us in the PowerShell Gallery.  If we don’t find specifically what we need we can generate it and publish it for others to consume as well.  The exciting thing about DSC is it is community driven.

So specifically around SQL Server I can think of a few things which every DBA should be interested in automating.

  • Installation of SQL Server
  • Configuration of SQL Server. (MaxMemory, MaxDop, ect)
  • Security Compliance

In addition it would be nice if after we built a server we knew the configuration would not change.  DSC can help us here as well because it can monitor the configuration and if it changes it can put it back to the correct configuration.

When we talk DSC the first thing we need to understand is how are the configurations being applied.  On each target node there is a local configuration manager (LCM) which is responsible for calling resources included in the DSC configuration script.  LCM is configurable such that you can specify the frequency it checks the configuration, which mode the confutation is in (Push vs. Pull), if the node can be rebooted if needed, as well as others.

Two different modes exist for the LCM push or pull.  Push allows for configurations to be pushed to the server and applied.  This works well for testing or for a small deployment, however in larger deployments we will most likely look to a pull configuration.  Pull can be a SMB file share or a HTTP website.  Configuration files are stored here and servers periodically check in to see if they are utilizing the most recent configuration.  Depending on their LCM settings they could apply or just monitor.

An example of Push and Pull LCM is provided below:

imageimage

Now we have an understanding of LCM,how about the configuration where we want to deploy SQL Server and configure it.

Here is the piece of code which will tell the LCM to install SQL Server.  A few parameters are required to be passed and then the configuration can be provided to the node and LCM will do the rest.

image

In addition we probably want to configure SQL Server after the install.  For instance SQL Server performs best when the power plan is set to High Performance. There are likely others we would want to set also such as min and max memory and page file.  There are many possibilities.  I took the most common ones I could think of and created a DSC module.  An example of how you can leverage them is listed below:

image

Location of the modules are provided below.  Once you have cSQLConfig installed locally you will find an example configuration script in the examples folder.  This should get you started and only need some minor changes for your environment.

cSQLConfig can be found in the PowerShell Gallery here –> https://www.powershellgallery.com/packages/cSQLConfig/

xSQLServer can be found in the PowerShell Gallery here –>  https://www.powershellgallery.com/packages/xSQLServer/

DSC opens up many possibilities for DBAs to automate and reduce unwanted change in their environment.

If you are looking to learn more about DSC and how you can leverage it Jeffrey Snover and Jason Helmick have an excellent video on Microsoft Virtual Academy.  Link provided here –>  https://www.microsoftvirtualacademy.com/en-US/training-courses/getting-started-with-powershell-desired-state-configuration-dsc—8672

Hopefully everyone is excited about the opportunities this presents!

Updates to leveraging DSC with the latest resources can be found in he following two posts.

Desired State Configuration(DSC) for SQL Server ***Update

Desired State Configuration(DSC) for SQL Server ***Update 2

Comments

  • Anonymous
    May 02, 2016
    Hello,Where do you get the xSqlPs and xSqlServerInstall modules? My servers don't have Internet access.What versions of SQL Server would these work for?What installation media is required for the LCM DSC push?Should the "BA" in the quote below be "DBA"?"So specifically around SQL Server I can think of a few things which every BA should be interested in automating."
    • Anonymous
      May 02, 2016
      xSqlServer can be downloaded from here --> https://github.com/powershell/xsqlserverCurrently the module supports 2008R2, 2012, 2014. When 2016 officially drops it will be added as well.Installation media needs to be extracted to a central location where DSC can reference.Yes "BA" was a Typo and has been correct thank you.
  • Anonymous
    May 03, 2016
    I tried modifying the LCM Push example to work for my Windows Servers. When I ran it, I got this "The term 'settings' is not recognized as the name of a cmdlet, function, script file or operable program." What am I doing wrong?I am using PowerShell version 4. All of the example scripts requires version 5. Will I be able to use DSC to install SQL Server with version 4?
    • Anonymous
      May 04, 2016
      xSQLServer requires WMF 5 due to someone the credentials which are passed.
  • Anonymous
    April 20, 2017
    There is a huge amount of assumed knowledge in this blog entry, such as configuration data.