Allways on Availability Group Himanshu Patel SQL Server Consultant developer & Administrator -

Here i am trying to explain most common internal objects to get information of database with always on.

Following query provides good overview of Always On availability groups health and status.

SELECT ag.name AS [AG Name]
	,ar.replica_server_name
	,ar.availability_mode_desc
	,adc.[database_name]
	,drs.is_local
	,drs.is_primary_replica
	,drs.synchronization_state_desc
	,drs.is_commit_participant
	,drs.synchronization_health_desc
	,drs.recovery_lsn
	,drs.truncation_lsn
	,drs.last_sent_lsn
	,drs.last_sent_time
	,drs.last_received_lsn
	,drs.last_received_time
	,drs.last_hardened_lsn
	,drs.last_hardened_time
	,drs.last_redone_lsn
	,drs.last_redone_time
	,drs.log_send_queue_size
	,drs.log_send_rate
	,drs.redo_queue_size
	,drs.redo_rate
	,drs.filestream_send_rate
	,drs.end_of_log_lsn
	,drs.last_commit_lsn
	,drs.last_commit_time
	,drs.database_state_desc
FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK) ON drs.group_id = adc.group_id
	AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag WITH (NOLOCK) ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK) ON drs.group_id = ar.group_id
	AND drs.replica_id = ar.replica_id
ORDER BY ag.name
	,ar.replica_server_name
	,adc.[database_name]
OPTION (RECOMPILE);

Show All availability groups visible to this server where this Server is a Secondary replica

SELECT Groups.[Name] AS AGname
	,primary_replica
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
WHERE primary_replica != @@Servername;

Show All Databases in an availability group visible to this server where this Server is the primary replica

SELECT Groups.[Name] AS AGname
	,AGD.database_name
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGD ON Groups.group_id = AGD.group_id
WHERE primary_replica = @@Servername
ORDER BY AGname ASC
	,database_name ASC;

Show Availability groups visible to the Server and Replica information such as Which server is the Primary
Sync and Async modes , Readable Secondary and Failover Mode, these can all be filtered using a Where clause
if you are running some checks, no Where clause will show you all of the information.

WITH AGStatus
AS (
	SELECT name AS AGname
		,replica_server_name
		,CASE 
			WHEN (primary_replica = replica_server_name)
				THEN 1
			ELSE ''
			END AS IsPrimaryServer
		,secondary_role_allow_connections_desc AS ReadableSecondary
		,[availability_mode] AS [Synchronous]
		,failover_mode_desc
	FROM master.sys.availability_groups Groups
	INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
	INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
	)
SELECT [AGname]
	,[Replica_server_name]
	,[IsPrimaryServer]
	,[Synchronous]
	,[ReadableSecondary]
	,[Failover_mode_desc]
FROM AGStatus
--WHERE IsPrimaryServer = 1 AND Synchronous = 1
ORDER BY AGname ASC
	,IsPrimaryServer DESC;

All status AG Availability Group

