Elastic Jobs on SQL Server Azure

6 min readNov 25, 2024

SQL Server on Azure offers a vast array of powerful tools and services that enhance the performance, scalability, and management of databases in the cloud. One such tool is Elastic Jobs, which plays a critical role in automating and managing routine database tasks across multiple databases at scale. Elastic Jobs are a part of the Azure SQL Database service and are designed to simplify complex operations on numerous databases, such as performing scheduled maintenance tasks, running queries, or collecting telemetry data.

Elastic Jobs allow you to run jobs on multiple Azure SQL databases at once, without the need for manual intervention on each individual database. These jobs are server-level tasks designed to automate and streamline routine operations, making it easier to manage large-scale SQL deployments.

Elastic Jobs are designed to handle tasks like:

Performing index maintenance (rebuilding or reorganizing indexes) across multiple databases.
Running custom T-SQL queries on databases at scale.
Monitoring and diagnostics, such as collecting performance data.
Performing backups or cleanup operations.
Data consistency checks or automated health checks across databases.

This is especially useful for organizations that manage multiple databases in Azure SQL Database, as they can automate these tasks and ensure they are consistently applied across all databases, saving time and reducing human errors.

When working with on premises server and databases, we use the SQL server agent to schedule our jobs; we use the agent for SSIS packages, reports, running maintenance plans, running stored procedures, and much more. However, we note that in azure database, there is no SQL server agent. To create jobs in a azure database, or elastic pools, or managed instances, we use elastic pool scripts. These scripts can be PowerShell or T-SQL scripts. In our examples I will demonstrate the use of TSQL as are more familiar with T-SQL. But first we will create or resourcer on Azure Cloud using Az-Powershell Cmdlet.

What will we do?

Step 1: Create a jobs database
Step 2: Create 2 logical server with 2 databases in each server
Step 3: Create elastic job agent
Step 4: Create credentials on jobs server jobs database
Step 5: Create a target group (servers) and Add containing server(s) to a target group
Step 6: Add a step for job and Add a job
Step 7: Run a job
Step 8: View job status
Step 9: Adding another Database

Our final solution, is gonna be something like that:

Let’s dirty our hands!

Step 1: Create a Job Database

#Creating a logical server and firewall access
$parameters = @{
    ResourceGroupName = '{Your_ResourceGroup}'
    ServerName = '{Your_ServerName}'
    Location = '{Your_ResourceLocation}'
    SqlAdministratorCredentials = $cred
}
New-AzSqlServer @parameters

$parameters = @{
    ResourceGroupName = '{Your_ResourceGroup}'
    ServerName = '{Your_ServerName}'
    FirewallRuleName = 'AllowedIps'
    StartIpAddress = '0.0.0.0'
    EndIpAddress = '0.0.0.0'
}
New-AzSqlServerFirewallRule @parameters

#Creating a Job database with pricing tier
$parameters = @{
    ResourceGroupName = '{Your_ResourceGroup}'
    ServerName = '{Your_ServerName}'
   DatabaseName = 'jobsdb'
   RequestedServiceObjectiveName = 'S0'
}
New-AzSqlDatabase @parameters

Step 2: Create 2 logical server with 2 databases in each server

Target Server 1 with 2 databases:

#TargetServer1
$parameters = @{
    ResourceGroupName = '{Your_ResourceGroup}'
    ServerName = 'targetserver1'
    Location = '{Your_ResourceLocation}'
    SqlAdministratorCredentials = $cred
}
New-AzSqlServer @parameters

$parameters = @{
    ResourceGroupName = '{Your_ResourceGroup}'
    ServerName = 'targetserver1'
    FirewallRuleName = 'AllowedIps'
    StartIpAddress = '0.0.0.0'
    EndIpAddress = '0.0.0.0'
}
New-AzSqlServerFirewallRule @parameters

#Db1 from TargetServer1
$parameters = @{
    ResourceGroupName = '{Your_ResourceGroup}'
    ServerName = 'targetserver1'
   DatabaseName = 'targetdb1'
   RequestedServiceObjectiveName = 'BASIC'
}
New-AzSqlDatabase @parameters

#Db2 from TargetServer1
$parameters = @{
    ResourceGroupName = '{Your_ResourceGroup}'
    ServerName = 'targetserver1'
   DatabaseName = 'targetdb2'
   RequestedServiceObjectiveName = 'BASIC'
}
New-AzSqlDatabase @parameters

TargetServer 2 with 2 databases:

#TargetServer2
$parameters = @{
    ResourceGroupName = '{Your_ResourceGroup}'
    ServerName = 'targetserver2'
    Location = '{Your_ResourceLocation}'
    SqlAdministratorCredentials = $cred
}
New-AzSqlServer @parameters

$parameters = @{
    ResourceGroupName = '{Your_ResourceGroup}'
    ServerName = 'targetserver2'
    FirewallRuleName = 'AllowedIps'
    StartIpAddress = '0.0.0.0'
    EndIpAddress = '0.0.0.0'
}
New-AzSqlServerFirewallRule @parameters

#Db1 from TargetServer2
$parameters = @{
    ResourceGroupName = 'targetresource1'
    ServerName = 'targetserver2'
   DatabaseName = 'targetdb1'
   RequestedServiceObjectiveName = 'BASIC'
}
New-AzSqlDatabase @parameters

#Db2 from TargetServer2
$parameters = @{
    ResourceGroupName = 'targetresource1'
    ServerName = 'targetserver2'
   DatabaseName = 'targetdb2'
   RequestedServiceObjectiveName = 'BASIC'
}
New-AzSqlDatabase @parameters

