Resource Block Schema #
Each resource block MAY have an ensure
attribute. The possible values are:
present
- If the resource doesn’t exist, create it. If it exists, alter it to match the resource block.absent
- If the resource exists, remove italter
- If the resource exists, alter it. If it doesn’t exist, do nothing.
If no ensure
attribute is found, it defaults to present
Server Resource Blocks #
The following resource block types are supported
configuration
name
(string - optional. If not provided, it defaults to the resource name) Any valid name fromsys.configurations
whereis_dynamic
= 1. Common value are:backup compression default
clr enabled
cost threshold for parallelism
max degree of parallism
max server memory (MB)
max text repl size (B)
optimize for ad hoc workloads
xp_cmdshell
value
(int32 - required)- Configuration entries can’t be created or deleted. Ensure
present
is treated likealter
. Ensureabsent
isn’t valid.
login
name
(string - optional starting in 1.18. If not provided, it defaults to the resource name.login
may be used in place ofname
.)disabled
(true/false)sqldsc_credential
(string - required for SQL Server logins) - the local saved vault entry use to set the passworddefault_database
(string)check_policy
(bool)check_expiration
(bool)sid
- (string - only for SQL Server logins)- Ensure defaults to
present
except for this pattern:resource "login" "sa" {}
. If we are on Azure or thesa
login has been renamed, it won’t try to recreate it. This is often used in the caseresource "login" "sa" {disabled = true}
which disablessa
everywhere it finds it.
server_permission
login
(string - required)permission
(string - required)
server_role_member
login
(string - required)role
(string - required)
sql_script
- starting in 1.41.8, this requires the PRO Editiontest_query
– SQL query that returns rows if the script has already run. If the queryexpected_rows
, the SQL file specified infile
will be run.expected_rows
(int - optional) Defaults to 1. If provided, this is how many rows thetest_query
is expected to return. If not provided, the script will only run if the test_query doesn’t return one row.script_file
– file to look for in thescripts
directory. This file is run usingsqlcmd.exe
. Prior to 1.17 this was namedfile_name
.ensure
–present
will run the file if no rows are returned bytest_query
. All other values are an error.database
– run the script in this database- The ‘
--push file.sql
’ parameter will always run a script if passed on the command line. This useful for situations where you want to update a common stored procedure across servers. Typically this is used to put something like sp_whoisactive on servers.
Database Resource Blocks #
database
name
(string - optional starting in 1.18. If not provided, it defaults to the resource name.database
may be used in place ofname
.)owner
(string) - this defaults tosa
for new databasesrecovery_model
(string - full/simple/bulk_logged)rcsi
(true/false) read committed snapshot enabledcompatibility_level
(int) database compatibility level (100, 110, 120, etc.). A value of -1 matches the version of the server.
database_user
database
(string - required)user
(string - required)default_schema
(string) - the default schema for this user. Prior to 1.18 this was namedschema
.
database_role
database
(string - required)role
(string - required)owner
(string - optional - defaults to ‘dbo’)
database_role_member
database
(string - required)role
(string - required)user
(string - required)
database_permission
database
(string - required)user
(string - required)permission
(string - required)
schema
schema
(string - required)database
(string - required)owner
(string - optional - defaults to ‘dbo’)
schema_permission
schema
(string - required)database
(string - required)user
(string - required - user or role to grant or revoke permission)permission
(string - required - EXEC, SELECT, etc.)
object_permission
sets a permission on a database objectdatabase
(string - required)schema
(string - required)object
(string - required)user
(string - required)permission
(string - required)
Agent Resource Blocks (BETA) #
These are enabled in the beta builds. This release only supports single-step TSQL jobs.
agent
This sets the SQL Server Agent properties (starting in 1.37.15)max_history
(int - optional) Maximum size of the jog history log (in rows). -1 is unlimited.max_job_history
(int - optional) Maximum job history rows per job. Must be less and or equal tomax_history
.
agent_operator
name
(string - optional starting in 1.18. If not provided, it defaults to the resource name)email
(string - required)
agent_job
- This is primarily used to remove old jobs. Jobs should be defined using the Job Blockjob
(string - optional. If not provided, it defaults to the resource name)enabled
(bool - optional. Defaults to true)owner
(string - optional. Defaults tosa
)notify_on
(string - optional -never
|success
|failure
|completed
) Send a notification email based on this.operator
(string - optional) Name of the operator to receive the email
Breaking Changes #
- Starting in 1.37.14, the Job Step Name field is
step
and the Job Schedule Name field isschedule
. - Starting in 1.35, the
login_name
attribute is replaced bylogin
and therole_name
attribute is replace byrole
. This affectsserver_role_member
andserver_permission
. - In the
sql_script
resource, the file is now specified in thescript_file
field.script
is also supported but will be removed in a future release. - The name of the resource was changed from
sql_file
tosql_script
in 1.18 (12 April 2020) - Resource Blocks now overwrite previous definitions. Prior to this they would merge. It’s just too complicated to figure out where different settings come from in files in 1.22 (22 April 2020)