quarta-feira, 8 de junho de 2016

How to Configure Database Mail in SQL Server 2008 R2?

How to Configure Database Mail in SQL Server 2008 R2?

In this article we will talk about configuration of Database mail feature in SQL Server 2008 R2. This feature was first introduced in SQL Server 2005. So, same steps will work for SQL Server 2005 and 2008 also.
Before we start the configuration make sure we have SMTP server details, which we can use to send emails form SQL Server. Also, we have enabled the database mail feature. In order to do that either we can run the below script: –
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Database Mail XPs’, 1;
GO
RECONFIGURE
GO
sp_configure ‘show advanced options’, 0;
GO
RECONFIGURE;
GO
Or, just start the configuration steps below and at the very first point, configuration wizard will show a window stating Database mail feature is not enabled, click yes to enable the feature.
NOTE: – We have already enabled the database mail feature before we started the below configuration. So, we didn’t get the window stating Database mail feature is not enabled.
STEPS
1) Connect to SQL Server instance from SSMS and navigate to Management a Database Mail. Right click on Database Mail and click on Configure Database Mail option.

2) Here select the first option “ Setup database mail…..” and move to next step.

3) Type the desired profile name and profile description. Then click on Add button to add the SMTP accounts to this profile.

4) Since this is the first profile we are creating, there will not be any Account name available. Here click on New Account to create a new account.

5) Provide the below details: –
Account Name –> Desires account name for this SMTP account
Description –> Description of the account
E-mail address –> Email account from which emails will be sent. When recipient receives email, the sender email is shown as this address.
Display Name –> Name associated with the email address
Reply e-mail –> where reply to the emails will be forwarded.
Server name –> Provide the SMTP, which is to be used to send emails from SQL Server.
Port Number –> Port number to be used by this account. Default is 25.
SMTP Authentication –> Select the desired authentication to be used and then click OK. Then click Next on new window.

6) This screen will show the public profiles available. We can see Test profile which we created above.

7) This screen will show the private profiles available. We can see Test profile which we created above. You can select the account that you want to associate to private profile.

8) Make necessary changes to the Database mail system parameters and move to next step.

9) Database Mail Configuration Wizard will show the summary of the actions to be performed. Click finish and database mail is configured.

10) You can test the newly configured database mail feature by right clicking on Database Mail and click on Test email. Then select the recipient email and send the test email.

Configurar o SQL Server Agent Mail para usar o Database Mail

Configurar o SQL Server Agent Mail para usar o Database Mail

 Aplica-se a: SQL Server 2016
Este tópico descreve como configurar o SQL Server Agent para usar o Database Mail para enviar notificação e alertas no SQL Server 2016 usando o SQL Server Management Studio.

Pré-requisitos

  • Habilite o Database Mail.
  • Crie uma conta do Database Mail para a conta de serviço do SQL Server Agent a ser usada.
  • Crie um perfil do Database Mail para a conta de serviço do SQL Server Agent a usar e adicione o usuário a DatabaseMailUserRole no banco de dados msdb.
  • Defina o perfil como o perfil padrão para o banco de dados msdb.

Segurança

Permissões

O usuário que cria as contas de perfis e executa procedimentos armazenados deve ser membro da função de servidor fixa sysadmin.
Para configurar o SQL Server Agent para usar o Database Mail
  • No Pesquisador de Objetos, expanda uma instância do SQL Server.
  • Clique com o botão direito do mouse em SQL Server Agent e então clique em Propriedades.
  • Clique em Sistema de Alerta.
  • Selecione Habilitar Perfil de Email.
  • Na lista Sistema de email, selecione Database Mail.
  • Na lista Perfil de email, selecione um perfil de email para o Database Mail.
  • Reinicie o SQL Server Agent.
As tarefas a seguir são necessárias para concluir a configuração do Agent a fim de enviar alertas e notificações.
  • Alertas
    Os alertas podem ser configurados para notificar um operador sobre um evento de banco de dados em particular ou uma condição do sistema operacional.
  • Operadores
    Os operadores são alias de pessoas ou grupos que podem receber notificação eletrônica.