Script to see the historical state changes of your AGs


/* Although you are confident that the primary was up, but are you sure the AGs/databases were normal on the primary? As soon as the secondary went down, the AGs may have gone offline (due to loss of quorum). I’d say run the following script to see the historical state changes of your AGs. You can then compare it with the time the secondary had gone down and see if they match.

Source: https://dba.stackexchange.com/a/76018/153965*/

declare @xel_path varchar(1024);
declare @utc_adjustment int = datediff(hour, getutcdate(), getdate());

——————————————————————————-
——————- target event_file path retrieval ————————–
——————————————————————————-
;with target_data_cte as
(
select
target_data =
convert(xml, target_data)
from sys.dm_xe_sessions s
inner join sys.dm_xe_session_targets st
on s.address = st.event_session_address
where s.name = ‘alwayson_health’
and st.target_name = ‘event_file’
),
full_path_cte as
(
select
full_path =
target_data.value(‘(EventFileTarget/File/@name)[1]’, ‘varchar(1024)’)
from target_data_cte
)
select
@xel_path =
left(full_path, len(full_path) – charindex(‘\’, reverse(full_path))) +
‘\AlwaysOn_health*.xel’
from full_path_cte;

——————————————————————————-
——————- replica state change events ——————————-
——————————————————————————-
;with state_change_data as
(
select
object_name,
event_data =
convert(xml, event_data)
from sys.fn_xe_file_target_read_file(@xel_path, null, null, null)
)
select
object_name,
event_timestamp =
dateadd(hour, @utc_adjustment, event_data.value(‘(event/@timestamp)[1]’, ‘datetime’)),
ag_name =
event_data.value(‘(event/data[@name = “availability_group_name”]/value)[1]’, ‘varchar(64)’),
previous_state =
event_data.value(‘(event/data[@name = “previous_state”]/text)[1]’, ‘varchar(64)’),
current_state =
event_data.value(‘(event/data[@name = “current_state”]/text)[1]’, ‘varchar(64)’)
from state_change_data
where object_name = ‘availability_replica_state_change’
order by event_timestamp desc;

AlwaysOn Availability Group Read Only Routing Script


–AlwaysOn Availability Group Read Only Routing Script
https://www.sqlservercentral.com/scripts/alwayson-availability-group-read-only-routing-script
============================================
If your objective is to use your secondary node(s) as readable secondary’s, read on.

While implementing my availability group into higher environments, after having it working quite easily in development,
we ran into a few issues. After a long weekend, and making sure all the appropriate firewall ports were open,
to make the listener, primary and secondary nodes accessible to all of the other components, we found these issues were important;
mainly, because the database servers are in a different domain than the other components.

Not all settings are done via the SSMS UI when setting up the Availability Group
The routing URLs may need to use IP addresses
The routing list is needed for proper read only routing
Set the first six (6) variables for your environment and run the script in text output mode. The commands will be generated to properly configure your read only routing. The select statement at the end will show the key settings once the configuration is applied.

* Note: this script configures the primary and a single secondary node.
=====================================================
USE [master]
GO

SET NOCOUNT ON
GO

DECLARE @AGName VARCHAR(40) = ‘myAvailabilityGroup’ — Availability Group Name
, @PrimaryNodeName VARCHAR(40) = ‘ProdDB01’
, @SecondaryNodeName VARCHAR(40) = ‘ProdDB02’
, @PrimaryNodeIP VARCHAR(40) = ‘10.5.6.10’
, @SecondaryNodeIP VARCHAR(40) = ‘10.6.6.11’
, @Domain VARCHAR(40) = ‘.prod.net’
, @RouteUsingIP TINYINT = 1 — 1 for True, 0 for False (1 is recommended)
, @PrimaryRoutingURL VARCHAR(40) = ” — gets set by script
, @SecondaryRoutingURL VARCHAR(40) = ” — gets set by script
, @SQLCommand VARCHAR(2000)

