SQL Agent Jobs in an AlwaysOn / Mirroring Environment

Running SQL Agent Jobs in an AlwaysOn or Database Mirroring environment can be a challenge, as the database might be not accessible at the time when the job starts. This is especially the case for all SAP collector and backup jobs. On the other hand there might be jobs that should not run on primary server (e.g. DBCC Checks), but on a readable secondary or readonly standby database (Log Shipping). In the worst case either the jobs fail or run on the wrong server. To prevent this, SAP created a small stored procedure to control the run of these kind of jobs.

To use the stored procedure, use this script

use master;
GO
IF (OBJECT_ID(N'dbo.CheckAccess') IS NOT NULL )
	DROP PROCEDURE [dbo].CheckAccess 
GO
CREATE PROCEDURE dbo.CheckAccess @DBName AS NVARCHAR(128) = N'master', @RunOnPrimary AS TINYINT = 1, @RunOnSecondary AS TINYINT = 0, @RunOnReadonly AS TINYINT = 0, RC AS TINYINT = 0 OUTPUT
AS BEGIN
	
	DECLARE @IsReadOnly TINYINT;
	DECLARE @State TINYINT;
	DECLARE @StandBy TINYINT;
	DECLARE @UserAccess TINYINT;
	DECLARE @AOState TINYINT;
	DECLARE @AORole TINYINT;
	DECLARE @Message NVARCHAR(50);

	SET RC = 0;
	SET @Message = N'Everything is OK.';
	
	IF EXISTS(SELECT TOP 1 database_id FROM sys.databases WHERE database_id = DB_ID(@DBName)) BEGIN
		-- Check for the normal database state
		SELECT @IsReadOnly = is_read_only, @State = state, @StandBy = is_in_standby, @UserAccess = user_access FROM sys.databases WHERE name = @DBName;
	
		IF NOT ((@State + @UserAccess)  = 0 AND (@IsReadOnly <= @RunOnReadonly) AND (@StandBy <= @RunOnReadonly)) BEGIN
			SET RC = 1;
			SET @Message =  N'Database is NOT accessible.';
		END;
	END ELSE BEGIN
		SET RC = 2;
		SET @Message = N'Database doesn''t exists.';
	END

	-- Check if we are running in an AlwaysOn Configuration
	IF EXISTS(SELECT TOP 1 database_id FROM sys.dm_hadr_database_replica_states WHERE database_id = DB_ID(@DBName)) BEGIN

		SELECT @AOState = db.database_state, @AORole = rs.role 
		FROM sys.dm_hadr_database_replica_states AS db 
			 INNER JOIN sys.dm_hadr_availability_replica_states AS rs ON db.group_id = rs.group_id
		WHERE db.is_local = 1 AND rs.is_local = 1 AND db.database_id = DB_ID(@DBName)

		IF NOT(@AOState = 0 AND ((@RunOnPrimary != 0 AND @RunOnPrimary = @AORole) OR (@RunOnSecondary != 0 AND @RunOnSecondary = @AORole - 1))) BEGIN
			SET RC = 3;
			SET @Message = N'The database is not in the required state for Always-ON access.';
		END;
	END;
	PRINT @Message
END;
GO

and run it on all nodes of the affected system (either AlwaysOn, Database Mirroring or Log Shipping). It will create a stored procedure  CheckAccess  in the master database. The procedure has 5 parameters:

With the parameters @RunOnPrimary, @RunOnSecondary and @RunOnReadonly you can control in which state of the AlwaysOn database the Job should start. A combination of these parameters is possible, so the job can run on primary and  secondary databases if you set both to 1. Even if you do not use AlwaysOn or readonly databases or if you set all the options to 0 the stored procedure will check for the availability of the database. It will return a failure (1) if the database is absent, offline, suspect, in recovery and all the other states that won't let you access the database. A valid call looks like this (Check the TST database if it is accessible and check if it is the primary database in an AlwaysOn configuration):

exec dbo.CheckAccess N'TST',1,0,0,@RC OUTPUT

To enable the CheckAccess stored procedure for jobs, we have to change the job a bit. In this example it is an SAP Collector Job (SAP_SCU_SAPMonitor_TST_MSSQL_COLLECTOR) for the TST SAP database.

Open the properties for the job (right click -> Properties)

Switch to the Steps branch on the left

and edit the first step with the name DBCOLL. Change the database to master (as the stored procedure lives there) and change the script from

EXECUTE sap_dbcoll

to

DECLARE @Msg NVARCHAR(128)
DECLARE RC TINYINT
exec dbo.CheckAccess N'TST',1,0,0,@RC OUTPUT

IF RC = 0 
  exec( 'use [TST] EXECUTE sap_dbcoll' )

Ensure that you change the database accordingly (here TST) for your system. The step then should look like:

On the advanced tab change the 'On failure action' field to 'Quit the job reporting failure'

and save and exit the job definition. This will ensure that the DBCOLL part will only start when the database is accessible and is the primary database in the AlwaysOn Configuration.

You can use this stored procedure for any job, when you follow this generic approach:

DECLARE @Msg NVARCHAR(128)
DECLARE RC TINYINT
exec dbo.CheckAccess N'<Database in Question>',1,0,0,@RC OUTPUT

IF RC = 0 
   exec( 'use [<Database in Question>] <Step definition>' )

Regards
Clas