Tworzenie zadań elastycznych i zarządzanie nimi przy użyciu języka T-SQL

Dotyczy: Azure SQL Database

Ten artykuł zawiera samouczek i przykłady umożliwiające rozpoczęcie pracy z zadaniami elastycznymi przy użyciu języka T-SQL. Zadania elastyczne umożliwiają uruchamianie jednego lub większej liczby skryptów języka Transact-SQL (T-SQL) równolegle w wielu bazach danych.

Przykłady w tym artykule korzystają z procedur składowanych i widoków dostępnych w bazie danych zadań.

Z tego kompleksowego samouczka dowiesz się, jak wykonać zapytanie w wielu bazach danych:

• Tworzenie agenta zadań elastycznych
• Tworzenie poświadczeń zadań, aby umożliwić wykonywanie przez zadania skryptów na ich elementach docelowych
• Definiowanie obiektów docelowych (serwerów, elastycznych pul, baz danych), dla których chcesz uruchomić zadanie
• Tworzenie poświadczeń o zakresie bazy danych w docelowych bazach danych, aby agent łączył się i wykonywał zadania
• Tworzenie zadania
• Dodawanie kroków zadania do zadania
• Rozpoczynanie wykonywania zadania
• Monitorowanie zadania

Tworzenie agenta zadań elastycznych

Język Transact-SQL (T-SQL) może służyć do tworzenia, konfigurowania, wykonywania i zarządzania zadaniami.

Tworzenie agenta zadań elastycznych nie jest obsługiwane w języku T-SQL, dlatego najpierw należy utworzyć agenta zadań elastycznych przy użyciu witryny Azure Portal lub utworzyć agenta zadań elastycznych przy użyciu programu PowerShell.

Tworzenie uwierzytelniania zadania

Agent zadań elastycznych musi mieć możliwość uwierzytelniania na każdym serwerze docelowym lub bazie danych. Zgodnie z opisem w artykule Tworzenie uwierzytelniania agenta zadań zalecane jest użycie uwierzytelniania firmy Microsoft Entra (dawniej Azure Active Directory) z tożsamością zarządzaną przypisaną przez użytkownika (UMI). Wcześniej poświadczenia o zakresie bazy danych były jedyną opcją.

Używanie uwierzytelniania firmy Microsoft Entra z interfejsem UMI na potrzeby wykonywania zadania

Aby użyć zalecanej metody uwierzytelniania firmy Microsoft Entra (dawniej Azure Active Directory) do tożsamości zarządzanej przypisanej przez użytkownika, wykonaj następujące kroki. Agent zadań elastycznych łączy się z żądanymi docelowymi serwerami logicznymi/bazami danych za pośrednictwem uwierzytelniania firmy Microsoft Entra.

Oprócz użytkowników logowania i bazy danych zanotuj dodanie GRANT poleceń w poniższym skry skryptzie. Te uprawnienia są wymagane dla skryptu, który wybrano dla tego przykładowego zadania. Zadania mogą wymagać różnych uprawnień. Ponieważ w przykładzie tworzona jest nowa tabela w docelowych bazach danych, użytkownik bazy danych w każdej docelowej bazie danych potrzebuje odpowiednich uprawnień do pomyślnego uruchomienia.

W każdym z serwerów docelowych/baz danych utwórz zawartego użytkownika zamapowanego na interfejs użytkownika.

• Jeśli zadanie elastyczne ma obiekty docelowe serwera logicznego lub puli, należy utworzyć zawartego użytkownika zamapowanego na interfejs użytkownika w master bazie danych docelowego serwera logicznego.
• Na przykład aby utworzyć zawarte dane logowania bazy danych w master bazie danych i użytkownika w bazie danych użytkownika, na podstawie tożsamości zarządzanej przypisanej przez użytkownika (UMI) o nazwie 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;

• Aby utworzyć użytkownika zawartej bazy danych, jeśli logowanie nie jest wymagane na serwerze logicznym:

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

Używanie poświadczeń o zakresie bazy danych na potrzeby wykonywania zadania

Poświadczenie o zakresie bazy danych służy do nawiązywania połączenia z docelowymi bazami danych na potrzeby wykonywania skryptu. Poświadczenie wymaga odpowiednich uprawnień w bazach danych określonych przez grupę docelową, aby pomyślnie wykonać skrypt. W przypadku korzystania z logicznego serwera SQL i /lub elementu docelowego grupy puli zaleca się utworzenie poświadczenia do użycia w celu odświeżenia poświadczeń przed rozszerzeniem serwera i/lub puli w czasie wykonywania zadania. Poświadczenia o zakresie bazy danych są tworzone w bazie danych agenta zadań.

