Database Mail Tip: Notifying Operators vs. Sending Emails

In the past few months I've provided a number of blog posts about ways to configure and use SQL Server's Database Mail functionality to proactively send alerts and notifications. However, one way that I've recently found to be more effective is to stop sending emails/notifications via Database Mail to email addresses and to instead start sending them to Operators.

Related: Avoiding 'Failed to notify via email' errors

I've long maintained that any alerts or emails sent via SQL Server (or any other automation) should be sent to aliases—not individuals. That way it's much easier to just modify "membership" in [email protected] than to go in and make changes to specific email addresses when employee changeover occurs.

But an even better way is to use a further bit of abstraction—an Operator—instead of mere aliases. Or, in other words, think of the SQL Server Agent's ability to create different operators kind of like the benefit of creating roles—you can create a couple of different "profiles" and then manage "membership" in those "roles" as needed. So, for example, you can create an operator called "Alerts" to be notified of job failures, degradation of performance conditions, errors, and so on. But if you've got an environment where folks in various departments need to be notified when various jobs (that are managing business logic instead of maintenance tasks) complete (or fail), then you could create additional operators such as Billing or CustomerService or Analytics.

You can then assign the Email name field for these operators to various aliases within your organization (e.g., [email protected]) and then, if you ever need to add "Managers" or whatever, you can just go in and include that alias in the same Email name field, as shown below:

From there, you can specify this Operator from any "Operator" drop-downs as necessary or send email directly to Operators (instead of email addresses/aliases) using code such as the following:

EXEC msdb..sp_notify_operator@profile_name = N'General',  -- email profile@name = 'CustomerService', -- operator name@subject = N'Such and Such Report is Done',@body = N'Body of the email/notification goes here.';GO

Overall, sp_notify_operator is fairly obvious/logical to use—the only thing that might not seem that intuitive right away is the @profile_name parameter, which is the Database Mail profile you'd like to use to send the email.