Automatizar tarefas de gerenciamento usando trabalhos do SQL Agent na Instância Gerenciada de SQL do Azure

  • Artigo

Aplica-se a:Instância Gerenciada de SQL do Azure

Usando o SQL Server Agent no SQL Server e na Instância Gerenciada de SQL, você pode criar e agendar trabalhos que podem ser executados periodicamente em um ou vários bancos de dados para executar consultas Transact-SQL (T-SQL) e executar tarefas de manutenção. Este artigo aborda o uso do SQL Agent para Instância Gerenciada de SQL.

Observação

O SQL Agent não está disponível no Banco de Dados SQL do Azure ou no Azure Synapse Analytics. Em vez disso, recomendamos a A automação de trabalhos com trabalhos elásticos.

Ao usar trabalhos do SQL Agent

Há vários cenários em que você pode usar trabalhos do SQL Agent:

  • Automatizar tarefas de gerenciamento e, em seguida, agendá-las para serem executadas a todo dia da semana, após horas etc.
    • Implantar alterações de esquema, gerenciamento de credenciais, coleta de dados de desempenho ou coleta de telemetria do locatário (cliente).
    • Atualizar dados de referência (informações comuns a todos os bancos de dados), carregar dados do armazenamento de blobs do Azure. A Microsoft recomenda usar a Assinatura de Acesso Compartilhado para autenticar o armazenamento de Blobs do Azure.
    • Tarefas comuns de manutenção, incluindo DBCC CHECKDB garantir a integridade dos dados ou a manutenção do índice para melhorar o desempenho de consulta. Configure trabalhos para serem executados em um conjunto de bancos de dados de modo recorrente, por exemplo, fora dos horários de pico.
    • Coletar resultados de consulta de um conjunto de bancos de dados em uma tabela central em uma base contínua. Consultas de desempenho podem ser executadas continuamente e configuradas para disparar tarefas adicionais a serem executadas.
  • Coletar dados para relatórios
    • Agregue dados de uma coleção de bancos de dados em uma tabela de destino único.
    • Executar consultas de processamento de dados mais longas em um grande conjunto de bancos de dados, por exemplo, a coleta de telemetria do cliente. Resultados são coletados em uma única tabela de destino para análise posterior.
  • Movimentações de dados
    • Crie trabalhos que replicam as alterações feitas em seus bancos de dados para outros bancos de dados ou colete atualizações feitas em bancos de dados remotos e aplique o que foi alterado no banco de dados.
    • Crie trabalhos que carregam dados de ou para seus bancos de dados usando o SSIS (SQL Server Integration Services).

Trabalhos do SQL Agent na Instância Gerenciada de SQL

Os trabalhos do SQL Agent são executados pelo serviço do SQL Agent, que continua a ser usado para automação de tarefas no SQL Server e na Instância Gerenciada de SQL.

Os trabalhos do SQL Agent são uma série especificada de scripts T-SQL com relação ao seu banco de dados. Use trabalhos para definir uma tarefa administrativa que pode ser executada uma ou mais vezes e monitorada quanto a êxito ou falha.

Um trabalho pode ser executado em um servidor local ou em vários servidores remotos. Os trabalhos do SQL Agent são um componente interno do Mecanismo de Banco de Dados executado dentro do serviço da Instância Gerenciada de SQL.

Há vários conceitos importantes em Trabalhos do SQL Agent:

  • Etapas de trabalho conjunto de uma ou mais etapas que devem ser executadas dentro do trabalho. Para cada etapa de trabalho, é possível definir a estratégia de repetição e a ação que deverá acontecer se a etapa de trabalho tiver êxito ou falhar.
  • Agendas definem quando o trabalho deve ser executado.
  • Notificações permitem que você defina regras que serão usadas para notificar operadores por email após a conclusão do trabalho.

Etapas de trabalho

As etapas do Trabalho do SQL Agent são sequências de ações que o SQL Agent deve executar. Cada etapa tem a seguinte etapa que deverá ser executada se a etapa tiver êxito ou falhar, número de repetições em caso de falha.

O SQL Agent permite que você crie diferentes tipos de etapas de trabalho, como a etapa de trabalho Transact-SQL que executa um único lote do Transact-SQL com relação ao banco de dados ou as etapas comando/PowerShell do sistema operacional que podem executar um script personalizado do sistema operacional, as etapas de trabalho do SSIS que permitem que você carregue dados usando o runtime do SSIS ou as etapas de replicação que podem publicar alterações do seu banco de dados em outros.

