Создание эластичных заданий и управление ими с помощью T-SQL

Применимо к: База данных SQL Azure

В этой статье приведены учебники и примеры для начала работы с эластичными заданиями с помощью T-SQL. Задания обработки эластичных баз данных позволяют выполнять скрипты Transact-SQL (T-SQL) в нескольких базах данных одновременно.

В примерах этой статьи используются хранимые процедуры и представления, доступные в базе данных заданий.

В этом комплексном руководстве описаны действия, необходимые для выполнения запроса в нескольких базах данных:

Создание агента эластичного задания

Transact-SQL (T-SQL) можно использовать для создания, настройки, выполнения и управления заданиями.

Создание агента эластичного задания не поддерживается в T-SQL, поэтому сначала необходимо создать агент эластичных заданий с помощью портал Azure или создать агент эластичных заданий с помощью PowerShell.

Создание проверки подлинности задания

Агент эластичного задания должен иметь возможность проходить проверку подлинности на каждом целевом сервере или базе данных. Как описано в статье "Создание проверки подлинности агента задания", рекомендуется использовать проверку подлинности Microsoft Entra (ранее Azure Active Directory) с управляемым удостоверением, назначаемое пользователем (UMI). Ранее учетные данные в области базы данных были единственным вариантом.

Использование проверки подлинности Microsoft Entra с UMI для выполнения задания

Чтобы использовать рекомендуемый метод проверки подлинности Microsoft Entra (ранее Azure Active Directory) для управляемого удостоверения, назначаемого пользователем (UMI), выполните следующие действия. Агент эластичных заданий подключается к требуемому целевому логическому серверу или базам данных через проверку подлинности Microsoft Entra.

Помимо пользователей входа и базы данных обратите внимание на добавление GRANT команд в следующем скрипте. Эти разрешения являются обязательными для скрипта, выбранного в этом примере задания. Для заданий могут потребоваться разные разрешения. Так как в примере создается новая таблица в целевых базах данных, пользователю базы данных в каждой целевой базе данных требуются соответствующие разрешения для успешного выполнения.

В каждом целевом сервере или базе данных создайте автономного пользователя, сопоставленного с UMI.

• Если у эластичного задания есть логические серверы или целевые объекты пула, необходимо создать автономного пользователя, сопоставленного с UMI в master базе данных целевого логического сервера.
• Например, чтобы создать имя входа автономной базы данных в master базе данных и пользователя в пользовательской базе данных на основе управляемого удостоверения, назначаемого пользователем (UMI) с именем 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;

• Чтобы создать пользователя автономной базы данных, если имя входа не требуется на логическом сервере:

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

Использование учетных данных с областью базы данных для выполнения задания

Учетные данные с областью базы данных используются для подключения к целевым базам данных для выполнения скрипта. Для успешного выполнения сценария этим учетным данным необходимы соответствующие разрешения для баз данных, указанных в целевой группе. При использовании логического члена целевой группы SQL Server и (или) пула рекомендуется создать учетные данные для использования для обновления учетных данных перед расширением сервера и (или) пула во время выполнения задания. Учетные данные в области базы данных создаются в базе данных агента задания.

Для создания имени входа и создания пользователя из имени входа необходимо использовать те же учетные данные, чтобы предоставить разрешения для базы данных входа во всех целевых базах данных.

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

Затем создайте имена входа на целевых серверах или пользователей автономной базы данных в целевых базах данных.

Создайте имя входа в master базу данных логического СЕРВЕРА SQL Server и пользователей в каждой пользовательской базе данных.

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

Создайте пользователя автономной базы данных, если имя входа не требуется на логическом сервере. Обычно это можно сделать только в том случае, если у вас есть одна база данных для управления с помощью этого агента эластичных заданий.

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

Определение целевых серверов и баз данных

В приведенном ниже примере показано, как выполнить задание со всеми базами данных на сервере.

Подключитесь к следующей команде job_database , чтобы добавить целевую группу и целевой член:

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

Исключение отдельной базы данных

В следующем примере показано, как выполнить задание для всех баз данных на сервере, за исключением базы данных с именем MappingDB.

При использовании проверки подлинности Microsoft Entra (ранее — Azure Active Directory) опустите @refresh_credential_name параметр, который следует указать только при использовании учетных данных с областью базы данных. В следующих примерах @refresh_credential_name параметр закомментирован.

Подключитесь к следующей команде job_database и выполните следующую команду:

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

Создание целевой группы (пулов)

В примере ниже показано, как выбрать в качестве цели все базы данных в одном или нескольких эластичных пулах.

