I did a couple of other posts on Elastic Jobs.
- How to set them up manually: https://sqlkitty.com/elastic-jobs-azure-sql-db/. Use this link to better understand how Elastic Jobs goes together. It will help you understand the Terraform a lot better.
- How to set them up with Bicep: https://sqlkitty.com/bicep-elastic-jobs/
But now I want to set them up with Terraform. This aligns with how we create infrastructure where I work.
TL;DR check out my Github Repo.
Creating Prerequisites
If you aren’t familiar with Terraform and its requisite bits and pieces, I have a post that walks you through them. Let’s hit the ground running. To create Elastic Jobs with Terraform, you need to use the azapi_resource, but before we go down that path, you will use the azurerm resource to create some supporting objects.
Azure SQL Server/Database
Depending on whether you have an elastic pool or want one, you can leave out the pool part. However, you will need an Azure SQL db to hold the jobs in the elastic agent. So here, I will create a pool and a server. You can put the db on an existing server in an existing resource group, too, if you want. I’m creating a new resource group to delete after testing, an Azure SQL Server, an elastic pool, and an Azure SQL db. You must have the SQL db, you can use existing resources for everything else.
Note: You will need at least a capacity of 50 in an elastic pool or an S1 if using a database outside of a pool. These are the minimum requirements for an Elastic Agent with an Azure SQL Database.
resource "azurerm_resource_group" "elasticjobrg" {
location = var.resource_group_location
name = "elasticjobrg"
}
resource "azurerm_mssql_server" "server" {
name = "elastic-${azurerm_resource_group.elasticjobrg.name}"
resource_group_name = azurerm_resource_group.elasticjobrg.name
location = azurerm_resource_group.rg.location
version = "12.0"
administrator_login = "adminuser"
administrator_login_password = "password@123!"
azuread_administrator {
login_username = "yourgroup/user"
object_id = "itsobjectid"
}
}
resource "azurerm_mssql_elasticpool" "example" {
name = "sqlelasticpool"
resource_group_name = azurerm_resource_group.elasticjobrg.name
location = azurerm_resource_group.rg.location
server_name = azurerm_mssql_server.server.name
sku {
name = "StandardPool"
tier = "Standard"
capacity = 50
}
max_size_gb = 50
per_database_settings {
min_capacity = 0
max_capacity = 50
}
}
resource "azurerm_mssql_database" "database" {
name = "dbelastic-${azurerm_resource_group.elasticjobrg.name}"
server_id = azurerm_mssql_server.server.id
collation = "SQL_Latin1_General_CP1_CI_AS"
/*
this is getting set with the pool
sku_name = "S1"
max_size_gb = 10 # Adjust this value as needed
*/ elastic_pool_id = azurerm_mssql_elasticpool.example.id
}
resource "azurerm_mssql_firewall_rule" "azure-services-rule" {
name = "allow-azure-services"
server_id = azurerm_mssql_server.server.id
start_ip_address = "0.0.0.0"
end_ip_address = "0.0.0.0"
depends_on = [
azurerm_mssql_server.server
]
} So now we have the basics setup, so we can create the agent and jobs.
Creating a Managed Identity
We also need a managed identity so the Elastic Job Agent can access the databases, including its own db and any databases you want to run jobs against.
resource "azurerm_user_assigned_identity" "managed_identity" {
name = "ElasticAgentJobsManagedID"
location = azurerm_resource_group.rg.location
resource_group_name = azurerm_resource_group.elasticjobrg.name
}Then, we need to add it to our db. I’m giving it owner as I want it to read/write and execute stored procs. I trust it’s not going to be used for elevated purposes. I’m going to have it run Ola stats updates and index maintenance. You might wonder why it needs db_owner, and I wondered about this, too. I tried with lower perms, but it couldn’t see certain tables. I needed it to see with lesser perms, which was very odd. Note you need to connect with an AD (Entra) account to add this, you can’t add it if you are logged in as the server admin.
CREATE USER ElasticAgentJobsManagedID FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER ElasticAgentJobsManagedID;You will also need the Ola scripts in whatever databases you want to run the job in.
You can get these from Ola directly.
Creating the Elastic Job Agent
Here’s where we need to add azapi_resource to the providers. For your reference, here is my providers.tf file.
terraform {
required_version = ">=0.12"
required_providers {
azurerm = {
source = "hashicorp/azurerm"
version = ">= 3.74.0"
}
azapi = {
source = "Azure/azapi"
}
}
}
provider "azurerm" {
features {
}
}
provider "azapi" {
}
Now, I will create the Elastic Job Agent.
/*
this will create elastic jobs agent with default JA100 setting
*/
resource "azapi_resource" "elasticjobagent" {
type = "Microsoft.Sql/servers/jobAgents@2023-05-01-preview"
name = "elasticagent-${azurerm_resource_group.elasticjobrg.name}"
location = azurerm_resource_group.rg.location
parent_id = azurerm_mssql_server.server.id
identity {
type = "UserAssigned"
identity_ids = [azurerm_user_assigned_identity.managed_identity.id]
}
body = jsonencode({
properties = {
databaseId = azurerm_mssql_database.database.id
}
})
}I will also need to set up a target group and add servers and/or databases to it, so those will have jobs executed against them. You can include or exclude members. I have more info on that in the other posts referenced at the top of this post. Microsoft provides guidance on the parameters in members here.
resource "azapi_resource" "elasticjobstargetgroups" {
type = "Microsoft.Sql/servers/jobAgents/targetGroups@2023-05-01-preview"
name = "AzureSQLDBs"
parent_id = azapi_resource.elasticjobagent.id
body = jsonencode({
properties = {
members = [
{
/* use this if your db is in an elastic pool */
elasticPoolName = azurerm_mssql_elasticpool.example.name
membershipType = "Include"
type = "SqlElasticPool"
/ * if no elastic pool, use only serverName */ serverName = azurerm_mssql_server.server.name
}
]
}
})
}
Creating Jobs
We are job creators! Or at least creators of elastic jobs.
This will create a job that will run once a day at 23:00 UTC.
resource "azapi_resource" "job" {
type = "Microsoft.Sql/servers/jobAgents/jobs@2023-05-01-preview"
name = "OlaStatsUpdateJob"
parent_id = azapi_resource.elasticjobagent.id
body = jsonencode({
properties = {
description = "Runs ola stats update only on all dbs in the target group"
schedule = {
enabled: true
startTime: "2024-04-16T23:00:00Z"
endTime: "9999-12-31T11:59:59Z"
interval: "P1D"
type: "Recurring"
}
}
})
}And this will add two steps to it. Note that you don’t specify the step number on the additional steps after step 1 because it will error out. If you add a stepid to any step after the first one, you will get the error:
Also, don’t mess around with the spacing on the SQL in the value parameter. It never works out well for me. Yes, it looks messy, but if I rearrange it, it tends to break the job.
resource "azapi_resource" "jobstep1" {
type = "Microsoft.Sql/servers/jobAgents/jobs/steps@2023-05-01-preview"
name = "OlaStatsUpdateStep"
parent_id = azapi_resource.job.id
body = jsonencode({
properties = {
action = {
source = "Inline"
type = "TSql"
value = "EXECUTE [dbo].[IndexOptimize]n @Databases = 'USER_DATABASES' ,n @FragmentationLow = NULL ,n @FragmentationMedium = NULL ,n @FragmentationHigh = NULL ,n @UpdateStatistics = 'ALL' ,n @LogToTable = 'Y';"
}
stepId = 1
targetGroup = azapi_resource.elasticjobstargetgroups.id
}
})
}
resource "azapi_resource" "jobstep2" {
type = "Microsoft.Sql/servers/jobAgents/jobs/steps@2023-05-01-preview"
name = "OlaCommandLogCleanupStep"
parent_id = azapi_resource.job.id
body = jsonencode({
properties = {
action = {
source = "Inline"
type = "TSql"
value = "DELETE FROM [dbo].[CommandLog]n WHERE StartTime <= DATEADD(DAY, -30, GETDATE());"
}
targetGroup = azapi_resource.elasticjobstargetgroups.id
}
})
}That’s all there is to it. I know it might seem like a lot at first glance, but if you’ve set up an Elastic Job Agent and jobs before, it’s not too bad, or if you are familiar with Terraform, it’s quite straightforward.
The post Terraform Elastic Jobs appeared first on sqlkitty.