Posted in High Availability, replica, Sql Server AlwaysOn, sqlserver, Yüksek Erişilebilirlik

MSSQL Server Always On Gerilik Durumu Takip

   27 Ocak 2026  Leave a Comment on MSSQL Server Always On Gerilik Durumu Takip

Mssql serverda secondary sunucuların senkronizasyon durumlarını görmek aynı zamanda secondary replica sunucuların primary sunucusu ile arasındaki veri farkını ve en son hangi lsn değerinin commit edilip diske yazıldığını detaylı bir şekilde görmüş olacağız. Çalışılan gerçek ortamdan alınmıştır.

Senkronizasyon durumunu kaydedeceğimiz tablomuzu oluşturuyoruz.

USE [DBAMON]
GO

CREATE TABLE [dbo].[AGDBLatency](
	[Collection_Time] [datetime] NOT NULL,
	[primary_replica] [nvarchar](256) NULL,
	[DatabaseName] [nvarchar](128) NULL,
	[secondary_replica] [nvarchar](256) NULL,
	[Pri_Last_Commit_Time] [datetime] NULL,
	[Sec_Last_Commit_Time] [datetime] NULL,
	[Sync_Lag_Secs] [int] NULL,
	[redo_queue_size_KB] [bigint] NULL,
	[redo_rate_KB_Sec] [bigint] NULL,
	[Redo_Lag_Secs] [bigint] NULL,
	[synchronization_state_desc] [nvarchar](60) NULL
) ON [PRIMARY]
GO

Aşağıda oluşturulan procedure yardımıyla yukarıdaki oluşturacağımız sorgumuza verileri kaydettiğimiz ve aynı zamanda son kullanıcıya belirli bir eşik değerinden sonra mail atan yapımızı oluşturmuş olacağız.

Aşağıdaki komut ile procedure yapımızı oluşturuyoruz.

USE [master]
GO

CREATE PROCEDURE [dbo].[USP_AlwaysOn] 
AS 
BEGIN 
 
DECLARE @xml NVARCHAR(MAX) 
DECLARE @body NVARCHAR(MAX) 
DECLARE @DonenKayit INT, @Sorgu NVARCHAR(1000) 
 
Set @DonenKayit = 0 
Set @Sorgu = N'Select @DonenKayitSayisi = COUNT(*) from DBAMON.dbo.AGDBLatency with (nolock) GROUP BY Collection_Time HAVING DATEDIFF(MINUTE,Collection_Time,getdate()) < 30 ORDER BY Collection_Time DESC' 
 
Set @xml =  
((SELECT STUFF((select top 5  
Collection_Time as 'td','' 
,primary_replica as 'td','' 
,secondary_replica as 'td','' 
,DatabaseName as 'td','' 
,Pri_Last_Commit_Time as 'td','' 
,Sec_Last_Commit_Time as 'td','' 
,CONVERT(varchar(5),DATEDIFF(s,Sec_Last_Commit_Time, Pri_Last_Commit_Time)/3600)+ ':' +CONVERT(varchar(5),DATEDIFF(s,Sec_Last_Commit_Time, Pri_Last_Commit_Time)%3600/60)+ ':' +CONVERT(varchar(5),DATEDIFF(s,Sec_Last_Commit_Time, Pri_Last_Commit_Time)%60) as 'td','' 
,Sync_Lag_Secs as 'td','' 
,redo_queue_size_KB as 'td','' 
,redo_rate_KB_Sec as 'td','' 
,Redo_Lag_Secs as 'td','' 
,Synchronization_state_desc as 'td','' 
from DBAMON.dbo.AGDBLatency 
ORDER BY Collection_Time desc 
FOR XML PATH('tr')),1,0,'') )) 
 
SET @body ='<html><body> 
<table border = 1> 
<tr> 
    <th> Collection Time </th> 
                        <th> Primary Replica Server </th> 
                        <th> Secondary Replica Server </th> 
                        <th> Database Name </th> 
                        <th> Primary Last Commit Time </th> 
                        <th> Secondary Last Commit Time </th> 
                        <th> Time Lag </th> 
                        <th> Sync Lag Secs </th> 
                        <th> Redo Queue Size </th> 
                        <th> Redo Rate KB/Sec </th> 
                        <th> Redo Lag Secs </th> 
                        <th> Synchronization_state_desc </th> 
