MSSQL Server Always On Yapılarında Job Senkronizasyonu

Yüksek erişilebilirlik çözümlerinin kalbi olan Always On Availability Groups mimarisinde, veritabanları otomatik olarak senkronize olsa da, SQL Agent Job’ları, Alert’ler ve Schedule’lar gibi sunucu seviyesindeki nesnelerin manuel olarak taşınması ve güncel tutulması operasyonel süreklilik için hayati önem taşır.

ilk olarak iki sunucu arasında linked server bağlantısının sağlanması gerekmektedir.

EXEC sp_addlinkedserver
@server = N'S2\INST01',
@srvproduct = N'SQL Server';
GO

Yukarıdaki bağlantı ile linked server oluşturduğumuzda bağlantı sağlanan linked server seçeneği ne olursa olsun linked server bağlantısı sağlanacak sunucuda bağlantı gerçekleştirdiği anda system metadata bilgilerini görmektedir. Linked server ile bağlantı sağlayacak kullanıcının karşı sunucuda yetkisinin olmasına gerek yoktur.

Aşağıdaki script, temel olarak Job’ın varlığını ve adım (step) bazlı içerik tutarlılığını denetler. Özellikle HASHBYTES metodunu kullanarak komut içeriklerini karşılaştırması, performanslı bir “eşitlik” kontrolü sağlar. Temel farkları hızlıca bir HTML tablosuna dökerek e-posta ile raporlamak için idealdir.

DECLARE @replicaname varchar(100),@servername varchar(100)=@@SERVERNAME
Select @replicaname=ar.replica_server_name from sys.availability_replicas ar
INNER JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id=ars.replica_id
where role=2
order by ar.replica_server_name asc
IF (@replicaname=@@SERVERNAME)
BEGIN
select @replicaname=primary_replica FROM sys.dm_hadr_name_id_map nim
                inner join sys.dm_hadr_availability_group_states ags
                               on nim.ag_id = ags.group_id
END
 
DECLARE @sqltext nvarchar(MAX)
SET @sqltext='
Select name,StepNumber,JobDifference INTO #tempbackup FROM(SELECT j.name,0 AS StepNumber,''Job Yok'' AS JobDifference
FROM msdb.dbo.sysjobs j
LEFT JOIN ['+@replicaname+'].msdb.dbo.sysjobs j2 ON j.name=j2.name
WHERE j2.name is null and j.name not like ''collection%'' and j.name not like ''sysutility%''
and j.enabled=1
UNION ALL
 
SELECT j.name,0,''Step Sayısı Farklı''
FROM (
                               SELECT j.name,COUNT(js.step_id) AS StepSayisi
                               FROM msdb.dbo.sysjobs j
                               INNER JOIN msdb.dbo.sysjobsteps js on j.job_id=js.job_id
                               Where j.enabled=1
                               GROUP BY j.name
               
                ) j
INNER JOIN (
                               SELECT j.name,COUNT(js.step_id) AS StepSayisi
                               FROM ['+@replicaname+'].msdb.dbo.sysjobs j
                               INNER JOIN ['+@replicaname+'].msdb.dbo.sysjobsteps js on j.job_id=js.job_id
                               Where j.enabled=1
                               GROUP BY j.name
                ) j2 ON j.name = j2.name AND j.StepSayisi<>j2.StepSayisi
 
UNION ALL
 
SELECT j.name,j.step_id,''Step Adları farklı''
FROM (
                               SELECT j.name,js.step_id,js.step_name
                               FROM msdb.dbo.sysjobs j
                               INNER JOIN msdb.dbo.sysjobsteps js on j.job_id=js.job_id
                               Where j.enabled=1
                ) j
INNER JOIN (
                               SELECT j.name,js.step_id,js.step_name
                               FROM ['+@replicaname+'].msdb.dbo.sysjobs j
                               INNER JOIN ['+@replicaname+'].msdb.dbo.sysjobsteps js on j.job_id=js.job_id
                               Where j.enabled=1
) j2 ON j.name = j2.name AND j.step_id=j2.step_id AND j.step_name<>j2.step_name
 
