AlwaysOn Availability Groups and SQL Server Jobs, Part 7: Detecting Primary Replica Ownership

In AlwaysOn Availability Groups and SQL Server Jobs, Part 6: High-Level Options and Rationale for Handling Batch Jobs, I outlined how there are, effectively, two ways to tackle the need to run batch jobs (or SQL Server Agent jobs that target a database that’s part of an AlwaysOn Availability Group). Either your SQL Server Agent jobs can target (or detect) whether the Server they’re running on currently owns the Primary Replica of the database you’re targeting, or you can enable/disable entire jobs based upon whether they’re running on servers hosting the Primary Replica for the job in question, or not. In either case, you’ll need code that lets you determine if the SQL Server host your code is running on currently owns the Primary Replica for a given Availability Group (AG) database, or not.

Part 1: Introduction
Part 2: Putting AlwaysOn into Context
Part 3: Defining Batch Jobs
Part 4: Synchronizing Server-Level Details
Part 5: Setting Up Failover Alerts
Part 6: High-Level Options and Rationale for Handling Batch Jobs

Two Options for Detection

In my mind, there are two logical ways that you might want to have your code check to see if the Primary Replica of a given AG database is currently hosted on a particular server or not: Either you can (1) check based on the name of the database itself; or you can (2) check based on the name of the AG it’s a part of. In my experience—as you’ll see throughout this series of posts—both approaches will make sense at various times.

To that end, I’ve provided two scripts below—one for each approach.

Checking By Availability Group Name:

USE master;
GO

IF OBJECT_ID('dbo.fn_hadr_group_is_primary','FN') IS NOT NULL
	DROP FUNCTION dbo.fn_hadr_group_is_primary
GO

CREATE FUNCTION dbo.fn_hadr_group_is_primary (@AGName sysname)
RETURNS bit 
AS
	BEGIN 
		
		DECLARE @PrimaryReplica sysname; 

		SELECT @PrimaryReplica = hags.primary_replica 
		FROM 
			sys.dm_hadr_availability_group_states hags
			INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
		WHERE
			ag.name = @AGName;

		IF UPPER(@PrimaryReplica) =  UPPER(@@SERVERNAME)
			RETURN 1; -- primary

		RETURN 0; -- not primary
		
	END; 
GO

Checking By Database Name:

USE master;
GO

IF OBJECT_ID('dbo.fn_hadr_database_is_primary','FN') IS NOT NULL
	DROP FUNCTION dbo.fn_hadr_database_is_primary
GO

CREATE FUNCTION dbo.fn_hadr_database_is_primary (@DBName sysname)
RETURNS bit
AS
	BEGIN
		DECLARE @description sysname;
		
		SELECT 
			@description = 	hars.role_desc
		FROM 
			sys.databases d
			INNER JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id
		WHERE 
			database_id = DB_ID(@DBName);
	
		IF @description = 'PRIMARY'
			RETURN 1;
	
		RETURN 0;
	END;
GO

If you’re already familiar with how backups can or should be handled with AlwaysOn Availability Groups, then you’ll realize that the naming convention I used above for both UDFs is patterned, more or less, on SQL Server’s built-in sys.fn_hadr_backup_is_preferred_replica(). Usage of either UDF listed above, then, is fairly similar to what you’d expect when running sys.fn_hadr_backup_is_preferred_replica()—except that each of the UDFs above will tell you if the Replica (or host you’re running the code on), is the Primary Replica or not.

A conceptual example (though, note: the following code won’t actually work like you think it might—you’ll have to read-up on following posts to get a better handle on WHY that might be the case in many instances):

-- Pseudo-code - Most Likely won't work as you expect: 
IF master.dbo.fn_hadr_group_is_primary('MyAGName') = 1 BEGIN 
	-- do whatever you were going to do in the Primary:
	PRINT 'Doing stuff in the Primary Replica';
  END
ElSE BEGIN 
	-- we're not in the Primary - exit gracefully:
	PRINT 'This is not the primary replica - exiting with success';
END

WARNING: Just as with SQL Server’s built-in sys.fn_hadr_backup_is_preferred_replica(), both of the UDFs I’ve created above are picky about how you spell or define database names or AG names—if you don’t spell them exactly correct (i.e., if you specify Toolz’i nstead of Tools as an input), you’ll get a Negative (0) response instead of running into an error EVEN if Toolz isn’t a valid AG or DB name. This is by design.

Up Next

Of course, with this code in place, you could then, conceptually, just tweak existing SQL Server Agent Jobs to interject a quick if/then check using one of these UDFs and then either continue processing on Primary Replicas or bail on non-Primaries as needed. Only, as subsequent posts will show, this actually ends up being a lot harder to actually execute than you might actually think.

Next: AlwaysOn Availability Groups and SQL Server Jobs, Part 8: Dynamic Detection Gotcha #1