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

Please or Register to post comments.

What's Practical SQL Server?

Practical advice, insight, and help for core SQL Server considerations.

Contributors

Michael K. Campbell

Michael K. Campbell is a contributing editor for SQL Server Pro and Dev Pro and is an ASPInsider. Michael is the president of OverAchiever Productions, a consultancy dedicated to technical evangelism...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×