Data integrity and protection are two of the most important tenets of using SQL databases. In most cases, IT will set up daily backups and weekly maintenance tasks to ensure these two tenets are observed. Yet, unless you dedicate one person to monitor every SQL agent job execution there could be a scenario where either the backup jobs or maintenance jobs fail. The worst case scenario is that you need to restore your GP databases after a catastrophic SQL failure and the most recent database backup is months old.
This is where SQL Database Mail can come to the rescue. SQL Database Mail allows SQL to send out emails based on conditions or delineated tasks. In order to setup Database Mail you will need to follow these simple steps (example uses SQL 2008R2):
Launch SQL Server Management Studio.
Expand the Management node.
Right-click Database Mail, click Configure Database Mail.
Click Next.
Select Set up Database Mail by performing the following tasks, click Next.
If Database Mail is not currently enabled, then you will be prompted to enable it, click Yes.
Click Add.
Enter in an appropriate account name and description. Enter in the email address you want to send the notifications from and the email server that you want to use. If the port number is not
the default port 25 then change it. If you don’t use Anonymous Authentication then choose the appropriate authentication method and credentials. Also, check off the SSL option if the email server requires a secure connection. Once you are done, click OK.
Enter an appropriate profile name and description, click Next.
Check the Public box and select Yes in the Default Profile drop-down, click Next.
Check the system parameters, click Next.
Check the synopsis, click Finish.
Once Database Mail has been configured, right-click Database Mail, click Send Test E-Mail…
You will be prompted to enter an email to send the test email to. Confirm that the email is received by the recipients.
Now that Database Mail has been configured, it is time to create the Operators in SQL.
Expand the SQL Server Agent node, right-click Operators, click New Operator.
Enter an appropriate operator name and email address, click OK.
That’s it! You can now add a Notify Operator task to any SQL Maintenance plan which will ensure that you are kept apprised of any issues with the database (backups/integrity/etc.).
Have any questions? Don’t hesitate to reach out at any time!
