- SQL Server Database DB Mail Configuration Step by Step:
This Article will help Configuring SQL Server Database mail in SQL Server for Alerts and Notifications.
Database Mail is very Useful and powerful ability in SQL Server. Send email in SQL Server is one of the best ability for monitoring SQL Server
One can configure SQL Server Database Mail so that it sends you important notifications and alerts for example
1. Alerts for any high severity errors, corruptions etc..
2. Notifications Agent jobs Success or failure
3. You can also use Database Mail to email query results and reports
Configuring Database Mail and Enabling it on the SQL Server Agent for Alerts can be done by following below steps
Step 1. Get the needed information handy for setting up database mail like below
SMTP Server Information. We will fully qualified name, port information, and authentication information for your smtp server. Get this from your IT Team.
It will be like mailrelay.yourcompanydomain.com and port will be like 25 and authentication.
We will need to enable Database Mail in SQL server which is not enabled default by below code
Below will tell us if its already enabled
SELECT * FROM sys.configurations WHERE name = 'Database Mail XPs'
GO
Below TSql commands will enable the Database mail option on SQL Server
EXEC sp_configure 'show advanced options', '1';
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE
GO
Normally by default Service Broker is enabled but we can check this as its needed
Below will tell us if its already enabled
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
GO
Step 2. Configuring Database Mail Using the Wizard
a. In Object Explorer, expand Management and right click Database Mail:
Click ‘Next’, then click the first option to set up Database Mail.
b. Create a Profile
Create a profile for Database mail. We can have multiple.
c. Create An Account
We will need to create a Account for Database Mail which is used by above Profile for Database Mail to work.
Email address: Say like [email protected]
Display name: We can use SQL Server’s name from where emails are sent.
Reply email: We can use [email protected] or sometimes DBA Email address
Server name: Ask your IT Team for the details of your Exchange server. This smtp server we will use. Select the Port and your authentication options as given by IT Team.
d. Send a Test Email
Right click on “Database Mail” in Object Explorer and click on “Send a Test Email.” and its pretty simple to send a test email to yourself
If it doesn’t work, there’s a problem in your setup. Right click “Database Mail” again and select “View Database Mail Log” for errors needed for troubleshooting
Step 3. We will need to enable Database Mail on the SQL Server Agent for notifications.
Configuring SQL Agent to use Database Email.
Right click on the SQL Server Agent and select properties, like this:
SQL Server Agent Properties
Now Click on the Alert System Tab
Now here is where we configure SQL Server Agent to tell which database mail profile to use. Enable the mail profile, then select your mail profile. we can also set failsafe operator here.
Restart the SQL Server Agent Service to Make That Take Effect