Officiellement, il n’existe pas d’agent SQL Server dans l’offre PaaS Azure : SQL Database. Microsoft proposait donc des solutions de contournement depuis des années : utiliser l’agent d’une instance en IaaS ou OnPremise, Automation etc… Mais il n’y avait pas vraiment de solution propres et simple.
C’est désormais en cours de résolution grâce à un service, encore en phase de Preview : Elastic Job Agent.
Ce service est dédié à l’exécution de tâches SQL Database. Son ajout se fait simplement par le portail et doit être associé à une base de données SQL Database qui servira à contenir un ensemble d’objets (tables, procédures stockées etc…) servant à piloter la solution. Celle-ci peut-être la même que votre base métier. Cette base doit être au minimum une S0. Il n’y a pas de configuration particulière à réaliser, le portail affichera des informations mais aucune administration des jobs n’est réalisable jusqu’à présent. Peut-être qu’à l’avenir, plus d’options seront possibles.
A partir de maintenant, toutes les actions à réaliser sont côté base de données, en T-SQL. Les étapes sont les suivantes :
- Ajouter une Master Key sur la base de données pilotant les jobs
- Créer un compte de connexion capable d’énumérer les bases sur le serveur et le credential associé
- Créer un compte de connexion dédié au job que l’on souhaite réaliser et le credential associé
- Créer un “Target Group” et y intégrer le serveur SQL qui nous intéresse
- Créer un job SQL
Ajout de la Master Key :
Pour assurer des communications sécurisés avec les credentials, la base de données qui pilote l’Elastic Job Agent doit avoir une master key. Celle-ci se créé très simplement :
if not exists(select 1 from sys.symmetric_keys) begin CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'MonGrosMotDePasse!2019'; end
Ici, le mot de passe est dédié à protéger la master key et à pouvoir l’exporter si besoin.
Création des comptes sur la base cible :
Il est nécessaire de disposer de deux comptes au moins, sur la base de données SQL Database que vous souhaitez cibler. Ou plus précisément : un sur la base Master du serveur de votre base PaaS, et l’autre sur votre base utilisateur.
On commence donc par la base de données Master :
CREATE LOGIN MyMasterUser with PASSWORD = 'MonGrosMotDePasseMaster2019!' ; CREATE USER MyMasterUser FOR LOGIN MyMasterUser ;
Puis on enchaine en se plaçant dans la base utilisateur, en y :
CREATE LOGIN MyJobUser with PASSWORD = 'MonGrosMotDePasseUser2019?' ; CREATE USER MyJobUser for LOGIN MyJobUser with DEFAULT_SCHEMA = dbo;
En y ajoutant les privilèges nécessaires à ce que l’on souhaite réaliser, bien sûr :
GRANT VIEW DATABASE STATE TO MyJobUser; GO ALTER ROLE db_ddladmin ADD MEMBER MyJobUser ; GO GRANT VIEW DATABASE STATE TO MyJobUser ; GO GRANT EXECUTE ON IndexOptimize TO MyJobUser ; GO
Désormais, il faut que l’Agent soit capable d’exécuter du code sur la base utilisateur. Pour permettre cette comunication “externe”, on va utiliser des CREDENTIALS . On va donc les créer sur la base de données qui pilote l’Agent. Le schéma utilisé sur la page de documentation Microsoft est plus clair qu’un long discours :
On va donc créer nos deux credentials :
CREATE DATABASE SCOPED CREDENTIAL MyMasterCredential WITH IDENTITY = 'MyMasterUser', SECRET = N'MonGrosMotDePasseMaster2019!' ; go CREATE DATABASE SCOPED CREDENTIAL MyJobCredential WITH IDENTITY = 'MyJobUser', SECRET = N'MonGrosMotDePasseUser2019?' ; go
On notera donc que le credential pointe vers le login / mot de passe du compte créé sur la base métier.
Création d’un Target Group et intégration de la base à celui-ci :
L’Elastic Job Agent étant un composant mutualisable entre plusieurs serveurs / bases de données PaaS, il y a une notion d’affectation des jobs. On créé un objet qui s’appelle le Target Group, auquel on donnera un nom fonctionnel (ici par exemple “Index and Statistics Maintenance Target Group”), puis on y ajoutera les serveurs que l’on veut concerner. Par défaut, toutes les bases associées à un serveur seront traitées, il faut exclure celles-ci qui ne nous intéressent pas.
EXEC jobs.sp_add_target_group @target_group_name = 'Index and Statistics Maintenance Target Group'; EXEC jobs.sp_add_target_group_member @target_group_name = 'Index and Statistics Maintenance Target Group', @membership_type = 'Include' , @target_type = 'SqlServer', @refresh_credential_name = 'MyMasterCredential', @server_name = 'vdesql.database.windows.net' ;
On notera que “target_type” peut prendre comme paramètre “sqldatabase”, mais dans la documentation, à l’heure où j’écris cest lignes, il faut mettre “SqlServer”… Allez comprendre… On peut par contre mettre “sqlElasticPool” si on travaille avec un modèle Elastic Pool.
On notera également qu’il est possible d’exclure une base avec :
EXEC [jobs].sp_add_target_group_member @target_group_name = N'Index and Statistics Maintenance Target Group', @membership_type = N'Exclude', @target_type = N'SqlDatabase', @server_name = N'vdesql.database.windows.net', @database_name =N'MaBaseAExclure' GO
Création du job :
Dans mon exemple je planifie l’exécution d’une tâche de maintenance issue de la solution d’Ola Hallengren :
EXEC jobs.sp_add_job @job_name = N'Index Maintenance Job', @description = 'Reindex et calcul de stat quotidien', @enabled = 1, @schedule_interval_type = N'Days', @schedule_interval_count = 1, @schedule_start_time = N'20190703 23:00:00' ; ; DECLARE @Command NVARCHAR(MAX) = N'EXECUTE dbo.IndexOptimize @Databases = ''USER_DATABASES'', @FragmentationLow = NULL, @FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'', @FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @UpdateStatistics = ''ALL''' ; EXEC jobs.sp_add_jobstep @job_name = N'Index Maintenance Job', @step_name = N'Index and stat maintenance', @command = @Command, @credential_name = 'MyJobCredential', @target_group_name = 'My Index maintenance Target Group', @max_parallelism = 4 ;
On notera que curieusement, il a été intégré, comme paramètre de l’exécution d’un job, son degré de parallélisme maximum.
On peut lancer le job sans attendre sa planification par :
EXEC jobs.sp_start_job @job_name = N'Index Maintenance Job'
Différentes tables sont disponibles dans le schéma “jobs” de la base qui héberge l’agent, on peut par exemple regarder le statut de notre tâche et éventuellement la débugger grâce à la colonne “last_message” :
select * from jobs.job_executions order by current_attempt_start_time desc
On notera globalement que tout ça manque un peu de raffinement et de simplification avec un outillage graphique équivalent à ce que l’on trouve historiquement dans SQL Server, mais l’orientation DevOps du Cloud rend de toute façon l’approche initiale appropriée.
La documentation n’est pas toujours très claire, voir bugguée, mais elle a le mérite d’exister : https://docs.microsoft.com/fr-fr/azure/sql-database/elastic-jobs-tsql
Continuez votre lecture sur le blog :
- SQL Server Managed Instance dans Azure et le SQL Agent : pareil que du On-Prem ? (Vincent Delabre) [AzureSQL Server]
- Le chiffrement et SQL Server – Episode 2 : Mise en oeuvre de TDE (Vincent Delabre) [SQL Server]
- SQL Server : resynchroniser un login avec le user d’une base après une restauration grâce au SID (Emmanuel RAMI) [SQL Server]
- Restauration de bases Azure SQL Database (Vincent Delabre) [AzureSQL Server]
- Oracle et SQL Server: Les Statistiques (Benjamin VESAN) [OracleSQL Server]