Azure SQL Elastic Database Jobs – uniwersalny scheduler na miarę potrzeb

Chmura Azure charakteryzuje się tym, że oferuje nam mnóstwo różnych usług, które możemy wykorzystać w naszych projektach. Co ciekawe mając jakąś konkretną potrzebę mamy do dyspozycji kilka różnych narzędzi czy serwisów, które wykonają powierzone zadania. Oczywiście wybór konkretnego narzędzia nie zawsze jest prosty ponieważ opiera się on na wielu czynnikach jak chociażby budżet, sposób użycia czy też możliwości integracji z użytymi przez nas usługami. Nie inaczej ma się sprawa związana z wykonywaniem zaplanowanych zadań na bazie danych. W świecie On-Premise wybór był raczej prosty i w zdecydowanej większości przypadków wybieraliśmy SQL Agent, nieco rzadziej Windows Scheduler. W Azure sytuacja jest nieco bardziej skomplikowana ponieważ narzędzi, które możemy użyć jest dużo. W przypadku bazy Azure SQL jeśli mamy do czynienia z Managed Instance nadal możemy używać SQL Agent ale w przypadku standardowej bazy tego narzędzia do dyspozycji już nie mamy. Azure oferuje kilka innych usług, które mogą nam zastąpić Agenta np.:

Database Elastic Jobs,
Data Factory,
Logic Apps,
Azure Automation.

Możliwości jest sporo i to wcale nie jest tak, że musimy wybrać jedno narzędzie i używać tylko jednego bo kluczem jest to, że każde z nich jest dobre w zależności od konkretnych okoliczności. Spośród wymienionych narzędzi postanowiłem napisać kilka słów o tym jak wykorzystać Database Elastic Jobs, który oprócz tego, że jest dosyć prosty w konfiguracji to dodatkowo ma kilka przydatnych funkcjonalności. Na moment pisania tego artykułu opisywane funkcjonalności są w fazie preview jednak z całą pewnością wiedza na ich temat jest kluczowa.

Czym zatem jest Database Elastic Jobs? Jest to mechanizm pozwalający wykonywać komendy TSQL na jednej lub wielu bazach Azure SQL według określonego harmonogramu lub na żądanie.
W tej definicji warto zwrócić uwagę na słowa kluczowe jak “komendy TSQL” oraz “na jednej lub wielu”. Przede wszystkim joby w Azure SQL nie są stuprocentowym odpowiednikiem SQL Agent więc z całą pewnością nie wykonamy tam całej gamy zadań takich jak wykonanie komendy Powershell lub uruchomienie pakietu Integration Services – możemy wykonać jedynie zapytania w TSQL. Słowa na “jednej lub wielu” wskazują na fakt, że utworzony przez nas job może być uruchomiony na wielu bazach równolegle! Bazy te mogą (ale nie muszą) być skonfigurowane w ramach Elastic Pool i mamy tutaj naprawdę duże pole do popisu.

Tak więc w jakiej sytuacji powinniśmy pomyśleć o użyciu tego mechanizmu? Dokumentacja wspomina o następujących scenariuszach:

zadania utrzymaniowe, które powinny być wykonywane według wskazanego harmonogramu np. przebudowa indeksów,
zadania, które powinny być wykonywane na kilku bazach np. zbieranie rezultatów różnych zapytań np. w celach statystycznych czy monitoringu,
zadania zbierające dane do celów raportowych,
zadania przenoszące dane z punktu A do B.

Tak więc zakres możliwości jest całkiem szeroki. Przejdźmy zatem do praktyki gdzie pokażemy sobie co i jak. Zainicjować Elastic Database Job możemy oczywiście z poziomu portalu – wystarczy, że wyszukamy sobie Elastic Job agents – czyli mechanizm, który będzie uruchamiał nasze joby:

Po kliknięciu przycisku New widocznego na powyższym zrzucie będziemy mogli przejść do stworzenia nowej definicji agenta:

