Delen via

Elastische taken maken en beheren met behulp van T-SQL

  • Artikel

van toepassing op:Azure SQL Database-

Dit artikel bevat een zelfstudie en voorbeelden om aan de slag te gaan met elastische taken met behulp van T-SQL. Elastische taken het uitvoeren van een of meer Transact-SQL (T-SQL)-scripts parallel in veel databases inschakelen.

In de voorbeelden in dit artikel worden de opgeslagen procedures en de weergaven , die beschikbaar zijn in de taakdatabase , gebruikt.

In deze end-to-end zelfstudie leert u de stappen die nodig zijn om een query uit te voeren op meerdere databases:

  • Een elastische taakagent maken
  • Inloggegevens aanmaken zodat taken scripts op hun doelwitten kunnen uitvoeren
  • Definieer de doelen (servers, elastische pools, databases) waarop u de taak wilt uitvoeren
  • Maak database-gescopeerde referenties in de doeldatabases, zodat de agent kan verbinden en taken kan uitvoeren.
  • Een taak maken
  • Taakstappen toevoegen aan een taak
  • De uitvoering van een taak starten
  • Een taak bewaken

Maak de elastische jobagent.

Transact-SQL (T-SQL) kan worden gebruikt voor het maken, configureren, uitvoeren en beheren van taken.

Het maken van de elastische-taakagent wordt niet ondersteund in T-SQL. U moet dus eerst een elastische-taakagent maken met behulp van azure Portalof een elastische-taakagent maken met behulp van PowerShell.

De taakverificatie maken

De elastische taakagent moet kunnen authentiseren bij elke doelserver of doeldatabase. Zoals beschreven in Verificatie van taakagent maken, is de aanbevolen methode om Microsoft Entra-verificatie (voorheen Azure Active Directory) te gebruiken met een door de gebruiker toegewezen beheerde identiteit (UMI). Voorheen waren referenties met databasebereik de enige optie.

Microsoft Entra-verificatie gebruiken met een UMI voor taakuitvoering

Volg deze stappen om de aanbevolen methode van Microsoft Entra-verificatie (voorheen Azure Active Directory) te gebruiken voor een door de gebruiker toegewezen beheerde identiteit (UMI). De elastische taakagent maakt verbinding met de gewenste logische doelserver(s)/databases via Microsoft Entra-verificatie.

Let naast de aanmeldings- en databasegebruikers op de toevoeging van de GRANT opdrachten in het volgende script. Deze machtigingen zijn vereist voor het script dat we hebben gekozen voor deze voorbeeldtaak. Voor uw taken zijn mogelijk andere machtigingen vereist. Omdat in het voorbeeld een nieuwe tabel in de doeldatabases wordt gemaakt, heeft de databasegebruiker in elke doeldatabase de juiste machtigingen nodig om te kunnen worden uitgevoerd.

Maak in elk van de doelserver(s)/database(s) een ingesloten gebruiker die is toegewezen aan de UMI.

  • Als de elastische taak logische server- of pooldoelen heeft, moet u de ingesloten gebruiker maken die is toegewezen aan de UMI in de master-database van de logische doelserver.
  • Als u bijvoorbeeld een ingesloten databaseaanmelding wilt maken in de master-database en een gebruiker in de gebruikersdatabase, op basis van de door de gebruiker toegewezen beheerde identiteit (UMI) met de naam job-agent-UMI:
  • Als u deze T-SQL-scripts wilt uitvoeren, gebruikt u Microsoft Entra-verificatie voor uw databaseverbinding.
-- Connect to the master database of the Azure SQL logical instance of job agent
-- To run these T-SQL scripts, use Microsoft Entra authentication for your database connection.

