Usar o Database Mail no Amazon RDS for SQL Server - Amazon Relational Database Service

Usar o Database Mail no Amazon RDS for SQL Server

Você pode usar o Database Mail para enviar mensagens de e-mail aos usuários a partir da instância de banco de dados do Amazon RDS no SQL Server. As mensagens podem conter arquivos e resultados de consultas. O Database Mail inclui os seguintes componentes:

  • Objetos deconfiguração e segurança – esses objetos criam perfis e contas e são armazenados no banco de dados msdb.

  • Objetos de mensagens – esses objetos incluem o procedimento armazenado sp_send_dbmail usado para enviar mensagens e estruturas de dados que contêm informações sobre mensagens. Eles estão armazenados no banco de dados msdb.

  • Objetos de registro e auditoria – o Database Mail grava informações de registro no banco de dados msdb e no log de eventos de aplicações do Microsoft Windows.

  • Executável do Database MailDatabaseMail.exe lê a partir de uma fila no banco de dados msdb e envia mensagens de e-mail.

O RDS oferece suporte ao Database Mail para todas as versões do SQL Server nas edições Web, Standard e Enterprise.

Limitações

As seguintes limitações se aplicam ao uso do Database Mail em sua instância de banco de dados SQL Server:

  • O Database Mail não é compatível com o SQL Server Express Edition.

  • A modificação dos parâmetros de configuração do Database Mail não é aceita. Para ver os valores predefinidos (padrão), use o procedimento armazenado sysmail_help_configure_sp .

  • Os anexos de arquivo não são totalmente compatíveis. Para ter mais informações, consulte Trabalhar com anexos de arquivos.

  • O tamanho máximo do anexo do arquivo é de 1 MB.

  • O Database Mail requer configuração adicional em instâncias de banco de dados Multi-AZ. Para ter mais informações, consulte Considerações para implantações Multi-AZ.

  • Não há suporte para configurar o SQL Server Agent para enviar mensagens de email para operadores predefinidos.

Habilitar Database Mail

Use o seguinte processo para habilitar o Database Mail para a instância de banco de dados:

  1. Crie um novo parameter group.

  2. Modifique o grupo de parâmetros para definir o parâmetro database mail xps como 1.

  3. Associe o grupo de parâmetros à instância de banco de dados.

Criar o grupo de parâmetros para o Database Mail

Crie um grupo de parâmetros para o parâmetro database mail xps que corresponde à edição e à versão do SQL Server da instância de banco de dados.

nota

Você também pode modificar um grupo de parâmetros existente. Siga o procedimento em Modificar o parâmetro que habilita o Database Mail.

O exemplo a seguir cria um grupo de parâmetros para o SQL Server Standard Edition 2016.

Como criar o grupo de parâmetros
  1. Faça login no AWS Management Console e abra o console do Amazon RDS em https://console.aws.amazon.com/rds/.

  2. No painel de navegação, escolha Parameter groups (Grupos de parâmetros).

  3. Escolha Create parameter group (Criar parameter group).

  4. No painelCreate parameter group (Criar parameter group), faça o seguinte:

    1. Em Família de grupos de parâmetros, escolha sqlserver-se-13.0.

    2. Em Nome do grupo, insira um identificador para o grupo de parâmetros, como dbmail-sqlserver-se-13.

    3. Em Descrição, insira Database Mail XPs.

  5. Escolha Criar.

O exemplo a seguir cria um grupo de parâmetros para o SQL Server Standard Edition 2016.

Como criar o grupo de parâmetros
  • Use um dos seguintes comandos.

    Para Linux, macOS ou Unix:

    aws rds create-db-parameter-group \ --db-parameter-group-name dbmail-sqlserver-se-13 \ --db-parameter-group-family "sqlserver-se-13.0" \ --description "Database Mail XPs"

    Para Windows:

    aws rds create-db-parameter-group ^ --db-parameter-group-name dbmail-sqlserver-se-13 ^ --db-parameter-group-family "sqlserver-se-13.0" ^ --description "Database Mail XPs"

