Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Everyday SQL

Patrick Keisler is a Premier Field Engineer for Microsoft with over 15 years of SQL Server experience working in various fields such as financial, healthcare, and government. He currently holds an MCSE Data Platform certification, MCITP certifications in SQL Server 2008 for administration and development, and CompTIA Security+. You can follow him on Twitter or listen to him speak at various SQL Saturdays and user group meetings.

Are You the Primary Replica?

UPDATED -- Jul 3, 2015 -- To verify database exists, per comments by Konstantinos Katsoridis. Thanks for finding the bug!

In my recent adventures with AlwaysOn Availability Groups, I noticed a gap in identifying whether or not a database on the current server is the primary or secondary replica.  The gap being Microsoft did not provide a DMO to return this information.  The good news is the documentation for the upcoming release of SQL Server 2014 looks to include a DMO, but that doesn't help those of us who are running SQL Server 2012.

I've developed a function, dbo.fn_hadr_is_primary_replica, to provide you with this functionality.  This is a simple scalar function that takes a database name as the input parameter and outputs one of the following values.

 0 = Resolving
 1 = Primary Replica
 2 = Secondary Replica
-1 = Database Does Not Exist

The return values correspond to the role status listed in sys.dm_hadr_availability_replica_states.

In this example, I have setup 2 SQL Servers (SQLCLU1\SPIRIT1 and SQLCLU2\SPIRIT2) to participate in some Availability Groups.  I have setup 2 Availability Groups; one for AdventureWorks2012 and a second for the Northwind database.  SQLCLU1\SPIRIT1 is the primary for AdventureWorks2012 and secondary for Northwind.  SQLCLU2\SPIRIT2 is the primary for Northwind and secondary for AdventureWorks2012.

First let's run the function for both databases on SQLCLU1\SPIRIT1.


On this server, the function returns 1 because it's the primary for AdventureWorks2012, and returns 2 because it's the secondary for Northwind.

Now let's run it again on SQLCLU2\SPIRIT2.


As expected we get the opposite result.

This function does not take into account the preferred backup replica; it only returns information based on whether it is the primary or secondary replica.  It was created to use within other scripts to help determine a database's role if it's part of an Availability Group.  I hope this script can help you as well.

USE master;
GO

IF OBJECT_ID(N'dbo.fn_hadr_is_primary_replica', N'FN') IS NOT NULL
DROP FUNCTION dbo.fn_hadr_is_primary_replica;
GO

CREATE FUNCTION dbo.fn_hadr_is_primary_replica (@DatabaseName SYSNAME)
RETURNS TINYINT
WITH EXECUTE AS CALLER
AS
/********************************************************************

File Name: fn_hadr_is_primary_replica.sql

Applies to: SQL Server 2012

Purpose: To return either 0, 1, 2, or -1 based on whether this
@DatabaseName is a primary or secondary replica.

Parameters: @DatabaseName - The name of the database to check.

Returns: 0 = Resolving
1 = Primary
2 = Secondary
-1 = Database does not exist

Author: Patrick Keisler

Version: 1.0.1 - 07/03/2015

Help: http://www.patrickkeisler.com/

License: Freeware

********************************************************************/

BEGIN
DECLARE @HadrRole TINYINT;

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
BEGIN
-- Return role status from sys.dm_hadr_availability_replica_states
SELECT @HadrRole = ars.role
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.databases dbs
ON ars.replica_id = dbs.replica_id
WHERE dbs.name = @DatabaseName;

-- @DatabaseName exists but does not belong to an AG so return 1
IF @HadrRole IS NULL RETURN 1;

RETURN @HadrRole;
END
ELSE
BEGIN
-- @DatabaseName does not exist so return -1
RETURN -1;
END
END;
GO

Comments

Leave a comment on the original post [www.patrickkeisler.com, opens in a new window]

Loading comments...