UNION ALL
 
SELECT j.name,j.step_id,''Step İçerikleri Farklı''
FROM (
                               SELECT j.name,js.step_id,HASHBYTES(''MD5'',js.command) AS StepHash
                               FROM msdb.dbo.sysjobs j
                               INNER JOIN msdb.dbo.sysjobsteps js on j.job_id=js.job_id
                               where js.step_name not like ''AG_%''
                               and j.enabled=1
                ) j
INNER JOIN (
                               SELECT j.name,js.step_id,HASHBYTES(''MD5'',js.command) AS StepHash
                               FROM ['+@replicaname+'].msdb.dbo.sysjobs j
                               INNER JOIN ['+@replicaname+'].msdb.dbo.sysjobsteps js on j.job_id=js.job_id
                               where js.step_name not like ''AG_%''
                               and j.enabled=1
) j2 ON j.name = j2.name AND j.step_id=j2.step_id AND j.StepHash<>j2.StepHash)a
 
DECLARE @tableHTML  NVARCHAR(MAX) ; 
SET @tableHTML = 
    N''<H1>Job Farkları</H1>'' + 
    N''<table border="1">'' + 
    N''<tr><th>Job Adı</th><th>Step Number</th><th>Job Difference</th>'' + 
    N''</tr>'' + 
    CAST ( (  SELECT             
       td=[name],''''
      ,td=[StepNumber],''''
      ,td=[JobDifference],''''
 
FROM
(SELECT [name]
      , [StepNumber]
      , [JobDifference]
FROM #tempbackup ) A
  FOR XML PATH(''tr''), TYPE  
    ) AS NVARCHAR(MAX) ) + 
    N''</table>'' ; 
 
 
--IF EXISTS(
--SELECT * FROM #tempbackup )
--                BEGIN
 
--            EXEC msdb.dbo.sp_send_dbmail
--            @recipients=''user@gmail.com'',
--            @profile_name= SqlProfile,
--            @subject = ''Primary-Secondary Job Farkları '+@servername+''', 
--            @body = @tableHTML, 
--            @body_format = ''HTML'' ;
--                END
select*from #tempbackup
DROP TABLE #tempbackup'
--print @sqltext
EXEC sp_executesql @sqltext

Yukarıdaki komutta HTML formatında mail gelmesi isteniyorsa mail konfigürasyonun yapılması gerekmektedir.

Aşağıdaki script ise analizi bir üst seviyeye taşır. Sadece Job adımlarıyla yetinmez; zamanlanmış görevlerin (Schedules) sıklık ve saat detaylarını, kritik hata bildirimlerini (Alerts) ve operasyonel bilgilendirme (Notifications) ayarlarını da karşılaştırır. Bu, sadece “Job orada mı?” sorusuna değil, “Job, Primary ile birebir aynı şekilde mi çalışacak?” sorusuna yanıt verir.

DECLARE @replicaname varchar(100), @servername varchar(100) = @@SERVERNAME
SELECT @replicaname = ar.replica_server_name 
FROM sys.availability_replicas ar
INNER JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
WHERE role = 2

IF (@replicaname = @@SERVERNAME)
BEGIN
    SELECT @replicaname = primary_replica FROM sys.dm_hadr_name_id_map nim
    INNER JOIN sys.dm_hadr_availability_group_states ags ON nim.ag_id = ags.group_id
END

DECLARE @sqltext nvarchar(MAX)
SET @sqltext = '
IF OBJECT_ID(''tempdb..#JobCheck'') IS NOT NULL DROP TABLE #JobCheck;

SELECT JobName, DifferenceType INTO #JobCheck FROM (
    
    -- 1. JOB VARLIK KONTROLÜ
    SELECT j.name AS JobName, ''Job Secondary Sunucuda Hiç Yok'' AS DifferenceType
    FROM msdb.dbo.sysjobs j
    LEFT JOIN ['+@replicaname+'].msdb.dbo.sysjobs j2 ON j.name = j2.name
    WHERE j2.name IS NULL AND j.enabled = 1

    UNION ALL

    -- 2. STEPS KONTROLÜ (İsim ve İçerik/Komut Bazlı)
    SELECT j.name, ''Step Farklı (Ad: '' + js.step_name + '')''
    FROM msdb.dbo.sysjobs j
    INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
    LEFT JOIN ['+@replicaname+'].msdb.dbo.sysjobs j2 ON j.name = j2.name
    LEFT JOIN ['+@replicaname+'].msdb.dbo.sysjobsteps js2 ON j2.job_id = js2.job_id AND js.step_id = js2.step_id
    WHERE j2.name IS NOT NULL 
      AND (js2.step_id IS NULL 
           OR js.step_name <> js2.step_name 
           OR js.command <> js2.command)

    UNION ALL

    -- 3. SCHEDULES KONTROLÜ (İçerik Detaylı)
    SELECT j.name, ''Schedule İçeriği Farklı (Detay: Saat/Gün/Sıklık uyuşmuyor)''
    FROM msdb.dbo.sysjobs j
    INNER JOIN msdb.dbo.sysjobschedules jsch ON j.job_id = jsch.job_id
    INNER JOIN msdb.dbo.sysschedules s ON jsch.schedule_id = s.schedule_id
    LEFT JOIN ['+@replicaname+'].msdb.dbo.sysjobs j2 ON j.name = j2.name
    LEFT JOIN ['+@replicaname+'].msdb.dbo.sysjobschedules jsch2 ON j2.job_id = jsch2.job_id
    LEFT JOIN ['+@replicaname+'].msdb.dbo.sysschedules s2 ON jsch2.schedule_id = s2.schedule_id
    WHERE j2.name IS NOT NULL 
      AND (s2.schedule_id IS NULL -- Zamanlama secondaryde bu joba bağlı değil
           OR s.freq_type <> s2.freq_type 
           OR s.freq_interval <> s2.freq_interval
           OR s.freq_subday_type <> s2.freq_subday_type
           OR s.freq_subday_interval <> s2.freq_subday_interval
           OR s.active_start_time <> s2.active_start_time
           OR s.active_end_time <> s2.active_end_time)

    UNION ALL

    -- 4. ALERTS KONTROLÜ
    SELECT j.name, ''Alert Tanımı Eksik''
    FROM msdb.dbo.sysjobs j
    INNER JOIN msdb.dbo.sysalerts al ON j.job_id = al.job_id
    LEFT JOIN ['+@replicaname+'].msdb.dbo.sysjobs j2 ON j.name = j2.name
    LEFT JOIN ['+@replicaname+'].msdb.dbo.sysalerts al2 ON j2.job_id = al2.job_id
    WHERE al2.id IS NULL AND j2.name IS NOT NULL

    UNION ALL

    -- 5. NOTIFICATIONS KONTROLÜ
    SELECT j.name, ''Notification Ayarları Farklı''
    FROM msdb.dbo.sysjobs j
    INNER JOIN ['+@replicaname+'].msdb.dbo.sysjobs j2 ON j.name = j2.name
    WHERE j.notify_level_email <> j2.notify_level_email 
       OR j.notify_email_operator_id <> j2.notify_email_operator_id

) res;

SELECT * FROM #JobCheck;
DROP TABLE #JobCheck;'

EXEC sp_executesql @sqltext

İlk komut günlük rutin kontroller ve hızlı raporlama için pratik bir çözüm sunarken, ikinci komut sistem geçişleri (failover) sonrası veya periyodik derin denetimlerde (audit) veri kaybı ve operasyonel aksaklıkları önlemek adına daha güvenilir ve detaylı bir envanter kontrolü sağlar.

Başka makalede görüşmek dileğiyle..

“Kim zerre kadar iyilik yapmışsa onu görür.”Zilzal Suresi; 7. Ayet