SQL Server Desired State Configuration

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

Sample Configuration File

Editing configuration

In this example,

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

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

Releases

Please see the SQLDSC: What’s New for details.

1.43.9 (7 April 2023)

The query command supports --top and --sort parameters
Better handle changes where new databases are created
Better handle disabling domain groups
Improve HTTP_PROXY handling for secure environments
Support purchasing PRO licenses
Add the domain to the audit log
Add usage command to show overall usage
Add servers command to show active servers
Add retire command to retire servers

1.43 (6 June 2022)

PRO: Improve handling of missing databases in login blocks
PRO: Query command supports sorting results
PRO: Added SQL Server Agent job blocks that include jobs, steps, and schedules
PRO: Added domain map to improve filtering speed

1.42.8 (27 December 2021)

Better error messages

1.42.6 (8 November 2021)

Update license file location
Queries support multiple directories. Please see the docs.
Executing queries no longer requires the .sql extension allowing a shorter syntax. For example, sqldsc query dbount runs dbcount.sql.

1.42.3 (14 September 2021)

A login resource block for sa defaults to alter so we don’t try to create the sa login in an Azure SQL Database.
A database resource block for model defaults to alter so we don’t try to create the model database in an Azure SQL Database.
agent_job now defaults to being owned by sa if not specified on creation. This seems like a more reasonable default.

1.41.9 (13 July 2021)

Add proxy server support
PRO Edition licensing now comes from the SQLDSC Portal
The PRO Edition includes beta support for running a raw SELECT query against each target

1.40.7 (8 June 2021)

BREAKING CHANGE: How roles are processed has changed. Please see PRO: Roles.
Roles now support nesting. This allows a role to include additional roles. For example, an Availibility Group role can include all the needed roles rather than defining them for each node.
login blocks allow a default_schema to be set for database and user_databases blocks
sqldsc list lists all the targets and their roles after applying filters
Early support for case-sensitive servers and databases
Added for_versions filter for resource blocks. Please see PRO: Filter for Versions for details.
New databases default to being owned by sa unless an owner is specified
Job schedules can now have their start time “jittered” so they don’t all start at the same time. Please see Agent Resource Blocks
database resource blocks can now set the database compatibility level. A value of -1 matches the server version.
Logging SQL statements now captures the database name
Improved handling of EXEC vs. EXECUTE in GRANT statements
SSISDB is considered a system database (i.e. NOT a user database)

1.37.18 (6 February 2021)

Improved SQL Server Agent job schedules and steps including weekly jobs
Can capture_output of job steps
Made field names for Logins consistent in login blocks
Added agent resource block to set job history step retention

1.37 (10 November 2020)

Please see the SQLDSC Documentation What’s New for details.

Resource blocks now merge instead of overwriting previous blocks. Please see Resource Block Precendence
Added user_databases blocks to the PRO Edition
Added login blocks to the PRO Edition
Added variables to the PRO Edition
BREAKING: Changed how roles are processed
BREAKING: Changed server role members and server permissions
Added support for schemas and schema permissions

1.34 (10 September 2020)

Please see the SQLDSC Documentation What’s New for details.

Start support for Azure “SQL Databases”.
Added limited support for SQL Server Agent Jobs.
Added support for Database Roles.
The application logging format has changed to make it easier to read.
Targets support SQL Server logins.
Targets can define roles which override the default Roles or added_roles which add to the default Roles.
Both test and apply stop processing on errors. Originally they would stop the current target but process other targets. However the safest action is to stop immediately if there’s a problem. If the test is running multi-threaded, each thread will finish its target but won’t start new a one.
Testing a password in the Vault can test both passwords and password hashes.

1.32.2 (24 June 2020)

New documentation site at docs.sqldsc.com
Added a --parallel flag for the PRO. This sets the number of testing threads equal to the number of targets. Applying is always done sequentially.
The application checks if a user is in the sysadmin role. If they are not, the target is skipped. This better supports running with two different accounts.
The PRO edition can configure the multi-threaded settings using a settings file. Please see the Application Settings section.
The application can update itself by running sqldsc update. If there is a newer version, it will prompt you to install it.
sqldsc init will create a .vscode directory with the suggested extensions.
Improved handling of SQL Server logins. See SQL Server Logins for details.
- Capture and reuse SIDs
- Store hashed passwords in the Credential Vault
- Get the SID and hashed password from an existing login
If a database isn’t writeable, any changes for that database are silently skipped.

1.30.1 (20 May 2020)

Documentation and bug fixes
Only apply to changed nodes
--threads option to run tests in parallel if you enable PRO

1.25 (28 April 2020)

sqldsc info prints a summary table that lists the target files and target counts and the resource files and resource counts

1.24.1 (26 April 2020)

Logging is now is written to individual files in a dir tree: YYYY/sqldsc_YYYY_01_Domain_User.log. This should prevent duplicates and eliminate the need to merge files.

1.23 (24 April 2020)

Cleaned up logging. There are now three levels that provide increased detail: Verbose, then Debug, then Trace. Verbose is really just printing the SQL statements.
Added expected_rows to the sql_script resource. If the test_query returns a different number of rows than this, the script will be run.
Added properties for target for dial timeout and connect timeout.
Added a target_defaults block to set defaults for all targets in the file.

1.22 (22 April 2020)

Resource Blocks now overwrite previous definitions. Prior to this they would be merged. It’s just too complicated to figure out where different settings come from in files. For example, assume you define a database resource for a “DBA” database in defaults.hcl. Then in a node-specific configuration file, you define a “DBA” database with a different set of properties. The later more specific resource will now completely overwrite the newer resource. That newer resource will then be applied to the database.

1.21 (19 April 2020)

Assort package updates and bug fixes

1.20.1 (17 April 2020)

Added an object_permission resource to set permissions on database objects

1.18 (12 April 2020)

The sql_file resource has been renamed to sql_script. Please rename the sql directory directory to scripts. The attribute names have also changed. Please see the schema document.
If no ensure attribute is found, default to present.
For logins, if no name attribute is found, default to the Resouce Name.
For agent operators, if no name attribute is found, default to the Resouce Name.
For databases, if no name attribute is found, default to the Resouce Name.
For configurations, if no name attribute is found, default to the Resouce Name.

1.17 (9 April 2020)

Add database_permission resource blocks
Fix messages on existing user when changing database owner
Generate error if the same resource is defined twice in a single file
Moved the Resource Block schema to a separate document
In the sql_file resource, the file name is just file instead of file_name

1.16 (4 April 2020)

Add database_role_member
Clean up attribute names so that a “_name” suffix isn’t requried for SQL Server objects such as logins and roles
If you experience connectivity issues, please contact me

1.0.15 (24 March 2020)

Added vault to store encrypted passwords locally
Added attributes to the login resource including the SID, expiration check, policy check, and default database.
Added a sql_file resource to create common procedures or objects on servers
Added a database_user resource
Added a syntax check for .hcl files at the start of processing
Switched SQL Server drivers. If you experience connectivity issues, please contact me

1.0.14 (15 March 2020)

Moved the audit logs to a logs directory
Allow single backslashes in server and login names
A Test is run before Applying unless you specify the –confirm flag