SQL Server

Build Apps that Provide Real-Time Information and Customized Content Using SQL Server Notification Services

Mark Brown

This article assumes you're familiar with T-SQL, XML, Web Services, and .NET

Level of Difficulty123

SUMMARY

The adoption of wireless devices continues to spread unabated, and organizations are looking for new ways to get in touch with customers through these new mobile devices. In the past, unsuccessful ideas such as push technology were used to send targeted information to customers. Now, SQL Server Notification Services uses the SQL Server 2000 database engine and the .NET Framework to promote a new breed of notification applications that will allow relevant, consensual communications to be sent to any subscriber device.

Here the author provides an architectural overview of the core features that make up SQL Server Notification Services. Along the way he discusses how they can be used for pushing Web content.

Contents

Business Perspective on the New Market
Leveraging the New Market Dynamic
SQL Server Notification Services Architecture
Subscription Management App Architecture
Subscription Management API
Event Classes and the ADF
Event Collection Architecture
Event Collection Models
Standard versus Custom Event Providers
Event Chronicles
Notification Generation
Notification Formatting and Delivery Architecture
Notification Services Deployment and Scalability
Scale-up versus Scale-out Configurations
Conclusion

Since the mid-1990s, content and service providers have devised countless schemes for the generation and delivery of content and notifications (or alerts) to users. Unfortunately, the broadcast style of these push technologies has received a bad rap over the years. Many of their delivery methods were poorly implemented and used too much bandwidth, and some messages and content were intrusive and of dubious value.

The limited success of early forms of the push model may be attributed to a lack of standards for notification services. But when combined with unwieldy delivery schemes like polling that didn't scale and a lack of fine-grained control over subscriptions, it's obvious why traditional forms of push were doomed to failure. In addition, most notification services required the use of proprietary client applications or push-enabled Web browsers in order to facilitate message and content delivery, and there was no way possible to guarantee whether the notifications actually reached the intended subscriber.

Subscription options were also quite limited; you essentially had to subscribe to topic-based content or channels such as news, sports, and stocks when the real need was for focused content based on personal interests and custom filter criteria. Personalized content is crucial to any notification application if it is to provide any redeeming value to its subscribers.

Clearly, rolling your own notification application and the services infrastructure necessary to support their delivery is a massive undertaking for any organization, and can be fraught with problems. Because of these numerous limitations, many push applications in use today resort to the ubiquitous and easily implemented e-mail (SMTP protocol) or text messaging (SMS) systems for delivery of notifications and content. However, these still rely upon proprietary event collection, notification generation, and subscription management schemes.

In the last few years, more efficient protocols have been developed for transmitting notifications that are highly scalable, much more reliable, and offer better security. With the widespread use of wireless mobile devices and infrastructure that can provide near real-time delivery of messages (such as SMS, Wireless Application Protocol or WAP push, instant messaging, and Microsoft .NET Alerts), companies are recognizing the need for a rich notification platform that will help them take advantage of these various delivery channels.

The next generation of Internet-enabled wireless devices promises to deliver user location information using integrated GPS. Wireless carriers will be able to deliver user location information securely via Web Services on an opt-in basis, in an effort to ensure privacy. User location information combined with a rich notification platform will enable the next wave of killer notification applications. The list of potential location-aware notification applications is endless.

Business Perspective on the New Market

Common notification scenarios that are used to send content to customers and employees today include reminders and appointment alerts, package tracking and delivery notification, financial or stock market alerts, breaking news, traffic reports, and even sports scores.

Because of the limitations of push, it has been difficult for companies to implement notification applications that are robust enough to serve millions (or even thousands) of subscribers. Furthermore, the lack of a standards-based application platform has made it difficult for developers to build any really compelling notification applications.

Now, with the new changes in mobility and the explosive growth of Web Services, it is becoming easier to reach anyone, anytime, anywhere. With the proliferation of wireless devices such as smart phones, PDAs, and handheld e-mail devices in the marketplace, companies are again looking for new ways to deliver personalized and timely information to their users.

Now, however, the emphasis will be on sending only relevant communications to customers. Furthermore, these notifications will encourage customers to browse your company's Web site looking for additional content or to place an order. This will undoubtedly provide companies with new opportunities for increasing sources of revenue.

Leveraging the New Market Dynamic

SQL Server™ Notification Services is a new application platform that overcomes the limitations of traditional push technologies. The notifcation services platform uses a publisher-subscriber model in which delivery of content is always consensual. Notifications are sent only to individuals who have subscribed to receive them. The messages may be formatted for a variety of device types, and the content of the notification may be personalized in order to reflect the individual preferences of the subscriber. This will enable small businesses and large enterprises alike to exploit this new market dynamic.

