Создание эластичных заданий и управление ими с помощью 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:
• Чтобы запустить эти скрипты T-SQL, используйте проверку подлинности 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;

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

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

Используйте учетные данные, привязанные к базе данных, для выполнения задания.

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

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

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

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

Внимание

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

Создайте имя входа в master базе данных логического сервера SQL и пользователей в каждой пользовательской базе данных.

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

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

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

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

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

Подключитесь к 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 Server.

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

• При использовании проверки подлинности Microsoft Entra для идентификатора Microsoft Entra или управляемого удостоверения, назначаемого пользователем, при проверке подлинности на целевых серверах или в базах данных аргумент @credential_name не должен быть указан для sp_add_jobstep или sp_update_jobstep. Аналогичным образом опустите необязательные аргументы @output_credential_name и @refresh_credential_name .
• При использовании учетных данных с областью действия базы данных для аутентификации на целевых серверах/базах данных требуется параметр @credential_name для sp_add_jobstep и 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());'

Примечание.

Все времена в эластичных работах указаны по часовому поясу UTC.

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

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

По умолчанию агент заданий создаст выходную таблицу для хранения возвращаемых результатов. Таким образом, участник базы данных, связанный с учетными данными для входа, должен иметь как минимум следующие разрешения: 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.

Хранимая процедура	Описание
sp_add_job	Добавляет новое задание.
sp_update_job	Обновляет существующее задание.
sp_delete_job	Удаляет существующее задание.
sp_add_jobstep	Добавляет шаг в задание.
sp_update_jobstep	Обновляет шаг задания.
sp_delete_jobstep	Удаляет шаг задания.
sp_start_job	Запускает выполнение задания.
sp_stop_job	Останавливает выполнение задания.
sp_add_target_group	Добавляет целевую группу.
sp_delete_target_group	Удаляет целевую группу.
sp_add_target_group_member	Добавляет базу данных или несколько баз данных в целевую группу.
sp_delete_target_group_member	Удаляет участника из целевой группы.
sp_purge_jobhistory	Удаляет записи журнала для задания.

Просмотры вакансий

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

Вид	Описание
выполнения задач	Отображает журнал выполнения задания.
вакансии	Отображает все работы.
job_versions	Отображает все версии задания.
jobsteps	Отображает все шаги в текущей версии каждого задания.
jobstep_versions	Отображает все шаги во всех версиях каждого задания.
целевые_группы	Отображает все целевые группы.
члены_целевой_группы	Отображает всех участников всех целевых групп.

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

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