Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Notification Services Architecture

By Joe Webb,

Like most services or applications, Microsoft SQL Server Notification Services is not really one monolithic application. It should be thought of as a series of smaller components that, when girded together in a precise manner, work in concert to produce a notification application. A good understanding of each of the components and how they interact with one another will provide a solid foundation for the rest of this book and for developing your own notification applications. This article quickly surveys the SQL Server Notification Services architecture and the underlying components.

Architecture at a Glance

In essence, every notification application is concerned with four fundamental things: subscribers, subscriptions, events, and notifications. However, upon further examination, you’ll see that each of these aspects is composed of one or more underlying components. Figure 2.1 shows how these pieces fit together to form SQL Server Notification Services.


Figure 2.1:
Overview of SQL Server Notification Services Architecture.

Subscribers and Subscriptions

People that sign up to use our notification application are known as subscribers. They have indicated to us that they want to be notified with specific information when an event occurs. Subscribers use a subscription management application to define the events of interest to them and to indicate how and when they would like to be notified of the event. The subscription management application creates a subscription for the subscriber in the notification application.

Frequently, the subscription management application is a web site that we have created to complement our notification services application. For example, we may create a web site that allows subscribers to sign up to receive an email every morning at a specific time, notifying them of the day’s weather forecast for their city.

Subscription management applications can be manifested in other ways, too. Subscribers can be added to the notification application as part of a traditional Windows application or even via a VBScript. For example, we may enhance a Windows-based order entry application to automatically create subscribers and their subscriptions in the notification application when a new customer is entered or a product is sold.

Note: Subscribers are not limited to individuals. Another application can be the recipient of a SQL Server Notification Services notification.

Regardless of how subscribers are created, the notification application needs an additional piece of information: how are we to communicate with the subscriber? Since the subscriber is typically an individual, the notification application requires a way to notify the subscriber electronically. We do this by creating one or more subscriber devices for a subscriber. Typically, the device is an email address, but additional means of communication, such as voice, FAX, email, SMS/wireless, and .NET Alerts are available through third-party vendors. Visit the SQL Server Notification Services web site at www.microsoft.com/sql/ns for a list of partners.

Once we know the subscriber, the subscriber device, and the information that is of interest to the subscriber, we can create a subscription in the notification application. A subscription combines a subscriber, the subscriber’s device, and the criteria by which notifications will be triggered.

Subscription management applications use the Subscription Management Objects, provided with SQL Server Notification Services, to submit the information entered by the subscribers to the system. Within Notification Services, subscribers and subscriber devices are stored in a central database for the Notification Services instance. This allows multiple notification applications to gain access to this information. All subscription-specific information is stored in the application database. This storage architecture allows subscribers to provide their information once and use it to sign up for multiple notifications in multiple applications if they wish.

Events

When people sign up for a notification application, they ask to be notified when a particular event occurs. In order for this to be possible, SQL Server Notification Services must be made aware of events. Thus, we need some way to provide SQL Server Notification Services with the knowledge of an external event. The components responsible for this activity are known as event providers.

Event providers monitor and obtain information from external sources. These sources can vary widely depending on the nature of the events and the type of event provider in question. Some sources, such as data from the stock market, can produce an almost continuous stream of events for a period of time. Other events, such as the final score of a professional hockey game, occur only intermittently.

Two standard, built-in event providers are installed with SQL Server Notification Services: the SQL Server event provider and the File System Watcher event provider. Each of these is quite useful and interesting in its own right.

The SQL Server event provider executes a developer-defined Transact-SQL query at predefined intervals. The objective is to recognize external events from another data source. The data source can be another SQL Server database in the same instance of SQL Server, a remote SQL Server database, or practically any linked data source with which SQL Server can communicate via an OLEDB Provider using heterogeneous queries.

The File System Watcher event provider monitors a predefined file system folder for the addition of XML-formatted files that contain event data. Rather than executing at predefined and configurable intervals, it runs continuously and creates events within SQL Server Notification Services almost instantly when a new event file is found.

If neither of these standard event providers suits your needs, you can create your own custom event providers using one of the four APIs included in SQL Server Notification Services: the Managed API, the COM API, the XML API, and the SQL API.

Tech Tip: When developing a custom event provider, consider using a .NET programming language, such as Visual C# or Visual Basic .NET, and the Managed API. This combination will yield significantly better performance than custom event providers developed using a COM-based programming language with the COM API.

Event providers can exist inside or outside of SQL Server Notification Services. Those that reside within SQL Server Notification Services are known as hosted event providers. They are controlled by the configuration parameters that govern SQL Server Notification Services. The SQL Server event provider is a hosted event provider that runs within the confines and configuration of the SQL Server Notification Services process.

Independent event providers are external to SQL Server Notification Services and are responsible for maintaining their own scheduling. An example of an independent event provider would be a custom application that continuously reads reports from the Associated Press Newswire and interjects certain events into SQL Server Notification Services via the XML API.

Notifications

Once we have data from the first three components (subscribers, subscriptions and events), we can begin evaluating the information, looking for the intersection of the subscription data and the event data. This procedure is known as subscription processing and it creates notifications.

Some notification applications are designed to initiate notifications almost as soon as a batch of event data is received. These event-driven notification applications do not need to consider historical data; they are only concerned with getting the latest information out to the subscribers. Rules for these types of notification applications are straightforward. They simply compare event data in the current batch to the subscription data.

However, some notification applications must consider historical information as part of subscription processing. These notification applications typically fall into two broad categories:

► Chronicle-based, event-driven subscriptions

► Scheduled subscriptions

With chronicle-based, event-driven notification subscriptions, subscribers want to be notified when an event occurs. However, they only want to be notified the first time that event occurs. For example, consider a notification application that monitors production equipment in a factory. It constantly receives information (events) and then periodically processes subscriptions based on the latest information. Once a critical indicator of productivity has been exceeded, subscribers want to receive an email notifying them of the event. Fine. Remember that events are continually being inserted into the notification application. If the subscription processing rule interval (known as the quantum duration) is set to three minutes, subscribers will receive an email at each interval notifying them of the exceeded threshold. Not so fine! We only want to notify subscribers the first time the threshold has been exceeded; we don’t want to fill up their inboxes.

Similarly, we may want to notify subscribers with summary data at certain times of the day. In the preceding example, we may want to notify our subscribers at the end of each shift. The notifications would indicate the number of times the threshold had been exceeded. These types of notifications are known as scheduled subscriptions. With scheduled subscriptions, subscribers are notified at a defined time only if an event has been entered that matches their subscription information. Notification Services provides a rich way to declare these schedules, including flexible recurrence options.

In each of the examples, historical data must be considered. Historical event data can be kept in a chronicle table. As events are entered in SQL Server Notification Services, the chronicle table can be updated to contain historical information, such as high watermarks or instance counters.

The Generator

Subscription processing is performed by a component known as the generator. Periodically, the generator executes a rule that compares each of the subscriptions to a batch of events that have been entered since the last time the generator fired.

Rules indicate the relationship between the subscription data and the event data. They are defined as Transact-SQL queries by the developer of the notification application. The rules can also include additional processing logic, such as flagging processed records.

When the generator fires a rule, the Transact-SQL query can examine the event table, the chronicle table, and the subscription information to look for matches. When matches are found, entries are written into a SQL Server Notification Services-controlled notification table where they await distribution. Figure 2.2 illustrates this process.


Figure 2.2:
Subscription Processing.

The Distributor

Another SQL Server Notification Services component, called the Distributor, periodically executes to facilitate the delivery of the queued notifications. When it fires, the distributor looks to the notification batches table in the SQL Server Notification Services application database for unprocessed notifications. If any are found, the distributor first applies any aggregations that may be needed.

Next, the distributor considers the subscriber device information to determine its formatting and addressing requirements. It also considers the subscriber’s locale (English, German, etc.) and selects a content formatter to transform the raw data into a format suitable for end-user viewing.

After the distributor formats the notifications, it passes them to the appropriate delivery channel. A delivery channel is the conduit through which SQL Server Notification Services routes a notification to a delivery service such as a SMTP server. Figure 2.3 portrays the architecture behind notification delivery. As shown in the figure, SQL Server Notification Services installs three standard delivery channels and protocols: the Extensible HTTP Protocol, the SMTP Protocol, and the File Protocol. If the standard built-in delivery channels and protocols do not provide enough flexibility, custom delivery protocols may be developed using any .NET programming language.


Figure 2.3:
SQL Server Notification Services Delivery Architecture.

Tech Tip: Formatting and delivering notifications can be a very resource-intensive process. To optimize performance, consider installing the distributor on a dedicated server.

Summary

The key facets of any notification application are subscribers, subscriptions, events, and notifications. In SQL Server Notification Services, each of these facets is represented by one or more underlying architectural components. These components work together to process subscriptions and generate notifications.

SQL Server Notification Services was designed from the ground up to be a highly scalable, yet very flexible add-on to Microsoft SQL Server. Understanding its architecture will allow you to leverage its scalability and flexibility to produce effective notification applications.

This article has been adapted from The Rational Guide to: SQL Server Notification Services by Joe Webb from Rational Press, ISBN 0972688811. Used by permission.

Total article views: 11604 | Views in the last 30 days: 3
 
Related Articles
ARTICLE

Getting Started with SQL Server Event Notifications

Learn the basics of Event Notifications from MVP Jonathan Kehayia. This article will show you how to...

FORUM

How notificate create database event

How notificate create database event

BLOG

New White Paper: SQL Server Extended Events and Notifications

Download the new white paper: How to Use SQL Server’s Extended Events and Notifications to Proacti...

FORUM

How to create server notification?

server notification for admin

FORUM

Service Broker/Query Notifications/Servicing Application Caches

Query Notification and OLTP DB Servers

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones