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. Birden fazla sunucu için kontrol sağlamaktadır.

IF OBJECT_ID('tempdb..#FinalJobCheck') IS NOT NULL DROP TABLE #FinalJobCheck;
IF OBJECT_ID('tempdb..#ExcludeJobs') IS NOT NULL DROP TABLE #ExcludeJobs;

-- 1. KONTROL EDİLMESİNİ İSTEMEDİĞİNİZ JOB İSİMLERİNİ BURAYA EKLEYİN
CREATE TABLE #ExcludeJobs (JobName sysname);
INSERT INTO #ExcludeJobs VALUES
('JOB1'), --hariç tutulan job 1
('JOB2'), -- hariç tutulan job 2
('JOB3'); -- hariç tutulan job 3

-- Tüm sunucu sonuçlarını toplamak için ana temp tablo
CREATE TABLE #FinalJobCheck (
LocalServer varchar(100),
RemoteServer varchar(100),
JobName sysname,
DifferenceType nvarchar(500)
);

DECLARE @replicaname varchar(100);
DECLARE @servername varchar(100) = @@SERVERNAME;

-- Kendisi dışındaki tüm replica sunucuları döner
DECLARE replica_cursor CURSOR FOR
SELECT DISTINCT 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 ar.replica_server_name <> @@SERVERNAME;

OPEN replica_cursor;
FETCH NEXT FROM replica_cursor INTO @replicaname;

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sqltext nvarchar(MAX);

SET @sqltext = '
INSERT INTO #FinalJobCheck (LocalServer, RemoteServer, JobName, DifferenceType)
SELECT
''' + @servername + ''' AS LocalServer,
''' + @replicaname + ''' AS RemoteServer,
JobName,
DifferenceType
FROM (

-- 1. JOB VARLIK KONTROLÜ
SELECT j.name AS JobName, ''Job Hedef 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
AND j.name NOT IN (SELECT JobName FROM #ExcludeJobs) -- Hariç tutma filtresi

UNION ALL

-- 2. STEPS KONTROLÜ
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 j.name NOT IN (SELECT JobName FROM #ExcludeJobs) -- Hariç tutma filtresi
AND (js2.step_id IS NULL
OR js.step_name <> js2.step_name
OR js.command <> js2.command)

UNION ALL

-- 3. SCHEDULES KONTROLÜ
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 j.name NOT IN (SELECT JobName FROM #ExcludeJobs) -- Hariç tutma filtresi
AND (s2.schedule_id IS NULL
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
AND j.name NOT IN (SELECT JobName FROM #ExcludeJobs) -- Hariç tutma filtresi

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.name NOT IN (SELECT JobName FROM #ExcludeJobs) -- Hariç tutma filtresi
AND (j.notify_level_email <> j2.notify_level_email
OR j.notify_email_operator_id <> j2.notify_email_operator_id)

) res;';

EXEC sp_executesql @sqltext;

FETCH NEXT FROM replica_cursor INTO @replicaname;
END;

CLOSE replica_cursor;
DEALLOCATE replica_cursor;

SELECT * FROM #FinalJobCheck
ORDER BY JobName, RemoteServer;

DROP TABLE #FinalJobCheck;
DROP TABLE #ExcludeJobs;

İ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

Bir yanıt yazın