Set up database mail cho SQL Server

A DBA should configure their SQL Server instance so that they can be alerted for any potential issues automatically. And we can configure database mail to receive email alert for any information we care.
To create a template for setting up database mail for multiple SQL Server instance, I have written a following script using Gmail account:

With the script, I can easily create database mail for multiple SQL Server instance

-------------------------------------------------------------
--  Database Mail Simple Configuration Template.
--
--  This template creates a Database Mail profile, an SMTP account and 
--  associates the account to the profile.
--  The template does not grant access to the new profile for
--  any database principals.  Use msdb.dbo.sysmail_add_principalprofile
--  to grant access to the new profile for users who are not
--  members of sysadmin.
-------------------------------------------------------------

DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
     @display_name NVARCHAR(128);

-- Profile name. Replace with the name for your profile
        SET @profile_name = 'SQLAlertEmail';

-- Account information. Replace with the information for your account.

  SET @account_name = 'QuangVinh-admin';
  SET @SMTP_servername = 'smtp.gmail.com';
  SET @email_address = 'MyEmailAddress';
        SET @display_name = 'Quang Vinh';

-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;

-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
  Print('The specified Database Mail profile already exists. We will delete and recreate it');
  EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = @profile_name ;
END;


IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
 Print('The specified Database Mail account already exists. We will delete it and recreate it') ;
 EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = @account_name ;
END;

DECLARE @rv INT;

-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
    @account_name = @account_name,
    @email_address = @email_address,
    @display_name = @display_name,
    @mailserver_name = @SMTP_servername,
 @port=587,
 @username='MyEmailAddress',
 @password='MyPassword',
 @enable_ssl=1;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail account.', 16, 1) ;
    GOTO done;
END

-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
    @profile_name = @profile_name ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail profile.', 16, 1);
 ROLLBACK TRANSACTION;
    GOTO done;
END;

-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @profile_name,
    @account_name = @account_name,
    @sequence_number = 1 ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to associate the speficied profile with the specified account.', 16, 1) ;
 ROLLBACK TRANSACTION;
    GOTO done;
END;

Print('Configure Database mail successfully')

COMMIT TRANSACTION;

done:

GO

Note: I used SQL Server management Studio -> View -> Template explorer -> I choose the template to create database mail and basing on the original script, I wrote the script above.

Refer: http://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/

Các câu truy vấn liên quan đến Database Mail:

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/