Observação

Para obter mais informações sobre como aproveitar o Azure-SSIS Integration Runtime com o SSISDB hospedado pela Instância Gerenciada de SQL do Azure, veja Usar a Instância Gerenciada de SQL do Azure com SQL Server Integration Services (SSIS) no Azure Data Factory.

A replicação transacional pode replicar as alterações de suas tabelas em outros bancos de dados na Instância Gerenciada de SQL do Azure, no Banco de Dados SQL do Azure ou no SQL Server. Para obter informações, veja Configurar a replicação na Instância Gerenciada de SQL do Azure.

No momento, não há suporte para outros tipos de etapas de trabalho na Instância Gerenciada de SQL, como replicação de mesclagem e leitor de fila.

Agendas de trabalho

Uma agenda especifica quando um trabalho é executado. Mais de um trabalho pode ser executado na mesma agenda e mais de uma agenda pode ser aplicada ao mesmo trabalho.

Uma agenda pode definir as condições a seguir para a hora em que um trabalho é executado:

  • Sempre que o SQL Server Agent é iniciado. O trabalho é ativado após cada failover.
  • Uma vez, em uma data e hora específicas, que é útil para a execução atrasada de algum trabalho.
  • Em uma agenda recorrente.

Para obter mais informações sobre como agendar um trabalho do SQL Agent, consulte agendar um trabalho.

Observação

No momento, a Instância Gerenciada de SQL do Azure não permite que você inicie um trabalho quando a CPU estiver “ociosa”.

Notificações de trabalho

Os trabalhos do SQL Agent permitem que você receba notificações quando o trabalho é concluído com êxito ou com falha. É possível receber a notificações por email.

Se ainda não estiver habilitado, primeiro você precisará configurar o recurso Database Mail na Instância Gerenciada de SQL:

GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE

Como um exercício de exemplo, configure a conta de email que será usada para enviar as notificações por email. Atribua a conta ao perfil de email chamado AzureManagedInstance_dbmail_profile. Para enviar email usando trabalhos do SQL Agent na Instância Gerenciada de SQL, deve haver um perfil que deve ser chamado AzureManagedInstance_dbmail_profile. Caso contrário, a Instância Gerenciada de SQL não poderá enviar emails por meio do SQL Agent.

Observação

Para o servidor de email, recomendamos que você use serviços de retransmissão SMTP autenticados para enviar email. Esses serviços de retransmissão normalmente se conectam por meio das portas TCP 25 ou 587 para conexões por TLS ou pela porta 465 para conexões SSL, no entanto, o Database Mail pode ser configurado para usar qualquer porta. Essas portas exigem uma nova regra de saída no grupo de segurança de rede da instância gerenciada. Esses serviços são usados para manter a reputação de IP e domínio para minimizar a possibilidade de domínios externos rejeitarem suas mensagens ou colocá-las na pasta SPAM. Considere um serviço de retransmissão SMTP autenticado já presente nos servidores locais. No Azure, o SendGrid é um desses serviços de retransmissão SMTP, mas há outros.

Use o seguinte script de exemplo para criar uma conta e um perfil do Database Mail e associá-los:

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQL Agent Account',
    @description = 'Mail account for Azure SQL Managed Instance SQL Agent system.',
    @email_address = '$(loginEmail)',
    @display_name = 'SQL Agent Account',
    @mailserver_name = '$(mailserver)' ,
    @username = '$(loginEmail)' ,
    @password = '$(password)';

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'AzureManagedInstance_dbmail_profile',
    @description = 'E-mail profile used for messages sent by Managed Instance SQL Agent.';

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'AzureManagedInstance_dbmail_profile',
    @account_name = 'SQL Agent Account',
    @sequence_number = 1;

Teste a configuração do Database Mail por meio do T-SQL usando o procedimento armazenado do sistema sp_send_db_mail:

DECLARE @body VARCHAR(4000) = 'The email is sent from ' + @@SERVERNAME;
EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'AzureManagedInstance_dbmail_profile',  
    @recipients = 'ADD YOUR EMAIL HERE',  
    @body = 'Add some text',  
    @subject = 'Azure SQL Instance - test email';

É possível notificar o operador de que algo aconteceu com seus trabalhos do SQL Agent. Um operador define informações de contato para um indivíduo responsável pela manutenção de uma ou mais instâncias na Instância Gerenciada de SQL. Algumas vezes, as responsabilidades do operador são atribuídas a um indivíduo.