-- 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 the master database mapped to a login
CREATE USER [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 job-agent-UMI;
GRANT CREATE TABLE TO job-agent-UMI;
  • Een ingesloten databasegebruiker maken als een aanmelding niet nodig is op de logische server:
-- 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 job-agent-UMI;
GRANT CREATE TABLE TO job-agent-UMI;

Een databasereferentie gebruiken voor taakuitvoering

Er wordt een referentie binnen het databasebereik gebruikt om verbinding te maken met uw doeldatabases voor het uitvoeren van scripts. De referentie heeft de juiste machtigingen nodig voor de databases die zijn opgegeven door de doelgroep om het script uit te voeren. Wanneer u een lid van een logische SQL-server en/of pooldoelgroep gebruikt, is het raadzaam om een referentie aan te maken om deze te vernieuwen voordat de server en/of pool worden uitgebreid bij het uitvoeren van de taak. De referentie voor databasebereik wordt gemaakt in de taakagentdatabase.

Dezelfde referentie moet worden gebruikt om Een aanmeldings- maken en Een gebruiker maken op basis van aanmelding om de machtigingen voor de aanmeldingsdatabase te verlenen op alle doeldatabases.

-- 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='<password>';  

-- 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 = '<password>';
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 = '<password>';
GO

Maak vervolgens aanmeldingen op de doelservers of ingesloten databasegebruikers in doeldatabases.

Belangrijk

De gebruikersaccount op elke doelserver/doeldatabase moet dezelfde naam hebben als de identiteit van de database-afhankelijke referentie voor de taakgebruiker, en hetzelfde wachtwoord als deze referentie.

Maak een aanmelding in de master-database van de logische SQL-server en creëer gebruikers in elke gebruikersdatabase.

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

-- 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;

Maak een ingesloten databasegebruiker als een aanmelding niet nodig is op de logische server. Normaal gesproken zou u dit alleen doen als u één database hebt om te beheren met deze elastische taakagent.

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

-- 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;

Doelservers en -databases definiëren

In het volgende voorbeeld ziet u hoe u een taak uitvoert op alle databases op een server.

Maak verbinding met de job_database en voer de volgende opdracht uit om een doelgroep en doellid toe te voegen:

-- 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';

Een afzonderlijke database uitsluiten

In het volgende voorbeeld ziet u hoe u een taak uitvoert op alle databases op een server, met uitzondering van de database met de naam MappingDB.

Wanneer u Microsoft Entra-verificatie (voorheen Azure Active Directory) gebruikt, laat u de parameter @refresh_credential_name weg. Deze parameter mag alleen worden opgegeven wanneer u referenties binnen het databasebereik gebruikt. In de volgende voorbeelden wordt de parameter @refresh_credential_name uitgeschakeld.

Maak verbinding met de job_database en voer de volgende opdracht uit:

-- 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';

Een doelgroep maken (pools)

In het volgende voorbeeld ziet u hoe u alle databases in een of meer elastische pools kunt richten.

Wanneer u Microsoft Entra-verificatie (voorheen Azure Active Directory) gebruikt, laat u de parameter @refresh_credential_name weg. Deze parameter mag alleen worden opgegeven wanneer u referenties binnen het databasebereik gebruikt. In de volgende voorbeelden wordt de parameter @refresh_credential_name uitgeschakeld.

Maak verbinding met de job_database en voer de volgende opdracht uit:

-- 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';

Een taak en stappen maken

Met T-SQL maakt u taken met behulp van door het systeem opgeslagen procedures in de takendatabase: jobs.sp_add_job en jobs.sp_add_jobstep. De syntaxis van T-SQL-opdrachten is gelijk aan de stappen die nodig zijn om SQL Agent-taken en -stappen in SQL Server te creëren.

U moet geen interne catalogusweergaven bijwerken in de taakdatabase. Als u deze catalogusweergaven handmatig wijzigt, kan de taakdatabase beschadigd raken en fouten veroorzaken. Deze weergaven zijn alleen bedoeld voor het uitvoeren van alleen-lezen queries. U kunt de opgeslagen procedures in het jobs schema gebruiken in de taakdatabase.

  • Bij het gebruik van Microsoft Entra-verificatie voor een door de gebruiker toegewezen beheerde identiteit voor verificatie bij de doelserver(s)/database(s), mag het argument @credential_name niet worden opgegeven voor sp_add_jobstep of sp_update_jobstep. Laat ook de optionele @output_credential_name en @refresh_credential_name argumenten weg.
  • Wanneer u referentiegegevens voor databasebereik gebruikt om te authenticeren bij de doelserver(s)/database(s), is de parameter @credential_name vereist voor sp_add_jobstep en sp_update_jobstep.
    • Bijvoorbeeld: @credential_name = 'job_credential'.

De volgende voorbeelden bevatten handleidingen voor het maken van taak- en taakstappen met behulp van T-SQL om algemene taken met elastische taken uit te voeren.

Voorbeelden

Nieuw schema implementeren in veel databases

In het volgende voorbeeld ziet u hoe u een nieuw schema implementeert in alle databases.

Maak verbinding met de job_database en voer de volgende opdracht uit:

-- 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';

Gegevensverzameling met behulp van ingebouwde parameters

In veel scenario's voor gegevensverzameling kan het handig zijn om enkele van deze scriptvariabelen op te nemen om de resultaten van de taak na te verwerken.

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

Als u bijvoorbeeld alle resultaten van dezelfde taakuitvoering wilt groeperen, gebruikt u $(job_execution_id) zoals wordt weergegeven in de volgende opdracht:

@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());'

Notitie

Alle tijden in elastische taken bevinden zich in de UTC-tijdzone.

Databaseprestaties monitoren

