Planification Azure SQL Databases–Azure Elastic Jobs

Après avoir abordé par deux fois déjà la planification de Jobs sur SQL Azure au travers de Azure Runbook et et Azure Functions, il est temps d’aborder une méthode toujours en preview : les jobs élastiques.

On attend toujours la GA de cette feature qui permet en “relative” simplicité d’automatiser l’exécution de jobs sur plusieurs bases, voire des bases sur plusieurs “serveurs” SQL Azure. Malgré le nom Elastic Jobs, cette fonctionnalité n’est pas réservée aux bases de données contenues dans un elastic pool. On peut très bien planifier une tâche dans une base stand alone.

En résumé, il s’agit d’un service SaaS (???) qui va centraliser les jobs et leur planification, ainsi que les connexions nécessaires pour accéder aux bases, stocker le résultat de l’exécution, voire même le résultat de requêtes exécutées dans les étapes de ce job.

On retrouve finalement le fonctionnement de l’agent SQL Server. Et c’était bien là le but. Et comme pour l’agent SQL Server qui s’appuie sur la base MSDB, in va nous falloir disposer d’une base de donnée qui sera dédiée à la gestion de ces jobs.

Une fois cette base de donnée créé, il suffit de se déplacer dans la section dédiée aux travaux élastiques :

Une fois n’est pas coutume, j’avoue être assez fan de l’icone présente au dessus du bouton nous invitant à créer un agent. Vraiment représentatif su service fourni.

Le fait de pouvoir créer plusieurs agent est un peu déconcertant, mais en y réfléchissant bien cela peut aussi permettre de cloisonner les choses. Garder de côté les jobs plutôt dédiés à la maintenance et avoir un autre agent dans lequel des utilisateurs pourraient planifier des opérations davantage orientées business.

Ah, oui, on vous a bien prévenu, c’est du preview … Toujours sur de vouloir continuer ???

Il suffit alors de sélectionner la base servant de réceptacles à la fonctionnalité :

Il suffit de patienter un peu pour que le système configure cette base.

Ah, oui, j’ai oublié de vous dire, c’est encore une version Preview … L’interface graphique s’arrête donc là !

En effet, tout ce que vous trouverez dans le portal est en lecture seule! Vous pourrez donc seulement consulter vos Jobs, Cedentials , …

La configuration consiste à créer un ensemble de tables, vues et procédures stockées qui permettront de “retrouver” nos petites habitudes liées à l’agent SQL …  Avec entre autre les procédures stockées permettant de créer un job, une étape (jobstep), démarrer un travail et le stopper

A présent il va falloir … coder. Au choix : PowerShell, ou bien T-SQL. Le plus  simple dans un premier temps est de conserver le langage natif de SQL Server, mais ceux pour qui l’automatisation est une priorité se pencheront naturellement vers du PowerShell.

La première étape consiste à travailler la sécurité. Il faut alors définir :

• une identité permettant de lister les bases du “serveur” Azure (souvenez vous, j’ai déjà mentionné le fait que l’on pouvait exécuter des jobs multi serveurs)
• une identité permettant d’exécuter le job dans chaque base de données.

D’abord les Logins (pensez bien à rester sur ma base “master” de votre instance)

CREATE LOGIN ElasticJobMasterUser WITH PASSWORD = N'R3@lly$tr0ngP@$$w0rd!';
GO
CREATE LOGIN ElasticJobJobUser WITH PASSWORD = N'R3@lly$tr0ngP@$$w0rd!';
GO

Ensuite, créer le User correspondant au Job Master dans votre base MSDB …

CREATE USER ElasticJobMasterUser FOR LOGIN ElasticJobMasterUser
GO
ALTER ROLE db_owner ADD MEMBER ElasticJobMasterUser;  
GO

Il faut a présent créer les credentials qui permettrons de se connecter aux bases cible :

CREATE MASTER KEY ENCRYPTION BY PASSWORD='An0therR3@lly$tr0ngP@$$w0rd!';  
GO

CREATE DATABASE SCOPED CREDENTIAL ElasticJobMasterUserCredential 
WITH IDENTITY = 'ElasticJobMasterUser', SECRET = 'R3@lly$tr0ngP@$$w0rd!'; 
GO

CREATE DATABASE SCOPED CREDENTIAL ElasticJobJobUserCredential 
WITH IDENTITY = 'ElasticJobJobUser', SECRET = 'R3@lly$tr0ngP@$$w0rd!'; 
GO 

 

Une fois fait, dans chaque base cible des jobs, il faut créer le User JobUser (DB01 et  DB02 dans cet exemple) :

Les permissions données dépendant alors des tâches que vous souhaitez effectuer via le job. db_datareader, db_datawriter peuvent suffire, ou mieux, travaillez directement au niveau de chaque Schéma …

CREATE USER ElasticJobJobUser FOR LOGIN ElasticJobJobUser
GO
ALTER ROLE db_owner ADD MEMBER ElasticJobJobUser;  
GO

 

L’idée suivante consiste à créer un TargetGroup : tout simplement une liste de bases sur lesquelles les jobs vont être exécutés.