Konfiguracja sprowadza się do podania nazwy agenta, wybrania subskrypcji oraz bazy danych w której przechowywane będą wszelkie konfiguracje związane z naszymi jobami. W tym miejscu warto wspomnieć o tym, że baza konfiguracyjna to powinien być Azure SQL na poziomie conajmniej S0. Po stworzeniu naszego agenta możemy zalogować się do bazy danych, którą wskazaliśmy jako konfiguracyjną i wtedy zobaczymy cały szereg obiektów, które zostały stworzone na potrzeby Elastic Database Jobs. Znajdują się one w schematach jobs oraz jobs_internal tak jak możecie zauważyć poniżej:

Jeśli chodzi o stworzonego wcześniej przez nas Elastic Job Agent to jeżeli z poziomu portalu przejrzymy dostępne zakładki to dostrzeżemy, że są one puste:

Ponadto na żadnej z zakładek nie znajdziemy opcji umożliwiającej dodanie nowego zadania ani niczego innego. Dzieje się tak dlatego, że widok ten jest przeznaczony jedynie do odczytu i w praktyce odczytuje dane zawarte w bazie konfiguracyjnej. Wszystkie potrzebne obiekty konfigurujemy z poziomu TSQL i bazy konfiguracyjnej.

Przejdźmy zatem do bazy konfiguracyjnej gdzie utworzymy potrzebne obiekty. Pierwszym krokiem jest stworzenie master key do zabezpieczenia database scoped credential, który będzie używany do uwierzytelnienia do docelowej bazy danych przez nasz job:

CREATE MASTER KEY ENCRYPTION BY PASSWORD='zaq1@WSX';  
GO

CREATE DATABASE SCOPED CREDENTIAL elasticJobCredential 
WITH IDENTITY = 'elasticJobCredential',
SECRET = 'zaq1@WSX';
GO

W dalszej kolejności możemy dodać tzw. target group czyli grupę baz danych na której będziemy wykonywać nasze joby. Grupą docelową może być pojedyncza baza danych czy też zestaw baz danych, które z kolei mogą (lub nie) używać tych samych zasobów będąc w tej samej Elastic Pool. Możliwości konfiguracji jest naprawdę dużo i np. wskazując elastic pool jako grupę docelową możemy wykluczyć niektóre bazy tam się znajdujące. Bardzo dobrze obrazuje to schemat dostępny w dokumentacji, którym posłużę się aby wyjaśnić cały koncept:

AzureSQLElasticJobs_05 — https://docs.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview

przykład 1 – możemy wskazywać pojedyncze bazy do wykonywania naszych jobów,
przykład 2 – możemy wskazywać wszystkie bazy w ramach danego serwera Azure SQL,
przykład 3 – możemy wskazywać wszystkie bazy w ramach danego serwera Azure SQL z wyłączeniem wybranych,
przykład 4 – możemy wskazywać wszystkie bazy w ramach określonej Elastic Pool.

Możliwości jest zatem całkiem dużo. My w naszym przykładzie stworzymy sobie grupę docelową jako Elastic Pool – aby to zrobić wykonujemy procedurę jobs.sp_add_target_group na bazie, którą wskazaliśmy jako konfiguracyjną:

EXEC jobs.sp_add_target_group 'MyElasticPool';

Następnie przy pomocy jobs.sp_add_target_group_member dodajemy do naszej grupy docelowej definicję:

EXEC jobs.sp_add_target_group_member
@target_group_name = 'MyElasticPool',
@target_type = 'SqlElasticPool',
@refresh_credential_name = 'elasticJobAdmin',
@server_name = 'sqsqlserver.database.windows.net',
@elastic_pool_name = 'SQLElasticPool';

Powyżej możecie zobaczyć, że jako wartość parametru target_type wskazaliśmy SqlElasticPool, dzięki czemu mechanizm Elastic Jobs wie, że docelowo będziemy wykonywać zadania na grupie baz w ramach Elastic Pool. Oczywiście parametr przyjmuje różne wartości dlatego też w zależności od scenariusza możemy ustawić następujące wartości:

SqlServer,
SqlElasticPool,
SqlDatabase,
SqlShardMap.

Myślę, że każda z tych wartości mówi sama przez siebie może oprócz tej ostatniej. Wartość SqlShardMap pozwala na ustawienie Elastic Job który będzie wykonywał zadania na zestawie baz, które są ze sobą połączone w ramach tzw. Shard Mapy czyli partycjonowania horyzontalnego (o którym z całą pewnością jeszcze napiszę).

Pozostałe parametry procedury raczej są jasne, warto w tym miejscu zaznaczyć, że musimy zadbać o fakt, żeby każda baza docelowa była dostępna dla joba czyli innymi słowy aby uprawnienia skonfigurowane przez nas jako Credential miały odpowiednie prawa na wszystkich bazach docelowych bo inaczej całe przedsięwzięcie się nie powiedzie.

Oczywiście konfigurację jesteśmy w stanie zmienić tzn. możemy dodawać, usuwać oraz wykluczać poszczególne bazy z naszej grupy docelowej. Wszystkie obiekty wraz ze szczegółowym opisem znajdziecie w dokumentacji. My w ramach testu wykluczymy sobie jedną baz z naszej grupy docelowej a posłuży nam do tego jobs.sp_add_target_group_member:

EXEC [jobs].sp_add_target_group_member
@target_group_name = N'MyElasticPool',
@membership_type = N'Exclude',
@target_type = N'SqlDatabase',
@server_name = N'sqsqlserver.database.windows.net',
@database_name = N'sqsqldb';
GO

Wykluczyliśmy zatem pojedynczą bazę danych podając wartość Exclude w parametrze @membership_type. Powyższe działanie spowodowało, że działania wywoływane przez job nie będą wykonywane na bazie konfiguracyjnej. Mamy zatem początkową konfigurację za sobą, w każdej chwili możemy podejrzeć jak to wygląda z odpytując odpowiednie struktury np. obiekt jobs.target_groups wyświetli wszystkie skonfigurowane przez nas grupy docelowe:

SELECT * FROM jobs.target_groups 
WHERE target_group_name='MyElasticPool';

Możemy również sprawdzić definicję grupy docelowej w jobs.target_group_members:

SELECT * FROM jobs.target_group_members 
WHERE target_group_name='MyElasticPool';

To właśnie w tym miejscu jesteśmy w stanie zobaczyć co jest zawarte w definicji grupy oraz wszelkie wykluczenia (o ile istnieją). Myślę, że to właśnie przy pomocy obiektów systemowych dostępnych w bazie konfiguracyjnej najlepiej i najłatwiej dotrzeć do informacji na temat jobów. Na moment pisania niniejszego artykułu widok na konfigurację Elastic Jobs w portalu jest ograniczony i sprowadza się do wylistowania niektórych obiektów:

Mając już skonfigurowane grupy docelowe możemy przejść do konfiguracji zadań. Najpierw tworzymy sobie samego joba przy pomocy jobs.sp_add_job:

EXEC jobs.sp_add_job @job_name = 'MySampleJob'
,@description = 'my sample job';

W dalszej kolejności dodajemy kroki do stworzonego wyżej joba. Oczywiście kroków może być wiele i są one wykonywane sekwencyjnie. W naszym przykładzie pierwszy krok będzie tworzył tabelę dbo.MySampleTable do której będziemy wrzucać pojedyncze wartości całkowitoliczbowe. Ważnym elementem na który warto zwrócić uwagę jest fakt, że parametr @credential_name jest przypisany do pojedynczego kroku tak więc kroki w ramach joba mogą być uruchamiane z różnymi uprawnieniami. Druga ważna rzecz to parametr @retry_attempts gdzie możemy skonfigurować ile razy job ma być ponawiany w przypadku wystąpienia błędu. Oczywiście to nie wszystkie możliwości jakie oferuje konfiguracja pojedynczego kroku – jesteśmy w stanie konfigurować również inne rzeczy takie jak np. maksymalny czas wykonania czy chociażby czas jaki ma upłynąć pomiędzy poszczególnymi krokami (link):