Modificar o parâmetro que habilita o Database Mail

Modifique o parâmetro database mail xps no grupo de parâmetros que corresponde à edição e à versão do SQL Server da instância de banco de dados.

Para habilitar o Database Mail, defina o parâmetro database mail xps como 1.

O exemplo a seguir modifica o grupo de parâmetros que você criou para o SQL Server Standard Edition 2016.

Como modificar o grupo de parâmetros
  1. Faça login no AWS Management Console e abra o console do Amazon RDS em https://console.aws.amazon.com/rds/.

  2. No painel de navegação, selecione Parameter groups.

  3. Escolha o grupo de parâmetros, como debmail-sqlserver-se-13.

  4. Em Parâmetros, filtre a lista de parâmetros para mail.

  5. Escolha xps de email de bancode dados.

  6. Escolha Edit parameters.

  7. Digite 1.

  8. Selecione Save changes.

O exemplo a seguir modifica o grupo de parâmetros que você criou para o SQL Server Standard Edition 2016.

Como modificar o grupo de parâmetros
  • Use um dos seguintes comandos.

    Para Linux, macOS ou Unix:

    aws rds modify-db-parameter-group \ --db-parameter-group-name dbmail-sqlserver-se-13 \ --parameters "ParameterName='database mail xps',ParameterValue=1,ApplyMethod=immediate"

    Para Windows:

    aws rds modify-db-parameter-group ^ --db-parameter-group-name dbmail-sqlserver-se-13 ^ --parameters "ParameterName='database mail xps',ParameterValue=1,ApplyMethod=immediate"

Associar o grupo de parâmetros à instância de banco de dados

É possível usar o AWS Management Console ou a AWS CLI para associar o grupo de parâmetros do Database Mail à instância de banco de dados.

É possível associar o grupo de parâmetros do Database Mail a uma instância de banco de dados nova ou existente.

É possível associar o grupo de parâmetros do Database Mail a uma instância de banco de dados nova ou existente.

Para criar uma instância de banco de dados com o grupo de parâmetros do Database Mail
  • Especifique o mesmo tipo de mecanismo de banco de dados e a versão principal que você usou ao criar o grupo de parâmetros.

    Para Linux, macOS ou Unix:

    aws rds create-db-instance \ --db-instance-identifier mydbinstance \ --db-instance-class db.m5.2xlarge \ --engine sqlserver-se \ --engine-version 13.00.5426.0.v1 \ --allocated-storage 100 \ --manage-master-user-password \ --master-username admin \ --storage-type gp2 \ --license-model li --db-parameter-group-name dbmail-sqlserver-se-13

    Para Windows:

    aws rds create-db-instance ^ --db-instance-identifier mydbinstance ^ --db-instance-class db.m5.2xlarge ^ --engine sqlserver-se ^ --engine-version 13.00.5426.0.v1 ^ --allocated-storage 100 ^ --manage-master-user-password ^ --master-username admin ^ --storage-type gp2 ^ --license-model li ^ --db-parameter-group-name dbmail-sqlserver-se-13
Para modificar uma instância de banco de dados e associar o grupo de parâmetros do Database Mail
  • Use um dos seguintes comandos.

    Para Linux, macOS ou Unix:

    aws rds modify-db-instance \ --db-instance-identifier mydbinstance \ --db-parameter-group-name dbmail-sqlserver-se-13 \ --apply-immediately

    Para Windows:

    aws rds modify-db-instance ^ --db-instance-identifier mydbinstance ^ --db-parameter-group-name dbmail-sqlserver-se-13 ^ --apply-immediately

Configurando o Correio do Banco

Execute as seguintes tarefas para configurar o Database Mail:

  1. Crie o perfil do Database Mail.

  2. Crie a conta Database Mail.

  3. Adicione a conta de Correio do Banco de Dados ao perfil de Correio do Banco de Dados.

  4. Adicione usuários ao perfil Database Mail.

