How to detect said database is primary Replica or Are You the Primary Replica? in SQL Server 2014 and SQL 2016

Are You the Primary Replica? in SQL Server 2014 and SQL 2016

-----------------------------------------------------------------------------------------------------------------------

How to identify whether or not a database on the current server is the primary or secondary replica.

The good news is on  SQL Server 2014 / SQL Server 2016  has included a DMO for same and the name is sys.fn_hadr_is_primary_replica 

Declare @Status int
SELECT  @Status=sys.fn_hadr_is_primary_replica ('AdventureWorks2014');
print @Status
if @Status = 1 
  begin
  print 'database on the current instance is the primary replica'
  end
  else
    begin
  if @Status =0
         begin
     print 'database on the current instance is not in primary replica'
  end
           else
             begin
                if @Status is NULL
                   begin
                     print 'Kindly provide correct database Name'
                   end

          end
 end

In SQL Server 2012 you can create this function and use for finding database is part of Primary Replica.
--------------------------------------------------------------------------------------------------------------------------------------------------------

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_is_database_primary_replica](@database_name sysname) returns bit as

begin

declare
@is_primary bit

declare
@is_role_primary tinyint 

select @is_role_primary =role
from sys.dm_hadr_availability_replica_states where replica_id=

(select replica_id from sys.dm_hadr_database_replica_states where
database_id=DB_ID(@database_name)  and is_local = 1 ); 

If @is_role_primary = 1               

set @is_primary = 1

else

set @is_primary = 0

return @is_primary

End 
------------------------------------------------------------------------------------------------------------------------------------------------------------------

/* Another script does not require to put a database Name */

if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U') 

  and o.id = object_id(N'tempdb..#AGdetails')
)
begin
DROP TABLE #AGdetails;
end;


declare @rd varchar(10)
IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
SELECT

   AGC.name -- Availability Group
, RCS.replica_server_name -- SQL cluster node name
, ARS.role_desc  -- Replica Role
, AGL.dns_name  -- Listener Name

into #AGdetails
FROM
sys.availability_groups_cluster AS AGC
  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
   ON
    RCS.group_id = AGC.group_id
  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
   ON
    ARS.replica_id = RCS.replica_id
  INNER JOIN sys.availability_group_listeners AS AGL
   ON
    AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = 'PRIMARY'
END

select @rd=role_desc from #AGdetails

if @rd = 'PRIMARY'

print  @@servername + ' is Primary Replica'
else
print @@servername + ' is Secondary Replica'
---------------------------------------------------------------------------------
output:-
(1 row(s) affected)

SQLNODE1\SQl1  is Primary Replica

-------------------------------------------------------------------------------------------------------------------
/* Check Which Availability group is primary on this replica - SQLNODE1\SQl1 .. */

if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')
  and o.id = object_id(N'tempdb..#Replicadetails')
              )
begin
DROP TABLE #Replicadetails;
end;
SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
into #Replicadetails
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC

select 'The primary Availability group for this replica  ' + @@servername +   ' is '  +  Name from #Replicadetails where LocalReplicaRole = 1

Output

-----------------------------------------------

The primary Availability group for this SQLNODE1\SQl1   is AG1

  

Script to find out how many seconds is a secondary replica database is behind the primary replica

Script to find out how many seconds is a secondary replica behind the primary replica 


There can be situations when the secondary replica will go behind the primary replica and you want to know how long will it take for both to get in sync but ensure replicas are not in disconnected state.

This script will tell you the estimated time for sync:-


/* Start of script -- Needs to be run on PRIMARY REPLICA*/

WITH PR(database_id, last_commit_time) AS

(
SELECT dr_state.database_id as database_id,
dr_state.last_commit_time FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id
where ar_state.role = 1
)
SELECT ar.replica_server_name AS 'Replica Instance', dr_state.database_id as 'Database ID',
DATEDIFF(s,dr_state.last_commit_time,PR.last_commit_time) AS 'Seconds Behind Primary'
  FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id and dr_state.replica_id = ar_state.replica_id
JOIN PR on PR.database_id = dr_state.database_id

WHERE ar_state.role != 1 and dr_state.synchronization_state = 1

/* End of script */

This script can be used to configure an Alert system where if the synchronization is beyond the RPO limit, an email gets triggered to the DBA mailbox. 

For Example, in below screenshot the secondary replicas [Win2k8r2-2] is 1342 seconds behind its primary replica.

Another Script
---------------------

SELECT AGS.NAME AS AGGroupName

    ,AR.replica_server_name AS InstanceName

    ,HARS.role_desc

    ,Db_name(DRS.database_id) AS DBName

    ,DRS.database_id

    ,is_ag_replica_local = CASE

        WHEN DRS.is_local = 1

            THEN N'LOCAL'

        ELSE 'REMOTE'

        END

    ,AR.availability_mode_desc AS SyncMode

    ,DRS.synchronization_state_desc AS SyncState

    ,DRS.last_hardened_lsn

    ,DRS.end_of_log_lsn

    ,DRS.last_redone_lsn

    ,DRS.last_hardened_time

    ,DRS.last_redone_time

    ,DRS.log_send_queue_size

    ,DRS.redo_queue_size AS 'Redo_Queue_Size(KB)'

    /*

    if the last_hardened_lsn from the primary server == last_hardened_lsn from secondary server

    then there is NO LATENCY

    */

    ,'seconds behind primary' = CASE

            WHEN EXISTS (

                    SELECT DRS.last_hardened_lsn

                    FROM (

                        (

                            sys.availability_groups AS AGS INNER JOIN sys.availability_replicas AS AR ON AGS.group_id = AR.group_id

                            ) INNER JOIN sys.dm_hadr_availability_replica_states AS HARS ON AR.replica_id = HARS.replica_id

                        )

                    INNER JOIN sys.dm_hadr_database_replica_states DRS ON AGS.group_id = DRS.group_id

                        AND DRS.replica_id = HARS.replica_id

                    WHERE HARS.role_desc = 'PRIMARY'

                        AND DRS.last_hardened_lsn = DRS.last_hardened_lsn

                    )

                THEN 0

            ELSE datediff(s, last_hardened_time, getdate())

            end

FROM sys.dm_hadr_database_replica_states DRS

LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id

LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id

LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id

    AND AR.replica_id = HARS.replica_id

ORDER BY Db_name(DRS.database_id)

    ,is_ag_replica_local

  

The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Msg 41131, Level 16, State 0, Line 3

Failed to bring availability group 'SQL00CansaAG01' online. The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Msg 41131, Level 16, State 0, Line 3

---------------------------------------------------------------------------------------------------------------------