При использовании проверки подлинности Microsoft Entra (ранее — Azure Active Directory) опустите @refresh_credential_name параметр, который следует указать только при использовании учетных данных с областью базы данных. В следующих примерах @refresh_credential_name параметр закомментирован.

Подключитесь к следующей команде job_database и выполните следующую команду:

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

Создание задания и шагов

С помощью T-SQL создайте задания с помощью системных хранимых процедур в базе данных заданий: jobs.sp_add_job и jobs.sp_add_jobstep. Команды T-SQL похожи на шаги, необходимые для создания заданий агента SQL и шагов задания в SQL Server.

Не следует обновлять внутренние представления каталога в базе данных заданий. Изменение этих представлений каталога вручную может привести к повреждению базы данных заданий и сбою. Эти представления предназначены только для запросов только для чтения. Хранимые процедуры можно использовать в схеме jobs в базе данных заданий.

• При использовании проверки подлинности Microsoft Entra для идентификатора Microsoft Entra или управляемого удостоверения, назначаемого пользователем, для проверки подлинности на целевых серверах или базах данных @credential_name аргумент не должен быть указан sp_add_jobstep илиsp_update_jobstep. Аналогичным образом опустите необязательные аргументы @output_credential_name и @refresh_credential_name .
• При использовании учетных данных с областью базы данных для проверки подлинности на целевых серверах/базах данных требуется sp_add_jobstep параметр @credential_name.sp_update_jobstep
  • Например, @credential_name = 'job_credential'.

В следующих примерах приведены руководства по созданию заданий и заданий с помощью T-SQL для выполнения распространенных задач с эластичными заданиями.

Примеры

Развертывание новой схемы в нескольких базах данных

В приведенном ниже примере показано, как развернуть новую схему во всех базах данных.

Подключитесь к следующей команде job_database и выполните следующую команду:

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

Сбор данных с помощью встроенных параметров

Во множестве сценариев сбора данных может быть удобно добавить некоторые из приведенных переменных сценариев, чтобы упростить последующую обработку результатов задания.

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

Например, чтобы сгруппировать все результаты из одного выполнения задания вместе, используйте $(job_execution_id) , как показано в следующей команде:

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

Мониторинг производительности базы данных

Приведенный ниже пример создает задание для сбора данных производительности из нескольких баз данных.

По умолчанию агент заданий создаст выходную таблицу для хранения возвращаемых результатов. Таким образом, участник базы данных, связанный с учетными данными для входа, должен иметь как минимум следующие разрешения: CREATE TABLE для базы данных, ALTER, SELECT, INSERT, DELETE для выходной таблицы или ее схемы, а также SELECT для представления sys.indexes каталога.

Если необходимо заранее вручную создать таблицу, тогда она должна иметь такие свойства.

1. Столбцы с правильными именами и типами данных для результирующего набора.
2. Дополнительный столбец для internal_execution_id типа данных uniqueidentifier.
3. Некластеризованный индекс с именем IX_<TableName>_Internal_Execution_ID столбца internal_execution_id .
4. Все ранее перечисленные разрешения, CREATE TABLE кроме разрешений для базы данных.

Подключитесь к базе данных заданий и выполните команды, приведенные ниже.

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

Запуск задания

В следующем примере показано, как сразу же запустить задание как вручную незапланированное действие.

Подключитесь к следующей команде job_database и выполните следующую команду:

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

Планирование выполнения задания

В следующем примере показано, как запланировать задание для будущего выполнения каждые 15 минут.

Подключитесь к следующей команде job_database и выполните следующую команду:

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

Просмотр определений заданий

В следующем примере показано, как просмотреть определения текущих заданий.

Подключитесь к следующей команде job_database и выполните следующую команду:

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

Отслеживание состояния выполнения задания

В приведенном ниже примере показано, как просмотреть сведения о состоянии выполнения всех заданий.

Подключитесь к следующей команде job_database и выполните следующую команду:

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

Отмена задания

В следующем примере показано, как получить идентификатор выполнения задания, а затем отменить выполнение задания.

Подключитесь к следующей команде job_database и выполните следующую команду:

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

Удаление старого журнала заданий

В приведенном ниже примере показано, как удалить старый журнал заданий до определенной даты.

Подключитесь к следующей команде job_database и выполните следующую команду:

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

Удаление задания и его журнала заданий

В приведенном ниже примере показано, как удалить задание и связанный с ним журнал заданий.

Подключитесь к следующей команде job_database и выполните следующую команду:

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

Хранимые процедуры задания

В базе данных заданий доступны следующие хранимые процедуры. Они также называются, но отличаются от системных хранимых процедур, используемых для службы агент SQL Server.

Представления заданий

Ниже приведены представления, доступные в базе данных заданий.

Следующий шаг