How to Setup SQL Server 2008/2008R2 Maintenance Plan and Email Notifications
How to Setup SQL Server 2008/2008R2 Maintenance Plan and Email Notifications
Most of the time we will have good installation and configuration for several systems but when we talk about maintenance and regular health check there should be a delay in talk and response. Database backup and maintenance should consider very important DBA should take responsibility to their assigned database. This article, we will discuss about creating Email notification and alert using out of box notification solutions designed by Microsoft in SQL 2008 /2008R2, we will go deep in maintenance plan and notification task. Backing up database in to tape or disk is not the scope of this articles however to explain SQL Database mail and Email notification ,we will back up the database in to disk and when the backup task is completed we will trigger the notification to the respective admin user for the status of the process. This will become a warning for the admin in case there is a failure in backup or any other task which we have scheduled through maintenance plan with in SQL. Then whole process and step described in this article are done through Microsoft SQL Server Management Studio, of course for email creation you have to go for the mail server.
First step is to configure Database Mail. Open Microsoft SQL Server Management Studio then right-click on Database Mail > select Configure Database Mail
Skip the welcome screen and select Next on the Select Configuration Task window.
Create new profile > fill out Profile name > Select Add under SMTP accounts:
Fill out New Database Mail Account info:
You can create New profile for SMTP Server and Email Address for different jobs and maintenance Plan here by adding more SMTP profile
Consider public and privet profile security, Default Profile and email flow according to your environment and alert requirements. Flowing are the screens for the completion and over view of your Database mail configuration which you have carried out.
After all went right Send test email by Right-click on Database Mail
Fill out test info, by typing the email address and select Send Test Email.
Check your inbox for the test mail .If you do not receive the test mail ,then make sure all the email address and SMTP setting you have configured on the SQL Database Mail configuration and Exchange server incase if your relaying your mail form SQL server.
Also for troubleshooting mail flow, View Database Mail log is a good place for starting.
In order to receive Email we must create at least one operator it will be under SQL Server Agent and right click Operators and click for New Operator
Here you can specify the Name of the Operator then in Notification options, we can set up email, Net send etc…and also the schedule, and these are self-explanatory. After fill all the necessary info and details click OK.
Now we will create a Maintenance Plan, right click on Maintenance Plans àMaintenance Plan Wizard. If you have already one just Modify that plan and add the notify operator task or success and failure.
Write the name and description and schedule it by click the change button.
I have schedule this for daily @12AM with No end date then as soon an you click OK, you will be prompted back to the Select Plan Properties; click Next. You will get help on this and any configuration part by clicking Help button if you have installed the documents and Books while installing SQL Server. This is good practice and you will get a quick reference point.
Here you can select the Maintenance Task for your Maintenance Plan, which is totally based on your requirements Click Next
Move the order if you required then select task and click Next. The few screens below are the backup settings. This is again your choice and requirement.
.
Here you can select the report location folder and Email that report to the DB admin or operator you have created earlier in the post. Click Next
This will finish the Maintenance Plan Wizard. Click the + sign and review the settings you have made in this plan.
I fall wend right you will be in Green window and close the Wizard.
Right click on the Maintenance Plan and click Modify this will open the Maintenance Plan Design windows
From the toolbox on your right side drag and drop the Notify Operator Task to any Maintenance Plan you want to notify email alert whether it is success or failure.
Below is the screen for my Maintenance Plan notification design.
Here the red is connector is Failure and Green is Success. You can right click on the connector and change the status to success or failure at any time and also you can edit. Below is the screen for Task connector status.
Now double click on each Notify Operator task and Select the Database Mail which you have created earlier and Type subject and message body according to your need.
Finally, you can test your Maintenance plan and Email notification, If you want to wait for the seclude to be run then fine , otherwise by right click on the Maintenance Plan you have created and click Execute. You will be having a status window like the one shown below close it after finish.
If every this is configured right or not but the Database Mail configuration and Operator email is configured properly you will get notification. There will always be some adjustment and special configuration is required depends upon the environment, that you can sort out with your skill and experience. I have created distribution mail group and add as many administrator you want notify by simply add the user in that AD distribution mail group, this way you can eliminate the errors caused when you fiddle around with SQL management studio and other settings.
This article will help you a better understating for your setup and configuration. Suggestion and questions are very important for me to improve and enhance next article. I always believe in praising of my mistakes.