To samo poświadczenie musi służyć do tworzenia nazwy logowania i tworzenia użytkownika z logowania w celu udzielenia uprawnień bazy danych logowania we wszystkich docelowych bazach danych.

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

Następnie utwórz identyfikatory logowania na serwerach docelowych lub zawartych użytkowników bazy danych w docelowych bazach danych.

Ważne

Nazwa logowania/użytkownik na każdym serwerze docelowym/bazie danych musi mieć taką samą nazwę jak tożsamość poświadczeń o zakresie bazy danych dla użytkownika zadania oraz to samo hasło co poświadczenia w zakresie bazy danych dla użytkownika zadania.

Utwórz nazwę logowania w master bazie danych logicznego serwera SQL i użytkowników w każdej bazie danych użytkowników.

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

Utwórz użytkownika zawartej bazy danych, jeśli logowanie nie jest wymagane na serwerze logicznym. Zazwyczaj można to zrobić tylko wtedy, gdy masz pojedynczą bazę danych do zarządzania za pomocą tego agenta zadań elastycznych.

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

Definiowanie serwerów docelowych i baz danych

W poniższym przykładzie pokazano, jak wykonać zadanie dla wszystkich baz danych na serwerze.

Połącz się z elementem job_database i uruchom następujące polecenie, aby dodać grupę docelową i element docelowy:

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

Wykluczanie pojedynczej bazy danych

W poniższym przykładzie pokazano, jak wykonać zadanie względem wszystkich baz danych na serwerze, z wyjątkiem bazy danych o nazwie MappingDB.

W przypadku korzystania z uwierzytelniania entra firmy Microsoft (dawniej Azure Active Directory) pomiń @refresh_credential_name parametr , który powinien być udostępniany tylko w przypadku używania poświadczeń o zakresie bazy danych. W poniższych przykładach @refresh_credential_name parametr jest komentowany.

Połącz się z elementem job_database i uruchom następujące polecenie:

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

Tworzenie grupy docelowej (pul)

W poniższym przykładzie pokazano, jak kierować wszystkie bazy danych do co najmniej jednej elastycznej pul.

W przypadku korzystania z uwierzytelniania entra firmy Microsoft (dawniej Azure Active Directory) pomiń @refresh_credential_name parametr , który powinien być udostępniany tylko w przypadku używania poświadczeń o zakresie bazy danych. W poniższych przykładach @refresh_credential_name parametr jest komentowany.

Połącz się z elementem job_database i uruchom następujące polecenie:

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

Tworzenie zadania i kroków

W języku T-SQL utwórz zadania przy użyciu systemowych procedur składowanych w bazie danych zadań: jobs.sp_add_job i jobs.sp_add_jobstep. Polecenia języka T-SQL są składnią podobną do kroków wymaganych do utworzenia zadań i zadań agenta SQL w programie SQL Server.

Nie należy aktualizować wewnętrznych widoków wykazu w bazie danych zadań. Ręczne zmienianie tych widoków wykazu może uszkodzić bazę danych zadań i spowodować niepowodzenie. Te widoki są przeznaczone tylko do wykonywania zapytań tylko do odczytu. Procedury składowane można używać w schemacie jobs w bazie danych zadań.

• W przypadku korzystania z uwierzytelniania entra firmy Microsoft dla identyfikatora entra firmy Microsoft lub przypisanej przez użytkownika tożsamości zarządzanej do uwierzytelniania na serwerach docelowych/bazach danych nie należy podawać argumentu @credential_name dla sp_add_jobstep lub sp_update_jobstep. Podobnie pomiń opcjonalne argumenty @output_credential_name i @refresh_credential_name .
• W przypadku używania poświadczeń o zakresie bazy danych do uwierzytelniania na serwerach docelowych/bazach danych parametr @credential_name jest wymagany dla sp_add_jobstep parametrów i sp_update_jobstep.
  • Na przykład @credential_name = 'job_credential'.

W poniższych przykładach przedstawiono przewodniki umożliwiające tworzenie kroków zadań i zadań przy użyciu języka T-SQL w celu wykonywania typowych zadań z zadaniami elastycznymi.

Przykłady

Wdrażanie nowego schematu w wielu bazach danych

W poniższym przykładzie pokazano, jak wdrożyć nowy schemat we wszystkich bazach danych.

Połącz się z elementem job_database i uruchom następujące polecenie:

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

Zbieranie danych przy użyciu wbudowanych parametrów

W wielu scenariuszach zbierania danych warto uwzględnić niektóre z tych zmiennych skryptowych, aby ułatwić przetwarzanie wyników zadania po przetworzeniu.

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

Aby na przykład zgrupować wszystkie wyniki z tego samego wykonania zadania, użyj polecenia $(job_execution_id) , jak pokazano w poniższym poleceniu:

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