--->> all status AG Avaibility Group
SELECT [AG].[name] AS [AvailabilityGroupName]
	,[AR].[replica_server_name] AS [AvailabilityReplicaServerName]
	,[dbcs].[database_name] AS [AvailabilityDatabaseName]
	,ISNULL([dbcs].[is_failover_ready], 0) AS [IsFailoverReady]
	,ISNULL([arstates].[role_desc], 3) AS [ReplicaRole]
	,[AR].[availability_mode_desc] AS [AvailabilityMode]
	,CASE [dbcs].[is_failover_ready]
		WHEN 1
			THEN 0
		ELSE ISNULL(DATEDIFF([ss], [dbr].[last_commit_time], [dbrp].[last_commit_time]), 0)
		END AS [EstimatedDataLoss_(Seconds)]
	,ISNULL(CASE [dbr].[redo_rate]
			WHEN 0
				THEN - 1
			ELSE CAST([dbr].[redo_queue_size] AS FLOAT) / [dbr].[redo_rate]
			END, - 1) AS [EstimatedRecoveryTime_(Seconds)]
	,ISNULL([dbr].[is_suspended], 0) AS [IsSuspended]
	,ISNULL([dbr].[suspend_reason_desc], '-') AS [SuspendReason]
	,ISNULL([dbr].[synchronization_state_desc], 0) AS [SynchronizationState]
	,ISNULL([dbr].[last_received_time], 0) AS [LastReceivedTime]
	,ISNULL([dbr].[last_redone_time], 0) AS [LastRedoneTime]
	,ISNULL([dbr].[last_sent_time], 0) AS [LastSentTime]
	,ISNULL([dbr].[log_send_queue_size], - 1) AS [LogSendQueueSize]
	,ISNULL([dbr].[log_send_rate], - 1) AS [LogSendRate_KB/S]
	,ISNULL([dbr].[redo_queue_size], - 1) AS [RedoQueueSize_KB]
	,ISNULL([dbr].[redo_rate], - 1) AS [RedoRate_KB/S]
	,ISNULL(CASE [dbr].[log_send_rate]
			WHEN 0
				THEN - 1
			ELSE CAST([dbr].[log_send_queue_size] AS FLOAT) / [dbr].[log_send_rate]
			END, - 1) AS [SynchronizationPerformance]
	,ISNULL([dbr].[filestream_send_rate], - 1) AS [FileStreamSendRate]
	,ISNULL([dbcs].[is_database_joined], 0) AS [IsJoined]
	,[arstates].[is_local] AS [IsLocal]
	,ISNULL([dbr].[last_commit_lsn], 0) AS [LastCommitLSN]
	,ISNULL([dbr].[last_commit_time], 0) AS [LastCommitTime]
	,ISNULL([dbr].[last_hardened_lsn], 0) AS [LastHardenedLSN]
	,ISNULL([dbr].[last_hardened_time], 0) AS [LastHardenedTime]
	,ISNULL([dbr].[last_received_lsn], 0) AS [LastReceivedLSN]
	,ISNULL([dbr].[last_redone_lsn], 0) AS [LastRedoneLSN]
FROM SYS.AVAILABILITY_GROUPS AS [AG]
INNER JOIN sys.availability_replicas AS [AR] ON [AR].[group_id] = [AG].[group_id]
INNER JOIN sys.dm_hadr_database_replica_cluster_states AS [dbcs] ON [dbcs].[replica_id] = [AR].[replica_id]
LEFT OUTER JOIN sys.dm_hadr_database_replica_states AS [dbr] ON [dbcs].[replica_id] = [dbr].[replica_id]
	AND [dbcs].[group_database_id] = [dbr].[group_database_id]
LEFT OUTER JOIN (
	SELECT [ars].[role]
		,[drs].[database_id]
		,[drs].[replica_id]
		,[drs].[last_commit_time]
	FROM sys.dm_hadr_database_replica_states AS [drs]
	LEFT JOIN sys.dm_hadr_availability_replica_states [ars] ON [drs].[replica_id] = [ars].[replica_id]
	WHERE [ars].[role] = 1
	) AS [dbrp] ON [dbr].[database_id] = [dbrp].[database_id]
INNER JOIN sys.dm_hadr_availability_replica_states AS [arstates] ON [arstates].[replica_id] = [AR].[replica_id]
--WHERE  [AG].[name] = ISNULL(@AGname,[AG].[name])
ORDER BY [AvailabilityReplicaServerName] ASC
	,[AvailabilityDatabaseName] ASC;

prtfromance and estimation

