Though this is a very old topic; DB Mail being introduced in SQL Server 2005 replacing annoying SQL Mail, i would like to brief about this topic because………………….
Well, i was configuring alerts using PBM(Policy Based Management) on one of our brand new SQL Server 2008R2 Instances today in such a way that if any policy has been violated, SQL Will send an email to our DBAops Group. I will cover this topic in upcoming posts! Prior to this, for SQL to be able to send email notifications, we’ve to configure DB Mail. In this demo let’s see how to send emails from SQL Server to Gmail. you can test alerts, operators, notifications, log shipping/Mirroring notifications, Job failure notifications and what not……(Basically you can simulate a real environment at home to learn things) on your own Personal laptop where you are trying to learn and explore new things within SQL Sever. why to wait?? Let’s proceed…then
First of all of course you need to have a GMAIL account 🙂 . If you don’t want your personal email account for SQL Server sending alerts, create a test account for this purpose. I did the same under the name firstname.lastname@example.org
Note – Gmail smtp sever: smtp.gmail.com
Go to your Account Settings and make sure that POP is enabled for your account as shown below.
Save your settings and you can sign off from your GMail. Now, let’s go to your SQL Server and very first thing which you’ve to make sure is your DB Mail XP’s are enabled. Just Query sp_configure ‘database mail’ and make sure that the running value returned is ‘1’ as shown below. Also, Service Broker should be enabled on MSDB Database. You can execute select name,is_broker_enabled from sys.databases where name = ‘msdb’ and make sure that “1” is returned.
Go to your Object Explorer – expand Management Node and select Configure Database mail as shown below.
Follow the below in Sequence as shown below.
Next – > and Click on Add to add new SMTP account to the profile as shown below.
Once you select Add, you’ll be welcomed with the below Screen, where we’ve to configure our account details and provide the smtp details for gmail.
Things to note in the above screenshot: Gmail Requires SSL to be checked and Port 587. Now Select Next and I’ve chosen this as my Default Public Profile as you can see below.
Select next->Next-> and Finish and you’ll be seeing a success message as shown below.
That’s all we need to do for configuring your DB Mail on SQL Server. Now, let’s see by sending a test email from SQL Server. Please see the below screenshots…
I’ve entered the same gmail account for testing purposes, and you can see the email being delivered to my Inbox as shown below…
Tadaaaaaaaaaa!!! That’t it Guys…..All I can say is, now you are all set to play with all your Automated(SQL Agent Jobs) stuff , create Operators, configure in such a way that SQL will send you notifications to the operator. Now it’s all yours!! In the upcoming Post, Let’s see how can we configure PBM to send email alerts, if any of the policies has been violated.
Hope this info is useful..Cheers!!