EXEC jobs.sp_add_jobstep 
	@job_name = 'MySampleJob',
	@command = N'
		IF NOT EXISTS 
			(SELECT * FROM sys.tables WHERE object_id = object_id(''MySampleTable''))
			CREATE TABLE [dbo].[MySampleTable]([ID] [int] NOT NULL);
			',
	@credential_name = 'elasticJobAdmin',
	@target_group_name = 'MyElasticPool',
	@retry_attempts = 1;

Mamy skonfigurowany jeden krok więc dodajmy jeszcze jeden. Tym razem w ramach tego nowego kroku dodamy wartość “1” do stworzonej wcześniej przykładowej tabeli:

EXEC jobs.sp_add_jobstep 
	@job_name = 'MySampleJob',
	@step_name = 'Insert Dummy data',
	@command = N'
		INSERT INTO [dbo].[MySampleTable]([ID])
		VALUES(1);
			',
	@credential_name = 'elasticJobAdmin',
	@target_group_name = 'MyElasticPool',
	@retry_attempts = 1;

Całość konfiguracji jest oczywiście do podejrzenia w obiektach systemowych:

SELECT js.* FROM jobs.jobsteps AS js
JOIN jobs.jobs AS j
  ON j.job_id = js.job_id 
  AND j.job_version = js.job_version;

Pierwsze konfiguracje za nami więc możemy przejść do uruchomienia naszego joba na żądanie używając jobs.sp_start_job:

EXEC jobs.sp_start_job 'MySampleJob';

Status wykonania możemy śledzić podobnie jak wszystko inne w odpowiednich obiektach powiązanych z mechanizmem:

SELECT 
	 job_execution_id
	,job_name
	,lifecycle
	,DATEDIFF(minute,start_time,end_time) AS Duration
	,last_message
FROM jobs.job_executions 
WHERE step_id IS NULL
ORDER BY start_time DESC;

Dane na temat poszczególnych wykonań są przechowywane domyślnie przez 45 dni więc jeśli mamy potrzebę przechowywać je dłużej musimy je cyklicznie zapisywać w wybranym przez nas miejscu. Jeśli chcielibyśmy się pozbyć historii samemu to możemy do tego celu wykorzystać procedurę jobs.sp_purge_jobhistory.

Wynik działania możemy również sprawdzić wchodząc do bazy ustawionej jako target group:

SELECT 
	 DB_NAME() AS DatabaseName
	,ID 
FROM dbo.MySampleTable

Zgodnie z oczekiwaniami tabela została stworzona i wrzucony został do niej jeden wiersz. Wskazaliśmy Elastic Pool jako Target Group ale jednocześnie wykluczyliśmy z niej jedną bazę dlatego w celu sprawdzenia próbowałem wykonać to samo zapytanie na tej właśnie wykluczonej bazie. Okazało się, że tak jak można było się spodziewać job nie został wykonany na tej właśnie bazie:

W celach testowych spróbujmy spowrotem załączyć wykluczoną wcześniej bazę do joba. Może się wydawać dobrym pomysłem wykonanie jobs.sp_add_target_group_member tylko tym razem z @membership_type=’Include’

EXEC [jobs].sp_add_target_group_member
@target_group_name = N'MyElasticPool',
@membership_type = N'Include',
@target_type = N'SqlDatabase',
@server_name = N'sqsqlserver.database.windows.net',
@database_name = N'sqsqldb';
GO

jednakże w tym wypadku otrzymamy błąd

