PRO: Jobs (BETA)

All features described on this page are part of the Professional Edition.

You can purchase licenses for the Professional Edition on the Portal: https://portal.sqldsc.com.

SQL Server Agent Jobs (BETA) #

These can be created using a job block. A simple job might look like this:

job "TestJob" {
    step "update table" {
        database = "Reporting"
        command = <<EOF
            UPDATE dbo.HeartBeat SET LastProcessed = SYSDATETIMEOFFSET();
        EOF
    }

    schedule "every minute" {
        frequency = "daily"
        interval = "1m"
    }
}

This creates a job named “TestJob” that runs every minute and updates the HeartBeat table in the Reporting database.

The primary goal of this feature is simple maintenance jobs that need to be identical across many servers.

Schedule Options #

Notes #

  1. IMPORTANT: Adding and removing steps, changing step order, and multiple schedules gets complicated QUICKLY. SSMS often just drops and recreates jobs if it detects certain types of changes. SQLDSC does the same. If it detects any changed value, the job and associated schedules are dropped and recreated.

  2. IMPORTANT: A job can only be defined once. Jobs can’t override jobs defined earlier. See Variable Start Times below for options on different servers running the same job at different times.

  3. The default owner of a job is “sa”

  4. Jobs are enabled by default

  5. A job can be dropped using an agent_job resource block:

    resource "agent_job" "old_job" {
    ensure = "absent"
}

Variable Start Times #

Since a job can only be defined in one place, all SQL Servers will run the job at the same time. This may put undue stress on the supporting infrastructure. There are two options to change this.

Jitter #

A schedule can have a “jitter” defined.

schedule "daily" {
    frequency = "daily"
    start_time = "19:00:00"
    jitter = "4h"
}

The jitter is a duration in minutes and/or hours. For example, “60m”, “4h”, “4h30m”. Based on a hash of the domain, computer, and instance, the start time of each job will be adjusted within the jitter duration. In the example, the earliest a job would start is 7pm and the latest is 7pm plus “4h” or 11pm. Ideally the start times are spread evenly across the servers.

Variables #

A job can also have a variable for the start time. default.hcl might define a job and default start time like this:

variable "index_start_time" {
    value = "22:00:00"
}

schedule "daily" {
    frequency = "daily"
    start_time = "${index_start_time}"
}

That makes all the jobs start at the same time. In a definition for a domain, role, or node, that variable value can be redefined (overridden). An example might be all the servers should reindex at 10pm except this really big one that starts at 1am.

Job Block Schema #

Jobs can have multiple steps and schedules:

job "SampleJob" {
    step "one" {}
    step "two" {}
    step "three" {}

    schedule "catch up" {
        frequency = "once"
    }
    schedule "daily" {}
}

The steps are created in the order defined. By default, each step fails the job on step error and continues the job on step success; except the last, which succeeds the job on the last step’s success.

Job Block #

  • enabled (bool - optional. Defaults to true)
  • owner (string - optional. Defaults to sa)
  • description (string - optional) These can be multiline strings. The text will be “dedented” (consistent leading white space removed) so format it so it looks good in the HCL.
  • notify_on (string - optional - never|success|failure|completed) Send a notification email based on this.
  • operator (string - optional) Name of the operator to receive

Job Step #

  • command (string) These can be multiline strings. The text will be “dedented” (consistent leading white space removed) so format it so it looks good in the HCL.
  • database (string - optional) The database where the command will run. This defaults to master.
  • capture_output (bool) Capture job step output to the database. Checks “Log to table” and “Include step output in history” in the job step. Sets @flags = 12.
  • on_success (string - optional) Determines what happens if the step completes successfully. Defaults to next for all steps except the last which defaults to success. Valid values are:
    • next: continue with next step
    • succcess: Quit with success
    • failure: Quit with failure
  • on_failure (string - optional) Determines what happens if the step fails. Defaults to failure. Typically this isn’t specified. Valid values are:
    • next: continue with next step
    • succcess: Quit with success
    • failure: Quit with failure
  • subsystem (string - optional. Defaults to TSQL. See sp_add_jobstep for values)
  • retry_attempts (int - optional. Defaults to 0)
  • retry_interval (int - optional. Defaults to 0) Interval between retry attempts in minutes.

Job Schedule #

  • frequency (string - required - once | daily | weekly)
  • interval (string - optional) “1h”, 2m”, “10s” Daily or weekly jobs with no interval will run once per day at the start_time.
  • start_date (string - optional) “2020-01-15”
  • end_date (string - optional) “2020-01-16”
  • start_time (string - optional) “13:14:15”
  • end_time (string - optional) “13:14:22”
  • days (string array) if frequency is weekly, this is required. Example: ["mon", "tues", "friday"]
  • jitter (string - optional) For example, “60m”, “4h”, “4h30m”. This allows job schedules to be spread over a window. The start time of the job will be set between start_time and start_time plus jitter based on the hash of the domain, computer, and instance. This will the allow same job on different servers running at different times.