What's New

What’s New #

Starting in 1.31, the application can update itself by running sqldsc update.

1.43 (23 May 2022) #

  • PRO: The query command supports sorting the results using the --orderby field_name flag

1.42.16 (19 May 2022) #

  • PRO: Target roles are now target tags. See the Tags section for more details.

1.42.15 (12 May 2022) #

  • Better handling of nested roles. Run sqldsc list to see all the targets and roles.
  • Better handling of missing databases in login blocks. If a database specified in a login block doesn’t exist, it is skipped. This is helpful for databases like SSISDB that are system databases but rarely exist.
  • Added a domain map so that targets can remember what domain they are in. This helps with filtering for queries and such.

1.42.7 (18 November 2021) #

  • The expected_rows handling is improved for SQL Scripts. Please see the documentation.

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) #

1.40.7 (8 June 2021) #

  • 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.
  • We’re getting close to real, actual licenses so PRO features expire 60 days after the build date.
  • Renamed the sids.0.csv file that holds the login-SID mapping to sids.sidmap. This will let me do things with git and CSVs later.
  • 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)
  • sql_script blocks now require the PRO Edition

1.40.2 (23 May 2021) #

  • New databases default to being owned by sa unless an owner is specified
  • Updated server_list.ndjson to include a hash has that will be used in licensing

1.40.1 (22 April 2021) #

  • Added for_versions filter for resource blocks. Please see PRO: Filter for Versions for details.
  • Improved various messages
  • The app writes a server_list.ndjson file to log all servers tested in the state directory

1.39.4 (13 April 2021) #

  • BREAKING CHANGE: How roles are processed has changed. Please see 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.
  • Case-sensitive servers now pass the full standard test suite but there a limited tests for case-sensitivity. Please report any errors using the scaleSQL Contact Form
  • sqldsc init now creates the roles directory
  • sqldsc list lists all the targets and their roles after applying filters

1.38.4 (4 April 2021) #

  • Early support for case-sensitive servers and databases

1.38.1 (10 February 2021) #

  • login blocks allow a default_schema to be set for database and user_databases blocks.

1.37.18 (6 February 2021) #

  • Minor formatting fixes

1.37.17 (20 December 2020) #

  • Worked on fixing the info command to handle the new parsing code.

1.37.15 (16 November 2020) #

  • 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.11 (11 November 2020) #

1.36.2 (10 October 2020) #

  • BREAKING CHANGE: How roles are processed has changed. Please see Roles

1.35.1 (18 September 2020) #

  • Improve handling of resource blocks
  • Breaking changes to server_role_member and server_permission schemas. Please see Resource Block Schema

1.35 (14 September 2020) #

  • Add support for schemas
  • Add support for schema permissions

1.34 (10 September 2020) #

  • Start support for Azure “SQL Databases”. Please see the Azure section. Only those resources listed in the Azure section have been tested against Azure SQL Databases.
  • Add limited support for SQL Server Agent Jobs. Please see the Resource Block Schema section.
  • Add support for Database Roles. Please see the Resource Block Schema section.

1.32.5 (4 July 2020) #

  • The application logging format has changed to make it easier to read. Please install the NDJSON Colorizer to better view the logs. There’s also a way to dump logs for inclusion in change control documents. See Logging
  • Targets support SQL Server logins. Please see the section on Targets
  • Targets can define roles which override the default Roles or added_roles which add to the default Roles. Please see Roles for more details.
  • 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 (22 June 2020) #

  • 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.31.2 (19 June 2020) #

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

1.31 (31 May 2020) #

  • Add a --parallel flag for the PRO. This sets the number of testing threads equal to the number of targets. Applying is always done sequentially.

1.27 (5 May 2020) #

  • Better permissions when you init a new directory structure
  • Improved error handling and reporting

1.26 (4 May 2020) #

  • Previously, when running, sqldsc apply, all servers are first tested, then all servers go through the apply phase. Now, only those servers that indicate a change will go through the apply phase. This can be overridden with the --applyall flag. This flag forces all servers to go through the apply phase.
  • If the PRO features are enabled (see the README_Pro), the --threads flag will allow you to set the number of concurrent threads that will be used for the test phase. The apply phase is always done single-threaded.

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.
  • Add expected_rows to the sql_script resource. If the test_query returns a different number of rows than this, the script will be run.
  • Add properties for target for dial timeout and connect timeout.
  • Add 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) #

  • Assorted bug fixes and package updates

1.20.1 (17 April 2020) #

  • Add 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 resource blocks
  • Clean up attribute names so that a “_name” suffix isn’t requried for SQL Server objects such as logins and roles

1.0.15 (24 March 2020) #

  • Add vault to store encrypted passwords locally
  • Add attributes to the login resource including the SID, expiration check, policy check, and default database.
  • Add a sql_file resource to create common procedures or objects on servers
  • Add database_user resource
  • Add 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

1.0.12 (14 March 2020) #

  • The message and SQL script are printed before executing
  • Important strings are trimmed
  • Switched the instance filter to a target filter

1.0.11 (8 March 2020) #

  • Add server_role_member resource
  • Add domain filtering for Professional edition
  • Add domian targets for Professional edition
  • Switch to defaults.hcl

1.0.10 (3 March 2020) #

  • Add role filtering for Professional edition
  • Add instance filtering