nota

Para configurar o Database Mail, certifique-se de que você tem execute permissão nos procedimentos armazenados no msdb banco de dados.

Criando o perfil de Correio de Banco de

Para criar o perfil Database Mail, use o procedimento armazenado sysmail_add_profile_sp . O exemplo a seguir cria um perfil chamado Notifications.

Para criar o perfil
  • Use a seguinte instrução SQL.

    USE msdb GO EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'Notifications', @description = 'Profile used for sending outgoing notifications using Amazon SES.'; GO

Criando a conta Database Mail

Para criar a conta Database Mail, use o procedimento armazenado sysmail_add_account_sp . O exemplo a seguir cria uma conta chamada SES em uma instância de banco de dados do RDS para SQL Server em uma VPC privada, usando o Amazon Simple Email Service.

O uso do Amazon SES requer os seguintes parâmetros:

Para criar a conta
  • Use a seguinte instrução SQL.

    USE msdb GO EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SES', @description = 'Mail account for sending outgoing notifications.', @email_address = 'nobody@example.com', @display_name = 'Automated Mailer', @mailserver_name = 'vpce-0a1b2c3d4e5f-01234567.email-smtp.us-west-2.vpce.amazonaws.com', @port = 587, @enable_ssl = 1, @username = 'Smtp_Username', @password = 'Smtp_Password'; GO
    nota

    Especifique credenciais diferentes dos prompts mostrados aqui como prática recomendada de segurança.

Adicionar a conta do Database Mail ao perfil do Database Mail

Para adicionar a conta Database Mail ao perfil Database Mail, use o procedimento armazenado sysmail_add_profileaccount_sp. O exemplo a seguir adiciona a conta do SES ao perfil Notifications.

Para adicionar a conta ao perfil
  • Use a seguinte instrução SQL.

    USE msdb GO EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Notifications', @account_name = 'SES', @sequence_number = 1; GO

Adicionar usuários ao perfil Database Mail

Para conceder permissão para que um principal do banco de dados msdb use um perfil do Database Mail, use o procedimento armazenado sysmail_add_principalprofile_sp. Um principal é uma entidade que pode solicitar recursos do SQL Server. O principal do banco de dados deve mapear para um usuário de autenticação do SQL Server, um usuário de Autenticação do Windows ou um grupo de autenticação do Windows.

O exemplo a seguir concede acesso público ao perfil Notifications.

Para adicionar um usuário ao perfil
  • Use a seguinte instrução SQL.

    USE msdb GO EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'Notifications', @principal_name = 'public', @is_default = 1; GO

Procedimentos armazenados e funções do Amazon RDS para o Database Mail

A Microsoft fornece procedimentos armazenados para usar o Database Mail, como criar, listar, atualizar e excluir contas e perfis. Além disso, o RDS fornece os procedimentos e funções armazenados para o Database Mail mostrados na tabela a seguir.

Procedimento/Função Descrição
rds_fn_sysmail_allitems Mostra as mensagens enviadas, incluindo as enviadas por outros usuários.
rds_fn_sysmail_event_log Mostra eventos, incluindo aqueles para mensagens enviadas por outros usuários.
rds_fn_sysmail_mailattachments Mostra anexos, incluindo aqueles para mensagens enviadas por outros usuários.
rds_sysmail_control Inicia e interrompe a fila de correio (processo DatabaseMail.exe).
rds_sysmail_delete_mailitems_sp Exclui mensagens de e-mail enviadas por todos os usuários das tabelas internas do Database Mail.

Enviando mensagens de e-mail usando o Database

Use o procedimento armazenado sp_send_dbmail para enviar mensagens de email usando o Database Mail.