The Notification Services platform provides developers with an intuitive development model for building centralized notification applications and deploying these on any scale. It uses a simple declarative programming framework that is based on languages that developers already know, such as Transact-SQL and XML. Furthermore, this platform has been designed to seamlessly integrate with third-party applications, event providers, and delivery services that may be using operating systems other than Windows®. Because the Notification Services platform shields the developer from the complexities associated with generation and delivery, a typical notification application can be built and deployed very quickly.

SQL Server Notification Services Architecture

Let's take a look at the high-level architecture of SQL Server Notification Services to see how notification application developers will be able to maximize productivity by utilizing the rich features of this framework.

The fundamental requirement for every notification application is to be able to collect events, match these events to subscriber preferences (subscriptions), generate and format the notification, and ultimately deliver the message to the subscriber's devices. The Notification Services platform implements all of these features and the plumbing necessary to allow you to quickly create and deploy your own custom notification applications.

In short, the Notification Services platform allows you to store and manage both subscriber and subscription data using the Notification Services API. You can collect events obtained from a variety of external sources using the stock event providers or by creating your own custom event provider. You can also match these events to individual subscriber preferences either upon receipt of the event or on a scheduled basis, and then generate the notification itself. Finally, you can format the notifications according to the subscriber's device type and locale, and then route it to an external delivery service.

A key architectural feature of the Notification Services platform is that the delivery services have been decoupled from the generation of the notification itself. Formatting and delivery of the notifications are relegated to a separate component of the services architecture.

This distributed architecture allows you to partition the components and services necessary to run your notification application across multiple machines. Rather than getting bogged down with platform design issues, SQL Server Notification Services allows you to concentrate on building your notification application logic.

In the following sections, I'll discuss the key architectural features of each of these core components in detail.

Subscription Management App Architecture

Subscriptions are a central feature of any notification application since these determine both what a subscriber receives and when. Subscriptions can also indicate the specific device to which notifications should be routed.

There are two categories of subscriptions. Scheduled subscriptions allow notification delivery to be based on calendar date, time of day, frequency, specific days of the week, or any combination thereof. Event-driven subscriptions deliver all of their notifications immediately upon receipt from the external event source.

Since every notification application is different, you'll need to create a custom application for managing the particular subscribers and their individual delivery preferences. This is known as a subscription management application.

Subscription management applications are typically developed as Web applications using ASP or ASP.NET. However, standard Windows-based applications can also be created using the Notification Services API. Figure 1 illustrates the high-level architecture of a subscription management application.

Figure 1** Subscription Management App **

You should keep in mind that in Notification Services parlance, subscriber data refers to information about the subscriber and their devices, whereas subscription data refers to the information concerning individual event classes that the subscriber is interested in (and related parameters).

Subscription Management API

Notification Services stores the information about the subscriber, device, and subscription in a central Notification Services database. This allows subscriber data to be stored once, while subscription data can vary for each discrete notification app.

The Notification Services API allows you to create and manipulate subscriber data through the use of managed code classes (or through interfaces via COM Interop) and enumerations. These classes greatly simplify the creation of subscription management applications by abstracting database access.

Event Classes and the ADF

In Notification Services, an occurrence or condition that a subscriber wants to be notified about is referred to as an event. In fact, events are the whole basis for creating a notification application.

Before events can be accepted by your application, they must first be defined as event classes using an XML metadata file called an application definition file (ADF). The ADF describes the data and structure of a Notification Services application. For each notification application that you create, you will define one or more event classes inside the ADF.

Figure 2 shows how a new event class called Stock may be created using the ADF. Notice how the data structure for this new event class is declared in the <Schema> section of the <EventClass> node. Also note that the <FieldType> element uses Transact-SQL syntax conventions to describe the type of event data that is to be stored. Likewise, the <FieldTypeMods> section is used to indicate whether the field may accept nulls.

Figure 2 Defining a New Event Class

<EventClasses> <!--Sample Definition of a Stock Event Class. --> <EventClass> <!--Name of the event class. --> <EventClassName>Stock</EventClassName>\ <!--Event fields are defined in the Schema section. --> <Schema> <!--Define the stock symbol field. --> <Field> <FieldName>StockSymbol</FieldName> <FieldType>char(10)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> <!--Define the stock value field. --> <Field> <FieldName>StockValue</FieldName> <FieldType>money</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> </Schema> ••• </EventClass> </EventClasses>

This schema is used by event providers to validate event data. More importantly, when a new notification application instance is generated, these fields are used to automatically create a SQL Server table in the notification application database for persisting event data.

Now that you have a rough idea of how events are defined in a Notification Services application, let's take a look at the event collection architecture.

Event Collection Architecture

The Notification Services platform is able to collect event data from a variety of sources using event providers. Event providers may be implemented as DLLs hosted by Notification Services itself or as external standalone executables. Hosted event providers can run either continuously or according to a schedule.

Event providers are responsible for monitoring an event source and subsequently writing the event data to the notification application event table. There are three different APIs that event providers may use to submit events to the notification application database. The first, a managed code API, allows event data to be written directly to the event table (the same API may be exposed as interfaces using COM Interop). The second, an XML API, allows events in an XML document (or stream) to be bulk-loaded and written to the event table as a single batch. And the third, stored procedures, can be called by the developer to load event data directly from database objects and write these to the event table.

Figure 3** Event Provider Architecture **

Although events are persisted to the event table one row at a time, event providers typically will submit events to Notification Services in units of work called batches. When the event provider commits a batch, the notification application may immediately begin acting on the events in order to start generating notifications. On the other hand, notification applications may also defer the processing of events according to a schedule. Figure 3 illustrates the high-level architecture of an event provider.

Event Collection Models

Event providers can monitor and gather data in a variety of ways. In general, however, most event providers will implement one of two models. Using a Push or Pull Collector, events are either pushed (sent to your provider) or they are pulled from an external source on a scheduled basis (by polling, for example). In a scheduled or event-driven model, the event provider may run periodically according to an application-defined interval. Alternatively, the provider may monitor the event source continuously (or wait for a callback) in order to submit the event to Notification Services as soon as it becomes available.

Standard versus Custom Event Providers

Two standard event providers, File System Watcher Event Provider and SQL Server Event Provider, are included with the Notification Services platform. They allow you to quickly begin development of your notification application logic and experiment with notification generation rules, formatting, and delivery of messages.

The File System Watcher Event Provider monitors files that are added to a file system directory. The only requirement is that the files must be well-formed XML. When a new file is placed in a developer-designated folder, the File System Watcher Event Provider bulk-loads the XML event data into memory using the XML API, which in turn writes the events into the notification application event table. The source file is subsequently renamed to indicate that it has now been processed.

The SQL Server Event Provider uses a developer-defined T-SQL query to retrieve event data directly from a database table. The provider uses the stored procedures mentioned earlier to collect the new event data and write it directly to the application event table. In addition, the event provider also allows for both pre- and post-processing of events within the application-defined collection interval.

Of course, more sophisticated custom event providers may be written using any of the aforementioned APIs. This allows notification application developers to monitor virtually any external event source (such as Web Services, e-mail servers, Web sites) and submit these for processing by the Notification Services platform.

Event Chronicles

Another feature of the event collection architecture is the ability to maintain event chronicles. A complete explanation of how these can be used by a notification application will come in the following section. That said, since an event batch is only processed once during notification generation (except in case of system failure or restart), event chronicle tables can be used to persist (or archive) event data for future processing during the notification generation process.

Notification Generation

Once the raw event data has been collected by the event providers and written to the event chronicle table, subscriptions are ready to be processed.

At the heart of Notification Services is the notification generator, which has the task of matching collected events to subscriptions and determining whether the necessary conditions have been met in order to generate a raw notification. A raw notification contains the unformatted event data, which includes pertinent information required for distribution of the notification such as subscriber and delivery device. Formatting and delivery of the notification itself is actually handled by a separate process (see the "Notification Formatting and Delivery Architecture" section).

These conditions are expressed in terms of an event subscription rule. The rule is actually a developer-created Transact-SQL statement that specifies how events and subscriptions relate to one another and any other criteria that must be satisfied before a notification will be produced. Event subscription rules execute each time the notification generator receives a new batch of events.

Notification generation rules can also use historical event data stored in an application's event chronicle table. This allows for complex state-based rules to be created, since new events can be compared with previously stored events in order to determine if an additional notification needs to be generated (see Figure 4).

Figure 4** Subscription Processing **

Once the notification has been generated, it's still not ready to be sent to the subscriber. Instead, it is written to an internal notification table along with related information about the source event, subscriber, delivery device, and other data needed to facilitate distribution of the message.

In the same way that the event provider writes events in batches for subsequent processing by the notification generator, the generator component also writes notifications that are ready for delivery in batches. The notification distributor in turn will process these batches of raw notification data and transform them into readable notifications that are properly formatted for the particular destination device.

Notification Formatting and Delivery Architecture

