Créer et gérer des tâches élastiques à l'aide de T-SQL

S’applique à : Azure SQL Database

Cet article fournit un tutoriel et des exemples pour commencer à travailler avec des tâches élastiques en utilisant T-SQL. Les travaux élastique permettent l’exécution d’un ou plusieurs scripts Transact-SQL (T-SQL) en parallèle sur plusieurs bases de données.

Les exemples de cet article utilisent les procédures stockées et les vues disponibles dans la base de données des tâches.

Dans ce tutoriel complet, vous apprendrez les étapes nécessaires à l'exécution d'une requête sur plusieurs bases de données :

Créer l’agent de la tâche élastique

Transact-SQL (T-SQL) peut être utilisé pour créer, configurer, exécuter et gérer des tâches.

La création d'un agent de tâches élastiques n'est pas prise en charge par T-SQL. Vous devez donc d'abord créer un agent de tâches élastiques en utilisant le portail Azure, ou créer un agent de tâches élastiques en utilisant PowerShell.

Créer l’authentification de la tâche

L'agent de tâche élastique doit être en mesure de s'authentifier auprès de chaque serveur ou base de données cible. Comme indiqué dans Créer l’authentification de l’agent de la tâche, l’approche recommandée consiste à utiliser l’authentification Microsoft Entra (anciennement Azure Active Directory) avec une identité managée affectée par l’utilisateur (UMI). Auparavant, les informations d’identification limitées à la base de données étaient la seule option.

Utiliser l’authentification Microsoft Entra avec une UMI pour l’exécution de la tâche

Pour utiliser la méthode recommandée de l’authentification Microsoft Entra (anciennement Azure Active Directory) à une identité managée affectée par l’utilisateur, procédez comme suit. L'agent de tâche élastique se connecte au ou aux serveurs logiques/bases de données cibles souhaités via l'authentification Microsoft Entra.

En plus des utilisateurs de connexion et de base de données, notez l’ajout des GRANT commandes dans le script suivant. Ces autorisations sont requises pour le script que nous avons choisi pour cet exemple de travail. Vos tâches peuvent nécessiter des autorisations différentes. Comme l'exemple crée une nouvelle table dans les bases de données ciblées, l'utilisateur de la base de données dans chaque base de données cible doit disposer des autorisations appropriées pour que l'exécution soit réussie.

Dans chacun des serveurs/bases de données cibles, créez un utilisateur autonome mappé à l’UMI.

• Si la tâche élastique a des cibles de serveur logique ou de pool, vous devez créer l’utilisateur contenu mappé à l’UMI dans la base de données master du serveur logique cible.
• Par exemple, pour créer une connexion de base de données autonome dans la base de données master et un utilisateur dans la base de données utilisateur, en fonction de l’identité managée affectée par l’utilisateur (UMI) nommée job-agent-UMI :

--Create a login on the master database mapped to a user-assigned managed identity (UMI)
CREATE LOGIN [job-agent-UMI] FROM EXTERNAL PROVIDER; 

--Create a user on a user database mapped to a login.
CREATE USER [job-agent-UMI] FROM LOGIN [job-agent-UMI];

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO jobuser;
GRANT CREATE TABLE TO jobuser;

• Pour créer un utilisateur de base de données autonome si une connexion n’est pas nécessaire sur le serveur logique :

--Create a contained database user on a user database mapped to a user-assigned managed identity (UMI)
CREATE USER [job-agent-UMI] FROM EXTERNAL PROVIDER; 

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO jobuser;
GRANT CREATE TABLE TO jobuser;

Utiliser des informations d’identification délimitées à la base de données pour l’exécution du travail

Les informations d’identification limitées à la base de données sont utilisées pour se connecter à vos bases de données cibles pour l’exécution du script. Les informations d’identification ont besoin d’autorisations appropriées pour les bases de données spécifiées par le groupe cible afin de pouvoir exécuter le script. Lors de l'utilisation d'un serveur SQL logique et/ou d'un membre du groupe cible du pool, il est recommandé de créer un identifiant qui sera utilisé pour actualiser l'identifiant avant l'expansion du serveur et/ou du pool au moment de l'exécution de la tâche. Les informations d’identification limitées à la base de données sont créées dans la base de données de l’agent de la tâche.

