SQL Server Desired State Configuration

SQL Server Desired State Configuration #

Easily standardize SQL Server settings based on version controlled configuration files.

In this example,

  • Set the model database to SIMPLE recovery
  • Disable the sa login
  • Set the cost threshold for parallelism to 50

Sample Configuration File #

Editing configuration

Applying the Configuration #

Applying the configuration

Features #

  • Test your configuration and find out what changes would be made
  • Target multiple SQL Servers
  • Default settings that can be overridden for each server
  • Configuration objects include
    • All sp_configure settings including disabling xp_cmdshell, maximum memory, cost threshold for parallelism, etc.
    • SQL Server and Domain logins
    • Server permissions
    • Server role membership
    • SQL Server Agent operators
    • Databases including owner, recovery model, and Read Committed Snapshot Isolation
    • Database users
    • Database role members
    • Database permissions
    • Running SQL Server script files for more complicated situations

Supported Versions #

  • SQL Server 2019
  • SQL Server 2017
  • SQL Server 2016
  • SQL Server 2014
  • SQL Server 2012
  • Azure SQL Databases – See the section on Azure Support
  • SQL Server 2008 - Many things work but isn’t tested or supported