Step 3: Create elastic job agent

Creating login and user:

CREATE LOGIN jobexecu 
WITH PASSWORD = '{your_password}';
GO

CREATE LOGIN mastercred 
WITH PASSWORD = '{your_password}';
GO

CREATE USER mastercred FOR LOGIN mastercred
WITH DEFAULT_SCHEMA = dbo;
GO

--CREATE THE FOLLOWING USER/LOGIN IN EACH TARGET SERVER DATABASES INCLUDING THE JOBDB MASTER
CREATE USER jobexecu FOR LOGIN jobexecu
WITH DEFAULT_SCHEMA = dbo;
GO

EXEC sp_addrolemember N'db_owner', N'jobexecu';
GO

Step 4: Create credentials on jobs server jobs database

We need to create a Database Master Key and two database scoped credentials first.
The credential needs appropriate permissions, on the databases specified by the target group, to successfully execute the script.


use jobsdb
go
-- Create a db master key using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='{your_password}'; 
GO

-- Create a database scoped credential for job execution. 
CREATE DATABASE SCOPED CREDENTIAL elasticjobexecucredemtial
WITH IDENTITY = 'jobexecu', SECRET = '{your_password}'; 
GO 

-- Create a database scoped credential for the master database of servers.
CREATE DATABASE SCOPED CREDENTIAL elasticmastercredential 
WITH IDENTITY = 'mastercred', SECRET = '{your_password}'; 
GO

Step 5: Create a target group (servers) and Add containing server(s) to a target group

Defining Target Servers and Databases:

-- Connected to the job database specified when creating the job agent
use jobsdb
go
EXEC jobs.sp_add_target_group 'servergroup1';

--ADD THE TARGET SERVERS TO THIS GROUP ON JOBSDB DATABASE

EXEC jobs.sp_add_target_group_member
@target_group_name = 'servergroup1'
, @target_type = 'SqlServer'
, @refresh_credential_name='elasticmastercredential' 
, @server_name='<your_server1>.database.windows.net';  -- THIS WILL YOUR SERVER

EXEC jobs.sp_add_target_group_member
@target_group_name = 'servergroup1'
, @target_type = 'SqlServer'
, @refresh_credential_name='elasticmastercredential' 
, @server_name='<your_Server2>.database.windows.net';  --<< THIS WILL YOUR SERVER

View recently target group created:

USE jobsdb
GO
SELECT 
target_group_name
, membership_type
, target_type
, server_name
FROM jobs.target_group_members 
WHERE target_group_name='servergroup1';

Step 6: Add a job and Step for a job

USE jobsdb
GO
EXEC jobs.sp_add_job @job_name='CreateTable'
, @description='CreateTableId';

--CREATE A STEP
EXEC jobs.sp_add_jobstep 
@job_name='CreateTable'
, @command = N'IF NOT EXISTS (SELECT * FROM sys.tables 
WHERE object_id = object_id(''tabletest''))
CREATE TABLE [dbo].[tabletest]([testtableId] [int] NOT NULL);'
, @credential_name= 'elasticjobexecucredemtial'
, @target_group_name='servergroup1'


SELECT *
FROM [jobs].[jobsteps]
WHERE job_name = 'CreateTable';

Step 7: Run a job

exec jobs.sp_start_job 'CreateTable'

Step 8: View job status

SELECT
is_active
, lifecycle
, last_message
, target_type
, target_server_name
, target_database_name 
FROM jobs.job_executions 
WHERE job_name = 'CreateTable'
AND job_execution_id = 
(
SELECT job_execution_id FROM jobs.job_executions 
WHERE step_id IS NULL and create_time = 
(
SELECT MAX(create_time) 
FROM jobs.job_executions WHERE step_id IS NULL
)
)
ORDER BY start_time DESC;
GO

Step 9: Adding another Database to targetServer2

CREATE USER jobexecu
FOR LOGIN jobexecu
WITH DEFAULT_SCHEMA = dbo;
GO

EXEC sp_addrolemember N'db_owner', N'jobexecu';
GO

--Add a database target member RUN ON JOBSDB
EXEC jobs.sp_add_target_group_member
'servergroup1'
, @target_type = 'SqlDatabase'
, @server_name='<your_server2>.database.windows.net'
, @database_name='<your_newDatabase>'

--VERIFY NEWLY ADDED DATABASE

SELECT
target_group_name
, membership_type
, target_type
, server_name
, [database_name]
FROM jobs.target_group_members 
WHERE target_group_name='servergroup1';

--rerun the job and verify the creation of table

exec jobs.sp_start_job 'CreateTable'

--find the running job with id of job

SELECT *
FROM [jobs].[job_executions];

--stopping the job
exec jobs.sp_stop_job 'CreateTable'

For more details and examples of how to create jobs, click below:
https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-tsql-create-manage

Conclusion

This step-by-step guide demonstrates how easy it is to get started with Elastic Jobs using T-SQL and Azure PowerShell Cmdlets. Whether you’re automating a routine maintenance task or ensuring your databases stay synchronized, Elastic Jobs provide the tools you need to simplify operations and reduce human error.
Dive into the world of Elastic Jobs on Azure SQL Database and discover how automation can boost efficiency, consistency, and scalability in your database operations. Start building your jobs today and unlock the full potential of Azure SQL!

Elastic Jobs on SQL Server Azure

Conclusion

Written by Rafael Rampineli

No responses yet