IF @RouteUsingIP > 0
BEGIN
SET @PrimaryRoutingURL = @PrimaryNodeIP
SET @SecondaryRoutingURL = @SecondaryNodeIP
END
ELSE
BEGIN
SET @PrimaryRoutingURL = @PrimaryNodeName + @Domain
SET @SecondaryRoutingURL = @SecondaryNodeName + @Domain
END

SET @SQLCommand = N’
ALTER AVAILABILITY GROUP [‘+@AGName+’] MODIFY REPLICA ON
N”’+@PrimaryNodeName+”’ WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));’
PRINT @SQLCommand

SET @SQLCommand = N’
ALTER AVAILABILITY GROUP [‘+@AGName+’] MODIFY REPLICA ON
N”’+@PrimaryNodeName+”’ WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N”TCP://’+@PrimaryRoutingURL+’:1433”));’
PRINT @SQLCommand

SET @SQLCommand = N’
ALTER AVAILABILITY GROUP [‘+@AGName+’] MODIFY REPLICA ON
N”’+@SecondaryNodeName+”’ WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));’
PRINT @SQLCommand

SET @SQLCommand = N’
ALTER AVAILABILITY GROUP [‘+@AGName+’] MODIFY REPLICA ON
N”’+@SecondaryNodeName+”’ WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N”TCP://’+@SecondaryRoutingURL+’:1433”));’
PRINT @SQLCommand

SET @SQLCommand = N’
ALTER AVAILABILITY GROUP [‘+@AGName+’] MODIFY REPLICA ON
N”’+@PrimaryNodeName+”’ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(”’+@SecondaryNodeName+”’,”’+@PrimaryNodeName+”’)));’
PRINT @SQLCommand

SET @SQLCommand = N’
ALTER AVAILABILITY GROUP [‘+@AGName+’] MODIFY REPLICA ON
N”’+@SecondaryNodeName+”’ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(”’+@PrimaryNodeName+”’,”’+@SecondaryNodeName+”’)));’
PRINT @SQLCommand

/*
SELECT replica_server_name ,endpoint_url ,availability_mode_desc ,failover_mode_desc
,session_timeout ,primary_role_allow_connections_desc ,secondary_role_allow_connections_desc
,create_date ,read_only_routing_url
FROM sys.availability_replicas
*/

AAG DB State Monitoring


–AAG DB State Monitoring
https://www.sqlservercentral.com/scripts/aag-db-state-monitoring
–This AAG Database State monitoring Script could be added as a step in a SQL job that runs frequently
–to check the status of AAG Database Synchronization.
====================================================================
declare @subject varchar(100)
declare @xml nvarchar(max)
declare @body nvarchar(max)
set nocount on
Create table #output(InstName varchar(50),
AGName varchar(50),
DBName varchar(50),
[State] varchar(20)
)

insert into #output(InstName, AGName, DBName, [State])

select @@servername, ag.[name], sd.[name], hdrs.synchronization_state_desc
from sys.availability_groups ag join sys.dm_hadr_availability_group_states hags
on ag.group_id = hags.group_id join sys.dm_hadr_database_replica_states hdrs
on hags.group_id = hdrs.group_id join sys.sysdatabases sd
on hdrs.database_id = sd.dbid
where hdrs.synchronization_state_desc not in(‘SYNCHRONIZING’, ‘SYNCHRONIZED’)

if @@rowcount > 0
begin
set @xml = cast((select InstName as ‘td’,”, AGName as ‘td’,”, DBName as ‘td’,”, [State] as ‘td’
from #output
for xml path(‘tr’), elements ) as nvarchar(max))

set @body = ‘<html><body><H3>AAG Status Report</H3>
<table border =1>
<tr>
<th> ServerName </th> <th> Availability Group </th> <th> Database </th> <th> Status </th></tr>’

set @subject = ‘Availability Group not Synchronizing: ‘
set @body = @body + @xml + ‘</table><body></html>’

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Default’,
@recipients=’DBA@SomeDomain.com’,
@subject = @subject,
@body = @body,
@body_format = ‘HTML’ ;

