Synchronizing Read / Write SQL Agent Jobs Across AlwaysOn Replicas

Intro

AlwaysOn availability groups are a powerful enhancement to SQLServer HA, and offer many improvements over older database synchronization techniques like mirroring and log shipping. However, there is also additional complexity involved in implementing this technology, and one of the stickiest areas for database administrators is having to deal with non-contained objects, or objects that exist at the instance level, but not the database level (logins vs users, as an example).

For new development, contained vs. uncontained objects can be accounted for and considered during application design. But what about those existing mission critical legacy applications that we want to benefit from AlwaysOn? Those are, undoubtedly, the instances that would benefit from automated failover and read / write workload balancing the most! Well, fortunately these older implementations can be modified to work seamlessly with AlwaysOn, but the instances they run on will most likely need some intelligent customization. Here’s one situation you might encounter: legacy applications using SQL Agent jobs to update data on a schedule.

Overview

In SQL Server AlwaysOn, all system level objects are non-synchronized, including SQL Agent. The assumption is that all applications need only database level objects to function, and that system level objects are only required for maintenance and administration of the instance. I had a client with a legacy mission critical application that they wanted to implement with AlwaysOn. This application relied and several SQL agent jobs to update data on a scheduled basis in order to maintain application integrity. Less then optimal, I know, but that’s what you get with legacy. The blocker with this implementation is that the jobs would fail if executed on a secondary replica, because the primary is the only write enabled replica.

So, how do we customize AlwaysOn replicas to ensure that these agent jobs are always and only running on the primary replica?

Solution

The solution I implemented is composed of four parts: A system function, a user stored procedure, a SQL Agent job, and a SQL Agent alert. The key to this implementation is ensuring that all of the jobs you need to manage are maintained in their own user defined category(s).

System Function

Microsoft provides us with a handy-dandy little system function: sys.fn_hadr_is_primary_replica. This function accepts one parameter, @dbname sysname, the name of a synchronized database, and “returns 1 if the current instance is primary replica; otherwise returns 0.” This is what we’ll use in our procedure to determine whether to enable or disable the read/write SQL Agent jobs.

Stored Procedure

We can now create a stored procedure to enable / disable SQL Agent jobs in a user defined category based on, coincidentally, the same bit value that sys.fn_hadr_is_primary_replica returns:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/* =============================================
-- Author:		Jared Zagelbaum, jaredzagelbaum.wordpress.com
-- Follow me on twitter: @JaredZagelbaum
-- Create date: 4/23/2015
-- Description:	Enables Read / Write jobs on primary replica after failover
-- Can be executed from failover alert and / or recurring schedule
-- =============================================
*/
CREATE PROCEDURE [dbo].[JobChange_AGFailover]  
(
@databasename sysname
,@jobcategory sysname
)
AS

BEGIN
	
  SET NOCOUNT ON;

  DECLARE @jobID UNIQUEIDENTIFIER  --variable for job_id 
  DECLARE @AG_enable tinyint
 
  select @AG_enable = sys.fn_hadr_is_primary_replica (@databasename)
   
  DECLARE jobidCursor CURSOR FOR   --used for cursor allocation 
 
  SELECT j.job_id 
  FROM msdb.dbo.sysjobs j
  INNER JOIN msdb.dbo.syscategories c
  ON j.category_id = c.category_id
  where c.name = @jobcategory

--update jobs
  OPEN jobidCursor
  FETCH NEXT FROM jobidCursor INTO @jobID
  WHILE @@Fetch_Status = 0     
  BEGIN                
        EXEC msdb.dbo.sp_update_job @job_id=@jobID, @enabled = @AG_enable                 
		  FETCH Next FROM jobidCursor INTO @jobID    
		   END 

  CLOSE jobidCursor
  DEALLOCATE jobidCursor

END


GO


 SQL Agent Job

Having fun yet!? Now we can add this s proc to a job. Here’s a script that will create the job for you using the much preferred CmdExec method (assuming you create [dbo].[JobChange_AGFailover] in master). Make sure you update the supplied values where noted.

USE [msdb]
GO

/****** Object:  Job [AG Failover Replica Changes]    Script Date: 5/5/2015 2:17:49 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AG Failover Replica Changes', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=2, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Enables / Disables read-write jobs against replicas when failover role change occurs', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa',						-- change if necessary per your environment
		@notify_email_operator_name=N'DBAdmins',		-- change to your default operator
		@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Exec JobChange_AGFailover]    Script Date: 5/5/2015 2:17:49 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Exec JobChange_AGFailover', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[JobChange_AGFailover] @Databasename = ''Databasename'', @jobcategory = ''jobcategory'' " -b', --supply your own databasename and jobcategory parameter values
		@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\AGFailover_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


 SQL Agent Alert

Finally, lets create an alert to trigger this job whenever a failover occurs. The alert should be raised based on Error number 1480. Here’s a script for it:

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'AG Role Change', 
		@message_id=1480, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_name=N'AG Failover Replica Changes'
GO

 Result

You should now have an instance of each of these objects on all of your replicas:

Stored Procedure:

sproc

SQL Agent Job:

Job

SQL Agent Alert:

Alert

Alright, we now have an automated way of enabling and disabling read / write SQL Agent jobs based on whether the replica role is primary or secondary. The alert will be raised whenever a failover occurs. If, for some reason the alert does not trigger successfully (I’ve not encountered this), you can always start the job manually on the affected replica as well.

The final result that we want is that SQL jobs that edit data are disabled on secondary replicas and enabled only on the primary by job category. Multiple jobs and alerts can be added for different databases and/ or categories. You can view the enabled property value easily under Job Activity Monitor in SQL Server Agent.

Hope this helps!

Advertisements