Shulou(Shulou.com)06/01 Report--

Based on the characteristics of SQL Server business database in production environment, several groups of AlwaysOn AG Primary Replicates for SQL Server production environment need to be deployed as follows:

1. Windows cluster node is abnormal

2. Any business database does not join the availability group

3. Any database status exception in AG

4. Any database latency exceeds threshold

1. primary replica Check the number of nodes with abnormal Windows cluster status (>0 alarm)

SELECT COUNT (*) as NotHealtyCountFROM sys.dm_hadr_cluster_membersWHERE member_state_desc != 'UP';

2. primary replica checks the comparison between the number of available databases for replica and the number of service databases to be added to AG (0 alarm)

SELECT COUNT (*) as NotHealtyCountFROM [master]. [sys]. [dm_hadr_database_replica_states]WHERE [synchronization_health_desc] != N'HEALTHY'

4. primary replica Delay in checking any database (>900 alarm)

;WITH AG_Stats AS (SELECT AR.replica_server_name, HARS.role_desc, Db_name(DRS.database_id) [DBName], DRS.last_commit_timeFROM sys.dm_hadr_database_replica_states DRS INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id AND AR.replica_id = HARS.replica_id ),Pri_CommitTime AS (SELECTreplica_server_name, DBName, last_commit_timeFROMAG_StatsWHERErole_desc = 'PRIMARY'),Sec_CommitTime AS (SELECTreplica_server_name, DBName, last_commit_timeFROMAG_StatsWHERErole_desc = 'SECONDARY')SELECT ISNULL(DATEDIFF(ss,s.last_commit_time,p.last_commit_time),0) AS [Sync_Lag_Secs]FROM Pri_CommitTime pLEFT JOIN Sec_CommitTime s ON [s]. [DBName] = [p]. [DBName]

Zabbix parameter configuration example:

UserParameter=MSSQL.SyncHealth,"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE" -d Master -U zabbix -P zabbix -h -1 -W -Q "SELECT COUNT (*) as NotHealtyCount

FROM [master]. [sys]. [dm_hadr_database_replica_states]

WHERE [synchronization_health_desc] != N''HEALTHY''

"

It is suggested that items 1, 3 and 4 be configured as templates, and 4 parameters be configured separately for item 2.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.