Une fois le groupe créé, plusieurs options sont alors possibles pour ajouter des membres au groupe (des bases de données tout simplement). La procédure stockée sp_add_target_group_member permet:

• d’ajouter toutes les bases de l’instance par le couple de paramètres target_type = ‘SqlServer’ et server_name = ‘xxx” »’. Dans ce cas, le login JobMaster va être utilisé pour lister les bases présentes
• d’ajouter unitairement une ou plusieurs bases au travers des paramètres target_type = ‘SqlServer’ et server_name=”xxx”  et database_name= “xxx”
• d’exclure (ou inclure) une base a postériori, ou dans le cas d’une selection complète de bases au travers des parametres membership_type = ‘Include/Exclude’ et datbase_name = ‘xxx’
• de travailler au niveau d’un Elastic Pool avec target_type = ‘SqlElasticPool’ suivi du nom du serveur et du nom du pool.

Des options qui permettent de moduler facilement la portée du job, ou de planifier à différents horaires des actions sur des Pools Elastiques distincts avec un seul job mais plusieurs targets.

Sachant également qu’il peut y avoir plusieurs targets dans le même target group, on peut donc exécuter un job sur plusieurs serveurs et / ou  plusieurs elastic pools d’un seul appel.

Pour l’exemple, j’ai choisi de ne travailler que sur 2 bases, non contenue dans un elastic pool, de mon serveur en France, avec donc une exclusion.

-- Add a target group containing server(s)
EXEC [jobs].sp_add_target_group N'ServerGroup'
GO

-- Add a server target member
EXEC [jobs].sp_add_target_group_member
	@target_group_name = N'ServerGroup',
	@target_type = N'SqlServer',
	@refresh_credential_name=N'ElasticJobMasterUserCredential', 
	@server_name=N'conseilit-fr.database.windows.net'
GO

--Exclude a database target member from the server target group
EXEC [jobs].sp_add_target_group_member
	@target_group_name = N'ServerGroup',
	@membership_type = N'Exclude',
	@target_type = N'SqlDatabase',
	@server_name = N'conseilit-fr.database.windows.net',
	@database_name =N'SQLAgent1'
GO

une requête sur target_group_member permet de visualiser le contenu des groupes:

Reste à présent à …. créer un job ! On retrouve les bonnes vieilles habitudes au travers de sp_add_job et sp_add_jobstep, même s’ile ne s’agit bien évidement pas du même code behind.

Comme pour les tests précédents, j’ai opté pour la procédure stockée de Ola Hallengren pour la gestion des index (Rebuil / Reorganize / Update Statistics) : Oui, cela reste à votre charge sur SQL Azure (PaaS et non SaaS !!). Les objets ont été créés dans chaque base cible.

EXEC jobs.sp_add_job 
	@job_name='IndexManagement', 
	@description='Index rebuild, index reorg, update statistics'


EXEC jobs.sp_add_jobstep 
	@job_name='IndexManagement',
	@command='exec [dbo].[IndexOptimize]',
	@credential_name='ElasticJobJobUserCredential',
	@target_group_name='ServerGroup'

Reste plus qu’à tester …

EXEC jobs.sp_start_job 'IndexManagement'

Il est possible de “suivre” l’exécution des jobs via des requêtes SQL, car il n’existe rien depuis le portail …

--View top-level execution status for the job named 'IndexManagement'
SELECT * FROM jobs.job_executions 
WHERE job_name = 'IndexManagement' and step_id IS NULL
ORDER BY start_time DESC

--View all top-level execution status for all jobs
SELECT * FROM jobs.job_executions WHERE step_id IS NULL
ORDER BY start_time DESC

--View all execution statuses for job named 'IndexManagement'
SELECT * FROM jobs.job_executions 
WHERE job_name = 'IndexManagement' 
ORDER BY start_time DESC

-- View all active executions
SELECT * FROM jobs.job_executions 
WHERE is_active = 1
ORDER BY start_time DESC	

En cours d’exécution :

et une fois le job terminé :

Une fois que tout est validé, il est possible de planifier le job. Contrairement à SQL Server “classique” il n’est pas possible de disposer de plusieurs planifications par job. La planification est directement rattachée au job et se paramètre via la procédure sp_update_job. Les possibilités sont moins étendues que pour un SQLAgent, mais devrait néanmoins couvrir les cas les plus fréquents.

DECLARE @StartTime DateTime
SET @StartTime = DATEADD(hour,1,CONVERT(DateTime,CONVERT(Date,GetDate())))

EXEC jobs.sp_update_job
	@job_name='IndexManagement',
	@enabled=1,
	@schedule_interval_type='Days',
	@schedule_interval_count=1,
	@schedule_start_time=@StartTime

Après quelques exécutions planifiées, on peut consulter le résultat des exécutions dans jobs.job_executions.

SELECT job_name,step_name,lifecycle,start_time,end_time,last_message,target_type,target_server_name,target_database_name 
FROM [jobs].[job_executions]
ORDER BY current_attempt_start_time desc 

Mais toujours rien depuis le portail Azure.

Un peu frustrant, espérons que lors de la General Availability un effort sera fait de ce côté là. Sinon, gageons que des éditeurs tiers fourniront une IHM adaptée.

Happy scheduling !