Criar e gerenciar trabalhos elásticos usando T-SQL (visualização)

Aplica-se a:Banco de Dados SQL do Azure

Este artigo fornece um tutorial e exemplos para começar a trabalhar com trabalhos elásticos usando T-SQL. As tarefas elásticas permitem a execução de um ou mais scripts Transact-SQL (T-SQL) em paralelo em muitas bases de dados.

Os exemplos neste artigo usam os procedimentos armazenados e 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 um agente de trabalho elástico
• Criar credenciais de tarefa para que as tarefas possam executar scripts nos respetivos destinos
• Defina os destinos (servidores, pools elásticos, bancos de dados) contra os quais deseja executar o trabalho
• Criar credenciais de escopo de banco de dados nos bancos de dados de destino para que o agente se conecte e execute trabalhos
• Criar um trabalho
• Adicionar passos de tarefa a uma tarefa
• Iniciar a execução de uma tarefa
• Monitorizar uma tarefa

Nota

Os trabalhos elásticos estão em pré-visualização. As funcionalidades atualmente em pré-visualização estão disponíveis em termos de utilização suplementares, rever os termos legais que se aplicam às funcionalidades do Azure que estão em pré-visualização. A Base de Dados SQL do Azure fornece pré-visualizações para lhe dar a oportunidade de avaliar e partilhar comentários com o grupo de produtos sobre funcionalidades antes de estas se tornarem disponíveis ao público (GA).

Criar o agente de trabalho elástico

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

Não há suporte para a criação do agente de trabalho elástico no T-SQL, portanto, você deve primeiro criar um agente de trabalho elástico usando o portal do Azure ou criar um agente de trabalho elástico usando o PowerShell.

Criar a autenticação de trabalho

O agente de trabalho elástico deve ser capaz de 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 (anteriormente Azure Ative Directory) com uma identidade gerenciada atribuída pelo usuário (UMI). Anteriormente, as credenciais com escopo de banco de dados eram a única opção.

Usar a autenticação do Microsoft Entra com um UMI para execução de tarefas

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

Além dos usuários de login e banco de dados, observe a GRANT adição dos comandos no script a seguir. Estas permissões são necessárias para o script que escolhemos para esta tarefa de exemplo. Seus trabalhos podem exigir permissões diferentes. Como o exemplo cria uma nova tabela nos bancos de dados de destino, o usuário do banco de dados em cada banco de dados de destino precisa das permissões adequadas para ser executado com êxito.

Em cada servidor(es)/banco de dados de destino, crie um usuário contido mapeado para a UMI.

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

Uma credencial com escopo de banco de dados é usada para se conectar aos bancos de dados de destino para execução de 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 SQL lógico e/ou membro do grupo de destino do pool, é recomendável criar uma credencial para uso para atualizar a credencial antes da expansão do servidor e/ou pool no momento da execução do trabalho. A credencial com escopo de banco de dados é criada no banco de dados do agente de trabalho.

A mesma credencial deve ser usada para Criar um Login e Criar um Usuário a partir do Login para conceder as Permissões do Banco de Dados de Login 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='<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

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

Importante

O login/usuário em cada servidor/banco de dados de destino deve ter o mesmo nome que a identidade da credencial com escopo de banco de dados para o usuário de trabalho e a mesma senha que a credencial de escopo de banco de dados para o usuário de trabalho.

Crie um logon no master banco de dados do servidor SQL 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='<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;

Crie um usuário de banco de dados contido se um login 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='<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;

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 ao job_database comando e execute o seguinte 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, exceto para o banco de dados chamado MappingDB.

Ao usar a autenticação do Microsoft Entra (anteriormente Azure Ative Directory), omita o @refresh_credential_name parâmetro, que só deve ser fornecido ao usar credenciais de escopo de banco de dados. Nos exemplos a seguir, o @refresh_credential_name parâmetro é comentado.

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-alvo (pools)

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