SELECT [AG].[name] AS [AvailabilityGroupName]
	,[AR].[replica_server_name] AS [AvailabilityReplicaServerName]
	,[dbcs].[database_name] AS [AvailabilityDatabaseName]
	,ISNULL([dbcs].[is_failover_ready], 0) AS [IsFailoverReady]
	,ISNULL([arstates].[role_desc], 3) AS [ReplicaRole]
	,CASE [dbcs].[is_failover_ready]
		WHEN 1
			THEN 0
		ELSE ISNULL(DATEDIFF([ss], [dbr].[last_commit_time], [dbrp].[last_commit_time]), 0)
		END AS [EstimatedDataLoss_(Seconds)]
	,ISNULL(CASE [dbr].[redo_rate]
			WHEN 0
				THEN - 1
			ELSE CAST([dbr].[redo_queue_size] AS FLOAT) / [dbr].[redo_rate]
			END, - 1) AS [EstimatedRecoveryTime_(Seconds)]
	,ISNULL(CASE [dbr].[log_send_rate]
			WHEN 0
				THEN - 1
			ELSE CAST([dbr].[log_send_queue_size] AS FLOAT) / [dbr].[log_send_rate]
			END, - 1) AS [SynchronizationPerformance]
	,ISNULL([dbr].[filestream_send_rate], - 1) AS [FileStreamSendRate]
	,ISNULL([dbcs].[is_database_joined], 0) AS [IsJoined]
	,[arstates].[is_local] AS [IsLocal]
FROM SYS.AVAILABILITY_GROUPS AS [AG]
INNER JOIN sys.availability_replicas AS [AR] ON [AR].[group_id] = [AG].[group_id]
INNER JOIN sys.dm_hadr_database_replica_cluster_states AS [dbcs] ON [dbcs].[replica_id] = [AR].[replica_id]
LEFT OUTER JOIN sys.dm_hadr_database_replica_states AS [dbr] ON [dbcs].[replica_id] = [dbr].[replica_id]
	AND [dbcs].[group_database_id] = [dbr].[group_database_id]
LEFT OUTER JOIN (
	SELECT [ars].[role]
		,[drs].[database_id]
		,[drs].[replica_id]
		,[drs].[last_commit_time]
	FROM sys.dm_hadr_database_replica_states AS [drs]
	LEFT JOIN sys.dm_hadr_availability_replica_states [ars] ON [drs].[replica_id] = [ars].[replica_id]
	WHERE [ars].[role] = 1
	) AS [dbrp] ON [dbr].[database_id] = [dbrp].[database_id]
INNER JOIN sys.dm_hadr_availability_replica_states AS [arstates] ON [arstates].[replica_id] = [AR].[replica_id]
--WHERE  [AG].[name] = ISNULL(@AGname,[AG].[name])
ORDER BY [AvailabilityDatabaseName] ASC;

AG log read

sp_readerrorlog 0,1,'The state of the local availability replica',''
sp_readerrorlog 0,1, 'RESOLVING'

SQL Server internal objects

SELECT member_name
	,member_state_desc
	,number_of_quorum_votes
	,*
FROM sys.dm_hadr_cluster_members;

SELECT cluster_name
	,quorum_type_desc
	,quorum_state_desc
	,*
FROM sys.dm_hadr_cluster WITH (NOLOCK)
OPTION (RECOMPILE);

SELECT *
FROM sys.dm_hadr_cluster_networks

select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_cluster_networks
select * from sys.dm_hadr_availability_group_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_instance_node_map
select * from sys.dm_hadr_availability_replica_states
select * from sys.dm_hadr_name_id_map
select * from sys.dm_hadr_cluster
select * from sys.dm_tcp_listener_states
select * from sys.dm_hadr_cluster_members
-- Availability Groups catalog views 
select * from sys.availability_databases_cluster
select * from sys.availability_groups_cluster
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_read_only_routing_lists
select * from sys.availability_group_listeners
select * from sys.availability_replicas
select * from sys.availability_groups

SQL Server extended event review using script

