HA status

HA status

SET DEADLOCK_PRIORITY -10;
IF SERVERPROPERTY('EngineEdition') NOT IN (2,3,4) BEGIN /*NOT IN Standard,Enterpris,Express*/
	DECLARE @ErrorMessage AS nvarchar(500) = 'Telegraf - Connection string Server:'+ @@ServerName + ',Database:' + DB_NAME() +' is not a SQL Server Standard,Enterprise or Express. Check the database_type parameter in the telegraf configuration.';
	RAISERROR (@ErrorMessage,11,1)
	RETURN
END

DECLARE
	 @SqlStatement AS nvarchar(max)
	,@MajorMinorVersion AS int = CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar),4) AS int)*100 + CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar),3) AS int)
	,@Columns AS nvarchar(MAX) = ''

IF @MajorMinorVersion >= 1300 BEGIN
	SET @Columns += N'
	,ag.basic_features
	,ag.is_distributed
	,ar.seeding_mode
	,ar.seeding_mode_desc'
END

SET @SqlStatement = N'
IF SERVERPROPERTY(''IsHadrEnabled'') = 1 BEGIN
	SELECT
		''sqlserver_hadr_replica_states'' AS [measurement]
		,REPLACE(@@SERVERNAME, ''\'', '':'') AS [sql_instance]
		,convert(nvarchar(36), hars.replica_id) as replica_id
		,ar.replica_server_name
		,convert(nvarchar(36), hars.group_id) as group_id
		,ag.name AS group_name
		,hags.synchronization_health_desc AS ag_synchronization_health_desc
		,ar.replica_metadata_id
		,ar.availability_mode
		,ar.availability_mode_desc
		,ar.failover_mode
		,ar.failover_mode_desc
		,ar.session_timeout
		,ar.primary_role_allow_connections
		,ar.primary_role_allow_connections_desc
		,ar.secondary_role_allow_connections
		,ar.secondary_role_allow_connections_desc
		,hars.is_local
		,hars.role
		,hars.role_desc
		,hars.operational_state
		,hars.operational_state_desc
		,hars.connected_state
		,hars.connected_state_desc
		,hars.recovery_health
		,hars.recovery_health_desc
		,hars.synchronization_health AS replica_synchronization_health
		,hars.synchronization_health_desc AS replica_synchronization_health_desc
		,hars.last_connect_error_number
		,hars.last_connect_error_description
		,hars.last_connect_error_timestamp'
		+ @Columns + N'
	FROM sys.dm_hadr_availability_replica_states AS hars
	INNER JOIN sys.availability_replicas AS ar on hars.replica_id = ar.replica_id
	INNER JOIN sys.availability_groups AS ag on ar.group_id = ag.group_id
	INNER JOIN sys.dm_hadr_availability_group_states AS hags ON hags.group_id = ag.group_id
END'

EXEC sp_executesql @SqlStatement