Vytváření a správa elastických úloh pomocí T-SQL

Platí pro: Azure SQL Database

Tento článek obsahuje kurz a příklady, které vám pomůžou začít pracovat s elastickými úlohami pomocí T-SQL. Elastické úlohy umožňují paralelní spouštění jednoho nebo více skriptů Transact-SQL (T-SQL) napříč mnoha databázemi.

Příklady v tomto článku používají uložené procedury a zobrazení dostupná v databázi úloh.

V tomto kompletním kurzu se naučíte kroky potřebné ke spuštění dotazu napříč několika databázemi:

Vytvoření agenta elastických úloh

Transact-SQL (T-SQL) se dá použít k vytváření, konfiguraci, spouštění a správě úloh.

Vytvoření agenta elastických úloh není v jazyce T-SQL podporované, proto musíte nejprve vytvořit agenta elastických úloh pomocí webu Azure Portal nebo vytvořit agenta elastických úloh pomocí PowerShellu.

Vytvoření ověřování úloh

Agent elastických úloh musí být schopný ověřit každý cílový server nebo databázi. Jak je popsáno v ověřování agenta vytvoření úlohy, doporučeným přístupem je použít ověřování Microsoft Entra (dříve Azure Active Directory) se spravovanou identitou přiřazenou uživatelem (UMI). Dříve byly přihlašovací údaje v oboru databáze jedinou možností.

Použití ověřování Microsoft Entra s UMI pro provádění úloh

Pokud chcete použít doporučenou metodu ověřování Microsoft Entra (dříve Azure Active Directory) na spravovanou identitu přiřazenou uživatelem, postupujte takto. Agent elastických úloh se připojí k požadovaným cílovým logickým serverům/databázím prostřednictvím ověřování Microsoft Entra.

Kromě přihlašovacích údajů a uživatelů databáze si všimněte přidání GRANT příkazů v následujícím skriptu. Tato oprávnění se vyžadují pro skript, který jsme zvolili pro tuto ukázkovou úlohu. Vaše úlohy můžou vyžadovat různá oprávnění. Vzhledem k tomu, že příklad vytvoří novou tabulku v cílových databázích, uživatel databáze v každé cílové databázi potřebuje správná oprávnění k úspěšnému spuštění.

V každém cílovém serveru nebo databázích vytvořte uživatele, který je namapovaný na rozhraní UMI.

• Pokud má elastická úloha cíle logického serveru nebo fondu, musíte v databázi cílového logického serveru vytvořit uživatele, který je namapovaný na UMI master .
• Pokud například chcete v databázi vytvořit přihlášení master k databázi s omezením a uživatele v uživatelské databázi na základě spravované identity přiřazené uživatelem ( job-agent-UMIUMI):

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

• Vytvoření uživatele databáze s omezením, pokud není na logickém serveru potřeba přihlášení:

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

Použití přihlašovacích údajů v oboru databáze ke spuštění úlohy

Přihlašovací údaje v oboru databáze slouží k připojení k cílovým databázím pro spouštění skriptu. Přihlašovací údaje potřebují příslušná oprávnění k úspěšnému spuštění skriptu v databázích určených cílovou skupinou. Při použití logického serveru SQL nebo člena cílové skupiny fondu se doporučuje vytvořit přihlašovací údaje, které se použijí k aktualizaci přihlašovacích údajů před rozšířením serveru nebo fondu v době provádění úlohy. Přihlašovací údaje v oboru databáze se vytvoří v databázi agenta úloh.

Stejné přihlašovací údaje se musí použít k vytvoření přihlášení a vytvoření uživatele z přihlášení, aby se u všech cílových databází udělila oprávnění k přihlašovací databázi.

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

Pak vytvořte přihlášení na cílových serverech nebo uživatele databáze s omezením v cílových databázích.

Vytvořte v master databázi logického SQL Serveru přihlášení a uživatele v každé uživatelské databázi.

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

Pokud není na logickém serveru potřeba přihlášení, vytvořte uživatele databáze s omezením. Obvykle byste to udělali jenom v případě, že máte jednu databázi pro správu s tímto agentem elastických úloh.

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

Definování cílových serverů a databází

Následující příklad ukazuje, jak spustit úlohu pro všechny databáze na serveru.

Připojte se k job_database cílové skupině a cílovému členu spuštěním následujícího příkazu:

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

Vyloučení jednotlivé databáze

Následující příklad ukazuje, jak spustit úlohu pro všechny databáze na serveru, s výjimkou databáze s názvem MappingDB.

Pokud používáte ověřování Microsoft Entra (dříve Azure Active Directory), vynecháte @refresh_credential_name parametr, který by se měl zadat pouze při použití přihlašovacích údajů v oboru databáze. V následujících příkladech @refresh_credential_name je parametr okomentován.

Připojte se k a job_database spusťte následující příkaz:

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

Vytvoření cílové skupiny (fondů)

Následující příklad ukazuje, jak cílit na všechny databáze v jednom nebo více elastických fondech.