SELECT  xml_data.value('(event/@name)[1]','varchar(max)') AS 'Name'  
,xml_data.value('(event/@package)[1]','varchar(max)') AS 'Package'  
,xml_data.value('(event/@timestamp)[1]','datetime') AS 'Time'  
,xml_data.value('(event/data[@name=''state'']/value)[1]','int') AS 'State'  
,xml_data.value('(event/data[@name=''state_desc'']/text)[1]','varchar(max)') AS 'State Description'  
,xml_data.value('(event/data[@name=''failure_condition_level'']/value)[1]','int') AS 'Failure Conditions'  
,xml_data.value('(event/data[@name=''node_name'']/value)[1]','varchar(max)') AS 'Node_Name'  
,xml_data.value('(event/data[@name=''instancename'']/value)[1]','varchar(max)') AS 'Instance Name'  
,xml_data.value('(event/data[@name=''creation time'']/value)[1]','datetime') AS 'Creation Time'  
,xml_data.value('(event/data[@name=''component'']/value)[1]','varchar(max)') AS 'Component'  
,xml_data.value('(event/data[@name=''message'']/value)[1]','varchar(max)') AS 'message'  
,xml_data.value('(event/data[@name=''destination'']/text)[1]','varchar(max)') AS 'destination' 
,xml_data.value('(event/data[@name=''availability_group_name'']/value)[1]','varchar(max)') AS 'availability_group_name' 
,xml_data.value('(event/data[@name=''current_state'']/text)[1]','varchar(max)') AS current_state
,xml_data.value('(event/data[@name=''previous_state'']/text)[1]','varchar(max)') AS previous_state
,xml_data.value('(event/data[@name=''data'']/value)[1]','varchar(max)') AS 'Data'  
,xml_data.value('(event/data[@name=''info'']/value)[1]','varchar(max)') AS 'Info'  ,xml_data
FROM   ( SELECT object_name AS 'event'    ,CONVERT(xml,event_data) AS 'xml_data' FROM sys.fn_xe_file_target_read_file('AlwaysOn*.xel',NULL,NULL,NULL)   
)   AS XEventData  
ORDER BY Time;  

;WITH cte_HADR
AS (
	SELECT object_name
		,CONVERT(XML, event_data) AS data
	FROM sys.fn_xe_file_target_read_file('AlwaysOn*.xel', NULL, NULL, NULL)
	WHERE object_name = 'error_reported'
	)
SELECT data.value('(/event/@timestamp)[1]', 'datetime') AS [timestamp]
	,data.value('(/event/data[@name=''error_number''])[1]', 'int') AS [error_number]
	,data.value('(/event/data[@name=''message''])[1]', 'varchar(max)') AS [message]
FROM cte_HADR
WHERE data.value('(/event/data[@name=''error_number''])[1]', 'int') = 1480

uspCalculateRPO
Calculate RPO of a secondary database.
@group_id uniqueidentifier: group_id of the secondary database.
@replica_id uniqueidentifier: replica_id of the secondary database.
@group_database_id uniqueidentifier: group_database_id of the secondary database.

CREATE PROCEDURE uspCalculateRPO (
	@group_id UNIQUEIDENTIFIER
	,@replica_id UNIQUEIDENTIFIER
	,@group_database_id UNIQUEIDENTIFIER
	)
AS
BEGIN
	DECLARE @db_name SYSNAME
	DECLARE @is_primary_replica BIT
	DECLARE @is_failover_ready BIT
	DECLARE @is_local BIT
	DECLARE @last_commit_time_sec DATETIME
	DECLARE @last_commit_time_pri DATETIME
	DECLARE @RPO NVARCHAR(max)

	-- secondary database's last_commit_time 
	SELECT @db_name = dbcs.database_name
		,@is_failover_ready = dbcs.is_failover_ready
		,@last_commit_time_sec = dbr.last_commit_time
	FROM sys.dm_hadr_database_replica_states dbr
	JOIN sys.dm_hadr_database_replica_cluster_states dbcs ON dbr.replica_id = dbcs.replica_id
		AND dbr.group_database_id = dbcs.group_database_id
	WHERE dbr.group_id = @group_id
		AND dbr.replica_id = @replica_id
		AND dbr.group_database_id = @group_database_id

	-- correlated primary database's last_commit_time 
	SELECT @last_commit_time_pri = dbr.last_commit_time
		,@is_local = dbr.is_local
	FROM sys.dm_hadr_database_replica_states dbr
	JOIN sys.dm_hadr_database_replica_cluster_states dbcs ON dbr.replica_id = dbcs.replica_id
		AND dbr.group_database_id = dbcs.group_database_id
	WHERE dbr.group_id = @group_id
		AND dbr.is_primary_replica = 1
		AND dbr.group_database_id = @group_database_id

	IF @is_local IS NULL
		OR @is_failover_ready IS NULL
	BEGIN
		PRINT 'RPO of database ' + @db_name + ' is not available'

		RETURN
	END

	IF @is_local = 0
	BEGIN
		PRINT 'You are visiting wrong replica'

		RETURN
	END

	IF @is_failover_ready = 1
		SET @RPO = '00:00:00'
	ELSE IF @last_commit_time_sec IS NULL
		OR @last_commit_time_pri IS NULL
	BEGIN
		PRINT 'RPO of database ' + @db_name + ' is not available'

		RETURN
	END
	ELSE
	BEGIN
		IF DATEDIFF(ss, @last_commit_time_sec, @last_commit_time_pri) < 0
		BEGIN
			PRINT 'RPO of database ' + @db_name + ' is not available'

			RETURN
		END
		ELSE
			SET @RPO = CONVERT(VARCHAR, DATEADD(ms, datediff(ss, @last_commit_time_sec, @last_commit_time_pri) * 1000, 0), 114)
	END

	PRINT 'RPO of database ' + @db_name + ' is ' + @RPO