Uso

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'profile_name', @recipients = 'recipient1@example.com[; recipient2; ... recipientn]', @subject = 'subject', @body = 'message_body', [@body_format = 'HTML'], [@file_attachments = 'file_path1; file_path2; ... file_pathn'], [@query = 'SQL_query'], [@attach_query_result_as_file = 0|1]';

Os seguintes parâmetros são obrigatórios:

  • @profile_name – o nome do perfil Database Mail a partir do qual enviar a mensagem.

  • @recipients – a lista delimitada por ponto e vírgula de endereços de e-mail para os quais enviar a mensagem.

  • @subject – o assunto da mensagem.

  • @body – o corpo da mensagem. Você também pode usar uma variável declarada como o corpo.

Os seguintes parâmetros são opcionais:

  • @body_format – esse parâmetro é usado com uma variável declarada para enviar e-mail em formato HTML.

  • @file_attachments – a lista delimitada por ponto e vírgula de anexos de mensagens. Os caminhos de arquivo devem ser caminhos absolutos.

  • @query – uma consulta SQL a ser executada. Os resultados da consulta podem ser anexados como um arquivo ou incluídos no corpo da mensagem.

  • @attach_query_result_as_file – Se deve anexar o resultado da consulta como um arquivo. Defina como 0 para não, 1 para sim. O padrão é 0.

Exemplos

Os exemplos a seguir demonstram como enviar mensagens de e-mail.

exemplo de enviar uma mensagem para um único destinatário
USE msdb GO EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @recipients = 'nobody@example.com', @subject = 'Automated DBMail message - 1', @body = 'Database Mail configuration was successful.'; GO
exemplo de enviar uma mensagem para vários destinatários
USE msdb GO EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @recipients = 'recipient1@example.com;recipient2@example.com', @subject = 'Automated DBMail message - 2', @body = 'This is a message.'; GO
exemplo de enviar um resultado de consulta SQL como um anexo de arquivo
USE msdb GO EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @recipients = 'nobody@example.com', @subject = 'Test SQL query', @body = 'This is a SQL query test.', @query = 'SELECT * FROM abc.dbo.test', @attach_query_result_as_file = 1; GO
exemplo de enviar uma mensagem em formato HTML
USE msdb GO DECLARE @HTML_Body as NVARCHAR(500) = 'Hi, <h4> Heading </h4> </br> See the report. <b> Regards </b>'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @recipients = 'nobody@example.com', @subject = 'Test HTML message', @body = @HTML_Body, @body_format = 'HTML'; GO
exemplo de enviar uma mensagem usando um gatilho quando ocorre um evento específico no banco de dados
USE AdventureWorks2017 GO IF OBJECT_ID ('Production.iProductNotification', 'TR') IS NOT NULL DROP TRIGGER Purchasing.iProductNotification GO CREATE TRIGGER iProductNotification ON Production.Product FOR INSERT AS DECLARE @ProductInformation nvarchar(255); SELECT @ProductInformation = 'A new product, ' + Name + ', is now available for $' + CAST(StandardCost AS nvarchar(20)) + '!' FROM INSERTED i; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @recipients = 'nobody@example.com', @subject = 'New product information', @body = @ProductInformation; GO

Visualizar mensagens, logs e anexos

Você usa procedimentos armazenados do RDS para visualizar mensagens, logs de eventos e anexos.

Para visualizar todas as mensagens de e-mail
  • Use a seguinte consulta SQL:

    SELECT * FROM msdb.dbo.rds_fn_sysmail_allitems(); --WHERE sent_status='sent' or 'failed' or 'unsent'
Para visualizar todos os logs de eventos de e-mail
  • Use a seguinte consulta SQL:

    SELECT * FROM msdb.dbo.rds_fn_sysmail_event_log();
Para visualizar todos os anexos de e-mail
  • Use a seguinte consulta SQL:

    SELECT * FROM msdb.dbo.rds_fn_sysmail_mailattachments();

Excluir mensagens

Use o procedimento armazenado rds_sysmail_delete_mailitems_sp para excluir mensagens.