In het volgende voorbeeld wordt een nieuwe taak gemaakt om prestatiegegevens van meerdere databases te verzamelen.

De taakagent maakt standaard de uitvoertabel om geretourneerde resultaten op te slaan. Daarom moet de database-principal die is gekoppeld aan de uitvoerreferentie minimaal de volgende machtigingen hebben: CREATE TABLE voor de database, ALTER, SELECT, INSERT, DELETE in de uitvoertabel of het bijbehorende schema, en SELECT in de sys.indexes catalogusweergave.

Als u de tabel van tevoren handmatig wilt maken, moet deze de volgende eigenschappen hebben:

  1. Kolommen met de juiste naam en gegevenstypen voor de resultatenset.
  2. Extra kolom voor internal_execution_id met het gegevenstype uniqueidentifier.
  3. Een niet-geclusterde index met de naam IX_<TableName>_Internal_Execution_ID in de kolom internal_execution_id.
  4. Alle eerder vermelde machtigingen, met uitzondering van CREATE TABLE machtiging voor de database.

Maak verbinding met de taakdatabase en voer de volgende opdrachten uit:

-- 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>';

De taak uitvoeren

In het volgende voorbeeld ziet u hoe u een taak onmiddellijk start als een handmatige, ongeplande actie.

Maak verbinding met de job_database en voer de volgende opdracht uit:

-- 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;

Uitvoering van een taak plannen

In het volgende voorbeeld ziet u hoe u elke 15 minuten een taak plant voor toekomstige uitvoering.

Maak verbinding met de job_database en voer de volgende opdracht uit:

-- 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;

Taakdefinities weergeven

In het volgende voorbeeld ziet u hoe u huidige taakdefinities kunt weergeven.

Maak verbinding met de job_database en voer de volgende opdracht uit:

-- 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;

Uitvoeringsstatus van taak bewaken

In het volgende voorbeeld ziet u hoe u details van de uitvoeringsstatus voor alle taken kunt weergeven.

Maak verbinding met de job_database en voer de volgende opdracht uit:

-- 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;

Een taak annuleren

In het volgende voorbeeld ziet u hoe u een taakuitvoerings-id ophaalt en vervolgens een taakuitvoering annuleert.

Maak verbinding met de job_database en voer de volgende opdracht uit:

-- 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';

Oude taakgeschiedenis verwijderen

In het volgende voorbeeld ziet u hoe u de taakgeschiedenis vóór een specifieke datum verwijdert.

Maak verbinding met de job_database en voer de volgende opdracht uit:

-- 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

Een taak en alle bijbehorende taakgeschiedenis verwijderen

In het volgende voorbeeld ziet u hoe u een taak en alle gerelateerde taakgeschiedenis verwijdert.

Maak verbinding met de job_database en voer de volgende opdracht uit:

-- 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

Opgeslagen procedures voor takenbeheer

De volgende opgeslagen procedures bevinden zich in de takendatabase . Ze hebben een vergelijkbare naam, maar verschillen verschillend van de door het systeem opgeslagen procedures die worden gebruikt voor de SQL Server Agent-service.

Opgeslagen procedure Beschrijving
sp_add_job Voegt een nieuwe taak toe.
sp_update_job Hiermee werkt u een bestaande taak bij.
sp_delete_job Hiermee verwijdert u een bestaande taak.
sp_add_jobstep Hiermee voegt u een stap toe aan een taak.
sp_update_jobstep Hiermee werkt u een taakstap bij.
sp_delete_jobstep Hiermee verwijdert u een taakstap.
sp_start_job Begint met het uitvoeren van een taak.
sp_stop_job Hiermee stopt u de uitvoering van een taak.
sp_add_target_group Hiermee voegt u een doelgroep toe.
sp_delete_target_group Hiermee verwijdert u een doelgroep.
sp_add_target_group_member Hiermee voegt u een database of groep databases toe aan een doelgroep.
sp_delete_target_group_member Hiermee verwijdert u een doelgroeplid uit een doelgroep.
sp_purge_jobhistory Hiermee verwijdert u de geschiedenisrecords voor een taak.

Taakweergaven

De volgende weergaven zijn beschikbaar in de banendatabase.

Bekijk Beschrijving
taakuitvoeringen Toont de uitvoeringsgeschiedenis van de taak.
banen Geeft alle taken weer.
job_versions Geeft alle taakversies weer.
jobsteps Toont alle stappen in de huidige versie van elke taak.
jobstep_versions Toont alle stappen in alle versies van elke taak.
doelgroepen Toont alle doelgroepen.
doelgroep_leden Toont alle leden van alle doelgroepen.

Volgende stap

Elastische taken maken en beheren met behulp van PowerShell