Wouldn’t it be cool if your database server reached out to you when it wants to let you know something like a job that completed or failed and error messages its getting ? SQL Server has two built-in ways to do it: SQLMail and Database Mail. SQLmail is the old way to do it and would require you to install Outlook components on the database server. I personally like database mail since its all nice and tightly integrated with sql server. Unlike SQLMail, database mail is cluster aware and does not stress the database server as it runs outside of the database engine.
Database Mail in SQL Server is pretty robust yet easy to setup. Few weeks ago I wrote an article on how to send emails from db2. In this , I am going to show you how to configure database mail in sql server, create an operator and have sql server agent send out notifications. Once configured SQL Server Agent can either send you emails or page you for agent jobs or for alerts from the sql server itself.
Here is a video that I created to show you how to accomplish this :
* If the video is not clear please change the resolution on the youtube video to 1080p and view it full screen.
Things to remember is :
1. You need database mail configured with an smtp server that the sql server can reach.
2. Setup Operator(s) for the SQL Server Agent to use for notification
3. Enable Mail Session on SQL Server Agent’s Alert System.
4. Enable notification on SQL Server Agent jobs to use the appropriate Operator profiles to send out emails.