Les mêmes informations d’identification doivent être utilisées pour créer une connexion et créer un utilisateur à partir de la connexion afin d’accorder les autorisations de connexion à la base de données sur toutes les bases de données cibles.

--Connect to the new job database specified when creating the elastic job agent

-- Create a database master key if one does not already exist, using your own password.  
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<EnterStrongPasswordHere>';  

-- Create two database-scoped credentials.  
-- The credential to connect to the Azure SQL logical server, to execute jobs
CREATE DATABASE SCOPED CREDENTIAL job_credential WITH IDENTITY = 'job_credential',
    SECRET = '<EnterStrongPasswordHere>';
GO
-- The credential to connect to the Azure SQL logical server, to refresh the database metadata in server
CREATE DATABASE SCOPED CREDENTIAL refresh_credential WITH IDENTITY = 'refresh_credential',
    SECRET = '<EnterStrongPasswordHere>';
GO

Ensuite, créez des connexions sur les serveurs cibles, ou des utilisateurs de base de données contenus sur les bases de données cibles.

Créez une connexion dans la base de données master du SQL Server logique et les utilisateurs de chaque base de données utilisateur.

--Create a login on the master database
CREATE LOGIN job_credential WITH PASSWORD='<Enter_same_StrongPassword_as_database_scoped_credential>';

--Create a user on a user database mapped to a login.
CREATE USER [job_credential] FROM LOGIN [job_credential];

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO job_credential;
GRANT CREATE TABLE TO job_credential;

Créez un utilisateur de base de données autonome si une connexion n’est pas nécessaire sur le serveur logique. En règle générale, vous ne le feriez que si vous disposez d'une base de données unique à gérer avec cet agent de tâche élastique.

--Create a contained database user on a user database mapped to a Microsoft Entra account
CREATE USER [job_credential] WITH PASSWORD='<Enter_same_StrongPassword_as_database_scoped_credential>';

-- Grant permissions as necessary to execute your jobs. For example, ALTER and CREATE TABLE:
GRANT ALTER ON SCHEMA::dbo TO job_credential;
GRANT CREATE TABLE TO job_credential;

Définir des serveurs et des bases de données cibles

L’exemple suivant montre comment exécuter un travail sur toutes les bases de données d’un serveur.

Connectez-vous au job_database et exécutez la commande suivante pour ajouter un groupe cible et un membre cible :

-- Connect to the job database specified when creating the job agent

-- Add a target group containing server(s)
EXEC jobs.sp_add_target_group 'ServerGroup1';

-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'ServerGroup1',
@target_type = 'SqlServer',
@server_name = 'server1.database.windows.net';

--View the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name='ServerGroup1';
SELECT * FROM jobs.target_group_members WHERE target_group_name='ServerGroup1';

Exclure une base de données individuelle

L’exemple suivant montre comment exécuter un projet sur toutes les bases de données d’un serveur, à l’exception de la base de données appelée MappingDB.

Lorsque vous utilisez l’authentification Microsoft Entra (anciennement Azure Active Directory), omettez le paramètre @refresh_credential_name, qui ne doit être fourni que lors de l’utilisation d’informations d’identification limitées à la base de données. Dans les exemples suivants, le paramètre @refresh_credential_name est commenté.

Connectez-vous au job_database et exécutez la commande suivante :

--Connect to the job database specified when creating the job agent

-- 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'refresh_credential', --credential required to refresh the databases in a server
@server_name = N'London.database.windows.net';
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'refresh_credential', --credential required to refresh the databases in a server
@server_name = 'server2.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'server1.database.windows.net',
@database_name = N'MappingDB';
GO

