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
• Vytvořte přihlašovací údaje k úlohám, aby úlohy mohly spouštět skripty na svých cílových místech.
• Definujte cíle (servery, elastické fondy, databáze), pro které chcete úlohu spustit.
• Vytvořte přihlašovací údaje s vymezením na databázi v cílových databázích, aby se agent mohl připojit a spustit úlohy.
• Vytvořit úlohu
• Přidání kroků do úlohy
• Spuštění provádění úlohy
• Monitorování úlohy

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řte ověřování úlohy

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

Použijte ověřování Microsoft Entra s UMI pro spouštění úloh

Pokud chcete použít doporučenou metodu ověřování Microsoft Entra (dříve známého jako Azure Active Directory, AAD) pro identitu přiřazenou uživatelem (UMI), postupujte podle následujících kroků. 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/databázi vytvořte uživatele, který je namapovaný na UMI.

• Pokud má elastická úloha cíle logického serveru nebo fondu, musíte vytvořit v databázi cílového logického serveru uživatele, který je obsažen a namapovaný na UMI master.
• Například k vytvoření přihlášení omezené databáze v databázi master a k vytvoření uživatele v této databázi na základě spravované identity přiřazené uživatelem (UMI) pojmenované job-agent-UMI.
• Ke spuštění těchto skriptů T-SQL použijte pro připojení k databázi ověřování Microsoft Entra.

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

• 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 job-agent-UMI;
GRANT CREATE TABLE TO job-agent-UMI;

Použití přihlašovacích údajů s omezením na databázi ke spuštění úlohy

Přihlašovací údaje v rámci databáze slouží k připojení k cílovým databázím pro vykonávání skriptů. 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 s rozsahem databáze jsou vytvořeny 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='<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

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

Důležité

Přihlašovací jméno/uživatel na každém cílovém serveru nebo databázi musí mít stejný název jako identita přihlašovacích údajů v oboru databáze pro uživatele úlohy a stejné heslo jako přihlašovací údaje v oboru databáze pro uživatele úlohy.

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

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

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

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 je parametr @refresh_credential_name zakomentován.

Připojte se k job_database a 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řit cílovou skupinu (pooly)

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. Ve následujících příkladech je parametr @refresh_credential_name okomentován.

Připojte se k job_database a 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 a syntaxe T-SQL jsou podobné těm krokům, které jsou potřebné k vytvoření úloh SQL Agenta 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 čtení. Uložené procedury ve schématu jobsmůžete použít v databázi úloh.

• Při použití ověřování Microsoft Entra pro Microsoft Entra ID nebo spravované identity přiřazené uživatelem k autentizaci na cílových serverech/databázích by neměl být zadán argument @credential_name pro sp_add_jobstep 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 job_database a 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());'

Poznámka:

Všechny časy v elastických úlohách jsou v časovém pásmu UTC.

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 s datovým typem "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>';

Spusťte úlohu

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

Připojte se k job_database a 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 ke komponentě job_database a 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 job_database a 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 job_database a 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 job_database a 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 job_database a 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

Odstranit úlohu a celou její historii

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

Připojte se k job_database a 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

Procedury úloh uložené

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.

Uložená procedura	Popis
sp_add_job	Přidá novou úlohu.
sp_update_job	Aktualizuje existující úlohu.
sp_delete_job	Odstraní existující úlohu.
sp_add_jobstep	Přidá krok do úlohy.
sp_update_jobstep	Aktualizuje krok úlohy.
sp_delete_jobstep	Odstraní krok úlohy.
sp_start_job	Spouští vykonávání úlohy.
sp_stop_job	Zastaví provádění úlohy.
sp_add_target_group	Přidá cílovou skupinu.
sp_delete_target_group	Odstraní cílovou skupinu.
sp_add_target_group_member	Přidá databázi nebo skupinu databází do cílové skupiny.
sp_delete_target_group_member	Odebere člena cílové skupiny z ní.
sp_purge_jobhistory	Odebere záznamy historie pro úlohu.

Zobrazení pracovních nabídek

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

Zobrazit	Popis
provádění úloh	Zobrazuje historii provádění úloh.
pracovní místa	Zobrazí všechny úlohy.
job_versions	Zobrazí všechny verze úloh.
kroky úlohy	Zobrazí všechny kroky v aktuální verzi každé úlohy.
jobstep_versions	Zobrazí všechny kroky ve všech verzích každé úlohy.
cílové skupiny	Zobrazí všechny cílové skupiny.
členové_cílové_skupiny	Zobrazí všechny členy všech cílových skupin.

Další krok

Vytváření a správa elastických úloh pomocí PowerShellu