Stało się tak dlatego, że ta konkretna baza została już dodana do definicji target groupy MyElasticPool. Wiemy że baza, którą dodajemy jest już w Elastic Pool więc sposobem aby job się na niej wykonywał jest usunięcie definicji wykluczenia co zrobimy wykonując procedurę jobs.sp_delete_target_group_member:

[jobs].sp_delete_target_group_member 
	@target_group_name =  'MyElasticPool'
   ,@target_id = '4E798BF6-E927-4262-B75E-29D3921204D3'

przy wykonaniu tej procedury jedyne o co musimy zadbać to nazwa targety group oraz identyfikator zasobu, którego definicje chcemy usunac z target groupy (identyfikator znajdziemy w jobs.target_group_members). Po wykonaniu powyższej procedury możemy raz jeszcze uruchomić joba

Druga baza z kolei zawiera już dwa wpisy w testowej tabeli:

Jak widzicie wszystko działa zgodnie z założeniami, a całość jest łatwo dostępna z poziomu Transact-SQL. Do tej pory uruchomiliśmy job na żądanie ale należy pamiętać, że mamy również możliwość stworzenia harmonogramu. Do tego celu służą oczywiście parametry @schedule_interval_type oraz @schedule_interval_count, które możemy przekazać przy tworzeniu joba w procedurze jobs.sp_update_job lub w procedurze jobs.sp_update_job, która zaaktualizuje isteniejący:

EXEC jobs.sp_update_job 
	@job_name = 'MySampleJob',
	@schedule_interval_type = 'Minutes',
	@schedule_interval_count = 1;

Parametr @schedule_interval_type może przyjąć następujące wartości:

Once,
Minutes,
Hours,
Days,
Weeks,
Months

czyli jest to nic innego jak jednostka czasu, a @schedule_interval_count mówi co ile jednostek czasu dany job będzie wykonywany. W moim przykładzie powyżej możecie zauważyć, że job będzie wykonywany co 15 minut. Poszczególne wykonania możemy podejrzeć aby przekonać się, że rzeczywiscie harmonogram działa zgodnie z założeniami:

	SELECT 
	 job_execution_id
	,job_name
	,lifecycle
	,start_time
	,end_time
	,DATEDIFF(minute,start_time,end_time) AS Duration
	,last_message
FROM jobs.job_executions 
WHERE step_id IS NULL
ORDER BY start_time DESC;

Oczywiście harmonogramy nie są “obiektami” samymi w sobie tak jak miało to miejsce w przypadku SQL Agent – tutaj są one po prostu właściwością związaną z jobem:

select * from jobs.jobs

Na powyższym zrzucie możecie również zobaczyć dwie dodatkowe właściwości związane z harmonogramem, które jednocześnie są dodatkowymi parametrami i możemy je podpiąć do naszego joba – są to: @schedule_start_time oraz @schedule_end_time czyli nic innego jak zakres dat od kiedy to kiedy dany job może być uruchomiony.

Powoli zbliżając się ku końcowi chciałbym wspomnieć o jeszcze dwóch właściwości na które chciałbym żebyście zwrócili uwagę pracując z Elastic Job. Pierwszą z nich jest @max_parallelism który jest przypisany do kroku joba gdzie możemy sterować ile maksymalnie może być wykonań joba na bazach będących częścią Elastic Pool. Wiemy, że bazy będące w tej samej Elastic Pool współdzielą ze sobą zasoby tak więc jeśli mamy jakieś ciężkie zadania do wykonania być może warto ograniczyć ilość równoległych przebudowań aby nie spowodować zbytniego obciążenia na puli. Druga właściwość warta uwagi to @enabled ustawiany na poziomie joba gdzie wskazujemy czy jest on aktywny czy też nie.

Oczywiście to nie wszystkie możliwości jakie oferuje mechanizm, mamy jeszcze możliwość przechwycenia rezultatu i kilka dodatkowych funkcjonalności, których szczegółowe opisy znajdziecie w dokumentacji. Na dziś to by było na tyle – zachęcam do subskrypcji i pozdrawiam!