Elastic Agent Jobs

elastic_agent_jobs.sql

-- Elastic Agent Jobs
-- https://sqlkitty.com/elastic-jobs-azure-sql-db/

-- Add target group
EXEC jobs.sp_add_target_group 'AzureSQLDBs';

-- Add single database (or server/elastic pool) to target group
EXEC jobs.sp_add_target_group_member
@target_group_name = 'AzureSQLDBs',
@target_type = 'SqlDatabase',
@server_name = '<SERVER_NAME>.database.windows.net',
@database_name = '<DB_NAME>';

-- Add job
EXEC jobs.sp_add_job 
@job_name = 'IndexMaintenance', 
@description = 'Run stored procedure dbo.IndexOptimize'; 

-- Add job step
EXEC jobs.sp_add_jobstep
@job_name = 'IndexMaintenance',
@step_name = 'Run IndexOptimize',
@command = N'EXECUTE [dbo].[IndexOptimize]
@Databases = ''USER_DATABASES'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = ''Y'',
@UpdateStatistics = ''ALL'',
@OnlyModifiedStatistics = ''Y'',
@LogToTable = ''Y'',
@Execute = ''N'';',
@target_group_name = 'AzureSQLDBs';

-- Update job step
EXEC jobs.sp_update_jobstep
@job_name = 'IndexMaintenance',
@step_name = 'Run IndexOptimize',
@command = N'EXECUTE [dbo].[IndexOptimize]
@Databases = ''USER_DATABASES'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = ''Y'',
@UpdateStatistics = ''ALL'',
@OnlyModifiedStatistics = ''Y'',
@LogToTable = ''Y'',
@Execute = ''Y'';',
@target_group_name = 'AzureSQLDBs';

-- Add 2nd job step
EXEC jobs.sp_add_jobstep 
@job_name = 'IndexMaintenance',
@step_name = 'Clean CommandLog',
@command = N'DELETE FROM [dbo].[CommandLog]
WHERE StartTime <= DATEADD(DAY, -30, GETDATE());',
@target_group_name = 'AzureSQLDBs';

-- Start job
EXEC jobs.sp_start_job 'IndexMaintenance';

-- Set job schedule
EXEC jobs.sp_update_job
@job_name = 'IndexMaintenance',
@enabled=1,
@schedule_interval_type = 'Days',
@schedule_interval_count = 1, 
@schedule_start_time = '2024-01-01 00:00:00'; 