nota

O RDS exclui automaticamente os itens da tabela de e-mail quando os dados do histórico do DBMail atingem 1 GB de tamanho, com um período de retenção de pelo menos 24 horas.

Se você quiser manter itens de e-mail por um período mais longo, poderá arquivá-los. Para ter mais informações, consulte Create a SQL Server Agent Job to Archive Database Mail Messages and Event Logs na documentação da Microsoft.

Para excluir todas as mensagens de e-mail
  • Use a seguinte instrução SQL.

    DECLARE @GETDATE datetime SET @GETDATE = GETDATE(); EXECUTE msdb.dbo.rds_sysmail_delete_mailitems_sp @sent_before = @GETDATE; GO
Para excluir todas as mensagens de e-mail com um status específico
  • Use a seguinte instrução SQL para excluir todas as mensagens com falha.

    DECLARE @GETDATE datetime SET @GETDATE = GETDATE(); EXECUTE msdb.dbo.rds_sysmail_delete_mailitems_sp @sent_status = 'failed'; GO

Iniciar a fila de e-mails

Use o procedimento armazenado rds_sysmail_control para iniciar o processo Database Mail.

nota

A habilitação do Database Mail inicia automaticamente a fila de e-mails

Para iniciar a fila de correio
  • Use a seguinte instrução SQL.

    EXECUTE msdb.dbo.rds_sysmail_control start; GO

Parar a fila de e-mails

Use o procedimento armazenado rds_sysmail_control para interromper o processo Database Mail.

Para interromper a fila de e-mails
  • Use a seguinte instrução SQL.

    EXECUTE msdb.dbo.rds_sysmail_control stop; GO

Trabalhar com anexos de arquivos

As seguintes extensões de anexo de arquivo não são compatíveis com as mensagens do Database Mail do RDS no SQL Server: .ade, .adp, .apk, .appx, .appxbundle, .bat, .bak, .cab, .chm, .cmd, .com, .cpl, .dll, .dmg, .exe, .hta, .inf1, .ins, .isp, .iso, .jar, .job, .js, .jse, .ldf, .lib, .lnk, .mde, .mdf, .msc, .msi, .msix, .msixbundle, .msp, .mst, .nsh, .pif, .ps, .ps1, .psc1, .reg, .rgs, .scr, .sct, .shb, .shs, .svg, .sys, .u3p, .vb, .vbe, .vbs, .vbscript, .vxd, .ws,. sc, .wsf e .wsh.

O Database Mail usa o contexto de segurança do Microsoft Windows do usuário atual para controlar o acesso aos arquivos. Os usuários que fazem login com o SQL Server Authentication não podem anexar arquivos usando o parâmetro @file_attachments com o procedimento armazenado sp_send_dbmail. O Windows não permite que o SQL Server forneça credenciais de um computador remoto para outro computador remoto. Portanto, o Database Mail não pode anexar arquivos de um compartilhamento de rede quando o comando é executado a partir de um computador diferente do computador que esteja executando o SQL Server.

No entanto, você pode usar tarefas do SQL Server Agent para anexar arquivos. Para ter mais informações sobre o SQL Server Agent, consulte Uso do SQL Server Agent e SQL Server Agent na documentação da Microsoft.

Considerações para implantações Multi-AZ

Quando você configura o Database Mail em uma instância de banco de dados Multi-AZ, a configuração não é propagada automaticamente para o secundário. Recomendamos converter a instância Multi-AZ em uma instância Single-AZ, configurar o Database Mail e, em seguida, converter a instância de banco de dados de volta para Multi-AZ. Em seguida, os nós primário e secundário têm a configuração do Database Mail.

Se você criar uma réplica de leitura a partir da instância Multi-AZ que tenha o Database Mail configurado, a réplica herdará a configuração, mas sem a senha para o servidor SMTP. Atualize a conta Database Mail com a senha.