Criar e gerenciar trabalhos elásticos usando o T-SQL

Aplica-se a: Banco de Dados SQL do Azure

Este artigo fornece um tutorial e exemplos para você começar a trabalhar com trabalhos elásticos usando T-SQL. Os trabalhos elásticos habilitam a execução de um ou mais scripts T-SQL (Transact-SQL) em paralelo entre vários bancos de dados.

Os exemplos neste artigo usam os procedimentos armazenados e as exibições disponíveis no banco de dados de trabalhos.

Neste tutorial completo, você aprenderá as etapas necessárias para executar uma consulta em vários bancos de dados:

Criar o agente de trabalho elástico

O T-SQL (Transact-SQL) pode ser usado para criar, configurar, executar e gerenciar trabalhos.

Como o agente de trabalho elástico não é compatível com T-SQL, você precisa primeiro criar um agente de trabalho elástico usando o portal do Azure ou o criar um agente de trabalho elástico usando o PowerShell.

Criar a autenticação do trabalho

O agente de trabalho elástico deve ser capaz de se autenticar em cada servidor ou banco de dados de destino. Conforme abordado em Criar autenticação de agente de trabalho, a abordagem recomendada é usar a autenticação do Microsoft Entra (o antigo Azure Active Directory) com uma identidade gerenciada atribuída pelo usuário (UMI). Anteriormente, as credenciais no escopo do banco de dados eram a única opção.

Usar a autenticação do Microsoft Entra com uma UMI para execução de trabalhos

Para usar o método recomendado de autenticação do Microsoft Entra (o antigo Azure Active Directory) para uma identidade gerenciada atribuída pelo usuário (UMI), siga estas etapas. O agente de trabalho elástico se conecta aos servidores lógicos/bancos de dados de destino desejados por meio da autenticação do Microsoft Entra.

Além dos usuários de logon e banco de dados, observe a adição dos comandos GRANT no script a seguir. Essas permissões são necessárias para o script que escolhemos como exemplo para o trabalho. Seus trabalhos podem exigir permissões diferentes. O exemplo cria uma nova tabela nos bancos de dados de destino, o usuário de banco de dados de cada banco de dados de destino precisa ter as permissões apropriadas para ser executado com êxito.

Em cada um dos servidores/bancos de dados de destino, crie um usuário independente mapeado para a UMI.

• Se o trabalho elástico tiver destinos de pool ou servidor lógico, você deverá criar o usuário independente mapeado para a UMI no banco de dados master do servidor lógico de destino.
• Por exemplo, para criar um logon de banco de dados independente no banco de dados master e um usuário no banco de dados de usuário com base na identidade gerenciada atribuída pelo usuário (UMI) denominada 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;

• Para criar um usuário de banco de dados independente se um logon não for necessário no servidor lógico:

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

Usar uma credencial no escopo de banco de dados para a execução de trabalhos

Uma credencial no escopo do banco de dados é usada para conectar-se aos seus bancos de dados de destino para a execução do script. A credencial precisa de permissões apropriadas, nos bancos de dados especificados pelo grupo de destino, para executar o script com êxito. Ao usar um servidor lógico SQL e/ou um membro do grupo de destino do pool, é altamente recomendado criar uma credencial a ser usada para atualizar a credencial antes de expansão do servidor e/ou do pool no momento da execução do trabalho. A credencial no escopo do banco de dados é criada no banco de dados do agente de trabalho.

A mesma credencial precisa ser usada para Criar um logon e Criar um usuário do logon para conceder as permissões do banco de dados de logon em todos os bancos de dados de destino.

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

Em seguida, crie logons nos servidores de destino ou usuários de bancos de dados independentes em bancos de dados de destino.

Crie um logon no banco de dados master do SQL Server lógico e usuários em cada banco de dados de usuário.

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

Crie um usuário de banco de dados independente se um logon não for necessário no servidor lógico. Normalmente, você só faria isso se tivesse um único banco de dados para gerenciar com esse agente de trabalho elástico.

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

Definir servidores e bancos de dados de destino

O exemplo a seguir mostra como executar um trabalho em todos os bancos de dados em um servidor.

Conecte-se a job_database e execute o seguinte comando para adicionar um grupo de destino e um membro de destino:

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

Excluir um banco de dados individual

O exemplo a seguir mostra como executar um trabalho em todos os bancos de dados em um servidor, com exceção do banco de dados denominado MappingDB.

Ao usar a autenticação do Microsoft Entra (o antigo Azure Active Directory), omita o parâmetro @refresh_credential_name, que só deve ser fornecido ao usar credenciais no escopo do banco de dados. Nos exemplos a seguir, o parâmetro @refresh_credential_name tem as barras de comentário removidas.

Conecte-se ao job_database e execute o seguinte comando:

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

Criar um grupo de destino (pools)

O exemplo a seguir mostra como direcionar a todos os bancos de dados em um ou mais pools elásticos.

Ao usar a autenticação do Microsoft Entra (o antigo Azure Active Directory), omita o parâmetro @refresh_credential_name, que só deve ser fornecido ao usar credenciais no escopo do banco de dados. Nos exemplos a seguir, o parâmetro @refresh_credential_name tem as barras de comentário removidas.

Conecte-se ao job_database e execute o seguinte comando:

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

Criar um trabalho e etapas

Com o T-SQL, crie trabalhos usando procedimentos armazenados do sistema no banco de dados de trabalhos: jobs.sp_add_job e jobs.sp_add_jobstep. Os comandos T-SQL e a sintaxe são semelhantes às etapas necessárias para criar trabalhos do SQL Agent e às etapas de trabalho no SQL Server.

Você não deve atualizar exibições do catálogo interno no banco de dados de trabalhos. A alteração manual dessas exibições do catálogo pode corromper o banco de dados de trabalhos e causar falhas. Esses modos de exibição são apenas para consulta somente leitura. Você pode usar os procedimentos armazenados no esquema jobs em seu banco de dados de trabalhos.

• Ao usar a autenticação do Microsoft Entra para uma ID do Microsoft Entra ou identidade gerenciada atribuída pelo usuário para autenticação em servidores/bancos de dados de destino, o argumento @credential_name não deve ser fornecido para sp_add_jobstep ou sp_update_jobstep. Da mesma maneira, omita os argumentos opcionais @output_credential_name e @refresh_credential_name.
• Ao usar credenciais no escopo de banco de dados para autenticação em servidores/bancos de dados de destino, o parâmetro @credential_name é necessário para sp_add_jobstep e sp_update_jobstep.
  • Por exemplo, @credential_name = 'job_credential'.

Os exemplos a seguir fornecem guias para criar trabalhos e etapas de trabalho usando T-SQL, para realizar tarefas comuns com trabalhos elásticos.

Amostras

Implantar o novo esquema para vários bancos de dados

O exemplo a seguir mostra como implantar o novo esquema em todos os bancos de dados.

Conecte-se ao job_database e execute o seguinte comando:

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

Coleta de dados usando parâmetros internos

Em muitos cenários de coleta de dados, pode ser útil incluir algumas dessas variáveis de script para ajudar no pós-processamento dos resultados do trabalho.

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

Por exemplo, para agrupar todos os resultados da mesma execução de trabalho, use $(job_execution_id), como mostra o comando a seguir:

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

Como monitorar o desempenho do banco de dados

O exemplo a seguir cria um novo trabalho para coletar dados de desempenho de vários bancos de dados.

Por padrão, o agente de trabalho criará a tabela de saída para armazenar resultados retornados. Portanto, a entidade de segurança do banco de dados associada à credencial de saída deve ter, no mínimo, as seguintes permissões: CREATE TABLE no banco de dados, ALTER, SELECT, INSERT e DELETE na tabela de saída ou em seu esquema e SELECT na exibição de catálogo do sys.indexes.

Se você quiser criar manualmente a tabela com antecedência, ela precisará ter as seguintes propriedades:

1. Colunas com o nome correto e tipos de dados para o conjunto de resultados.
2. Coluna adicional para internal_execution_id com o tipo de dados de uniqueidentifier.
3. Um índice não clusterizado denominado IX_<TableName>_Internal_Execution_ID na coluna internal_execution_id.
4. Todas as permissões listadas anteriormente, exceto a permissão CREATE TABLE no banco de dados.

Conecte-se ao banco de dados de trabalhos e execute os seguintes comandos:

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

Executar o trabalho

O exemplo a seguir mostra como iniciar um trabalho imediatamente, como uma ação manual não planejada.

Conecte-se ao job_database e execute o seguinte comando:

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

Agendar a execução de um trabalho

O exemplo a seguir mostra como agendar um trabalho para execução futura, em uma base recorrente a cada 15 minutos.

Conecte-se ao job_database e execute o seguinte comando:

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

Exibir definições de trabalho

O exemplo a seguir mostra como exibir as definições de trabalho atuais.

Conecte-se ao job_database e execute o seguinte comando:

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

Monitorar o status de execução do trabalho

O exemplo a seguir mostra como exibir detalhes do status de execução de todos os trabalhos.

Conecte-se ao job_database e execute o seguinte comando:

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

Cancelar um trabalho

O exemplo a seguir mostra como recuperar uma ID de execução de trabalho e, em seguida, cancelar a execução de trabalho.

Conecte-se ao job_database e execute o seguinte comando:

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

Excluir o histórico de trabalhos antigo

O exemplo a seguir mostra como excluir o histórico de trabalhos antes de uma data específica.

Conecte-se ao job_database e execute o seguinte comando:

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

Excluir um trabalho e todo o seu histórico de trabalhos

O exemplo a seguir mostra como excluir um trabalho e todo o histórico de trabalhos relacionado.

Conecte-se ao job_database e execute o seguinte comando:

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

Procedimentos armazenados de trabalho

Os seguintes procedimentos armazenados ficam no banco de dados de trabalhos. Eles têm nomes semelhantes, mas distintamente diferentes dos procedimentos armazenados do sistema usados para o serviço do SQL Server Agent.

Exibições de trabalho

As seguintes exibições estão disponíveis no banco de dados de trabalhos.

Próxima etapa