Uwaga

Wszystkie czasy zadań elastycznych znajdują się w strefie czasowej UTC.

Monitorowanie wydajności bazy danych

Poniższy przykład tworzy nowe zadanie do zbierania danych wydajności z wielu baz danych.

Domyślnie agent zadania utworzy tabelę danych wyjściowych do przechowywania zwróconych wyników. W związku z tym podmiot zabezpieczeń bazy danych skojarzony z poświadczeniami wyjściowymi musi mieć co najmniej następujące uprawnienia: CREATE TABLE w bazie danych, ALTER, SELECT, INSERTDELETE w tabeli wyjściowej lub jej schemacie oraz SELECT w widoku katalogu sys.indexes.

Jeśli chcesz ręcznie utworzyć tabelę z wyprzedzeniem, musi ona mieć następujące właściwości:

1. Kolumny z poprawną nazwą i typami danych dla zestawu wyników.
2. Dodatkowa kolumna dla internal_execution_id parametru z typem danych uniqueidentifier.
3. Indeks nieklastrowany o nazwie IX_<TableName>_Internal_Execution_ID w kolumnie internal_execution_id .
4. Wszystkie wcześniej wymienione uprawnienia z wyjątkiem CREATE TABLE uprawnień do bazy danych.

Połącz się z bazą danych zadań i uruchom następujące polecenia:

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

Uruchamianie zadania

W poniższym przykładzie pokazano, jak natychmiast uruchomić zadanie jako ręczną, nieplanowaną akcję.

Połącz się z elementem job_database i uruchom następujące polecenie:

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

Planowanie wykonywania zadania

W poniższym przykładzie pokazano, jak zaplanować zadanie na potrzeby przyszłego wykonywania cyklicznie co 15 minut.

Połącz się z elementem job_database i uruchom następujące polecenie:

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

Wyświetlanie definicji zadań

W poniższym przykładzie pokazano, jak wyświetlić bieżące definicje zadań.

Połącz się z elementem job_database i uruchom następujące polecenie:

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

Monitorowanie stanu wykonywania zadania

W poniższym przykładzie pokazano, jak wyświetlić szczegóły stanu wykonania dla wszystkich zadań.

Połącz się z elementem job_database i uruchom następujące polecenie:

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

Anulowanie zadania

W poniższym przykładzie pokazano, jak pobrać identyfikator wykonania zadania, a następnie anulować wykonanie zadania.

Połącz się z elementem job_database i uruchom następujące polecenie:

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

Usuwanie starej historii zadań

W poniższym przykładzie pokazano, jak usunąć historię zadań przed określoną datą.

Połącz się z elementem job_database i uruchom następujące polecenie:

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

Usuwanie zadania i całej jego historii zadań

W poniższym przykładzie pokazano, jak usunąć zadanie i całą powiązaną historię zadań.

Połącz się z elementem job_database i uruchom następujące polecenie:

--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 składowane zadania

Poniższe procedury składowane znajdują się w bazie danych zadań. Są one podobnie nazwane, ale różnią się inaczej niż systemowe procedury składowane używane dla usługi SQL Server Agent.

Procedura składowana	opis
sp_add_job	Dodaje nowe zadanie.
sp_update_job	Aktualizuje istniejące zadanie.
sp_delete_job	Usuwa istniejące zadanie.
sp_add_jobstep	Dodaje krok do zadania.
sp_update_jobstep	Aktualizuje krok zadania.
sp_delete_jobstep	Usuwa krok zadania.
sp_start_job	Rozpoczyna wykonywanie zadania.
sp_stop_job	Zatrzymuje wykonywanie zadania.
sp_add_target_group	Dodaje grupę docelową.
sp_delete_target_group	Usuwa grupę docelową.
sp_add_target_group_member	Dodaje bazę danych lub grupę baz danych do grupy docelowej.
sp_delete_target_group_member	Usuwa członka grupy docelowej z grupy docelowej.
sp_purge_jobhistory	Usuwa rekordy historii zadania.

Widoki zadań

Następujące widoki są dostępne w bazie danych zadań.

Wyświetlanie	opis
job_executions	Pokazuje historię wykonywania zadania.
Zadania	Pokazuje wszystkie zadania.
job_versions	Pokazuje wszystkie wersje zadań.
jobsteps	Przedstawia wszystkie kroki w bieżącej wersji każdego zadania.
jobstep_versions	Przedstawia wszystkie kroki we wszystkich wersjach każdego zadania.
target_groups	Pokazuje wszystkie grupy docelowe.
target_group_members	Przedstawia wszystkich członków wszystkich grup docelowych.

Następny krok

Tworzenie zadań elastycznych i zarządzanie nimi przy użyciu programu PowerShell