end
drop table #output

Always On Status Report


–AlwaysOn — SQL 2012 Always On Status Report
— Always On Status Report

— This script will show the status of the Alway On replication status

SELECT DISTINCT
primary_replica as ‘Primary Server’,
[endpoint_url] as ‘End Point URL’,
primary_recovery_health_desc as ‘Primary Server Health Status’,
secondary_recovery_health_desc as ‘Secondary Server Health Status’,
operational_state_desc as ‘Operational State’,
connected_state_desc as ‘Connection State’,
recovery_health_desc as ‘Recovery Health’,
synchronization_state_desc as ‘Synchronization State’,
database_state_desc as ‘Database State’,
JOIN_state_desc as ‘Join State’,
suspend_reason_desc as ‘Suspended Reason’,
availability_mode_desc as ‘Availability Mode’,
failover_mode_desc as ‘Failover Mode’,
primary_role_allow_connections_desc as ‘Primary Connections Allowed’,
secondary_role_allow_connections_desc as ‘Secondary Connections Allowed’,
create_date as ‘Date Created’,
modify_date as ‘Date Modified’,
[backup_priority] as ‘Backup Priority’,
role_desc as ‘Role Type’,
last_connect_error_description as ‘Last Connection Error’,
last_connect_error_timestamp as ‘Last Connection Error Time’,
last_sent_time as ‘Last Data Send Time’,
last_received_time as ‘Last Data Recieved TIme’,
last_hardened_time as ‘Last Hardened Time’,
last_redone_time as ‘Last Redone Time’,
log_send_queue_size as ‘Log Send Queue Size’,
log_send_rate as ‘Log Send Rate’,
redo_queue_size as ‘Redo Queue Size’,
redo_rate as ‘Rate of Redo’,
filestream_send_rate as ‘Filestream Send Rate’,
last_commit_time as ‘ Last Commit Time’,
low_water_mark_for_ghosts as ‘Low Water Mark for Ghosts’
FROM sys.dm_hadr_availability_group_states

JOIN sys.availability_replicas
ON sys.dm_hadr_availability_group_states.group_id = sys.availability_replicas.group_id

JOIN sys.dm_hadr_availability_replica_cluster_states
ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_availability_replica_cluster_states.group_id

JOIN sys.dm_hadr_availability_replica_states
ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_availability_replica_states.group_id

JOIN sys.dm_hadr_database_replica_states
ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_database_replica_states.group_id

WHERE operational_state_desc IS NOT NULL
AND database_state_desc IS NOT NULL
ORDER BY [endpoint_url] DESC

— Testing section
/*
— Suspend replication from primary
ALTER DATABASE [AdventureWorksLT2008] SET HADR SUSPEND

— Resume replication from secondary
ALTER DATABASE [AdventureWorksLT2008] SET HADR RESUME

— Force a manual failover of replication with data loss. MUST EXECUTE ON SECONDARY SQL SERVER
ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FORCE_FAILOVER_ALLOW_DATA_LOSS;

— Force a manual failover of replication with NO data loss MUST EXECUTE ON SECONDARY SQL SERVER
— YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
–Connect VDV1OPS03

ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FAILOVER;
GO

— YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
–:Connect OPSDBSRV

ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FAILOVER;
GO

*/

Configuring-alerts-for-sql-server-alwayson-availability-groups/


https://www.mssqltips.com/sqlservertip/2973/configuring-alerts-for-sql-server-alwayson-availability-groups/

SET NOCOUNT ON

— first create a temporary table to store your target error numbers
DECLARE @errorNumbers TABLE ( ErrorNumber VARCHAR(6) )
INSERT INTO @errorNumbers
VALUES (‘35273’),(‘35274’),(‘35275’),(‘35254’),(‘35279’),(‘35262’),(‘35276’),(‘41048’),(‘41049’),(‘41050’),
(‘41089’)

— get the correct DB context
PRINT ‘USE [msdb]’
PRINT ‘GO’
PRINT ‘/* *************************************************************** */ ‘

