2016-06-21

SQL Server: Rolling Your Own Alerts

If you're familiar with SQL Server Alerts, this will probably look familiar:

SQL Server Alerts

Taking a look at the [msdb] database, I see there are some Service Broker objects created:


You can mimic some (maybe all?) Alerts via Queues, Services, and Event Notifications. For instance, this script will create Event Notifications for EVENTLOG events.

CREATE QUEUE queEventLogNotification
GO

CREATE SERVICE svcEventLogNotification
ON QUEUE queEventLogNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

CREATE EVENT NOTIFICATION enEventLogEvents
ON SERVER
WITH FAN_IN
FOR EVENTLOG 
TO SERVICE 'svcEventLogNotification', 'current database';
GO

ALTER QUEUE dbo.queEventLogNotification 
WITH 
STATUS = ON
GO

Next, I generate the EVENTLOG event by logging an error to the event log:

RAISERROR ('This is a test.', 11, 1) WITH LOG;

Then, if I query the related queue, I can see the EVENTDATA() of the EVENTLOG event that just occurred:

SELECT *, CAST(message_body AS XML) AS message_body_xml
FROM dbo.queEventLogNotification
WHERE [service_name] = 'svcEventLogNotification'
GO

Here is the XML data for the event:

<EVENT_INSTANCE>
  <EventType>EVENTLOG</EventType>
  <PostTime>2016-06-21T16:36:41.680</PostTime>
  <SPID>67</SPID>
  <TextData>Error: 50000 Severity: 11 State: 1 This is a test.</TextData>
  <BinaryData>UMMAAAsAAAAPAAAASQBOAEYALQBXAFMAUwAwADAAOQBcAEQAQgBBAAAACAAAAEQAYgBhAEQAYQB0AGEAAAA=</BinaryData>
  <DatabaseID>5</DatabaseID>
  <TransactionID />
  <NTUserName>MysteryUser</NTUserName>
  <NTDomainName>MysteryDomain</NTDomainName>
  <HostName>MysteryServer</HostName>
  <ClientProcessID>6128</ClientProcessID>
  <ApplicationName>Microsoft SQL Server Management Studio - Query</ApplicationName>
  <LoginName>MysteryDomain\MysteryUser</LoginName>
  <StartTime>2016-06-21T16:36:41.680</StartTime>
  <Severity>11</Severity>
  <ServerName>MysteryServer\DBA</ServerName>
  <State>1</State>
  <Error>50000</Error>
  <DatabaseName>DbaData</DatabaseName>
  <LoginSid>AQUAAAAAAAUVAAAA53DCrRi9KvnePLdohQQAAA==</LoginSid>
  <RequestID>0</RequestID>
  <EventSequence>211694</EventSequence>
  <IsSystem />
  <SessionLoginName>MysteryDomain\MysteryUser</SessionLoginName>
</EVENT_INSTANCE>

I can do something similar to mimic the Alerts based on error number (ie Error 823, Error 824, Error 825) using Event Notifications for EXCEPTION events. WMI Alerts can be mimicked too--it seems there is always a corresponding SQL Server Event. For example, Event Notifications for backup/restore events.

So many questions...

  1. Are SQL Server Alerts a simple (or modified) wrapper around Event Notifications?
  2. If I "roll my own" Event Notifications, will they be less efficient than the "native" SQL Server Alerts?
  3. Can you create an Event Notification that mimics a SQL Server performance condition alert?
  4. Am I nuts for considering any of this?


**UPDATE**

I started looking at stored proc [msdb].[dbo].[sp_add_alert] to see if I could get a better understanding of what SQL Server was doing. From what I could tell, that stored proc called some other stored procs. It looks like toward the end of the call stack, there is a call to [master].[dbo].[xp_sqlagent_notify]. It's my understanding that the "xp_" prefixed stored procs are undocumented. I'm also thinking there's not a readily available way for me to examine the code of that proc. It feels like I've hit a dead end.


SHARE