Database Mail

What is Database Mail?

In a short answer, Database Mail is a feature of SQL Server which let Database sends Mail or SMS to anybody after a transaction

SQL Server has a functionality to do such a thing. In fact whenever a transaction commits or Rollbacks after that SQL Server can send SMS or e-Mail to anybody with any limitation.

Database Mail run outside of SQL Server so it is not pressure on your SQL Server Engine. It also is support clustered environment and can use SMTP Servers. On the other hand it sends mails asynchronously with Service broker so there will be no waste time and it has some security maintenance which let you filter messages

The best example to send SMS is after a Backup procedure it doesn’t matter that it is commit or rollback, you will receive SMS (mail) after the procedure and you can remotely run the backup procedure after a rollback. It is very good for DBAs

When SQL Server 2005 was released, the added functionality of Database Mail came with it, giving us the ability to use SMTP to send email from SQL Server instances.  This was a huge leap forward from SQL Server 2000 SQL Mail, in that you no longer needed to set-up a mail client first (such as Outlook) to send emails.

Reliability No Microsoft Outlook or Extended MAPI requirement. Database Mail

uses SMTP to send mail. Process isolation. To minimize the impact on SQL Server, the

component that delivers e-mail runs outside of SQL Server, in a separate process. SQL Server will continue to queue e-mail messages even if the external process stops or fails. The queued messages will be sent once the outside process or SMTP server comes online.

Failover accounts. A Database Mail profile allows you to specify more than one SMTP server. Should an SMTP server be unavailable, mail can still be delivered to another SMTP server.

Cluster support. Database Mail is cluster-aware and is fully supported on a cluster.

Scalability Background delivery. Database Mail provides background, or

asynchronous, delivery. When you call sp_send_dbmail to send a message, Database Mail adds a request to a Service Broker queue. The stored procedure returns immediately. The external e-mail component receives the request and delivers the e-mail.

Multiple profiles. Database Mail allows you to create multiple profiles within a SQL Server instance. Optionally, you can choose the profile that Database Mail uses when you send a message.

Multiple accounts. Each profile can contain multiple failover accounts. You can configure different profiles with different accounts to distribute e-mail across multiple e-mail servers.

64-bit compatibility. Database Mail is fully supported on 64-bit installations of SQL Server.

Security Off by default. To reduce the surface area of SQL Server, Database

Mail stored procedures are disabled by default. To send Database Mail, you must be a member of

the DatabaseMailUserRole database role in the msdb database. Profile security: Database Mail enforces security for mail profiles. You choose the msdb database users or groups that have access to

a Database Mail profile. You can grant access to either specific users, or all users in msdb.

A private profile restricts access to a specified list of users. A public profile is available to all users in a database.

Security Attachment size governor.

Database Mail enforces a configurable limit on the attachment file size. You can change this limit by using the sysmail_configure_sp stored procedure.

Prohibited file extensions. Database Mail maintains a list of prohibited file extensions. Users

cannot attach files with an extension that appears in the list. You can change this list by using sysmail_configure_sp.

Database Mail runs under the SQL Server Engine service account. To attach a file from a folder to an email, the SQL Server engine account should have permissions to access the folder with the file.

Supportability Integrated configuration.

Database Mail maintains the information for e-mail accounts within SQL Server Database Engine. There is no need to manage a mail profile in an external client application.

Database Mail Configuration Wizard provides a convenient interface for configuring Database Mail. You can also create and maintain Database Mail configurations using Transact-SQL.

With above information you can guess that what the Database Mail requirements are.

SQL Server (2005 – 2008) – SQL Express don’t have Database Mail.

Enabling Service Broker Enabling Database Mail A Mail Server (Usually Exchange Server) to

send Mails. Configuring Database Mail to use it.

How to Configure Database Mail

Database Mail is disabled by default and you have to enable it. To enabling it you have three options.

First one is to use Database Mail Configuration Wizard.

The Second solution to configure Database Mail, is to use Store procedures.

The third solution is using the Surface Area Configuration facet of Policy-Based Management.

Configure Database Mail SQL Server 2008

http://www.youtube.com/watch?v=3tC4hbFXHE8

Enabling and configuring Database Mail in SQL Server using T-SQL

The second method is to use Transact SQL (T-SQL) and stored procedures to enable and configure Database Mail, which is much quicker, and less prone to human error, after the initial run has been tested and confirmed as working.

http://www.snapdba.com/2013/04/enabling-and-configuring-database-mail-in-sql-server-using-t-sql/

Enabling and configuring Database Mail in SQL Server using T-SQL

This walk through will execute a number of stored procedures to accomplish the following tasks:

  Enable the Database Mail feature Create a profile for Database Mail Create an account for use with the profile Send a test email to the DBAs email address Enable the SQL Server Agent to use Database Mail Add the ‘DBAs’ as an operator for notifications Before running any of scripts below, I would take a backup of your

system databases (master, msdb), and ensure you have sysadmin rights.

Enable the Database Mail XPs:

USE masterGOsp_configure 'show advanced options',1GORECONFIGURE WITH OVERRIDEGOsp_configure 'Database Mail XPs',1GORECONFIGURE GO

Create a new mail profile:

USE msdbGOEXECUTE msdb.dbo.sysmail_add_profile_sp@profile_name = 'admin',@description = 'Profile for sending Automated DBA Noti-fications'GO

Create an account for the notifications (changing the email address, mail server, port as appropriate to your environment):

EXECUTE msdb.dbo.sysmail_add_account_sp@account_name = 'SQLAlerts',@description = 'Account for Automated DBA Notifica-tions',@email_address = 'sqlalerts@example.com',@display_name = 'SQL Alerts',@mailserver_name = 'smtp.example.com',@port = 25GO

Add the account to the profile:

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp@profile_name = 'admin',@account_name = 'SQLAlerts',@sequence_number = 1GO

Enable the SQL Server Agent to use Database Mail profile by updating the registry settings:

USE msdbGOEXEC master.dbo.xp_instance_regwriteN'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',N'UseDatabaseMail',N'REG_DWORD', 1EXEC master.dbo.xp_instance_regwriteN'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',N'DatabaseMailProfile',N'REG_SZ',N'admin‘

NOTE: (0 row(s) affected) is normal here 

Restart the SQL Server Agent:

At this point, the SQL Server Agent needs to be restarted. If it isn’t, the Database Mail configuration changes will not be picked up, and the Database Mail process will not start / function correctly.

If Database Mail is being configured on a SQL Server cluster, you’ll need to perform this using the Cluster Administrator tool by selecting the appropriate cluster group, then restarting the SQL Server Agent resource for the appropriate instance:

Windows Server 2003:C:\WINDOWS\Cluster\CluAdmin.exe

Windows Server 2008:C:\Windows\System32\Cluadmin.msc

Once the SQL Server Agent has been restarted, try sending an email to test the configuration is working as expected:

EXECUTE msdb.dbo.sp_send_dbmail@profile_name = 'admin',@recipients = 'mail@example.com',@Subject = 'Test Message generated from SQL Server Database Mail',@Body = 'This is a test message from SQL Server Data-base Mail'GO

Finally, setup an operator called ‘DBAs’ for the job notifications (24×7 schedule in this case) for the email address you supplied earlier:

EXEC msdb.dbo.sp_add_operator @name=N'DBAs', @enabled=1, @weekday_pager_start_time=0, @weekday_pager_end_time=235959, @saturday_pager_start_time=0, @saturday_pager_end_time=235959, @sunday_pager_start_time=0, @sunday_pager_end_time=235959,@pager_days=127, @email_address=N'sqlalerts@example.com', @category_name=N'[Uncategorized]'GO

To generate notifications when a job succeeds, fails, or completes, you can run a stored procedures like below on a job by job basis:

USE msdbGOEXEC msdb.dbo.sp_update_job @job_name='System databases - backups.Subplan_1',@notify_level_email=2,@notify_level_netsend=2,@notify_level_page=2,@notify_email_operator_name=N'DBAs'GO

NOTE: The notify_levels can be set to: 1 (job succeeds), 2 (job fails), or 3 (job completes)

To enable failure notifications for all jobs, run the following script, which will update the notifications jobs for you, and output the T-SQL that’s been executed for each job identified:

DECLARE @JobName SYSNAME, @JobID UNIQUEIDENTIFIER, @No-tifyLevel INT, @SQL NVARCHAR(3000) DECLARE job_operator_cursor CURSOR FORSELECT name, job_id, notify_level_email FROM msdb.d-bo.sysjobs_view  OPEN job_operator_cursorFETCH NEXT FROM job_operator_cursor INTO @JobName, @Jo-bID, @NotifyLevelWHILE @@FETCH_STATUS = 0BEGINIF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobs_view WHERE notify_level_email = 2 and name LIKE @JobName)

BEGINPRINT ''SELECT @SQL = 'EXEC msdb.dbo.sp_update_job @job_name=N'''+@JobName+''',@notify_level_email=2,@notify_level_netsend=2,@notify_level_page=2,@notify_email_operator_name=N''DBAs'''PRINT @SQLEXEC sp_executesql @SQLENDFETCH NEXT FROM job_operator_cursor INTO @JobName, @Jo-bID, @NotifyLevelEND

CLOSE job_operator_cursorDEALLOCATE job_operator_cursor

Quick troubleshooting queries for Database Mail

Check to see if the service broker is enabled (should be 1):SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'

Check to see if Database Mail is started in the msdb database:EXECUTE dbo.sysmail_help_status_sp

…and start Database Mail if necessary:EXECUTE dbo.sysmail_start_sp

Quick troubleshooting queries for Database Mail

Check the status of the mail queue:sysmail_help_queue_sp @queue_type = 'Mail'

 Check the Database Mail event logs:SELECT * FROM sysmail_event_log

Check the mail queue for the status of all items (including sent mails):

SELECT * FROM sysmail_allitems

Logging. Database Mail logs e-mail activity to SQL Server, the Microsoft

Windows application event log, and to tables in the msdb database. Auditing.

Database Mail keeps copies of messages and attachments sent in the msdb database. You can easily audit Database Mail usage and review the retained messages.

Support for HTML. Database Mail allows you to send e-mail formatted as HTML.

GO HOME