— use a cursor to iterate over each error number (yes, I know)…
DECLARE @thisErrorNumber VARCHAR(6)

DECLARE cur_ForEachErrorNumber CURSOR LOCAL FAST_FORWARD
FOR SELECT ErrorNumber FROM @errorNumbers

OPEN cur_ForEachErrorNumber

FETCH NEXT FROM cur_ForEachErrorNumber INTO @thisErrorNumber
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
‘EXEC msdb.dbo.sp_add_alert @name=N”HA Error – ‘ + @thisErrorNumber + ”’,
@message_id=’ + @thisErrorNumber + ‘,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N”00000000-0000-0000-0000-000000000000”
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N”HA Error – ‘ + @thisErrorNumber + ”’,
@operator_name=N”Smart”, @notification_method = 1
GO ‘
PRINT ‘/* *************************************************************** */ ‘
FETCH NEXT FROM cur_ForEachErrorNumber INTO @thisErrorNumber
END

CLOSE cur_ForEachErrorNumber
DEALLOCATE cur_ForEachErrorNumber

Monitor Status and Health of AlwaysOn Availability Groups


–monitor Status and Health of AlwaysOn Availability Groups
DECLARE @DRName varchar(30)
SET @DRName = @@SERVERNAME
select
n.group_name
,n.replica_server_name
,n.node_name,rs.role_desc
,db_name(drs.database_id) as database_name
,drs.synchronization_state_desc
,drs.synchronization_health_desc
from sys.dm_hadr_availability_replica_cluster_nodes n
join sys.dm_hadr_availability_replica_cluster_states cs
on n.replica_server_name = cs.replica_server_name
join sys.dm_hadr_availability_replica_states rs
on rs.replica_id = cs.replica_id
join sys.dm_hadr_database_replica_states drs
on rs.replica_id=drs.replica_id
where n.replica_server_name <> @DRName

Check the historical state changes of your AGs Information


/* Although you are confident that the primary was up, but are you sure the AGs/databases were normal on the primary? As soon as the secondary went down, the AGs may have gone offline (due to loss of quorum). I'd say run the following script to see the historical state changes of your AGs. You can then compare it with the time the secondary had gone down and see if they match.

Source: https://dba.stackexchange.com/a/76018/153965*/


declare @xel_path varchar(1024);
declare @utc_adjustment int = datediff(hour, getutcdate(), getdate());

-------------------------------------------------------------------------------
------------------- target event_file path retrieval --------------------------
-------------------------------------------------------------------------------
;with target_data_cte as
(
select 
target_data = 
convert(xml, target_data)
from sys.dm_xe_sessions s
inner join sys.dm_xe_session_targets st
on s.address = st.event_session_address
where s.name = 'alwayson_health'
and st.target_name = 'event_file'
),
full_path_cte as
(
select
full_path = 
target_data.value('(EventFileTarget/File/@name)[1]', 'varchar(1024)')
from target_data_cte
)
select
@xel_path = 
left(full_path, len(full_path) - charindex('\', reverse(full_path))) + 
'\AlwaysOn_health*.xel'
from full_path_cte;

-------------------------------------------------------------------------------
------------------- replica state change events -------------------------------
-------------------------------------------------------------------------------
;with state_change_data as
(
select
object_name,
event_data = 
convert(xml, event_data)
from sys.fn_xe_file_target_read_file(@xel_path, null, null, null)
)
select
object_name,
event_timestamp = 
dateadd(hour, @utc_adjustment, event_data.value('(event/@timestamp)[1]', 'datetime')),
ag_name = 
event_data.value('(event/data[@name = "availability_group_name"]/value)[1]', 'varchar(64)'),
previous_state = 
event_data.value('(event/data[@name = "previous_state"]/text)[1]', 'varchar(64)'),
current_state = 
event_data.value('(event/data[@name = "current_state"]/text)[1]', 'varchar(64)')
from state_change_data
where object_name = 'availability_replica_state_change'
order by event_timestamp desc;