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.

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.

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.

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.