SQL Server supports the notification of administrators through operators. Operators are aliases for users or groups that can receive notifications when jobs complete or alerts have been raised. Each SQL Server instance can have its own set of defined operators.
Today, I am going to walk you thru the following tasks in Transaction SQL.
- Adding an operator
- Updating an operator
- Deleting an operator
- Defining a fail-safe operator
To define an operator, one needs to know the email address of the user and what time he is responsible for the SQL Server in question. The start and end times are expressed using a twenty four hour clock. Days of the week are expressed by summing individual days expressed as powers of two.
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday
The example below adds me as an operator. It is important to note that a pager address was supplied. With today’s mail system and hand held devices; it is easy to find out the SMTP text address of the phone given the provider.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- Add operator with email/pager from 8am-to-5pm weekdays EXEC msdb.dbo.sp_add_operator @name = N'John Miner', @enabled = 1, @email_address = N'john@craftydba.com', @pager_address = N'14015551212@mobile.att.net', @weekday_pager_start_time = 080000, @weekday_pager_end_time = 170000, @pager_days = 62 ; GO -- Show the new entry EXEC msdb.dbo.sp_help_operator @operator_name = N'John Miner' ; GO |
To view the settings of an operator, use the sp_help_operator stored procedure.
Most things in life are never perfect on the first try. Therefore, TSQL supplies an update stored procedure to modify an existing operator. The example before disables the operator we just defined.
|
1 2 3 4 5 6 7 8 9 |
-- Disable the operator EXEC msdb.dbo.sp_update_operator @name = N'John Miner', @enabled = 0; -- Show the updated entry EXEC msdb.dbo.sp_help_operator @operator_name = N'John Miner' ; GO |
Last but not least, people and groups change over time. It will be neccessary to remove an operator when it is no longer needed. See sample code below.
|
1 2 3 4 5 6 7 8 |
-- Remove the operator EXEC msdb.dbo.sp_delete_operator @name = 'John Miner' ; GO -- Show the deleted entry EXEC msdb.dbo.sp_help_operator @operator_name = N'John Miner' ; GO |
I suggest that you define operators using AD groups and allow the Active Directory administrator deal with the maintenance overhead. Also, use a 24 x 7 notification in which the person on call reacts to the notifications and all other DBA’s will be notified but are not responsible at that time.
Many of these server level services are defined with tables in the msdb database. Operators are not exception to this fact since the msdb.dbo.sysoperators table contains information
The fail-safe operator(s) are responsible if the alert could not be paged or SQL Server Agent cannot access system tables in the msdb database. The operator can be setup thru the SQL Server Management Studio (SSMS) under SQL Server Agent Properties, Alert system, Enable fail-safe operator. Truth be told, the information for the fail safe operator is stored in the operating system registry.
The following stored procedure in the master database can be called to define the fail-safe operator.
|
1 2 |
-- Define a fail-safe operator EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator=N'John Miner'; |
The key points to remember from this article are operators are defined to recieve notifications from alerts and jobs. TSQL supplies system stored procedures to add, modify and delete these operators. Always define a fail-safe operator just in-case the msdb database is having issues.
I will continue next time by talking about setting up system alerting and notifications.
I wish to explain my personal fascination with your goodness giving support to people nobody want assist on this issue. Your individual resolve for obtaining the information throughout ended up being certainly informative and also have frequently enabled many people much like me to achieve their own objectives. Your individual invaluable information means a lot a person like me and further much more to my personal office workers. Many thanks a lot; from all people.
Great! thanks for the share!
Arron
It is hard to find knowledgeable individuals on this particular subject, but you seem like you realize perfectly what you are covering! Many thanks
I am not sure where you’re getting your information, but great topic. I needs to spend some time learning more or understanding more. Thanks for wonderful information I was looking for this info for my mission.
Greetings, do you have a Twitter page that I may follow? Thanks
Loving the information on this web site , you have done outstanding job on the content .
Appreciation to my father who told me about this web site, this blog is in fact awesome.
Thanks so much for providing individuals with an extremely terrific chance to discover important secrets from this web site. It really is so great plus packed with amusement for me personally and my office friends to visit your website on the least thrice every week to read through the latest items you have. And definitely, I’m also usually impressed concerning the surprising secrets you give. Certain 2 tips in this article are in reality the most efficient I have had.
An exceptionally nice blog you could have here. I am keen your typing style and it’s very informative posts, I shall get back here again!
One of the better blog posts I’ve come across in a number of years. Thank you for posting I really love the value you’ve presented.
Somebody necessarily help to make significantly posts I might state. That is the very first time I frequented your website page and to this point? I amazed with the analysis you made to make this actual publish extraordinary. Wonderful activity!
Keep up the fantastic work, I read few posts on this internet site and I think that your site is rattling interesting and holds lots of excellent info.
27. Wow, superb weblog format! How lengthy have you ever been running a blog for? you make blogging look easy. The entire glance of your website is fantastic, as smartly as the content!
I greatly like this article. If you will post more…
As soon as I discovered this internet site I went on reddit to share some of the love with them.
Love your blog!