Ao usar a autenticação do Microsoft Entra (anteriormente Azure Ative Directory), omita o @refresh_credential_name parâmetro, que só deve ser fornecido ao usar credenciais de escopo de banco de dados. Nos exemplos a seguir, o @refresh_credential_name parâmetro é comentado.

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 são sintaxe são semelhantes às etapas necessárias para criar trabalhos do SQL Agent e etapas de trabalho no SQL Server.

Você não deve atualizar as exibições de catálogo interno no banco de dados de tarefas. Alterar manualmente essas exibições de catálogo pode corromper o banco de dados de tarefas e causar falhas. Esses modos de exibição são somente para consulta somente leitura. Você pode usar os procedimentos armazenados no jobs esquema 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 autenticar no(s) servidor(es)/banco de dados de destino, o argumento @credential_name não deve ser fornecido para sp_add_jobstep ou sp_update_jobstep. Da mesma forma, omita os argumentos opcionais @output_credential_name e @refresh_credential_name .
• Ao usar credenciais de escopo de banco de dados para autenticar no(s) servidor(es)/banco(s) 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 tarefas e etapas de trabalho usando T-SQL, para realizar tarefas comuns com trabalhos elásticos.

Amostras

Implantar novo esquema em muitos bancos de dados

O exemplo a seguir mostra como implantar um 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';

Recolha de dados utilizando parâmetros incorporados

Em muitos cenários de coleta de dados, pode ser útil incluir algumas dessas variáveis de script para ajudar a pós-processar os 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 mostrado no 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());'

Nota

Todas as horas em trabalhos elásticos estão no fuso horário UTC.

Monitorizar o desempenho da base 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 os resultados retornados. Portanto, a entidade de banco de dados associada à credencial de saída deve, no mínimo, ter as seguintes permissões: CREATE TABLE no banco de dados, , , INSERT, SELECTALTERna DELETE tabela de saída ou em seu esquema e SELECT na exibição do catálogo 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 os tipos de dados para o conjunto de resultados.
2. Coluna adicional para internal_execution_id com o tipo de dados uniqueidentifier.
3. Um índice não clusterizado nomeado IX_<TableName>_Internal_Execution_ID na internal_execution_id coluna.
4. Todas as permissões listadas anteriormente, exceto a CREATE TABLE permissão no banco de dados.

Conecte-se ao banco de dados de tarefas 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 a tarefa

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 de forma 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 de status de execução para 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 uma 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 histórico de trabalho 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 trabalho 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 procedimentos armazenados a seguir estão no banco de dados de trabalhos. Eles têm nomes semelhantes, mas são distintamente diferentes dos procedimentos armazenados do sistema usados para o serviço SQL Server Agent.

Procedimento armazenado	Description
sp_add_job	Adiciona um novo trabalho.
sp_update_job	Atualiza um trabalho existente.
sp_delete_job	Exclui um trabalho existente.
sp_add_jobstep	Adiciona uma etapa a um trabalho.
sp_update_jobstep	Atualiza uma etapa do trabalho.
sp_delete_jobstep	Exclui uma etapa de trabalho.
sp_start_job	Começa a executar um trabalho.
sp_stop_job	Interrompe a execução de um trabalho.
sp_add_target_group	Adiciona um grupo-alvo.
sp_delete_target_group	Exclui um grupo-alvo.
sp_add_target_group_member	Adiciona um banco de dados ou grupo de bancos de dados a um grupo-alvo.
sp_delete_target_group_member	Remove um membro do grupo-alvo de um grupo-alvo.
sp_purge_jobhistory	Remove os registros de histórico de um trabalho.

Visualizações de trabalho

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

Ver	Description
job_executions	Mostra o histórico de execução do trabalho.
empregos	Mostra todos os trabalhos.
job_versions	Mostra todas as versões do trabalho.
passos do trabalho	Mostra todas as etapas na versão atual de cada trabalho.
jobstep_versions	Mostra todas as etapas em todas as versões de cada trabalho.
target_groups	Mostra todos os grupos-alvo.
target_group_members	Mostra todos os membros de todos os grupos-alvo.

Próximo passo

Criar e gerenciar trabalhos elásticos usando o PowerShell (visualização)