Professional Edition

All features described in this document are part of the Professional Edition.

While in beta, you can enable the Professional Edition by creating an empty key.xt file next to the sqldsc.exe file.

The PRO edition will become a paid version. PRO features expire 90 days after the build date. Please see https://www.scalesql.com for updated builds.

What’s New

Domain Targets

The PRO version can use domain specific target files. If you do nothing different, it will continue to use targets.hcl.

You can create domain specific target files. For example,

By default, the PRO version will run for the domain specified in the USERDOMAIN environment variable. This variable should be set for all domain joined computers. SQLDSC will search for a target file for that domain. If it doesn’t find one it will fall back to targets.hcl. If you’re using domain specific target files, you should probably delete targets.hcl or leave it empty.

To run for a differnt domain than the current one, specify a domain using the the --domain parameter. This parameter only applies to the test and apply commands. For example,

sqldsc test --domain other-domain --verbose 

This will process the targets listed in OTHER-DOMAIN.targets.hcl (or targets.hcl if that file isn’t found). Processing for a separate domain is often used to handle multiple domains where a trust relationship and connectivity are available.

Domain Defaults

The PRO version also support domain specific defaults. After processing defaults.hcl, it will look for domain specific defaults.

The order of default files processed is

  1. defaults.hcl
  2. DOMAIN_ONE.defaults.hcl
  3. All role files in order (see below)
  4. nodes\node.hcl
  5. nodes\node__instance.hcl
  6. nodes\DOMAIN_ONE\node.hcl
  7. nodes\DOMAIN_ONE\node__instance.hcl

Remember that later processed files override values in earlier files.

NOTE: The domain targets aren’t strict. You could put reachable targets in DOMAIN_ONE.defaults.hcl that aren’t actually in the domain. When getting the settings files, it looks at the domain the target says it is in (using USER_DOMAIN()) and gets those configurations. Please don’t cross domain lines in this way.

Using Roles

Each target can be assigned multiple roles. Instances in roles can be given identical configuration. Good uses of roles include:

Targets

A targets.hcl file looks something like this

target_defaults {
    roles = ["baseline"]
}

target "D40\SQL2016" {}

target "server1.domain.com" {
    roles = ["baseline", "baseline", "prod"]
}

target "node1.domain.com" {
    roles = ["baseline", "prod", "data-center-1", "ag-one"]
}

target "node2.domain.com" {
    roles = ["baseline", "prod", "data-center-2", "ag-one"]
}

Configuration

The file structure also supports roles. The init command doesn’t create the roles folder.

config\
    nodes\
        DOMAIN1\   
            node4.hcl
        node1.hcl
        node2.hcl
        server1.hcl
    roles\
        dev.hcl
        prod.hcl
        ag-one.hcl
        data-center-1.hcl
        data-center-2.hcl
    defaults.hcl
    targets.hcl
    DOMAIN1.targets.hcl

Precedence Rules

Each of these files can define the same configuration. For example, to configure node2 in this example, the following files would be used. Each lower file overrides the values in the higher file.

  1. defaults.hcl
  2. roles\prod.hcl
  3. roles\data-center-2.hcl
  4. roles\ag-one.hcl
  5. nodes\node2.hcl

The full rules are

  1. Global Defaults: defaults.hcl
  2. Domain Defaults: DOMAIN.defaults.hcl
  3. Roles - The role files in the roles directory in the order specified
  4. Per Computer Defaults (in order):

And remember that the LAST file procesed overrides the previous files.

Filtering

The sqldsc.exe command has a filter for role. When specified, it will only process nodes in that role. This parameter is available for test and apply. Some sample command lines are:

Please be aware, the filtering for roles on decides which computers to target based on the role membership. It will still process all the settings for all the roles for those computers.

Advanced sql_script usage

sql_script also support passing sqlcmd style variables to a script. Consider the following resource block:

resource "sql_script" "vars.sql" {
    script = "vars.sql"
    test_query  = "SELECT 'X' WHERE 1=0;"
    V1 = "first"
    V2 = "1"
}

The fields script and test_query are used for the script. Any other key values are passed as variables to SQLCMD.EXE When paired with this script:

SET XACT_ABORT OFF;
PRINT 'V1: $(V1)'
PRINT 'V2: $(V2)'
GO

generates this command line (with the path omitted for simplicity):

sqlcmd.exe -S "localhost" -i "error.sql" -E -b -X1 -v v1="first" -v v2="1"

This produces the following results:

V1: first
V2: 1

This can often be used to build stored procedures that need to point at different databases in different environments.

Best Practices

If you work with multiple domains, go all in on that.

Common uses for roles include * Separate DEV from PROD * Group all nodes of an AG together * Group servers that are of specific versions * Group servers by language or time zone * Group servers by business unit * Remember that roles can be used to group servers even if there aren’t settings for that role – though it still needs an empty .hcl file though. This can be used to stage large changes. * This can be done by using the --role filter to only process certain servers. For example, you could edit defaults.hcl but then run with --role alpha and it would only process for those servers assigned to role alpha. * Or it could be done by changing the role specific setting file. For example, you could update alpha.hcl and run for all servers. Then you could put those settings in defaults.hcl and remove them from alpha.hcl and run again.


© 2020 ScaleOut Consulting, LLC