END

uspCalculateRTO:- Calculate RTO of a secondary database

parameters: @secondary_database_name nvarchar(max): name of the secondary database.

CREATE PROCEDURE uspCalculateRTO (@secondary_database_name NVARCHAR(max))
AS
BEGIN
	DECLARE @db SYSNAME
	DECLARE @is_primary_replica BIT
	DECLARE @is_failover_ready BIT
	DECLARE @redo_queue_size BIGINT
	DECLARE @redo_rate BIGINT
	DECLARE @replica_id UNIQUEIDENTIFIER
	DECLARE @group_database_id UNIQUEIDENTIFIER
	DECLARE @group_id UNIQUEIDENTIFIER
	DECLARE @RTO FLOAT

	SELECT @is_primary_replica = dbr.is_primary_replica
		,@is_failover_ready = dbcs.is_failover_ready
		,@redo_queue_size = dbr.redo_queue_size
		,@redo_rate = dbr.redo_rate
		,@replica_id = dbr.replica_id
		,@group_database_id = dbr.group_database_id
		,@group_id = dbr.group_id
	FROM sys.dm_hadr_database_replica_states dbr
	JOIN sys.dm_hadr_database_replica_cluster_states dbcs ON dbr.replica_id = dbcs.replica_id
		AND dbr.group_database_id = dbcs.group_database_id
	WHERE dbcs.database_name = @secondary_database_name

	IF @is_primary_replica IS NULL
		OR @is_failover_ready IS NULL
		OR @redo_queue_size IS NULL
		OR @replica_id IS NULL
		OR @group_database_id IS NULL
		OR @group_id IS NULL
	BEGIN
		PRINT 'RTO of Database ' + @secondary_database_name + ' is not available'

		RETURN
	END
	ELSE IF @is_primary_replica = 1
	BEGIN
		PRINT 'You are visiting wrong replica';

		RETURN
	END

	IF @redo_queue_size = 0
		SET @RTO = 0
	ELSE IF @redo_rate IS NULL
		OR @redo_rate = 0
	BEGIN
		PRINT 'RTO of Database ' + @secondary_database_name + ' is not available'

		RETURN
	END
	ELSE
		SET @RTO = CAST(@redo_queue_size AS FLOAT) / @redo_rate

	PRINT 'RTO of Database ' + @secondary_database_name + ' is ' + convert(VARCHAR, ceiling(@RTO))
	PRINT 'group_id of Database ' + @secondary_database_name + ' is ' + convert(NVARCHAR(50), @group_id)
	PRINT 'replica_id of Database ' + @secondary_database_name + ' is ' + convert(NVARCHAR(50), @replica_id)
	PRINT 'group_database_id of Database ' + @secondary_database_name + ' is ' + convert(NVARCHAR(50), @group_database_id)
END

RAISERROR (
		'creating procedure proc_calculate_RPO'
		,0
		,1
		)
WITH NOWAIT
GO

Spread the love

Allways on Availability Group

All status AG Availability Group

prtfromance and estimation

AG log read

SQL Server extended event review using script

Leave a Comment Cancel Reply

auxiliary.it.india@gmail.com

Skype @hapatel76