-- Generate stop job commands
SELECT 
'EXEC jobs.sp_stop_job '''+ CAST(job_execution_id AS CHAR(36)) +''';' AS [command],
*
FROM jobs.job_executions
WHERE job_name = 'IndexMaintenance' and is_active = 1
ORDER BY start_time DESC;

-- Target group details
SELECT * FROM jobs.target_group_members;

-- Job details
SELECT * FROM jobs.jobs;

-- Job step details
SELECT js.*
FROM jobs.jobsteps js
JOIN jobs.jobs j
ON j.job_id = js.job_id AND j.job_version = js.job_version;

-- Job run history
 SELECT 
job_name,
step_name,
target_server_name,
target_database_name,
target_type, 
last_message,
start_time,
end_time,
is_active,
lifecycle,
current_attempts
FROM jobs.job_executions 
ORDER BY start_time DESC;

elastic_agent_jobs_logging.sql

EXEC jobs.sp_update_jobstep 
@job_name = 'DBCopy',
@step_name = 'Copy DB',
@command = N'
SET NOCOUNT ON;

DECLARE 
@print_only BIT = 1,
@job NVARCHAR(100) = $(job_name),
@job_step NVARCHAR(100) = $(step_name)

DECLARE @temp_cmd
TABLE (
    row_num INT IDENTITY (1, 1) Primary key NOT NULL,
    job NVARCHAR(100) NOT NULL,
    job_step NVARCHAR(100) NOT NULL,
    cmd NVARCHAR(MAX) NULL,
    error NVARCHAR(MAX) NULL,
    runtime DATETIME NULL
)

BEGIN TRY  
    IF (@print_only <> 0)
    BEGIN
        PRINT ''DB COPY DISABLED'';
    END
    ELSE
    BEGIN
        DROP DATABASE IF EXISTS [MyDBDeID]
        CREATE DATABASE [MyDBDeID] AS COPY OF [MyDB]
        --(EDITION = ''Hyperscale'', SERVICE_OBJECTIVE = ''HS_S_Gen5_80'')
        (EDITION = ''Premium'', SERVICE_OBJECTIVE = ''P15'')
        WITH (BACKUP_STORAGE_REDUNDANCY = ''LOCAL'');

        WAITFOR DELAY ''00:05''

        DECLARE @msg NVARCHAR(1000)
        WHILE EXISTS(Select 1 from sys.databases where  Name like ''MyDBDeID'' and state_desc <> ''Online'')
        BEGIN
            SELECT @msg =  ''Database MyDBDeID  has status '' + state_desc  from sys.databases  where  Name like ''MyDBDeID'' and state_desc <> ''Online''
            RAISERROR(@msg, 0, 1) WITH NOWAIT
            WAITFOR DELAY ''00:05''
        END
    END
END TRY  
BEGIN CATCH
    INSERT INTO @temp_cmd (job, job_step, error, runtime)
    SELECT
        @job,
        @job_step,
        ''ERROR_NUMBER: '' + ISNULL(TRY_CAST(ERROR_NUMBER() AS NVARCHAR), '''') +
        ''|ERROR_SEVERITY: '' + ISNULL(TRY_CAST(ERROR_SEVERITY() AS NVARCHAR), '''') +
        ''|ERROR_STATE: '' + ISNULL(TRY_CAST(ERROR_STATE() AS NVARCHAR), '''') +
        ''|ERROR_PROCEDURE: '' + ISNULL(TRY_CAST(ERROR_PROCEDURE() AS NVARCHAR), '''') +
        ''|ERROR_LINE: '' + ISNULL(TRY_CAST(ERROR_LINE() AS NVARCHAR), '''') +
        ''|ERROR_MESSAGE: '' + ISNULL(TRY_CAST(ERROR_MESSAGE() AS NVARCHAR), '''') AS [error],
        GETUTCDATE() AS [runtime]
END CATCH;  

SELECT
job,
job_step,
cmd,
error,
runtime
FROM @temp_cmd
',
@target_group_name = 'master_MyServer',
@step_timeout_seconds = 10800,
@output_type = 'SqlDatabase',
@output_server_name = 'MyServer.database.windows.net',
@output_database_name = 'MyAdminDB',
@output_schema_name = 'dbo',
@output_table_name = 'jobstep_results';

EXEC jobs.sp_update_jobstep 
@job_name = 'DBCopy - MyDB',
@step_name = 'DeID DB',
@command = N'
SET NOCOUNT ON;

DECLARE 
@print_only BIT = 1,
@job NVARCHAR(100) = $(job_name),
@job_step NVARCHAR(100) = $(step_name)

DECLARE @temp_cmd
TABLE (
    row_num INT IDENTITY (1, 1) Primary key NOT NULL,
    job NVARCHAR(100) NOT NULL,
    job_step NVARCHAR(100) NOT NULL,
    cmd NVARCHAR(MAX) NULL,
    error NVARCHAR(MAX) NULL,
    runtime DATETIME NULL
)

BEGIN TRY  
    DECLARE @loop_num INT = 1

    INSERT INTO @temp_cmd (job, job_step, cmd)
    SELECT
        @job,
        @job_step,
        ''BEGIN TRAN; UPDATE '' + [table] + '' SET '' + STRING_AGG([column] + ''='''''' + [mask] + '''''''', '', '') + ''; COMMIT;'' AS [cmd]
    FROM (
        SELECT
        QUOTENAME(schema_name(O.schema_id)) + ''.'' + QUOTENAME(O.NAME) AS [table],
        QUOTENAME(C.NAME) AS [column],
        t.name AS [type],
        sc.label,
        CASE t.name
            WHEN ''date'' THEN ''2000-01-01''
            ELSE sc.label
        END AS [mask]
        FROM sys.sensitivity_classifications sc
            JOIN sys.objects O ON  sc.major_id = O.object_id
	        JOIN sys.columns C ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id
	        JOIN sys.types AS [t] ON (c.user_type_id = t.user_type_id)
        WHERE sc.label <> ''NO_DEID''
    ) a
    GROUP BY [table]

    WHILE (@loop_num <= (SELECT MAX(row_num) FROM @temp_cmd))
    BEGIN
        DECLARE @cmd NVARCHAR(MAX) = (SELECT [cmd] FROM @temp_cmd WHERE row_num = @loop_num)
        UPDATE @temp_cmd SET runtime = GETUTCDATE() WHERE row_num = @loop_num
        IF (@print_only = 0)
            EXEC sp_executesql @cmd
        SET @loop_num+=1
        WAITFOR DELAY ''00:00:00:10''
    END

END TRY  
BEGIN CATCH
    INSERT INTO @temp_cmd (job, job_step, error, runtime)
    SELECT
        @job,
        @job_step,
        ''ERROR_NUMBER: '' + ISNULL(TRY_CAST(ERROR_NUMBER() AS NVARCHAR), '''') +
        ''|ERROR_SEVERITY: '' + ISNULL(TRY_CAST(ERROR_SEVERITY() AS NVARCHAR), '''') +
        ''|ERROR_STATE: '' + ISNULL(TRY_CAST(ERROR_STATE() AS NVARCHAR), '''') +
        ''|ERROR_PROCEDURE: '' + ISNULL(TRY_CAST(ERROR_PROCEDURE() AS NVARCHAR), '''') +
        ''|ERROR_LINE: '' + ISNULL(TRY_CAST(ERROR_LINE() AS NVARCHAR), '''') +
        ''|ERROR_MESSAGE: '' + ISNULL(TRY_CAST(ERROR_MESSAGE() AS NVARCHAR), '''') AS [error],
        GETUTCDATE() AS [runtime]
END CATCH;  

SELECT
job,
job_step,
cmd,
error,
runtime
FROM @temp_cmd
',
@target_group_name = 'MyDBDeID',
@step_timeout_seconds = 86400,
@output_type = 'SqlDatabase',
@output_server_name = 'MyServer.database.windows.net',
@output_database_name = 'MyAdminDB',
@output_schema_name = 'dbo',
@output_table_name = 'jobstep_results';

elastic_agent_provision.tf

/*
--Assign as identity to elastic job agent
--master db
CREATE USER ElasticJobsIdentity FROM EXTERNAL PROVIDER;
--user db
CREATE USER ElasticJobsIdentity FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER ElasticJobsIdentity;
*/
resource "azurerm_user_assigned_identity" "elastic_jobs" {
  location            = azurerm_resource_group.default.location
  name                = "ElasticJobsIdentity"
  resource_group_name = azurerm_resource_group.default.name
}
resource "azurerm_mssql_job_agent" "elastic_jobs" {
  name        = "ElasticJobsAgent"
  location    = azurerm_resource_group.default.location
  database_id = azurerm_mssql_database.elastic_jobs.id
}
resource "azurerm_mssql_database" "elastic_jobs" {
  name      = "ElasticJobsDB"
  server_id = azurerm_mssql_server.default.id
}