--View the recently created target group and target group members
SELECT * FROM [jobs].target_groups WHERE target_group_name = N'ServerGroup';
SELECT * FROM [jobs].target_group_members WHERE target_group_name = N'ServerGroup';

Créer un groupe cible (pools)

L’exemple suivant montre comment cibler toutes les bases de données dans un ou plusieurs pools élastiques.

Lorsque vous utilisez l’authentification Microsoft Entra (anciennement Azure Active Directory), omettez le paramètre @refresh_credential_name, qui ne doit être fourni que lors de l’utilisation d’informations d’identification limitées à la base de données. Dans les exemples suivants, le paramètre @refresh_credential_name est commenté.

Connectez-vous au job_database et exécutez la commande suivante :

--Connect to the job database specified when creating the job agent

-- Add a target group containing pool(s)
EXEC jobs.sp_add_target_group 'PoolGroup';

-- Add an elastic pool(s) target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'PoolGroup',
@target_type = 'SqlElasticPool',
--@refresh_credential_name = 'refresh_credential', --credential required to refresh the databases in a server
@server_name = 'server1.database.windows.net',
@elastic_pool_name = 'ElasticPool-1';

-- View the recently created target group and target group members
SELECT * FROM jobs.target_groups WHERE target_group_name = N'PoolGroup';
SELECT * FROM jobs.target_group_members WHERE target_group_name = N'PoolGroup';

Créer un travail et une tâche

Avec T-SQL, créez des tâches à l’aide de procédures stockées système dans la base de données de tâches : jobs.sp_add_job et jobs.sp_add_jobstep. Les commandes T-SQL sont une syntaxe similaire aux étapes nécessaires pour créer des tâches SQL Agent et des étapes de projet dans SQL Server.

Vous ne devez pas mettre à jour les affichages catalogue internes dans la base de données des tâches. Les modifications manuelles de ces affichages catalogue peuvent endommager la base de données des tâches et provoquer une défaillance. Ces vues sont destinées aux requêtes en lecture seule. Vous pouvez utiliser les procédures stockées dans le schéma jobs de votre base de données des tâches.

• Lors de l’utilisation de l’authentification Microsoft Entra pour un Microsoft Entra ID ou une identité managée affectée par l’utilisateur pour s’authentifier auprès des serveurs/bases de données cibles, l’argument @credential_name ne doit pas être fourni pour sp_add_jobstep ou sp_update_jobstep. De même, omettez les arguments facultatifs @output_credential_name et @refresh_credential_name.
• Lors de l’utilisation des informations d’identification limitées à la base de données pour s’authentifier auprès des serveurs/bases de données cibles, le paramètre @credential_name est requis pour sp_add_jobstep et sp_update_jobstep.
  • Par exemple : @credential_name = 'job_credential'.

Les exemples suivants fournissent des guides pour créer des tâches et des étapes de tâche à l’aide de T-SQL pour accomplir des tâches courantes avec des tâches élastiques.

Exemples

Déployer un nouveau schéma sur plusieurs bases de données

L’exemple suivant montre comment déployer le nouveau schéma sur toutes les bases de données.

Connectez-vous au job_database et exécutez la commande suivante :

--Connect to the job database specified when creating the job agent

--Add job for create table
EXEC jobs.sp_add_job @job_name = 'CreateTableTest', @description = 'Create Table Test';

-- Add job step for create table
EXEC jobs.sp_add_jobstep @job_name = 'CreateTableTest',
@command = N'IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id(''Test''))
CREATE TABLE [dbo].[Test]([TestId] [int] NOT NULL);',
@target_group_name = 'PoolGroup';

Collecte de données à l’aide de paramètres intégrés

Dans de nombreux scénarios de collecte de données, il peut être utile d’inclure certaines de ces variables de script pour favoriser le post-traitement des résultats du travail.

• $(job_name)
• $(job_id)
• $(job_version)
• $(step_id)
• $(step_name)
• $(job_execution_id)
• $(job_execution_create_time)
• $(target_group_name)