</tr>' 
 
SET @body = @body + @xml + ' 
</table> 
</body></html>' 
 
EXECUTE SP_EXECUTESQL @Sorgu,N'@DonenKayitSayisi INT OUTPUT', @DonenKayitSayisi = @DonenKayit OUTPUT; 
If @DonenKayit > 0 
Begin 
exec msdb.dbo.sp_send_dbmail  
@profile_name = 'AlwaysOnLagProfile',
@recipients = 'DByonetimi@xxx.gov.tr',
@Subject = 'AlwaysOn Lag', 
@body = @body , 
@body_format = 'HTML' 
END 
END

Aşağıdaki komutu bir job aracılığı ile belirli aralıklarla çalıştırmamız gerekmektedir.

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Data Collector' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Data Collector'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AGDBLatency', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'AlwaysOn Gerilik Bilgilendirmesi', 
		@category_name=N'Data Collector', 
		@owner_login_name=N'LOGIN_NAME', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CheckPrimaryAGControl', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=1, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @IsPrimary int
 
SET @IsPrimary = 0
 
SELECT @IsPrimary  = 1
 
FROM sys.availability_groups sag
 
INNER JOIN sys.dm_hadr_availability_group_states hags ON sag.group_id = hags.group_id
 
WHERE hags.primary_replica = @@SERVERNAME AND sag.name  = ''SQLCLS_AG''
 
IF @IsPrimary  = 1
BEGIN
RETURN
END
ELSE
BEGIN
RAISERROR (''Database is running as secondary'', 16, 1)
END', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N';WITH
AG_Stats AS
(
SELECT AR.replica_server_name,
HARS.role_desc,
Db_name(DRS.database_id) [DBName],
DRS.last_commit_time,
DRS.redo_queue_size redo_queue_size_KB,
DRS.redo_rate redo_rate_KB_Sec,
DRS.synchronization_state_desc
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
),
Pri_CommitTime AS
(
SELECT replica_server_name
, DBName
, last_commit_time
, redo_queue_size_KB
, redo_rate_KB_Sec
, synchronization_state_desc
FROM AG_Stats
WHERE role_desc = ''PRIMARY''
),
Sec_CommitTime AS
(
SELECT replica_server_name
, DBName
, last_commit_time
, redo_queue_size_KB
, redo_rate_KB_Sec
, synchronization_state_desc
FROM AG_Stats
WHERE role_desc = ''SECONDARY''
)
 
insert [DBAMON].dbo.AGDBLatency
 
SELECT
getdate() as Collection_Time
, p.replica_server_name [primary_replica]
, p.[DBName] AS [DatabaseName]
, s.replica_server_name [secondary_replica]
, p.last_commit_time as Pri_Last_Commit_Time
, s.last_commit_time as Sec_Last_Commit_Time
, DATEDIFF(ss,s.last_commit_time,p.last_commit_time) AS [Sync_Lag_Secs]
, s.redo_queue_size_KB
, s.redo_rate_KB_Sec
, case s.redo_rate_KB_Sec when 0 then 0 else CAST(s.redo_queue_size_KB / s.redo_rate_KB_Sec / 60 AS BIGINT) end [Redo_Lag_Secs]
, s.synchronization_state_desc
FROM Pri_CommitTime p
LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName]
where [s].[DBName] in (select db.[name] from sys.databases db where db.database_id > 4)
and ISNULL(s.redo_queue_size_KB / NULLIF(s.redo_rate_KB_Sec,0),0) > 0
and DATEDIFF(ss,s.last_commit_time,p.last_commit_time) > 500
order by Collection_Time asc
 
USE [master]
 
EXEC [dbo].[USP_AlwaysOn]', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=10, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20231108, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'9f4aa95f-46f2-4698-8bb5-ae7b4a36d2ad'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Yukarıda oluşturulan yapıyı çalıştırdıktan sonra 500 saniye üzeri olan veritabanlarımız dönmektedir.

Tablomuza tüm değerlerin kaydedildiği görülmektedir.

Bu makalede Alwayson replicalar arasında oluşan senkronizasyon farkını bize atan yapıyı görmüş olduk. Başka makalede görüşmek dileğiyle..

“Hayra vesile olan, hayrı yapan gibidir.” (Tirmizî, İlm, 14.)

Görüntüleme: 7
Author: Yunus YÜCEL

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir