Database Mail is an Microsoft’s enterprise solution for sending e-mail messages from the SQL Server Database Engine to users such as query results from created database applications.
Benefits of using Database Mail
- Reliability: - Process Isolation, Failover Accounts & Cluster Support
- Scalability: – Background Delivery, Multiple profiles, Multiple accounts: – including multiple failover accounts, 64-bit compatibility
- Security: – Profile security, Prohibited file extensions
- Supportability: -Integrated configuration, Logging, Auditing, Support for HTML
Configuring Database Mail via the Configure Database Mail Wizard
- Open SQL Server Management Studio.
- Right-click Database Mail under the Management node
- Click Configure Database Mail. (This launches the Database Mail Configuration Wizard)
- Click Next.
- Choose Set Up Database Mail By Performing The Following Tasks
- Click Next.
- In the Microsoft SQL Server Management Studio dialog box informing you that the Database Mail feature is not available
- Click Yes
- On the New Profile page, provide a profile name and a description
- Click Add.In the New Database Mail Account dialog box,
- Provide email account details
- Click OK. On the New Profile page
- Click Next to get to the Manage Profile Security page
- Click Next
- Choose values for Account Retry Attempts and other settings from the Configure System Parameters page
- Click Next to reach the Complete The Wizard page,
- click Finish.
Enabling Database Mail by using T-SQL
We can enable Database Mail by using the sp_configure stored procedure with the Database Mail XPs option, to disable it we simply replace the 1 with a 0.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Database Mail XPs', 1; GO RECONFIGURE; GO
Creating Database Mail accounts by using the sysmail_add_account_sp stored procedure.
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Example', @email_address = 'firstname.lastname@example.org', @mailserver_name = 'smtp.mydomain.com';
Below is an example of modifying Database Mail to retry 15 times before considering the account unreachable
EXECUTE msdb.dbo.sysmail_configure_sp 'AccountRetryAttempts', '15';
This is a good link to troubleshoot Database Mail issues
To send Database Mail, users must be a member of the DatabaseMailUserRole,
Note: the sysadmin fixed server role and msdbdb_owner role are automatically members of the DatabaseMailUserRole role.
To list all other members of the DatabaseMailUserRoleexecute the following statement:
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
Adding users to the DatabaseMailUserRole role
sp_addrolemember @rolename = 'DatabaseMailUserRole' ,@membername = '<database user>';
Database Mail Profiles
Database mail profiles are split into
- Public profiles that can be accessed by all users of any mail-host
- Private profiles which can only be accessed by a specific user who must be a msdb database user.
Quick Review Question
Which stored procedure is used to enable Database Mail on an instance?
KEY POINTS TO REMEMBER
sp_configure to enable Database Mail on an SQL instance