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 thesqldsc.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.
target_defaults
block that can assign default roles to targets.sql_script
resource accepts variables that are passed to sqlcmd.exe
.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,
DOMAIN1.targets.hcl
for domain1 (it’s not case-sensitive)DOMAIN2.targets.hcl
for domain2By 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.
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
defaults.hcl
DOMAIN_ONE.defaults.hcl
nodes\node.hcl
nodes\node__instance.hcl
nodes\DOMAIN_ONE\node.hcl
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.
Each target can be assigned multiple roles. Instances in roles can be given identical configuration. Good uses of roles include:
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"]
}
target_defaults
section can define a base set of roles for every target.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
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.
defaults.hcl
roles\prod.hcl
roles\data-center-2.hcl
roles\ag-one.hcl
nodes\node2.hcl
The full rules are
defaults.hcl
DOMAIN.defaults.hcl
nodes\computer.hcl
nodes\computer__instance.hcl
(note the double underscores)nodes\DOMAIN\computer.hcl
nodes\DOMAIN\computer__instance.hcl
And remember that the LAST file procesed overrides the previous files.
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:
sqldsc.exe test --role ag-one
- Test the configuration on all servers assigned the ag-one
role. Assigning all the nodes in an AG to a specific role is a good way to process them all together.sqldsc.exe apply --role dev
- Apply changes do all servers assigned the dev
role.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.
sql_script
usagesql_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.
If you work with multiple domains, go all in on that.
DOMAIN.targets.hcl
and NO targets.hcl
nodes
and NO .hcl
files under the root of nodes
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