Em sistemas com várias instâncias na Instância Gerenciada de SQL ou no SQL Server, muitos indivíduos podem compartilhar as responsabilidades do operador. Um operador não contém informações de segurança nem define uma entidade de segurança. O ideal é que um operador não seja um indivíduo cujas responsabilidades possam ser alteradas, mas um grupo de distribuição de email.

Você pode criar operadores usando o SQL Server Management Studio (SSMS) ou o script Transact-SQL mostrado no exemplo a seguir:

EXEC msdb.dbo.sp_add_operator
    @name=N'AzureSQLTeam',
    @enabled=1,
    @email_address=N'AzureSQLTeamn@contoso.com';

Confirme o êxito ou a falha do email por meio do log do Database Mail no SSMS.

Você pode modificar qualquer trabalho do SQL Agent e atribuir operadores que serão notificados por email se o trabalho for concluído, falhar ou for bem-sucedido usando SSMS ou o seguinte script Transact-SQL:

EXEC msdb.dbo.sp_update_job @job_name=N'Load data using SSIS',
    @notify_level_email=3, -- Options are: 1 on succeed, 2 on failure, 3 on complete
    @notify_email_operator_name=N'AzureSQLTeam';

Histórico de trabalho

Atualmente, a Instância Gerenciada de SQL não permite que você altere nenhuma propriedade do SQL Agent, pois elas são armazenadas nos valores de registro subjacentes. Isso significa que as opções para ajustar a política de retenção do Agent para registros de histórico de trabalho são fixadas no padrão de 1.000 registros totais e no máximo 100 registros de histórico por trabalho.

Para obter mais informações, consulte exibir o histórico de trabalhos do SQL Agent.

Associação de função de banco de dados fixa

Se os usuários vinculados a logons que não são de administrador do sistema forem adicionados a uma das três funções de banco de dados fixas do SQL Agent no banco de dados do sistema msdb, haverá um problema em que as permissões EXECUTE explícitas precisarão ser concedidas aos três procedimentos armazenados do sistema no banco de dados master. Se esse problema for encontrado, a mensagem de erro The EXECUTE permission was denied on the object <object_name> (Microsoft SQL Server, Error: 229) será mostrada.

Depois de adicionar usuários a uma função de banco de dados fixa do SQL Agent (SQLAgentUserRole, SQLAgentReaderRole ou SQLAgentOperatorRole) em msdb, para cada um dos logons de usuário adicionados a essas funções, execute o script T-SQL abaixo para conceder explicitamente permissões EXECUTE aos procedimentos armazenados do sistema listados. Este exemplo pressupõe que os nomes de usuário e de logon são os mesmos:

USE [master]
GO
CREATE USER [login_name] FOR LOGIN [login_name];
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO [login_name];
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO [login_name];
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO [login_name];

Limitações de trabalho do SQL Agent na Instância Gerenciada de SQL

Vale a pena observar as diferenças entre o SQL Agent disponível no SQL Server e como parte da Instância Gerenciada de SQL. Para obter mais informações sobre as diferenças de recursos com suporte entre o SQL Server e a Instância Gerenciada de SQL, veja Diferenças de T-SQL da Instância Gerenciada de SQL do Azure do SQL Server.

Alguns recursos do SQL Agent disponíveis no SQL Server não são compatíveis com a Instância Gerenciada de SQL:

  • As configurações do agente SQL são somente leitura.
    • Não há suporte para sp_set_agent_properties o procedimento armazenado do sistema.
  • No momento, não há suporte para habilitar/desabilitar o SQL Agent. O SQL Agent sempre está em execução.
  • Embora as notificações sejam parcialmente compatíveis, não há suporte para o seguinte:
    • Não há suporte para pager.
    • Não há suporte a NetSend.
    • Não há suporte para alertas.
  • Não há suporte para proxies.
  • Não há suporte para Eventlog.
  • Não há suporte para o gatilho de agendamento de trabalho com base em uma CPU ociosa.
  • Não há suporte para as etapas de trabalho de replicação de mesclagem.
  • Não há suporte para leitor de fila.
  • Não há suporte para o Analysis Services.
  • Não há suporte para a execução de um script armazenado como um arquivo em disco.
  • Não há suporte para a importação de módulos externos, como dbatools e dbachecks.
  • Não há suporte para o PowerShell Core.

Saiba mais

Próximas etapas