Elastische taken maken en beheren met behulp van T-SQL (preview)

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. Elastisc Jobs maken het mogelijk om één of meerdere T-SQL-scripts (Transact-SQL) parallel in veel databases uit te voeren.

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

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
• Taakreferenties maken zodat taken scripts kunnen uitvoeren op de doelen ervan
• Definieer de doelen (servers, elastische pools, databases) waarop u de taak wilt uitvoeren
• Databasereferenties maken in de doeldatabases, zodat de agent verbinding maakt en taken uitvoert
• Een taak maken
• Taakstappen toevoegen aan een taak
• Uitvoering van een taak starten
• Een taak bewaken

Notitie

Elastische taken zijn in preview. Functies die momenteel in de preview-versie beschikbaar zijn, zijn beschikbaar onder aanvullende gebruiksvoorwaarden. Bekijk de juridische voorwaarden die van toepassing zijn op Azure-functies die in preview zijn. Azure SQL Database biedt previews om u de kans te geven feedback te evalueren en te delen met de productgroep over functies voordat deze algemeen beschikbaar worden.

De elastische-taakagent maken

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 Portal of een elastische-taakagent maken met behulp van PowerShell.

De taakverificatie maken

De elastische taakagent moet kunnen worden geverifieerd bij elke doelserver of -database. Zoals beschreven in verificatie van een taakagent maken, is het raadzaam om Microsoft Entra-verificatie (voorheen Azure Active Directory) te gebruiken met een door de gebruiker toegewezen beheerde identiteit (UMI). Voorheen waren referenties binnen het 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.

Naast de aanmeldings- en databasegebruikers moet u rekening houden met 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 in de master database en een gebruiker in de gebruikersdatabase wilt maken, op basis van de door de gebruiker toegewezen beheerde identiteit (UMI) met de naam 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;

• 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 jobuser;
GRANT CREATE TABLE TO jobuser;

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 te maken voor gebruik om de referentie te vernieuwen voordat u de server en/of pool bij het uitvoeren van de taak uitbreidt. De referentie voor databasebereik wordt gemaakt in de taakagentdatabase.

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

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

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

Belangrijk

De aanmelding/gebruiker op elke doelserver/database moet dezelfde naam hebben als de identiteit van de referentie in het databasebereik voor de taakgebruiker en hetzelfde wachtwoord als de referentie in het databasebereik voor de taakgebruiker.

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

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

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

Doelservers en -databases definiëren

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

Verbinding maken aan 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 voor 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 @refresh_credential_name parameter weg, die alleen moet worden opgegeven wanneer u referenties binnen het databasebereik gebruikt. In de volgende voorbeelden wordt de @refresh_credential_name parameter als commentaar weergegeven.

Verbinding maken naar de job_database volgende opdracht en voer deze 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 @refresh_credential_name parameter weg, die alleen moet worden opgegeven wanneer u referenties binnen het databasebereik gebruikt. In de volgende voorbeelden wordt de @refresh_credential_name parameter als commentaar weergegeven.

Verbinding maken naar de job_database volgende opdracht en voer deze 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 T-SQL-opdrachten zijn syntaxis die vergelijkbaar zijn met de stappen die nodig zijn voor het maken van SQL Agent-taken en taakstappen in SQL Server.

U moet geen interne catalogusweergaven in de taakdatabase bijwerken. Als u deze catalogusweergaven handmatig wijzigt, kan de taakdatabase beschadigd raken en mislukt. Deze weergaven zijn alleen-lezenquery's. U kunt de opgeslagen procedures in het jobs schema in uw taakdatabase gebruiken.

• 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 referenties voor databasebereik gebruikt om te verifiëren 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.

Verbinding maken naar de job_database volgende opdracht en voer deze 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 $(job_execution_id) u deze, 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 bewaken

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, INSERTin DELETE de uitvoertabel of het bijbehorende schema en SELECT in de catalogusweergave sys.indexes .

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 internal_execution_id kolom.
4. Alle eerder vermelde machtigingen, met uitzondering van machtigingen voor CREATE TABLE de database.

Verbinding maken naar 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.

Verbinding maken naar de job_database volgende opdracht en voer deze 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.

Verbinding maken naar de job_database volgende opdracht en voer deze 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.

Verbinding maken naar de job_database volgende opdracht en voer deze 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.

Verbinding maken naar de job_database volgende opdracht en voer deze 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.

Verbinding maken naar de job_database volgende opdracht en voer deze 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.

Verbinding maken naar de job_database volgende opdracht en voer deze 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.

Verbinding maken naar de job_database volgende opdracht en voer deze 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 taken

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

Beeld	Beschrijving
job_executions	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.
target_groups	Toont alle doelgroepen.
target_group_members	Toont alle leden van alle doelgroepen.

Volgende stap

Elastische taken maken en beheren met behulp van PowerShell (preview)