Buenas a todos! Hoy veremos cómo monitorizar el GAP en AlwaysOn para poder reaccionar rápidamente y minimizar el impacto que tiene recuperar al punto de endurecimiento. Este punto es de crucial importancia ya que es el umbral donde el log se va vaciando, originando la parada de otros nodos por causa de este y desembocando en problemas no esperados. Más información sobre AlwaysOn en https://www.gpsos.es/2020/09/alta-disponibilidad-en-versiones-standard/
¿Cómo monitorizar el GAP en AlwaysOn?
Para monitorizar utilizaremos las tablas:
sys.dm_hadr_availability_replica_states sys.dm_hadr_database_replica_states sys.availability_replicas
Cruzando estas tablas obtenemos el número de segundos de diferencia de sincronización por BBDD así como el destino contra el que sincronizan:
;WITH UpTime AS ( SELECT DATEDIFF(SECOND,create_date,GETDATE()) [upTime_secs] FROM sys.databases WHERE name = 'tempdb' ), AG_Stats AS ( SELECT AR.replica_server_name, HARS.role_desc, Db_name(DRS.database_id) [DBName], CAST(DRS.log_send_queue_size AS DECIMAL(19,2)) log_send_queue_size_KB, (CAST(perf.cntr_value AS DECIMAL(19,2)) / CAST(UpTime.upTime_secs AS DECIMAL(19,2))) / CAST(1024 AS DECIMAL(19,2)) [log_KB_flushed_per_sec] FROM 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 --I am calculating this as an average over the entire time that the instance has been online. --To capture a smaller, more recent window, you will need to: --1. Store the counter value. --2. Wait N seconds. --3. Recheck counter value. --4. Divide the difference between the two checks by N. INNER JOIN sys.dm_os_performance_counters perf ON perf.instance_name = Db_name(DRS.database_id) AND perf.counter_name like 'Log Bytes Flushed/sec%' CROSS APPLY UpTime ), Pri_CommitTime AS ( SELECT replica_server_name , DBName , [log_KB_flushed_per_sec] FROM AG_Stats WHERE role_desc = 'PRIMARY' ), Sec_CommitTime AS ( SELECT replica_server_name , DBName --Send queue will be NULL if secondary is not online and synchronizing , log_send_queue_size_KB FROM AG_Stats WHERE role_desc = 'SECONDARY' ) SELECT p.replica_server_name [primary_replica] , p.[DBName] AS [DatabaseName] , s.replica_server_name [secondary_replica] , CAST(s.log_send_queue_size_KB / p.[log_KB_flushed_per_sec] AS BIGINT) [Sync_Lag_Secs] FROM Pri_CommitTime p LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName]
La salida será la siguiente:

Una vez tenemos los segundos por BBDD nos es fácil agregar una suma y programar un job para que cuando dicha suma sea superior a 5 min (300) salte. Debemos cambiar la salida del WITH con el siguiente código:
declare @TEMPPGAP int; SELECT sum(CAST(s.log_send_queue_size_KB / p.[log_KB_flushed_per_sec] AS BIGINT)) [Sync_Lag_Secs] into ##TEMPPGAP FROM Pri_CommitTime p LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName] set @TEMPPGAP= (select [Sync_Lag_Secs] from ##TEMPPGAP) If ( @TEMPPGAP > 3600) begin EXEC msdb..sp_send_dbmail @profile_name='Alert Manager', @recipients='alertas@gpsos.es', @subject='GAP con las réplicas', @body='Revisar los nodos', @query='select [Sync_Lag_Secs] from ##TEMPPGAP ', @query_result_header = 1, @query_no_truncate = 0 end
A partir de ahora recibiremos un email cuando nuestras réplicas estén desincronizadas.
Si quieres que te echemos una mano con tu entorno ya sea de gestión o de consultoría no dudes en contactarnos!! Nos vemos en la próxima!!
No dudes en suscribirte a nuestra newsletter mensual para no perderte ninguna de nuestras publicaciones con un solo email al mes.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn
Más información sobre AlwaysOn en https://docs.microsoft.com/es-es/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15