At this stage of the process, the raw notification data created by the notification generator has been stored as batches in the notification application tables. After a notification batch is closed by the generator process, the raw notifications are ready for formatting and delivery to subscribers by the notification distributor (see Figure 5).

Figure 5** Notification Format + Delivery **

In order to process the notification batches as quickly as possible, the distributor uses a thread pool to process multiple batches concurrently. Batches consist of all notifications that will be delivered via the same delivery channel. This allows for optimal distribution of workload by partitioning the notification process across multiple logical delivery systems.

Once a batch has been selected by the distributor for processing, notifications that are intended for the same subscriber are aggregated into a single notification, known as a digest notification. The aggregator process is also able to optimize formatting when the notification content is the same (or similar) for multiple subscribers. This allows generic notification data to be formatted only once for a group of subscribers.

The content formatter uses the recipient information (such as subscriber device type, network address, delivery channel, and locale) to determine how the content should be rendered. Locale can be used to automatically format content in the subscriber's native language. Furthermore, knowing the characteristics of the subscriber's device allows content to be formatted and displayed in a proper manner.

When the notification data has been formatted, it is passed to an available delivery channel for transmission to the subscriber. Delivery channels create the protocol-specific notification message and route these messages to an external delivery system. The delivery channel usually implements a well-known protocol such as SMTP; however, custom delivery protocols may be implemented as a class in a managed code assembly and made available to your notification services application.

In the case of multicast notifications, it is up to the delivery protocol implementer to determine whether a single message can be broadcast to all the subscribers or if a separate message will need to be sent to each individual subscriber.

It's also possible to use several delivery channels that implement the same delivery protocol in order to facilitate communications with multiple endpoints and distribute the workload. For example, you may have several protocols that are delivered to a single server. Each delivery channel instance interacts directly with the external delivery service, so the server name and authentication information must be maintained on a per-channel basis in a notification services configuration file.

Another key feature of the notification distribution architecture that's worth mentioning is the ability to handle delivery notification failures. The distributor can attempt to deliver a failed notification based on a developer-defined retry schedule. This retry logic, however, applies to an entire notification batch. If any notification within a batch is unable to be delivered, then delivery of the entire batch will fail.

The application definition file can specify how many delivery retries to attempt for a specific event class. In addition, an expiration age may be specified for notifications that have time-sensitive information. Once a notification batch exceeds the expiration age, the distributor will cease any further delivery attempts.

Notification Services Deployment and Scalability

As I noted at the outset, Notification Services applications are highly scalable, largely due to their underlying distributed services architecture. In addition, every notification application is partitioned into application instances. Each instance has its own registry entries and runs as a separate Windows service. This allows multiple invocations of the same notification application to execute on a single system or in multi-machine configurations.

The scalability of Notification Services is also enhanced by its tight integration with SQL Server. SQL Server enables storage of instance and application data, as well as set-oriented processing of events and subscriptions during the notification generation process.

Because SQL Server has been highly optimized for joins, this integration will ensure excellent performance for your notification applications. Needless to say, there is no substitute for writing efficient T-SQL queries when collecting events or developing your notification generation rules!

Scale-up versus Scale-out Configurations

Through XML-based application definition and configuration files, notification application instances can be tuned in a variety of ways and, if needed, partitioned across multiple machines. Application configuration files also ease deployment and registration chores on separate machines.

Furthermore, many of the notification application components (such as event collection, notification generation, distribution, and delivery) utilize multithreading in order to distribute the workload across multiple processors. This accommodates scale-up configurations whereby performance can be improved simply by adding more memory and CPUs to a single-server machine.

However, in situations where single-machine configurations cannot keep up with the workload, many of the Notification Services components may be distributed across multiple servers. Instance and application data must always reside on a single SQL server, but the event providers, notification generator, and distributor components may all be registered on separate servers.

In order to facilitate these scale-out configurations, the Notification Services runtime environment must be installed on each machine where your notification application will be registered. Deployment is greatly simplified using the XML application configuration and application definition files. These facilitate registration and creation of your notification application instance on each machine.

Conclusion

SQL Server Notification Services allows you to monitor earlier versions of SQL Server as well as various other data sources such as the file system and custom data sources. Notification Services (available for download at https://www.microsoft.com/en-us/download/details.aspx?id=31214) makes it easier to build notification applications that deliver both timely and relevant information.

For related articles see:
Microsoft SQL Server Notification Services Technical Overview
SQL Server Home

Mark Brownis Chief Software Architect for IdentityMine Inc. (https://www.identitymine.com) based in Tacoma, WA. IdentityMine designs and builds next-generation Internet business solutions using Microsoft .NET software and server technologies. You can reach Mark at mark.brown@identitymine.com.