Par exemple, pour regrouper tous les résultats d'une même tâche, utilisez $(job_execution_id) comme dans la commande suivante :

@command= N' SELECT DB_NAME() DatabaseName, $(job_execution_id) AS job_execution_id, * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(mi, -20, GETDATE());'

Surveiller les performances de la base de données

L’exemple suivant crée un nouveau travail pour collecter des données de performances de plusieurs bases de données.

Par défaut, l’agent de travail crée la table de sortie dans laquelle sont stockés les résultats retournés. Le principal de base de données associé aux informations d’identification de sortie doit donc disposer au minimum des autorisations suivantes : CREATE TABLE sur la base de données, ALTER, SELECT, INSERT et DELETE sur la table de sortie ou son schéma et SELECT sur la vue de catalogue sys.indexes.

Si vous souhaitez créer manuellement la table à l’avance, elle doit avoir les propriétés suivantes :

1. Colonnes avec le nom et les types de données corrects pour le jeu de résultats.
2. Colonne supplémentaire pour internal_execution_id avec le type de données uniqueidentifier.
3. Index non cluster nommé IX_<TableName>_Internal_Execution_ID sur la colonne internal_execution_id.
4. Toutes les autorisations énumérées précédemment, à l'exception de l'autorisation CREATE TABLE sur la base de données.

Se connecter à la base de données de travail et exécuter les commandes suivantes :

--Connect to the job database specified when creating the job agent

-- Add a job to collect perf results
EXEC jobs.sp_add_job @job_name ='ResultsJob', @description='Collection Performance data from all customers'

-- Add a job step w/ schedule to collect results
EXEC jobs.sp_add_jobstep
@job_name = 'ResultsJob',
@command = N' SELECT DB_NAME() DatabaseName, $(job_execution_id) AS job_execution_id, * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(mi, -20, GETDATE());',
@target_group_name = 'PoolGroup',
@output_type = 'SqlDatabase',
@output_server_name = 'server1.database.windows.net',
@output_database_name = '<resultsdb>',
@output_table_name = '<output_table_name>';

--Create a job to monitor pool performance

--Connect to the job database specified when creating the job agent

-- Add a target group containing elastic job database
EXEC jobs.sp_add_target_group 'ElasticJobGroup';

-- Add a server target member
EXEC jobs.sp_add_target_group_member
@target_group_name = 'ElasticJobGroup',
@target_type = 'SqlDatabase',
@server_name = 'server1.database.windows.net',
@database_name = 'master';

-- Add a job to collect perf results
EXEC jobs.sp_add_job
@job_name = 'ResultsPoolsJob',
@description = 'Demo: Collection Performance data from all pools',
@schedule_interval_type = 'Minutes',
@schedule_interval_count = 15;

-- Add a job step w/ schedule to collect results
EXEC jobs.sp_add_jobstep
@job_name='ResultsPoolsJob',
@command=N'declare @now datetime
DECLARE @startTime datetime
DECLARE @endTime datetime
DECLARE @poolLagMinutes datetime
DECLARE @poolStartTime datetime
DECLARE @poolEndTime datetime
SELECT @now = getutcdate ()
SELECT @startTime = dateadd(minute, -15, @now)
SELECT @endTime = @now
SELECT @poolStartTime = dateadd(minute, -30, @startTime)
SELECT @poolEndTime = dateadd(minute, -30, @endTime)

SELECT elastic_pool_name , end_time, elastic_pool_dtu_limit, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, max_worker_percent, max_session_percent,
        avg_storage_percent, elastic_pool_storage_limit_mb FROM sys.elastic_pool_resource_stats
        WHERE end_time > @poolStartTime and end_time <= @poolEndTime;
',
@target_group_name = 'ElasticJobGroup',
@output_type = 'SqlDatabase',
@output_server_name = 'server1.database.windows.net',
@output_database_name = 'resultsdb',
@output_table_name = '<output_table_name>';

Exécuter le travail

L'exemple suivant montre comment lancer une tâche immédiatement dans le cadre d'une action manuelle non planifiée.

