Skip to content

Instantly share code, notes, and snippets.

@JosiahSiegel

JosiahSiegel/elastic_agent_jobs.sql

Last active September 11, 2024 17:41
Show Gist options
  • Save JosiahSiegel/df3cd7b6bebc06163c2a1d222bb0885d to your computer and use it in GitHub Desktop.
Save JosiahSiegel/df3cd7b6bebc06163c2a1d222bb0885d to your computer and use it in GitHub Desktop.
Download ZIP
Elastic Agent Jobs
Raw
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;
Raw
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';
Raw
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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment