Merhaba,
Dünkü çalışmada bir blog konusu daha vardı, şimdi fırsatım varken o konuda da yazmak istedim.
Diğer test edip emin olmak istediğimiz konu da hangi versiyonda hatırlamıyorum, ama halihazırda SQL Server'ın "Deprecated" özellikler listesine giren, UPDATE ve DELETE komutlarıyla "NOLOCK" Hint'inin kullanımı konusuydu. Dünkü yaptığımız çalışmada, çalıştığımız sunucuda "NOLOCK or READUNCOMMITTED in UPDATE or DELETE" uyarılarının olduğunu gördük. İlgili arkadaşlar bunun olamayacağını, böyle bir kullanım pratiğinin kurumda uygulanmadığını, başka bir kullanım şeklinin bu sayacı arttırmış olabileceğini düşünüyorlardı. O zaman test edelim ve görelim dedik, böylece yeni bir blog konusu çıktı ortaya.
Öncelikle "Deprecated" olmuş bir özellik ne demek çok özetle buna değineyim. Bir özellik SQL Server'ın yeni versiyonlarından kaldırılmadan birkaç versiyon önce "Deprecated" özellikler listesine girer. Özellik yeni versiyonda kaldırılmışsa da "Discontinued" özellikler listesine girer artık. SQL Server'ın yeni versiyonlarıyla güncellenir bu listeler. Bu nedenle veritabanı uygulaması geliştirirken, eğer birkaç yıl içerisinde uygulamanızda ilginç ilginç hatalar görmek istemiyorsanız bu listelerdeki değişiklikleri de takip etmenizde fayda var.
Efendim bu kontrolü aşağıdaki sorgu ile yapabilirsiniz:
SELECT * FROM sys.dm_os_performance_counters
WHERE [object_name] = 'SQLServer:Deprecated Features' and [instance_name] = 'NOLOCK or READUNCOMMITTED in UPDATE or DELETE' AND [cntr_value] > 0
Aşağıdaki gibi bir komutu her çalıştırdığınızda, bu sayacın değeri bir bir artacaktır:
Not: Önce X isimli ve içerisinde metin veritipiyle "name" alanı olan ve içinde de en azından "Ekrem" kaydı bulunan bir tablo oluşturmalısınız.
Örnek1:
UPDATE alias_name SET name = 'Ekrem' FROM x AS alias_name WITH (NOLOCK);
Not: Eğer sürekli aynı değerle güncellerseniz sayaç artmıyor. Sadece değer değiştiğinde artıyor. Yani ilk testinizde değeri "Ekrem" olarak belirttikten sonra, mesela "Ekrem1" yapın, sayaç öyle artıyor.
Eğer aşağıdaki gibi bir komut çalıştırırsanız bu sayacın değeri artmıyor:
Örnek2:
UPDATE alias_name SET name = 'Ekrem' FROM (SELECT * FROM X WITH (NOLOCK)) AS alias_name;
Bu nedenle sanılanın aksine, ilgili ortamdaki uygulamada ilk örnekteki gibi bir kullanım olduğunu sanıyorum. Belli ki gerçekten NOLOCK Hint'ini doğrudan UPDATE komutuyla kullandığımızda artıyor bu değer. Tabii ki daha da emin olmak için ikinci örnekteki kod farklı şekillerde değiştirilerek de uygulanabilir. Fakat benim ilk etapta aklıma gelen bu örneklerle sonuç böyle.
Sevgiler,
Ekrem Önsoy
27 Eylül 2016 Salı
Veritabanı bütünlük kontrolünün kontrolü
Selamlar,
Dün bir müşterimde sağlık bakımı çalışması yaparken bir şey dikkatimi çekti ve ilgili arkadaşlarla aramızda bu konuda konuştuk ve bir fikir ayrılığı oldu, daha doğrusu aslında hepimiz birlikte emin olamadık ve bu bir blog konusu olsun, biz de sonuç hakkında daha kesin bir fikir sahibi olalım ve herkesle de paylaşalım dedik.
Konumuz şuydu efendim, bir SQL Server Agent Job'ı ile DBCC CHECKDB komutu kullanılarak rutin olarak veritabanı bütünlük kontrolü yapılıyor. Örneğin:
Job içerisinde her bir veritabanı için ayrı bir Step var ve her birinde ayrı ayrı
Step1: DBCC CHECKDB('Veritabanı1')
Step2: DBCC CHECKDB('Veritabanı2')
Step3: DBCC CHECKDB('Veritabanı3')
...
Şeklinde tanımlanmış diyelim, şayet DBCC CHECKDB komutu ikinci adım için aşağıdaki gibi bir sonuç döndürürse ne olur?
CHECKDB found 0 allocation errors and 4 consistency errors in table 'tablo_adı' (object ID 1154103152).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'Veritabanı2'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Veritabanı2).
Esas merak ettiğimiz şey buydu: İlgili Job hata verir ve bu sayede Job'ın Notification bölümündeki ilgili operatöre e-posta gönderilir mi? Yoksa DBCC komutu böyle bir sonuç üretse de hatanın nedeni ve kaynağı DBCC komutu olmadığı için komut başarıyla tamamlanmış olur ve Job da başarıyla tamamlandığı için Notification bölümündeki operatöre e-posta göndermez mi? Sonuç olarak eğer böyle bir kontrol sonucu bir alarm üretilmezse bu kontrolün pek bir önemi kalmamış olacak ve bu da riskli bir durum oluşturacak.
"Peki bunu bugüne kadar hiç mi fark etmedin?" diyebilirsiniz, efendim ben bu kontrolü daha farklı şekilde yapıyorum. Benim yönettiğim ortamlarda yaptığım bütünlük testlerinin sonuçlarını kendime eposta ile göndertiyorum. Bu iş için sadece bu bütünlük Job'ı hata alırsa e-posta gelsin gibi bir mekanizma kullanmıyorum. Bu nedenle bu konu hakkında çok net bir fikrim yoktu.
Öncelikle şunu bilmek gerekiyor, bir veritabanı birçok şekilde Corrupt duruma düşebilir. Mesela bazen veritabanı Corrupt olduğunda hiç açılmıyor. Suspect durumda olabiliyor. Recovery Pending durumda olabiliyor. Bazen de Online gibi görünüyor, bazı tablolara ve verilere ulaşılabiliyor, fakat bazılarına ulaşılamıyor... Hatta aynı tablodaki bazı kayıtlara ulaşılabilip, bazılarına ulaşılamadığı da olabiliyor.
DBCC CHECKDB komutu da veritabanının içinde bulunduğu duruma göre bazen veritabanı hiç açılamadığı için daha baştan hata verip çalışmıyor, bazen de tarama yapılabiliyor; bunun sonucunda yukarıdaki gibi sonuçlar üretebiliyor. Bir veritabanının böyle birçok farklı Corruption senaryosu olduğunu bildiğim için, ben de dünkü tartışmamızda bu nedenle net olarak emin olamadım ve test etmek istedim.
BÖYLE BİR TESTİ KESİNLİKLE PRODUCTION, PRE-PRODUCTION, TEST ve DEV ORTAMLARINIZDA YAPMAYIN. Tamamen gözden çıkartabileceğiniz bir SQL Server Instance'ında ve ortamında yapabilirsiniz.
Test için:
- Testim için yalıtılmış test ortamlarımdan birindeki AdventureWorks2012 veritabanını kullandım.
- Veritabanımı Detach etmeden OFFLINE duruma getirdim.
- Veritabanımın veri dosyasının içeriğinde bir Hex Editör ile değişiklik yaparak test veritabanımı Corrupt duruma getirdim.
- Veritabanımı tekrar ONLINE duruma getirdim.
- Veritabanım tamamen sağlam görünüyordu. DBCC CHECKDB'yi çalıştırdığımda aşağıdaki sonucun oluştuğunu gözlemledim:
Bunun sonucunda, yukarıdaki ekran görüntüsünden de görülebileceği üzere DBCC CHECKDB komutu hata ile tamamlanmış sayıldı. Yani DBCC CHECKDB komutu bir veritabanında Corruption olduğuna dair bir sonuç üretirse, bu komutun kendi de hata ile tamamlanıyordu. Tabii hal böyle olunca, ilgili SQL Server Agent Job'ı da hata ile tamamlanmış oluyor ve Job'ın Notification bölümünde tanımlanan operatöre e-posta gönderiyor.
Önceden de belirttiğim gibi Corruption birçok şekilde gerçekleşebiliyor, bu nedenle her ne kadar bu test sonucunda en azından tablo/indeks boyutunda yaşanan bir Corruption sonucu DBCC CHECKDB komutunun hata ile tamamlanacağını ve ilgili Job'ın da hata üretip ilgili noktaları tetikleyeceğini görsek de, ben bu konuda hala temkinliyim. Özellikle kritik ortamlarımda DBCC CHECKDB sonuçlarını e-posta ile almayı yeğlerim.
Sevgiler,
Ekrem Önsoy
Dün bir müşterimde sağlık bakımı çalışması yaparken bir şey dikkatimi çekti ve ilgili arkadaşlarla aramızda bu konuda konuştuk ve bir fikir ayrılığı oldu, daha doğrusu aslında hepimiz birlikte emin olamadık ve bu bir blog konusu olsun, biz de sonuç hakkında daha kesin bir fikir sahibi olalım ve herkesle de paylaşalım dedik.
Konumuz şuydu efendim, bir SQL Server Agent Job'ı ile DBCC CHECKDB komutu kullanılarak rutin olarak veritabanı bütünlük kontrolü yapılıyor. Örneğin:
Job içerisinde her bir veritabanı için ayrı bir Step var ve her birinde ayrı ayrı
Step1: DBCC CHECKDB('Veritabanı1')
Step2: DBCC CHECKDB('Veritabanı2')
Step3: DBCC CHECKDB('Veritabanı3')
...
Şeklinde tanımlanmış diyelim, şayet DBCC CHECKDB komutu ikinci adım için aşağıdaki gibi bir sonuç döndürürse ne olur?
CHECKDB found 0 allocation errors and 4 consistency errors in table 'tablo_adı' (object ID 1154103152).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'Veritabanı2'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Veritabanı2).
Esas merak ettiğimiz şey buydu: İlgili Job hata verir ve bu sayede Job'ın Notification bölümündeki ilgili operatöre e-posta gönderilir mi? Yoksa DBCC komutu böyle bir sonuç üretse de hatanın nedeni ve kaynağı DBCC komutu olmadığı için komut başarıyla tamamlanmış olur ve Job da başarıyla tamamlandığı için Notification bölümündeki operatöre e-posta göndermez mi? Sonuç olarak eğer böyle bir kontrol sonucu bir alarm üretilmezse bu kontrolün pek bir önemi kalmamış olacak ve bu da riskli bir durum oluşturacak.
"Peki bunu bugüne kadar hiç mi fark etmedin?" diyebilirsiniz, efendim ben bu kontrolü daha farklı şekilde yapıyorum. Benim yönettiğim ortamlarda yaptığım bütünlük testlerinin sonuçlarını kendime eposta ile göndertiyorum. Bu iş için sadece bu bütünlük Job'ı hata alırsa e-posta gelsin gibi bir mekanizma kullanmıyorum. Bu nedenle bu konu hakkında çok net bir fikrim yoktu.
Öncelikle şunu bilmek gerekiyor, bir veritabanı birçok şekilde Corrupt duruma düşebilir. Mesela bazen veritabanı Corrupt olduğunda hiç açılmıyor. Suspect durumda olabiliyor. Recovery Pending durumda olabiliyor. Bazen de Online gibi görünüyor, bazı tablolara ve verilere ulaşılabiliyor, fakat bazılarına ulaşılamıyor... Hatta aynı tablodaki bazı kayıtlara ulaşılabilip, bazılarına ulaşılamadığı da olabiliyor.
DBCC CHECKDB komutu da veritabanının içinde bulunduğu duruma göre bazen veritabanı hiç açılamadığı için daha baştan hata verip çalışmıyor, bazen de tarama yapılabiliyor; bunun sonucunda yukarıdaki gibi sonuçlar üretebiliyor. Bir veritabanının böyle birçok farklı Corruption senaryosu olduğunu bildiğim için, ben de dünkü tartışmamızda bu nedenle net olarak emin olamadım ve test etmek istedim.
BÖYLE BİR TESTİ KESİNLİKLE PRODUCTION, PRE-PRODUCTION, TEST ve DEV ORTAMLARINIZDA YAPMAYIN. Tamamen gözden çıkartabileceğiniz bir SQL Server Instance'ında ve ortamında yapabilirsiniz.
Test için:
- Testim için yalıtılmış test ortamlarımdan birindeki AdventureWorks2012 veritabanını kullandım.
- Veritabanımı Detach etmeden OFFLINE duruma getirdim.
- Veritabanımın veri dosyasının içeriğinde bir Hex Editör ile değişiklik yaparak test veritabanımı Corrupt duruma getirdim.
- Veritabanımı tekrar ONLINE duruma getirdim.
- Veritabanım tamamen sağlam görünüyordu. DBCC CHECKDB'yi çalıştırdığımda aşağıdaki sonucun oluştuğunu gözlemledim:
Bunun sonucunda, yukarıdaki ekran görüntüsünden de görülebileceği üzere DBCC CHECKDB komutu hata ile tamamlanmış sayıldı. Yani DBCC CHECKDB komutu bir veritabanında Corruption olduğuna dair bir sonuç üretirse, bu komutun kendi de hata ile tamamlanıyordu. Tabii hal böyle olunca, ilgili SQL Server Agent Job'ı da hata ile tamamlanmış oluyor ve Job'ın Notification bölümünde tanımlanan operatöre e-posta gönderiyor.
Önceden de belirttiğim gibi Corruption birçok şekilde gerçekleşebiliyor, bu nedenle her ne kadar bu test sonucunda en azından tablo/indeks boyutunda yaşanan bir Corruption sonucu DBCC CHECKDB komutunun hata ile tamamlanacağını ve ilgili Job'ın da hata üretip ilgili noktaları tetikleyeceğini görsek de, ben bu konuda hala temkinliyim. Özellikle kritik ortamlarımda DBCC CHECKDB sonuçlarını e-posta ile almayı yeğlerim.
Sevgiler,
Ekrem Önsoy
8 Eylül 2016 Perşembe
An alternative way to manage jobs during an AlwaysOn AG failover
Hello beautiful SQL people!
In this article, I'd like to share a solution to handle SQL Server Agent Jobs (I'll call them "jobs" from now on) during a failover scenario in an AlwaysOn Availability Group environment. I know, there are some different suggestions around but I promise, this is a new one as far as I am aware.
I'll skip the basics and I assume you already know about SQL Server AlwaysOn Availability Group (AG), jobs and SQL Server Agent Alerts (alerts from now on) features. This is all about managing the status (enabled/disabled) of the jobs and not synchronising / copying them among AG replicas. Also for now, this solution works between 2 replicas only, but it can be expanded to cover more with some extra work.
Note: I haven't tested it yet, but I strongly believe that with some tweaking, this solution can be implemented in a Log Shipping or Database Mirroring environment too.
The solution is absolutely not a totally-new one. I love to read published articles written by my peers about SQL Server and try to follow them daily and up until now I've seen some variations of this solution around. However, I find this way much simpler to implement and manage. That's why I want to share it with the community and to reach out to a wider portion of the community, I'm writing it in English obviously which I do not do so often.
As you know SQL Server does not have a native solution to handle jobs after a AG failover. In terms of its enabled/disabled status, a job stays as is after a failover occurs, they are not failover-aware. In some environments, jobs are critical if not vital. So at least for a lot of DBAs out there, it's very important to switch the status of the jobs at the primary and secondary replicas immediately after a failover.
Some suggest adding conditional checks to each job, however it is sometimes almost impossible (SSIS, too many jobs to modify etc.), some suggest adding some tags to each job and schedule a job at both replicas to enable / disable them accordingly after a failover. Michael K. Campbell has a very long series of articles about AG on SQLMag. I'd like to thank to him to let me use his Function code below to detect the primary and secondary AG replicas in this solution. I prefer to use this function so that I can implement the solution to my SQL Server 2012 instances too.
With this solution, my aim is to set up & forget about jobs should a failover occurs. After implementing this, the only thing you may want to do is adding the names of some jobs to the exclusion table to exclude them from switching status after failovers. The rest will be handled by the solution and when a failover occurs, the status of your jobs will be the same at the new primary and secondary as it was previously. Enabled/disabled jobs at the previously-primary replica will be enabled/disabled at the new-primary replica and the same for the secondary. It does not matter if you perform a manual failover or an automatic failover in case the primary shuts down. Even if your primary shuts down / powered off, it will update the status of the jobs when the SQL Server Agent service is up. It just works.
I recommend you to create the following objects in your DBA / Tools database if you have any. Here are the codes and descriptions to create the solution.
************************** Beginning of the script **************************
-- REMINDER: The following codes must be executed at both of the replicas.
-- Create a Linked Server named "PARTNER" that goes to the other replica and same for the other one.
-- [Optional] Create a schema named "jobs" to distinguish this solution from others. If you do not prefer to create this schema or if it's already taken and can not be used for this purpose, then please modify rest of the code below.
USE;
GO
CREATE SCHEMA [jobs];
GO
-- This table will be updated automatically by a stored procedure "[jobs].[update_job_status_accross_nodes]". I prefer to execute this SP hourly by another scheduled job which is not scripted here. But hey! You already know how to do it.
CREATE TABLE [jobs].[job_status](
[job_name] [nvarchar](256) NULL,
[primary_enabled] [bit] NULL,
[secondary_enabled] [bit] NULL,
[collection_date] [datetime] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX CUIX_jobname ON [jobs].[job_status](job_name);
GO
-- This is the table you may want to add some jobs to exclude from switching automatically.
CREATE TABLE [jobs].[job_status_exclusion_list](
[job_name] [nvarchar](256) NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX CUIX_jobname ON [jobs].[job_status_exclusion_list](job_name);
GO
-- To find out if the current replica is primary or not (courtesy of Michael K. Campbell)
CREATE FUNCTION [dbo].[fn_hadr_group_is_primary] (@AGName sysname)
RETURNS bit
AS
BEGIN
DECLARE @PrimaryReplica sysname;
SELECT TOP (1) @PrimaryReplica = hags.primary_replica
FROM
sys.dm_hadr_availability_group_states hags
INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
WHERE
ag.name = CASE WHEN @AGName IS NULL THEN ag.name ELSE @AGName END;
IF UPPER(@PrimaryReplica) = UPPER(@@SERVERNAME)
RETURN 1; -- primary
RETURN 0; -- not primary
END;
-- Updates the "[jobs].[job_status]" table about the status of the jobs at the primary and secondary separately.
CREATE PROCEDURE [jobs].[update_job_status_accross_nodes]
AS
SET NOCOUNT ON;
IF (SELECT dbo.fn_hadr_group_is_primary(NULL)) = 1
BEGIN
DECLARE @date DATETIME;
SELECT @date = GETDATE();
DELETE FROM [jobs].[job_status];
INSERT INTO [jobs].[job_status]([job_name], [primary_enabled], [secondary_enabled], [collection_date])
SELECT
[name] = CASE WHEN [primary].[name] IS NULL THEN [secondary].[name] ELSE [primary].[name] END,
[primary].[enabled],
[secondary].[enabled],
@date
FROM [msdb].[dbo].[sysjobs] AS [primary]
FULL OUTER JOIN [PARTNER].[msdb].[dbo].[sysjobs] AS [secondary] ON [primary].[name] = [secondary].[name];
DELETE FROM [PARTNER].[DBA].[jobs].[job_status];
INSERT INTO [PARTNER].[DBA].[jobs].[job_status]([job_name], [primary_enabled], [secondary_enabled], [collection_date])
SELECT
[name] = CASE WHEN [primary].[name] IS NULL THEN [secondary].[name] ELSE [primary].[name] END,
[primary].[enabled],
[secondary].[enabled],
@date
FROM [msdb].[dbo].[sysjobs] AS [primary]
FULL OUTER JOIN [PARTNER].[msdb].[dbo].[sysjobs] AS [secondary] ON [primary].[name] = [secondary].[name];
END
-- Disables the jobs at the primary and secondary according to the records in "[jobs].[job_status]" and "[jobs].[job_status_exclusion_list]" tables and is executed by a job named "_DBA: Configure SQL Server Agent jobs after a failover" which is triggered by an alert.
CREATE PROCEDURE [jobs].[disable_jobs_after_failover]
AS
BEGIN
SET NOCOUNT ON;
-- If the SP is running at the new-Secondary Replica, except for the ones in the exclusion list, disable all jobs which was disabled at the previously Secondary Replica.
IF (SELECT [dbo].[fn_hadr_group_is_primary](NULL)) = 0
BEGIN
DECLARE @sql NVARCHAR(max) = '';
SELECT
@sql += N'EXEC msdb.dbo.sp_update_job @job_name = ''' + [job_name] + N''', @enabled = 0;'
FROM [jobs].[job_status] AS [j]
WHERE
NOT EXISTS(SELECT [job_name] FROM [jobs].[job_status_exclusion_list] AS [ex] WHERE [ex].[job_name] = [j].[job_name]) AND
[j].[secondary_enabled] = 0
ORDER BY [j].[job_name];
EXEC (@sql);
END
-- If the SP is running at the new-Primary Replica, except for the ones in the exclusion list, disable all jobs which was disabled at the previously Primary Replica.
IF (SELECT [dbo].[fn_hadr_group_is_primary](NULL)) = 1
BEGIN
DECLARE @sql2 NVARCHAR(max) = '';
SELECT
@sql2 += N'EXEC msdb.dbo.sp_update_job @job_name = ''' + [job_name] + N''', @enabled = 0;'
FROM [jobs].[job_status] AS [j]
WHERE
NOT EXISTS(SELECT [job_name] FROM [jobs].[job_status_exclusion_list] AS [ex] WHERE [ex].[job_name] = [j].[job_name]) AND
[j].[primary_enabled] = 0
ORDER BY [j].[job_name];
EXEC (@sql2);
END
END
-- The job that executes the stored procedures above during a failover.
I find it unnecessary to copy/paste the whole script of the job here, it's a simple job consisting of 3 steps.
The name of the job must be "_DBA: Configure SQL Server Agent jobs after a failover", if you do not agree, than please modify the code below accordingly.
REMINDER: Ensure the following code is executed in the correct database where these objects are created.
1. step's code: EXEC [jobs].[enable_jobs_after_failover];
2. step's code: EXEC [jobs].[disable_jobs_after_failover];
3. step's code: EXEC [jobs].[update_job_status_accross_nodes];
A schedule in the type of "Start automatically when SQL Server Agent starts" is needed so that the steps above will be executed at startup in case a replica shuts down unexpectedly.
You also may want to add a notification (Database Mail configuration and an existing operator is needed) so that you'll be informed about a failover.
-- The following alert is triggered when a failover occurs and it is used to execute the job at both of the replicas.
************************** End of the script **************************
After executing the scripts above, the SP "[jobs].[update_job_status_accross_nodes]" needs to be executed at the primary replica at least once to update the "[jobs].[job_status]" table so that the solution will know about the status of your jobs at both of the replicas.
During an AG failover the alert is triggered at the previously-primary (if it's still up) and new-primary and every time it is triggered, it executes the job that is created above. Then job enables and disables the related jobs at both of the replicas. Simple.
Some critical points:
- If you happen to decide to use a different name for the job, then please be careful about the create alert step, because the default job name is used to find the correct job_id from the related system table.
- The job must be enabled all the time and at all replicas. This is vital. Otherwise the alert does not execute it.
- If you want to change the name of the Linked Server, then you must update the script accordingly, otherwise it'll be broken.
- Make sure that your SQL Server Agent service's startup parameter is "Automatic", so that it executes the job at startup and update the status of the jobs accordingly.
As I said from the beginning, most of these code blocks are familiar to almost all of us thanks to my peers' hard work. I guess the only bit I add is during a failover, executing a job by a triggered alert at both replicas and enabling/disabling related jobs this way. If a method exactly like this (obviously I'm unaware) has already been shared by someone else, please let me know so that I can write a note about it.
If you have a suggestion, question or if there's something wrong with the code or method above, please tell me and let's make it better together!
That's it folks! I hope you find this solution useful and practical as I do.
Thanks for reading,
Ekrem Onsoy
Microsoft SQL Server Consultant
In this article, I'd like to share a solution to handle SQL Server Agent Jobs (I'll call them "jobs" from now on) during a failover scenario in an AlwaysOn Availability Group environment. I know, there are some different suggestions around but I promise, this is a new one as far as I am aware.
I'll skip the basics and I assume you already know about SQL Server AlwaysOn Availability Group (AG), jobs and SQL Server Agent Alerts (alerts from now on) features. This is all about managing the status (enabled/disabled) of the jobs and not synchronising / copying them among AG replicas. Also for now, this solution works between 2 replicas only, but it can be expanded to cover more with some extra work.
Note: I haven't tested it yet, but I strongly believe that with some tweaking, this solution can be implemented in a Log Shipping or Database Mirroring environment too.
The solution is absolutely not a totally-new one. I love to read published articles written by my peers about SQL Server and try to follow them daily and up until now I've seen some variations of this solution around. However, I find this way much simpler to implement and manage. That's why I want to share it with the community and to reach out to a wider portion of the community, I'm writing it in English obviously which I do not do so often.
As you know SQL Server does not have a native solution to handle jobs after a AG failover. In terms of its enabled/disabled status, a job stays as is after a failover occurs, they are not failover-aware. In some environments, jobs are critical if not vital. So at least for a lot of DBAs out there, it's very important to switch the status of the jobs at the primary and secondary replicas immediately after a failover.
Some suggest adding conditional checks to each job, however it is sometimes almost impossible (SSIS, too many jobs to modify etc.), some suggest adding some tags to each job and schedule a job at both replicas to enable / disable them accordingly after a failover. Michael K. Campbell has a very long series of articles about AG on SQLMag. I'd like to thank to him to let me use his Function code below to detect the primary and secondary AG replicas in this solution. I prefer to use this function so that I can implement the solution to my SQL Server 2012 instances too.
With this solution, my aim is to set up & forget about jobs should a failover occurs. After implementing this, the only thing you may want to do is adding the names of some jobs to the exclusion table to exclude them from switching status after failovers. The rest will be handled by the solution and when a failover occurs, the status of your jobs will be the same at the new primary and secondary as it was previously. Enabled/disabled jobs at the previously-primary replica will be enabled/disabled at the new-primary replica and the same for the secondary. It does not matter if you perform a manual failover or an automatic failover in case the primary shuts down. Even if your primary shuts down / powered off, it will update the status of the jobs when the SQL Server Agent service is up. It just works.
I recommend you to create the following objects in your DBA / Tools database if you have any. Here are the codes and descriptions to create the solution.
************************** Beginning of the script **************************
-- REMINDER: The following codes must be executed at both of the replicas.
-- Create a Linked Server named "PARTNER" that goes to the other replica and same for the other one.
-- [Optional] Create a schema named "jobs" to distinguish this solution from others. If you do not prefer to create this schema or if it's already taken and can not be used for this purpose, then please modify rest of the code below.
USE
GO
CREATE SCHEMA [jobs];
GO
-- This table will be updated automatically by a stored procedure "[jobs].[update_job_status_accross_nodes]". I prefer to execute this SP hourly by another scheduled job which is not scripted here. But hey! You already know how to do it.
CREATE TABLE [jobs].[job_status](
[job_name] [nvarchar](256) NULL,
[primary_enabled] [bit] NULL,
[secondary_enabled] [bit] NULL,
[collection_date] [datetime] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX CUIX_jobname ON [jobs].[job_status](job_name);
GO
-- This is the table you may want to add some jobs to exclude from switching automatically.
CREATE TABLE [jobs].[job_status_exclusion_list](
[job_name] [nvarchar](256) NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX CUIX_jobname ON [jobs].[job_status_exclusion_list](job_name);
GO
-- To find out if the current replica is primary or not (courtesy of Michael K. Campbell)
CREATE FUNCTION [dbo].[fn_hadr_group_is_primary] (@AGName sysname)
RETURNS bit
AS
BEGIN
DECLARE @PrimaryReplica sysname;
SELECT TOP (1) @PrimaryReplica = hags.primary_replica
FROM
sys.dm_hadr_availability_group_states hags
INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
WHERE
ag.name = CASE WHEN @AGName IS NULL THEN ag.name ELSE @AGName END;
IF UPPER(@PrimaryReplica) = UPPER(@@SERVERNAME)
RETURN 1; -- primary
RETURN 0; -- not primary
END;
-- Updates the "[jobs].[job_status]" table about the status of the jobs at the primary and secondary separately.
CREATE PROCEDURE [jobs].[update_job_status_accross_nodes]
AS
SET NOCOUNT ON;
IF (SELECT dbo.fn_hadr_group_is_primary(NULL)) = 1
BEGIN
DECLARE @date DATETIME;
SELECT @date = GETDATE();
DELETE FROM [jobs].[job_status];
INSERT INTO [jobs].[job_status]([job_name], [primary_enabled], [secondary_enabled], [collection_date])
SELECT
[name] = CASE WHEN [primary].[name] IS NULL THEN [secondary].[name] ELSE [primary].[name] END,
[primary].[enabled],
[secondary].[enabled],
@date
FROM [msdb].[dbo].[sysjobs] AS [primary]
FULL OUTER JOIN [PARTNER].[msdb].[dbo].[sysjobs] AS [secondary] ON [primary].[name] = [secondary].[name];
DELETE FROM [PARTNER].[DBA].[jobs].[job_status];
INSERT INTO [PARTNER].[DBA].[jobs].[job_status]([job_name], [primary_enabled], [secondary_enabled], [collection_date])
SELECT
[name] = CASE WHEN [primary].[name] IS NULL THEN [secondary].[name] ELSE [primary].[name] END,
[primary].[enabled],
[secondary].[enabled],
@date
FROM [msdb].[dbo].[sysjobs] AS [primary]
FULL OUTER JOIN [PARTNER].[msdb].[dbo].[sysjobs] AS [secondary] ON [primary].[name] = [secondary].[name];
END
-- Enables the jobs at the primary and secondary according to the records in "[jobs].[job_status]" and "[jobs].[job_status_exclusion_list]" tables and is executed by a job named "_DBA: Configure SQL Server Agent jobs after a failover" which is triggered by an alert. These are going to be created later.
CREATE PROCEDURE [jobs].[enable_jobs_after_failover]
AS
BEGIN
SET NOCOUNT ON;
-- If the SP is running at the new-Primary Replica, except for the ones in the exclusion list, enable all jobs which was enabled at the previously Primary Replica.
IF (SELECT [dbo].[fn_hadr_group_is_primary](NULL)) = 1
BEGIN
DECLARE @sql NVARCHAR(max) = '';
SELECT
@sql += N'EXEC msdb.dbo.sp_update_job @job_name = ''' + [name] + N''', @enabled = 1; '
FROM [msdb].[dbo].[sysjobs] AS [j]
INNER JOIN [jobs].[job_status] AS [js] ON [j].[name] = [js].[job_name]
WHERE
NOT EXISTS(SELECT [job_name]
FROM [jobs].[job_status_exclusion_list] AS [jsel] WHERE [jsel].[job_name] = [j].[name]) AND
[js].[primary_enabled] = 1
ORDER BY [j].[name];
EXEC (@sql);
END
-- If the SP is running at the new-Secondary Replica, except for the ones in the exclusion list, enable all jobs which was enabled at the previously Secondary Replica.
IF (SELECT [dbo].[fn_hadr_group_is_primary](NULL)) = 0
BEGIN
DECLARE @sql2 NVARCHAR(max) = '';
SELECT
@sql2 += N'EXEC msdb.dbo.sp_update_job @job_name = ''' + [name] + N''', @enabled = 1; '
FROM [msdb].[dbo].[sysjobs] AS [j]
INNER JOIN [jobs].[job_status] AS [js] ON [j].[name] = [js].[job_name]
WHERE
NOT EXISTS(SELECT [job_name]
FROM [jobs].[job_status_exclusion_list] AS [jsel] WHERE [jsel].[job_name] = [j].[name]) AND
[js].[secondary_enabled] = 1
ORDER BY [j].[name];
EXEC (@sql2);
END
END
-- Disables the jobs at the primary and secondary according to the records in "[jobs].[job_status]" and "[jobs].[job_status_exclusion_list]" tables and is executed by a job named "_DBA: Configure SQL Server Agent jobs after a failover" which is triggered by an alert.
CREATE PROCEDURE [jobs].[disable_jobs_after_failover]
AS
BEGIN
SET NOCOUNT ON;
-- If the SP is running at the new-Secondary Replica, except for the ones in the exclusion list, disable all jobs which was disabled at the previously Secondary Replica.
IF (SELECT [dbo].[fn_hadr_group_is_primary](NULL)) = 0
BEGIN
DECLARE @sql NVARCHAR(max) = '';
SELECT
@sql += N'EXEC msdb.dbo.sp_update_job @job_name = ''' + [job_name] + N''', @enabled = 0;'
FROM [jobs].[job_status] AS [j]
WHERE
NOT EXISTS(SELECT [job_name] FROM [jobs].[job_status_exclusion_list] AS [ex] WHERE [ex].[job_name] = [j].[job_name]) AND
[j].[secondary_enabled] = 0
ORDER BY [j].[job_name];
EXEC (@sql);
END
-- If the SP is running at the new-Primary Replica, except for the ones in the exclusion list, disable all jobs which was disabled at the previously Primary Replica.
IF (SELECT [dbo].[fn_hadr_group_is_primary](NULL)) = 1
BEGIN
DECLARE @sql2 NVARCHAR(max) = '';
SELECT
@sql2 += N'EXEC msdb.dbo.sp_update_job @job_name = ''' + [job_name] + N''', @enabled = 0;'
FROM [jobs].[job_status] AS [j]
WHERE
NOT EXISTS(SELECT [job_name] FROM [jobs].[job_status_exclusion_list] AS [ex] WHERE [ex].[job_name] = [j].[job_name]) AND
[j].[primary_enabled] = 0
ORDER BY [j].[job_name];
EXEC (@sql2);
END
END
-- The job that executes the stored procedures above during a failover.
I find it unnecessary to copy/paste the whole script of the job here, it's a simple job consisting of 3 steps.
The name of the job must be "_DBA: Configure SQL Server Agent jobs after a failover", if you do not agree, than please modify the code below accordingly.
REMINDER: Ensure the following code is executed in the correct database where these objects are created.
1. step's code: EXEC [jobs].[enable_jobs_after_failover];
2. step's code: EXEC [jobs].[disable_jobs_after_failover];
3. step's code: EXEC [jobs].[update_job_status_accross_nodes];
A schedule in the type of "Start automatically when SQL Server Agent starts" is needed so that the steps above will be executed at startup in case a replica shuts down unexpectedly.
You also may want to add a notification (Database Mail configuration and an existing operator is needed) so that you'll be informed about a failover.
-- The following alert is triggered when a failover occurs and it is used to execute the job at both of the replicas.
DECLARE @job_id_var uniqueidentifier;
SELECT @job_id_var = job_id FROM msdb..sysjobs WHERE name = '_DBA: Configure SQL Server Agent jobs after a failover'
EXEC msdb.dbo.sp_add_alert
@name = N'AG Role Change',
@message_id = 1480,
@severity = 0,
@enabled = 1,
@delay_between_responses = 0,
@include_event_description_in = 1,
@category_name=N'[Uncategorized]',
@job_id=@job_id_var
GO
REMINDER: Take the following part out if you do not have a SQL Server Agent Operator.
EXEC msdb.dbo.sp_add_notification
@alert_name = N'AG Role Change',
@operator_name = N'DBA',
@notification_method = 1;
GO
************************** End of the script **************************
After executing the scripts above, the SP "[jobs].[update_job_status_accross_nodes]" needs to be executed at the primary replica at least once to update the "[jobs].[job_status]" table so that the solution will know about the status of your jobs at both of the replicas.
During an AG failover the alert is triggered at the previously-primary (if it's still up) and new-primary and every time it is triggered, it executes the job that is created above. Then job enables and disables the related jobs at both of the replicas. Simple.
Some critical points:
- If you happen to decide to use a different name for the job, then please be careful about the create alert step, because the default job name is used to find the correct job_id from the related system table.
- The job must be enabled all the time and at all replicas. This is vital. Otherwise the alert does not execute it.
- If you want to change the name of the Linked Server, then you must update the script accordingly, otherwise it'll be broken.
- Make sure that your SQL Server Agent service's startup parameter is "Automatic", so that it executes the job at startup and update the status of the jobs accordingly.
As I said from the beginning, most of these code blocks are familiar to almost all of us thanks to my peers' hard work. I guess the only bit I add is during a failover, executing a job by a triggered alert at both replicas and enabling/disabling related jobs this way. If a method exactly like this (obviously I'm unaware) has already been shared by someone else, please let me know so that I can write a note about it.
If you have a suggestion, question or if there's something wrong with the code or method above, please tell me and let's make it better together!
That's it folks! I hope you find this solution useful and practical as I do.
Thanks for reading,
Ekrem Onsoy
Microsoft SQL Server Consultant
Kaydol:
Kayıtlar (Atom)