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 it
• alter - 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 from sys.configurations where is_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 like alter. Ensure absent 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 of name.)
  • disabled (true/false)
  • sqldsc_credential (string - required for SQL Server logins) - the local saved vault entry use to set the password
  • default_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 the sa login has been renamed, it won’t try to recreate it. This is often used in the case resource "login" "sa" {disabled = true} which disables sa everywhere it finds it.
• server_permission
  • login (string - required)
  • permission (string - required)
• server_role_member
  • login (string - required)
  • role (string - required)
• linked_server - Please see the Linked Server page.
• linked_login - Please see the Linked Server page.
• sql_script - starting in 1.41.8, this requires the PRO Edition
  • test_query – SQL query that returns rows if the script has already run. If the query expected_rows, the SQL file specified in file will be run.
  • expected_rows (int - optional) Defaults to 1. If provided, this is how many rows the test_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 the scripts directory. This file is run using sqlcmd.exe. Prior to 1.17 this was named file_name.
  • ensure – present will run the file if no rows are returned by test_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 of name.)
  • owner (string) - this defaults to sa for new databases
  • recovery_model (string - full/simple/bulk_logged)
  • rcsi (true/false) read committed snapshot enabled
  • compatibility_level (int) database compatibility level (100, 110, 120, etc.). A value of -1 matches the version of the server.
• query_store
  • size_mb (int)
  • size_map (see below)
• database_user
  • database (string - required)
  • user (string - required)
  • default_schema (string) - the default schema for this user. Prior to 1.18 this was named schema.
• 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 object
  • database (string - required)
  • schema (string - required)
  • object (string - required)
  • user (string - required)
  • permission (string - required)

Query Store #

The Query Store Resource Block allows you to set the desired size of the Query Store. The simplest format below sets the size to 100 MB.

resource "query_store" "db_name" {
    size_mb = 100
}

A better way to dynamically set the size using a size_map. This sets the size of the Query Store based on the size of the database. The result of this

• A database less than 100 MB won’t have Query Store enabled.
• A database at least 100 MB but less than 200 MB, will have a 10 MB Query Store
• A database 200 MB or greater will have a 20 MB query store. This should keep the Query Store size from changing too often but still make it dynamic.

resource "query_store" "db_name" {
    size_map = {
                    "100mb": "10mb",
                    "200 mb": "20mb"
                }
}

An even better way is to use the user_databases block in PRO vesion to set Query Store for all databases. This sets the Query Store size for each user database based on the size of that database.

user_databases {
    resource "query_store" "dummy" {
        size_map = {
                        "100mb": "10mb",
                        "200 mb": "20mb"
                    }
    }
}

Query Store Resource Blocks merge differnetly than most blocks. If a Resource Block defines a size map, and then a later Resource Block sets a specific size, only the later value will be applied.

Agent Resource Blocks #

The Regular Edition only supports single-step TSQL jobs. The Professional Edition supports more complicated 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 to max_history.
• agent_operator
  • name (string - optional starting in 1.18. If not provided, it defaults to the resource name)
  • email (string - required)
• agent_alert supports setting alerts based on error number or severity. It doesn’t support performance conditions.
  • name (string - optional that defaults to the resource name)
  • enabled (bool - optional that defaults to true)
  • message_id (int) Message number that defines the alert. Either message_id or severity must be specified.
  • severity (int) Severity that defines the alert. Either message_id or severity must be specified.
  • delay_between_responses (string - optional) Must be a duration in the format “5m” or “3m30s”
  • notification_message (string - optional) Message to appear as part of the alert. Limited to 512 characters.
  • include_event_description (string - optional that defaults to true)
  • job_name (string - optional) Job to run in repsonse to alert
  • email_operators (string array - optional) List of operators to email when the alert fires
• agent_job - This is primarily used to remove old jobs. Jobs should be defined using the Job Block
  • job (string - optional. If not provided, it defaults to the resource name)
  • enabled (bool - optional. Defaults to true)
  • owner (string - optional. Defaults to sa)
  • 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 is schedule.
• Starting in 1.35, the login_name attribute is replaced by login and the role_name attribute is replace by role. This affects server_role_member and server_permission.
• In the sql_script resource, the file is now specified in the script_file field. script is also supported but will be removed in a future release.
• The name of the resource was changed from sql_file to sql_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)