Pokud používáte ověřování Microsoft Entra (dříve Azure Active Directory), vynecháte @refresh_credential_name parametr, který by se měl zadat pouze při použití přihlašovacích údajů v oboru databáze. V následujících příkladech @refresh_credential_name je parametr okomentován.

Připojte se k a job_database spusťte následující příkaz:

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

Vytvoření úlohy a kroků

S T-SQL vytvořte úlohy pomocí systémových uložených procedur v databázi úloh: jobs.sp_add_job a jobs.sp_add_jobstep. Příkazy T-SQL jsou syntaxe podobné krokům potřebným k vytvoření úloh agenta SQL a kroků úloh v SQL Serveru.

V databázi úloh byste neměli aktualizovat zobrazení interního katalogu. Ruční změna těchto zobrazení katalogu může poškodit databázi úloh a způsobit selhání. Tato zobrazení jsou určená pouze pro dotazování jen pro čtení. Uložené procedury ve schématu jobs můžete použít v databázi úloh.

• Při použití ověřování Microsoft Entra pro spravované identity Přiřazené uživatelem nebo Microsoft Entra k ověření na cílových serverech/databázích by neměl být zadán sp_add_jobstep argument @credential_name pro nebo sp_update_jobstep. Podobně vynecháte volitelné @output_credential_name a @refresh_credential_name argumenty.
• Při použití přihlašovacích údajů s oborem databáze k ověření na cílových serverech/databázích je vyžadován parametr @credential_name pro sp_add_jobstep a sp_update_jobstep.
  • Například @credential_name = 'job_credential'.

Následující příklady poskytují příručky k vytvoření úlohy a kroků úloh pomocí T-SQL k provádění běžných úloh s elastickými úlohami.

Ukázky

Nasazení nového schématu do mnoha databází

Následující příklad ukazuje, jak nasadit nové schéma do všech databází.

Připojte se k a job_database spusťte následující příkaz:

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

Shromažďování dat pomocí předdefinovaných parametrů

V mnoha scénářích shromažďování dat může být užitečné zahrnout některé z těchto skriptovaných proměnných, které pomůžou zpracovat výsledky úlohy.

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

Pokud například chcete seskupit všechny výsledky ze stejného spuštění úlohy, použijte $(job_execution_id) je, jak je znázorněno v následujícím příkazu:

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

Monitorování výkonu databáze

Následující příklad vytvoří novou úlohu pro shromažďování dat o výkonu z více databází.

Ve výchozím nastavení agent úloh vytvoří výstupní tabulku pro uložení vrácených výsledků. Instanční objekt databáze přidružený k výstupním přihlašovacím údajům proto musí mít minimálně následující oprávnění: CREATE TABLE v databázi, ALTER, , SELECTINSERTve DELETE výstupní tabulce nebo jeho schématu a SELECT v zobrazení katalogu sys.indexes.

Pokud chcete tabulku předem vytvořit ručně, musí mít následující vlastnosti:

1. Sloupce se správným názvem a datovými typy pro sadu výsledků
2. Další sloupec pro internal_execution_id datový typ uniqueidentifier.
3. Neclusterovaný index pojmenovaný IX_<TableName>_Internal_Execution_ID ve sloupci internal_execution_id .
4. Všechna dříve uvedená oprávnění s výjimkou CREATE TABLE oprávnění k databázi.

Připojte se k databázi úloh a spusťte následující příkazy:

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

Spuštění úlohy

Následující příklad ukazuje, jak spustit úlohu okamžitě jako ruční neplánovanou akci.

Připojte se k a job_database spusťte následující příkaz:

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

Naplánování provádění úlohy

Následující příklad ukazuje, jak naplánovat úlohu pro budoucí spuštění pravidelně každých 15 minut.

Připojte se k a job_database spusťte následující příkaz:

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

Zobrazení definic úloh

Následující příklad ukazuje, jak zobrazit aktuální definice úloh.

Připojte se k a job_database spusťte následující příkaz:

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

Monitorování stavu spuštění úlohy

Následující příklad ukazuje, jak zobrazit podrobnosti o stavu spuštění pro všechny úlohy.

Připojte se k a job_database spusťte následující příkaz:

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

Zrušení úlohy

Následující příklad ukazuje, jak načíst ID spuštění úlohy a pak zrušit provádění úlohy.

Připojte se k a job_database spusťte následující příkaz:

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

Odstranění staré historie úloh

Následující příklad ukazuje, jak odstranit historii úloh před konkrétním datem.

Připojte se k a job_database spusťte následující příkaz:

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

Odstranění úlohy a historie všech jejích úloh

Následující příklad ukazuje, jak odstranit úlohu a všechny související historie úloh.

Připojte se k a job_database spusťte následující příkaz:

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

Uložené procedury úloh

Následující uložené procedury jsou v databázi úloh. Jsou podobně pojmenované, ale liší se od systémových uložených procedur používaných pro službu agenta SQL Serveru.

Zobrazení úloh

V databázi úloh jsou k dispozici následující zobrazení.

Další krok