SQL Server with Mr. Denny

Jul 22 2015   6:00PM GMT

Updating All Jobs To Check For AG Primary Replica

Denny Cherry Denny Cherry Profile: Denny Cherry

Tags:
High Availability
Job scheduling
SQL job scheduler
SQL scripts
SQL Server

One of the really annoying things about moving existing SQL Server databases into an Availability Group is setting up the jobs to work within the confines of the AlwaysOn Availability Group. This is because you have to manually create the jobs on each server, then modify the jobs on each server to check and see if the database for that job is on the primary server.

Well, here’s a T-SQL script which will help solve this problem for you. It’ll add a job step to the beginning of each database job that looks to see if it’s on the primary replica or not. If figures out the database to use based on the databases which are used in the job steps (by looking at the database which is set for each job step).

If the databases are in multiple different Availability Groups, this still works fine. After the databases are setup in their Availability Groups, just run this script. If you add more jobs later you can run this script again as it will skip any jobs which have this job step already added.

To be safe, backup your msdb database before running this. Nothing should go wrong, but you’ll notice that this script deletes the job steps then puts them back in. You wouldn’t want to loose all your job steps, so it’s just safer to backup msdb before doing anything like this against it.

USE [msdb]

CREATE TABLE #sysjobsteps(
[job_id] [uniqueidentifier] NOT NULL,
[step_id] [int] NOT NULL,
[step_name] [sysname] NOT NULL,
[subsystem] [nvarchar](40) NOT NULL,
[command] [nvarchar](max) NULL,
[flags] [int] NOT NULL,
[additional_parameters] [nvarchar](max) NULL,
[cmdexec_success_code] [int] NOT NULL,
[on_success_action] [tinyint] NOT NULL,
[on_success_step_id] [int] NOT NULL,
[on_fail_action] [tinyint] NOT NULL,
[on_fail_step_id] [int] NOT NULL,
[server] [sysname] NULL,
[database_name] [sysname] NULL,
[database_user_name] [sysname] NULL,
[retry_attempts] [int] NOT NULL,
[retry_interval] [int] NOT NULL,
[os_run_priority] [int] NOT NULL,
[output_file_name] [nvarchar](200) NULL,
[last_run_outcome] [int] NOT NULL,
[last_run_duration] [int] NOT NULL,
[last_run_retries] [int] NOT NULL,
[last_run_date] [int] NOT NULL,
[last_run_time] [int] NOT NULL,
[proxy_id] [int] NULL,
[step_uid] [uniqueidentifier] NULL
)
insert into #sysjobsteps
SELECT *
FROM msdb.dbo.sysjobsteps

DECLARE @job_id uniqueidentifier, @step_id int, @step_name sysname, @subsystem nvarchar(40), @command nvarchar(max) , @flags int, @additional_parameters nvarchar(max),
@cmdexec_success_code int, @on_success_action tinyint, @on_success_step_id int, @on_fail_action tinyint, @on_fail_step_id int, @server sysname, @database_name sysname,
@database_user_name sysname, @retry_attempts int, @retry_interval int, @os_run_priority int, @output_file_name nvarchar(200), @last_run_outcome int, @last_run_duration int,
@last_run_retries int, @last_run_date int, @last_run_time int, @proxy_id int, @step_uid uniqueidentifier

DECLARE jobs CURSOR FOR SELECT DISTINCT job_id
from msdb.dbo.sysjobs
WHERE NOT EXISTS (SELECT *
FROM msdb.dbo.sysjobsteps
WHERE sysjobsteps.step_name = 'Check Is AG Primary'
AND sysjobs.job_id = sysjobsteps.job_id)
open jobs
FETCH NEXT FROM jobs into @job_id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @database_name = (SELECT database_name
FROM (SELECT TOP (1) database_name, count(*) ct
FROM #sysjobsteps
WHERE job_id = @job_id
GROUP BY database_name
ORDER BY count(*) desc) a)

IF @database_name NOT IN (SELECT database_name
FROM sys.availability_databases_cluster)
BEGIN
GOTO SkipJob
END

SET @command = 'IF [master].sys.fn_hadr_is_primary_replica (''' + @database_name + ''') 1
RAISERROR(''Not the PRIMARY server for this job, exiting with SUCCESS'' ,11,1)'

DECLARE steps CURSOR FOR SELECT step_id
FROM #sysjobsteps
WHERE job_id = @job_id
ORDER BY step_id DESC
OPEN steps
FETCH NEXT FROM steps into @step_id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_delete_jobstep @job_id=@job_id, @step_id=@step_id

FETCH NEXT FROM steps into @step_id
END
CLOSE steps
DEALLOCATE steps

EXEC msdb.dbo.sp_add_jobstep @job_id=@job_id, @step_name='Check Is AG Primary',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_fail_action=1,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem='TSQL',
@command=@command,
@database_name='master',
@flags=0

DECLARE steps CURSOR FOR SELECT step_id+1, step_name, subsystem, command, flags, additional_parameters, cmdexec_success_code, on_success_action, on_success_step_id+1, on_fail_action, on_fail_step_id+1, server,
database_name, database_user_name, retry_attempts, retry_interval, os_run_priority, output_file_name
FROM #sysjobsteps
WHERE job_id = @job_id
order by step_id
OPEN steps
FETCH NEXT FROM steps into @step_id, @step_name, @subsystem, @command, @flags, @additional_parameters, @cmdexec_success_code, @on_success_action, @on_success_step_id, @on_fail_action, @on_fail_step_id, @server,
@database_name, @databasE_user_name, @retry_attempts, @retry_interval, @os_run_priority, @output_file_name
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC msdb.dbo.sp_add_jobstep @job_id=@job_id, @step_name=@step_name,
@step_id=@step_id,
@cmdexec_success_code=@cmdexec_success_code,
@on_success_action=@on_success_action,
@on_fail_action=@on_fail_action,
@on_success_step_id=@on_success_step_id,
@on_fail_step_id=@on_fail_step_id,
@retry_attempts=@retry_attempts,
@retry_interval=@retry_interval,
@os_run_priority=@os_run_priority, @subsystem=@subsystem,
@command=@command,
@database_name=@database_name,
@flags=@flags

FETCH NEXT FROM steps into @step_id, @step_name, @subsystem, @command, @flags, @additional_parameters, @cmdexec_success_code, @on_success_action, @on_success_step_id, @on_fail_action, @on_fail_step_id, @server,
@database_name, @databasE_user_name, @retry_attempts, @retry_interval, @os_run_priority, @output_file_name
END
CLOSE steps
DEALLOCATE steps

EXEC msdb.dbo.sp_update_job @job_id = @job_id, @start_step_id = 1

SkipJob:

FETCH NEXT FROM jobs into @job_id
END
CLOSE jobs
DEALLOCATE jobs

DROP TABLE #sysjobsteps

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: