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.
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
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.
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.
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
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.
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.
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
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.
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.