The purpose of the Notify Operator Task is specifically to set up a notification task. It can be for any event within the plan; that is, the takeaway for this one. It doesn’t have to be tied to any specific task either. You could conceivably have this task, by itself, and all it will do is notify an operator. What good could that possibly do? If this task is tied to nothing else, what could a simple notification do? Let’s look at how to set it up and I will explain the answer to that question as we go.
Setting Up the Maintenance Plan
Right-click Maintenance Plans and choose New Maintenance Plan… to continue. Enter Notify Operator Plan as the name, as shown in Figure 12-1, and click OK.
Figure 12-1. New Maintenance Plan
Make sure that the Toolbox is open at this point (Ctrl+Alt+X). The first bit to do is update the default options for the plan, so double-click the Subplan name (Subplan_1) and update your interface, as shown in Figure 12-2.
Figure 12-2. Subplan Properties
Notice that the Schedule box is not set yet. Change this to Daily at 12:00 AM, click OK on the Schedule page, and then click OK when you get back to the Properties window. This leads us to the updated interface, as shown in Figure 12-3.
Figure 12-3. Notify Operator Plan stage
At this point, we are ready to add the Notify Operator Task, so click and drag the Notify Operator Task from the toolbar into the stage.
The first thing that you notice is the big red X. Just like in Chapter 11, we need to define the parameters of this task. Double-click the Notify Operator Task. You will see what is shown in Figure 12-4.
Figure 12-4. Notify Operator Task
Guess what we have to do here? Thankfully, Microsoft makes this fairly easy to ascertain. You can see that “Local server connection” is already selected, so that’s fine. Underneath that, you have the available operators on the system.
Notice that you don’t have the option here to create a new operator, or edit the current operator. I believe this is by design, as it shows a definite disjoint between the process of adding a new operator and using an existing operator. Keeping those two tasks separate forces the user to understand the ramifications of their decision, as well as reinforces the idea of proper flow within a maintenance plan. I believe that Microsoft said something like, “You can’t add an operator that doesn’t exist, now can you? Therefore, you must add an operator first. And don’t think we’re going to let you do it the Microsoft Lite way either. You’re going to have to do this from the operator’s area in SSMS. Once you do that, then you can come back in here and add an operator.” Does that make sense? Think of it as Microsoft sort of forcing the user to make the conscious decision to follow a prescribed method for using this particular task.
At this point in the flow, you want to select the operator we already set up. Under that, you can see that there are boxes for subject and body. Sounds like an e-mail, doesn’t it? That’s because it is. Why else would we use the operator that we already set up? Now, granted, in the operator settings, we can specify e-mail, net send, or pager notifications. What happens if we don’t specify an e-mail address for an operator, but choose them to be notified here? Short answer: you can’t. This task is specifically for e-mail addresses. Want proof?
Let’s quickly set up two more operators. Cancel the current open window and right-click Operators (inside of SQL Server Agent; you may need to click Object Explorer at the bottom of the screen to show this) and choose New Operator… Enter the information shown in Figure 12-5. We will call this one Net Send Operator, with the “Net send address” being in the format of DOMAINusername.
Figure 12-5. New Operator
Click OK when you’re done, open another New Operator… window, and enter the information shown in Figure 12-6. We will call this one Pager Operator, with all days selected.
Figure 12-6. New Operator, General tab
Click OK when you’re done. At this point, you should have three operators, as shown in Figure 12-7.
Figure 12-7. Operators
Easy enough. So with the Notify Operators task, let’s determine which of these operators we should choose to notify.
Double-click the Notify Operator Task (it should still be up in SSMS) and notice that only the initial operator is listed, as shown in Figure 12-8.
Figure 12-8. Notify Operator Task
Hmm. That means that none of the others are valid operators in this context. Want more proof? Double-click either the Net Send Operator or the Pager Operator and notice that the e-mail field is blank. I picked the Pager Operator to work with. Copy and paste the e-mail address in the “E-mail name” box. You should now see what is shown in Figure 12-9.
Figure 12-9. Pager Operator properties
So now we have an e-mail address specified for the Pager Operator. Click OK. Go back to the Notify Operator Task and double-click it. Figure 12-10 shows the updated interface.
Figure 12-10. Notify Operator Task (updated)
Would you look at that? The Pager Operator is added.
Therefore, we can positively assert that an operator must have an e-mail address specified in order to be included in the Notify Operator Task in any capacity.
You can remove the Pager Operator and Net Send Operator now by right-clicking and choosing Delete, and then accepting the deletion operation. You should then be left with just the original operator.
On with the task! We left off with the specification of the interface, so update it to look like Figure 12-11.
Figure 12-11. Notify Operator Task (updated)
All we did was add the operator and define a generic subject and body. Click OK at this point.
Notice that our interface has changed to display the information shown in Figure 12-12.
Figure 12-12. Notify Operator Task
This says that we are using our local server connection and Bradley Beard is our operator.
Perfect so far. So what is next? We need to test it. Either we can wait until midnight, when we scheduled it to fire, or we can run it now. Let’s run it now.
First of all, you want to save the plan so far. Once you save it, the Notify Operator Plan.Email Operator job appears in the SQL Server Agent ➤ Jobs folder in SSMS, as shown in Figure 12-13.
Figure 12-13. SQL Server Agent Jobs
There are other jobs, but I want to focus on just this one for now. Right-click it and choose Start Job at Step… and watch what happens. Figure 12-14 shows what to expect.
Figure 12-14. Success!
Success! I love it. If you got an error like the one shown in Figure 12-15, read on. If not, skip ahead to the end of this chapter.
Figure 12-15. Or not…
It failed, you say?! Now why would it fail? In a nutshell, it failed because we haven’t set up a profile for the operator yet. Let’s look at how to do that now.
Creating an Operator Profile
We need to create an operator profile for our job to be able to run. Double-click Database Mail and click the “Manage profile security” radio button, as in the interface shown in Figure 12-16.
Figure 12-16. Select Configuration Task
Click Next to continue.
You are brought to a screen that has Public Profiles and Private Profiles as tabs across the top of the window. The default screen is Public Profiles, as shown in the interface in Figure 12-17.
Figure 12-17. Manage Profile Security
At this point, click the Public check box and change the Default Profile option to Yes. Your interface should look like Figure 12-18.
Figure 12-18. Manage Profile Security (updated)
Click Next to continue, and then click Finish to wrap it up. You should get a Success message, as shown in Figure 12-19. Close the window, right-click the job again, and choose Start Job at Step… to see what happens next.
Figure 12-19. Success! (again)
Check your inbox now. Hey, look at that! Pretty fancy, huh?
Go ahead and rename the job to Notify Operator and update it as we did in the previous chapters. At this point, you should have all the jobs shown in Figure 12-20.
Figure 12-20. SQL Server Agent Jobs
Your Maintenance Plans folder should look as shown in Figure 12-21.
Figure 12-21. Maintenance Plans
Summary
Let’s recap this chapter really quickly.
We learned about the Notify Operator Task.
We learned that the operator must have an e-mail address to be notified using the Notify Operator Task.
We learned that the Database Mail profile security needed to be enabled for this to work correctly.
We confirmed our settings by receiving an e-mail.
Excellent work so far. Really, you’ve done a great job if you’ve gotten this far in one piece; even more so if you’ve gotten here in one sitting. Press onward and we will finally bring everything to a close with the creation of our custom maintenance plan.