Connectez-vous au job_database et exécutez la commande suivante :

--Connect to the job database specified when creating the job agent

-- Execute the latest version of a job
EXEC jobs.sp_start_job 'CreateTableTest';

-- Execute the latest version of a job and receive the execution ID
declare @je uniqueidentifier;
exec jobs.sp_start_job 'CreateTableTest', @job_execution_id = @je output;
select @je;

-- Monitor progress
SELECT * FROM jobs.job_executions WHERE job_execution_id = @je;

Planifier l’exécution d’un travail

L'exemple suivant montre comment planifier une tâche pour qu'elle soit exécutée de manière récurrente toutes les 15 minutes.

Connectez-vous au job_database et exécutez la commande suivante :

--Connect to the job database specified when creating the job agent

EXEC jobs.sp_update_job
@job_name = 'ResultsJob',
@enabled=1,
@schedule_interval_type = 'Minutes',
@schedule_interval_count = 15;

Afficher les définitions des travaux

L’exemple suivant montre comment afficher les définitions du travail actuel.

Connectez-vous au job_database et exécutez la commande suivante :

--Connect to the job database specified when creating the job agent

-- View all jobs
SELECT * FROM jobs.jobs;

-- View the steps of the current version of all jobs
SELECT js.* FROM jobs.jobsteps js
JOIN jobs.jobs j
  ON j.job_id = js.job_id AND j.job_version = js.job_version;

-- View the steps of all versions of all jobs
SELECT * FROM jobs.jobsteps;

Surveiller l’état d’exécution du travail

L’exemple suivant montre comment afficher les détails de l’état d’exécution de tous les travaux.

Connectez-vous au job_database et exécutez la commande suivante :

--Connect to the job database specified when creating the job agent

--View top-level execution status for the job named 'ResultsPoolJob'
SELECT * FROM jobs.job_executions
WHERE job_name = 'ResultsPoolsJob' 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 'ResultsPoolsJob'
SELECT * FROM jobs.job_executions
WHERE job_name = 'ResultsPoolsJob'
ORDER BY start_time DESC;

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

Annulation d’une tâche

L’exemple suivant montre comment récupérer un ID d’exécution de projet, puis annuler une exécution de tâche.

Connectez-vous au job_database et exécutez la commande suivante :

--Connect to the job database specified when creating the job agent

-- View all active executions to determine job execution ID
SELECT * FROM jobs.job_executions
WHERE is_active = 1 AND job_name = 'ResultPoolsJob'
ORDER BY start_time DESC;
GO

-- Cancel job execution with the specified job execution ID
EXEC jobs.sp_stop_job '01234567-89ab-cdef-0123-456789abcdef';

Supprimer l’historique d’un ancien travail

L’exemple suivant montre comment supprimer l’historique d’un travail avant une date spécifique.

Connectez-vous au job_database et exécutez la commande suivante :

--Connect to the job database specified when creating the job agent

-- Delete history of a specific job's executions older than the specified date
EXEC jobs.sp_purge_jobhistory @job_name='ResultPoolsJob', @oldest_date='2016-07-01 00:00:00';

--Note: job history is automatically deleted if it is >45 days old

Supprimer un travail et tous ses historiques

L’exemple suivant montre comment supprimer un travail et tout l’historique qui s’y rapporte.

Connectez-vous au job_database et exécutez la commande suivante :

--Connect to the job database specified when creating the job agent

EXEC jobs.sp_delete_job @job_name='ResultsPoolsJob';
EXEC jobs.sp_purge_jobhistory @job_name='ResultsPoolsJob';

--Note: job history is automatically deleted if it is >45 days old

Procédures de travail stockées

Les procédures stockées suivantes se trouvent dans la base de données des travaux. Elles sont également nommées, mais distinctement différentes des procédures stockées système utilisées pour le service SQL Server Agent.

Affichages des travaux

Les vues suivantes sont disponibles dans la base de données des travaux.

Étape suivante