Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Configuring Alerts for SQL Server AlwaysOn Availability Groups

MSSQLTips author Derek Colley By:   |   Read Comments (4)   |   Related Tips: More > AlwaysOn Availability
Problem

You need to configure alerts to notify your DBA or out-of-hours team if there is a problem with your SQL Server replication or high-availability groups; for example if an automatic failover occurs, or an unacceptable number of queued transactions builds up at the distributor. Standard alerts by severity are too coarse; you are looking to build a customized set of alerts to convey accurate, specific information for each event.  Check out this tip to learn more.

Solution

For this solution I am going to first explore the standard alert functionality offered by SQL Server Agent and show how to configure alerts for normal severity ranges. I'll then look at sys.messages to examine the ranges of alerts I need for AlwaysOn availability groups, and show how to filter on a particular severity by keyword. I'll also show a method of writing a script that will churn out the code to create new alerts automatically with little manual effort.

Disclaimer: All views here are my own and some readers may implement their alerting mechanisms differently. I do not claim this is the best approach - merely mine - and it is based on my interpretations of best practice and my experience as a DBA. YMMV!

SQL Server Alerts - Introduction

The alerts feature is provided in SQL Server Agent and serves as a mechanism to allow certain logged events to be captured by the Agent and actions taken after they are captured. These actions can be the execution of a job (and consequently a stored procedure); notification by email of one or more operators, or notification by net send or pager of one or more operators. This is useful if you need up-to-the-minute information on the health of your database systems, for example if you or your team are required to provide 24-hour support or you operate an OLTP system with minimal tolerated downtime.

Alerts are normally configured by the DBA alongside other features such as operators and Database Mail. These three keystones of the alerting feature are detailed extensively by other excellent writers so I will not detail them here. A walkthrough is provided by David Bird here (http://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/) and is a good starting point for those who wish to know more. Alternatively consult Books Online for detailed information about the SQL Server Agent.

Configuring a Basic SQL Server Agent Alert

Below you'll see how to configure a simple SQL Server Agent alert for all events categorized as Severity 16, notifying the DBA by email if an event is detected.

Using Object Explorer in SQL Server Management Studio, expand SQL Server Agent and right-click on Alerts. Click New Alert... (if you can't expand SQL Server Agent, ensure the SQL Server Agent service is started for the instance you are using):


Alerts - Introduction

Fill in the fields in the first three tabs as follows:

Configuring a Basic Alert

configure a simple SQL Server Agent alert

Using Object Explorer in SQL Server Management Studio, expand SQL Server Agent

Hit OK. Note I've already configured Database Mail and an operator called 'Derek Colley'. Now test your alert using the following syntax:

RAISERROR('This is a test Severity 16 alert, please ignore.',16,1) WITH LOG;

If you don't receive the error (and you've just configured Database Mail), right-click on SQL Server Agent in Object Explorer and select Properties. On the Alert System tab, tick the 'Enable Mail Profile' box, press OK then restart SQL Server Agent (for good measure):

ight-click on SQL Server Agent in Object Explorer

And here is my error in my inbox:

restart SQL Server Agent

Configuring Alerts for SQL Server AlwaysOn availability Groups - Filter by Keyword

As you've noticed in the section above, one way of configuring alerts is to use severity levels. However as the database product has expanded through versions 2000 to 2012, more and more potential error messages have been added to sys.messages (formerly sys.sysmessages) making the creation of specific alerts for specific error numbers a daunting task. For some classes of errors (independently of severity) you will note that various consecutive ranges of error number are used. This can be seen below.

Let's have a look at sys.messages for alerts relating to the word 'availability' (as in, 'AlwaysOn / AlwaysOn availability'):

SELECT message_id [error_number], severity, text
FROM sys.messages 
WHERE text LIKE ('%availability%')
AND  is_event_logged = 1;

This returns 48 rows in SQL Server 2012. You can further filter by language_id if required - English is 1033 - by adding it to the WHERE clause. This will return 17 rows for language_id = 1033.

You will immediately note that most of the messages are marked as Severity 10. Severity 10 is an *informational* message only and, while logged, will not normally trigger a database alert as most DBAs do not want an email on every Severity 10 - they are normally benign. Annoyingly, however, Microsoft has categorized some otherwise quite interesting errors that may indicate cluster or HA problems as Severity 10. So how do you capture these errors?

Go back to Object Explorer and right click Alerts, then hit New Alert... You will get the screen below. This time, tick 'Raise alert when message contains:' then type 'availability' (without quotes) in the text field. Ensure Severity 10 is highlighted in the drop-down menu above. Remember to fill in the Response and Options tabs to notify the DBA as shown in the basic example above, then click OK. You can test the alert using the following syntax:

RAISERROR('This is a test availability groups alert',10,1) WITH LOG;

You should see the alert appear in your inbox. You can customize the filter text to suit you - you may want to filter on 'replication', 'cluster', 'corruption', etc.

Configuring Alerts for AlwaysOn availability Groups - Specific Error Numbers

There's another way of doing this too, depending on how finicky you are about having individual alerts for individual events. This isn't strictly necessary, since choosing to include the error text in the alert in the New Alert dialog should ensure the error text makes it to your inbox. However, you may wish to have specific alerts so, for example, you can customize the subject field in the outbound email, or handle specific alerts differently depending on the exact problem.

First, let's pick some alerts from sys.messages that look promising:

Configuring Alerts for AlwaysOn availability Groups - Specific Error Numbers

Here's my code, which will create a script that you can run to create a new alert for each error message of interest to you (caution: dynamic SQL!)

SET NOCOUNT ON

-- first create a temporary table to store your target error numbers
DECLARE @errorNumbers TABLE ( ErrorNumber VARCHAR(6) )
INSERT INTO @errorNumbers
 VALUES ('35273'),('35274'),('35275'),('35254'),('35279'),('35262'),('35276')

-- get the correct DB context
PRINT 'USE [msdb]'
PRINT 'GO'
PRINT '/* *************************************************************** */ '

-- use a cursor to iterate over each error number (yes, I know)...
DECLARE  @thisErrorNumber VARCHAR(6)

DECLARE  cur_ForEachErrorNumber CURSOR LOCAL FAST_FORWARD
FOR SELECT ErrorNumber FROM @errorNumbers

OPEN  cur_ForEachErrorNumber

FETCH NEXT FROM cur_ForEachErrorNumber INTO @thisErrorNumber
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT 
  'EXEC msdb.dbo.sp_add_alert @name=N''HA Error - ' + @thisErrorNumber + ''',
  @message_id=' + @thisErrorNumber + ', 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1, 
  @job_id=N''00000000-0000-0000-0000-000000000000''
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N''HA Error - ' + @thisErrorNumber + ''', 
    @operator_name=N''Derek Colley'', @notification_method = 1
  GO '
 PRINT '/* *************************************************************** */ '
 FETCH NEXT FROM cur_ForEachErrorNumber INTO @thisErrorNumber
END

CLOSE  cur_ForEachErrorNumber
DEALLOCATE cur_ForEachErrorNumber

Now change the query output to text and execute the query. You'll get the following output:

USE [msdb]
GO
/* *************************************************************** */ 
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35273',
  @message_id=35273, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1, 
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35273', 
    @operator_name=N'Derek Colley', @notification_method = 1
  GO 
/* *************************************************************** */ 
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35274',
  @message_id=35274, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1, 
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35274', 
    @operator_name=N'Derek Colley', @notification_method = 1
  GO 
/* *************************************************************** */ 
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35275',
  @message_id=35275, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1, 
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35275', 
    @operator_name=N'Derek Colley', @notification_method = 1
  GO 
/* *************************************************************** */ 
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35254',
  @message_id=35254, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1, 
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35254', 
    @operator_name=N'Derek Colley', @notification_method = 1
  GO 
/* *************************************************************** */ 
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35279',
  @message_id=35279, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1, 
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35279', 
    @operator_name=N'Derek Colley', @notification_method = 1
  GO 
/* *************************************************************** */ 
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35262',
  @message_id=35262, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1, 
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35262', 
    @operator_name=N'Derek Colley', @notification_method = 1
  GO 
/* *************************************************************** */ 
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35276',
  @message_id=35276, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1, 
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35276', 
    @operator_name=N'Derek Colley', @notification_method = 1
  GO 
/* *************************************************************** */ 

Now execute the script, and you'll see your new HA alerts created under SQL Server Agent / Alerts in Object Explorer:

you'll see your new HA alerts created under SQL Server Agent / Alerts in Object Explorer

We can test this alert using the following syntax:

RAISERROR(35254,10,1) WITH LOG;

You should see the email hit your inbox. Note: Books Online warns that errors below Severity 19 (i.e. that are not written to the Windows Application Log) will not be alerted on. By specifying a particular alert by number, we have overridden this requirement - to verify, execute:

RAISERROR(35254,10,1);

And you will see another email dispatched to your inbox.

Next Steps


Last Update: 6/11/2013


About the author
MSSQLTips author Derek Colley
Derek Colley is a SQL Server DBA based in Manchester, UK, with a focus on performance management and data architecture.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, March 26, 2014 - 10:21:41 AM - Roberto Read The Tip

Good Article. I'm modified generating script so you can choice Categories based on message contained words and group Alerts based on Categories (availability, ...) and Severities. So if you don't want monolitic Alert nor an Alert for every message id....

 

SET NOCOUNT ON

 

-- define operator

DECLARE  @myOperator VARCHAR(20) = 'admin operators'

 

-- first create a temporary table to store your target message contained string

DECLARE @strContained TABLE ( ContainedStr VARCHAR(20) )

INSERT INTO @strContained

 VALUES ('availability'),('replication'),('cluster'),('corruption')

 

-- get the correct DB context

PRINT 'USE [msdb]'

PRINT 'GO'

PRINT '/* *************************************************************** */ '

 

-- use a cursor to iterate over each string

DECLARE  @thisContainedStr VARCHAR(20)

DECLARE  cur_ForEachContainedStr CURSOR LOCAL FAST_FORWARD

FOR SELECT ContainedStr FROM @strContained

OPEN  cur_ForEachContainedStr

FETCH NEXT FROM cur_ForEachContainedStr INTO @thisContainedStr

WHILE @@FETCH_STATUS = 0

BEGIN

 

DECLARE @severities TABLE ( Severity VARCHAR(20) )

INSERT INTO @severities (Severity)

 SELECT distinct(severity) FROM sys.messages WHERE text LIKE ('%'+@thisContainedStr+'%') and language_id = 1033 and is_event_logged = 1 order by severity

 

 -- use a cursor to iterate over severities

DECLARE  @thisSeverity VARCHAR(20)

DECLARE  cur_ForEachSeverity CURSOR LOCAL FAST_FORWARD

FOR SELECT Severity FROM @severities

OPEN  cur_ForEachSeverity

FETCH NEXT FROM cur_ForEachSeverity INTO @thisSeverity

WHILE @@FETCH_STATUS = 0

BEGIN

 

PRINT

  'EXEC msdb.dbo.sp_add_alert @name=N''HA Error - ' + @thisContainedStr + ' - Sev.' + @thisSeverity + ''', 

@message_id=0, 

@severity=' + @thisSeverity + ', 

@enabled=1, 

@delay_between_responses=0, 

@include_event_description_in=1, 

@event_description_keyword=' + @thisContainedStr + ', 

@category_name=N''[Uncategorized]'', 

@job_id=N''00000000-0000-0000-0000-000000000000''

GO

EXEC msdb.dbo.sp_add_notification

 @alert_name=N''HA Error - ' + @thisContainedStr + ' - Sev.' + @thisSeverity + ''', 

 @operator_name=N''' + @myOperator + ''',

 @notification_method = 1

GO '

 

 PRINT '/* *************************************************************** */ '

 

 

FETCH NEXT FROM cur_ForEachSeverity INTO @thisSeverity

END

CLOSE  cur_ForEachSeverity

DEALLOCATE cur_ForEachSeverity

 

DELETE FROM @severities

 

FETCH NEXT FROM cur_ForEachContainedStr INTO @thisContainedStr

END

CLOSE  cur_ForEachContainedStr

DEALLOCATE cur_ForEachContainedStr


Wednesday, March 26, 2014 - 10:58:01 AM - Roberto Read The Tip

I'm sorry... a little correction for line with @event_description_keyword:

 

Change:

   @event_description_keyword=' + @thisContainedStr + ', 

into

    @event_description_keyword=N'' + @thisContainedStr + ', 

 

 

 


Wednesday, March 26, 2014 - 12:34:44 PM - Roberto Read The Tip

I am sorry ... this is the correct line, with correct quotes:

 

event_description_keyword @ = N'' '+ @ thisContainedStr +''',


Tuesday, June 17, 2014 - 9:36:54 AM - Ranga Read The Tip

Nice article Derek, as always!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.