16. Notification Services
NOTIFICATION SERVICES IS another example of how SQL Server becomes a more visible part of a multitier, service-based application architecture. This chapter is a brief introduction to Notification Services.
What Is SQL Server Notification Services?
SQL Server Notification Services was originally released in the summer of 2002 as a licensed part of SQL Server 2000. It shipped as an MSI file that you downloaded from the Microsoft Web site, similar to the way improvements to SQLXML support were available as “Web releases.” When installed, SQL Server Notification Services did not change the internals of sqlservr.exe; neither were the services loaded in SQL Server’s process. It was not integrated into the SQL Server 2000 installation process but provided its own install and a command-line utility called NSControl to deploy and administer the service on the target computers.
SQL Server Notification Services (we’ll abbreviate this as SQLNS from now on, for brevity) consists of an engine, which usually runs as part of an executable process (NSService.exe), and a framework for building a specific type of application, notification applications. Like SQL Server’s Analysis Services and Service Broker, it extends your application coding reach, and it moves SQL Server and related services toward being an integrated platform for scalable application systems. Unlike with Service Broker, you do not code DDL for Notification Services or store its metadata inside SQL Server catalogs, though when you generate an SQLNS application, its metadata is stored in SQL Server databases. SQLNS is, in SQL Server 2005, one of the series of application-enabling services that ship “in the box.” When you install SQL Server 2005, one of the first installation screens provides a choice of installing one or more of the following:
• SQL Server Database
• Analysis Services
• Notification Services
• Reporting Services
• SQL Server Integration Services
• Documentation and Samples
That is because these services work in concert to provide pieces of a service-oriented architecture for building data-centric applications. In SQL Server 2005, you can install, control, and monitor SQLNS applications from SQL Server Management Studio, along with SQL Server itself. SQLNS application coding is not integrated into the SQL Server Management Studio environment, though you can use C++ makefile projects in Visual Studio to group the components in a Notification Services application and run the appropriate application generation utilities. You can also use Visual Studio 2005’s XML editor with schema validation to provide validation and IntelliSense when editing the SQLNS XML configuration files.
There are a few key enhancements to SQL Server Notification Services in SQL Server 2005, some of which we have already alluded to:
• Notification Services installation is integrated into SQL Server installation.
• There is a new management interface for NSControl operations in SQL Server Management Studio.
• There are managed APIs for configuration, application generation, and application management known as Notification Management Objects (NMO).
• There is a new built-in provider for Analysis Services data.
• You can use a Notification Services instance using an existing database, or it can use its own separate databases.
• You can run the Notification Services engine as part of your own service program.
• You can use condition actions in Notification Services applications. A condition action provides the ability to have a subscriber-defined WHERE clause for a Transact-SQL (T-SQL) match rule.
• The application-dependent Notify function has been replaced with SQL INSERT INTO...SELECT syntax to improve security and scalability.
• There is 64-bit support for scale-up.
• Vacuuming performance improvements have been made.
• Object model reflection enhancements for subscription objects have been added to support subscriber-defined match rules.
Notification Applications
Almost everyone has been affected by notification applications in daily life. When you receive a daily weather forecast through your cell phone or PDA, when you’re notified that your upgrade to first class has been granted on the way to the airport, and when you receive traffic reports over instant messenger at the office so you can more effectively plan your route home, you are interacting with notification applications. From a consumer point of view, notification applications consist of two pieces:
• You subscribe to receive notifications based on certain criteria through a subscription application, often over the Web.
• You receive notifications through the communication vehicle of your choice. Some popular choices are messaging products, cell phones, and e-mail.
Some examples of existing Notification Services applications and scenarios are the following:
• Financial services—Receiving and reacting to personal portfolio changes and current market conditions
• Travel and hospitality—Flight arrivals, schedule changes
• Electronic commerce—Search criteria, buying and selling
• Monitoring business data
• Alerting the appropriate person or system when action is required
• Defining the events that occur in a line-of-business application
• Tracking critical company data
• Numeric data reaching a threshold
• Focusing on key performance indicators
• Coexisting with and broadening existing monitoring—Going beyond the console to devices like cell phones
• Keeping employees informed—Updates to projects, timely research, alerts to actions that may be required
The SQL Server Notification Services architecture abstracts event, subscription, and notification data into classes using XML within two configuration files built by the application developer. The application generation process generates database tables and procedures based on application-specific data. It abstracts the mechanism for providing events, formatting notifications, and delivering notifications into a provider model. The SQLNS product comes with some providers built in; other providers come with related products; and if none of the providers meets your needs, you can build your own using managed code and optionally a COM wrapper API.
As an application developer, you must define the following:
• Incoming events that may provide the fodder for your notifications
• What types of notifications your users will be allowed to sign up for
• A subscription application, so users can sign up
• Pieces of information (data) that will make up your notification
• An algorithm for matching the existing subscriptions with incoming events (or other data to be queried) to produce notifications
• The format of the notification on different delivery vehicles (devices)
Through this short description, you can see that notification applications are publish–subscribe applications. The SQLNS feature gives you all the tools you need to build the applications, SQLNS provides a service process that runs the Notification Services engine, or you can use your host engine in your own application. The engine service process can be scaled to run as multiple pieces on multiple machines as well. SQLNS uses SQL Server to store its metadata and state data (SQL Server is mandatory, although it can integrate with outside data in other DBMS products and with Analysis Services) and also uses SQL Server stored procedures and T-SQL statements to affect its matching rules. Figure 16-1 shows an overview of the SQLNS application architecture.
Figure 16-1. An overview of SQL Server Notification Services
This diagram shows that some parts of Notification Services are provided for you; some follow an extensible provider-based architecture; and you must code some yourself. Subscriptions enter the system through a custom subscription management application (1), which can be written using a Notification Services subscription object model. This object model contains subscriber, subscription, and subscriber device classes (among others) and stores its data in the database (2). Events enter the system through event providers (3) and are stored in the database in batches. ASQLNS component known as the generator executes T-SQL match rules, which generate notifications from events and subscriptions (4). Another component, known as the distributor, formats the notifications (using formatters) and delivers them to the ultimate destination (5).
Components of SQL Server Notification Services
When you choose to install SQL Server Notification Services, all its components install in a separate subdirectory of the SQL Server installation directory. You can see that there are very few discrete components:
• NSService.exe—Instances of Notification Services run as Windows services, using this program as the executable to run. Notification Services instances should not be confused with SQL Server instances.
• Microsoft.SQLServer.NotificationServices.dll—NSService. exe loads this .NET Framework assembly, which contains the code for SQLNS, the built-in providers, and components that you call to insert and retrieve data in the SQLNS tables when building subscription applications.
• Microsoft.SQLServer.NotificationServices.Rules.dll—This .NET Framework assembly contains the additional components that you use in subscription applications to program subscription-specific conditions for user-defined event conditions.
• Microsoft.SqlServer.SMO.dll—This .NET Framework assembly includes (as part of SQL Server Management Objects) the NMO.
• NSControl.exe—This utility program generates the SQL Server databases and database objects that the Windows service program and your subscription application use. The application also maintains metadata about Notification Services instances (instances of NSService.exe) that is stored in the Registry. You can also use NSControl.exe to enable and disable parts of your SQLNS application, provide status information, and detect the version(s) of Notification Services installed.
• XML schemas—When generating Notification Services applications, you specify the information in XML control files. NSControl.exe uses these schemas to validate your XML control files before it uses them to store metadata inside SQL Server database tables.
• Sample applications—SQLNS comes with a series of sample applications that you can use as a starting point to build your own applications or try out various SQLNS features. These applications come with C++ makefile projects and test data. Visual C++ must be installed for you to use these projects.
• Providers—As mentioned earlier in the chapter, what makes SQLNS extensible is the provider model for event providers, formatters, and delivery protocol providers.
Here’s a list of built-in providers at this writing, providers in development, and the interface you would implement to build your own.
These are the event providers:
• File Watcher event provider—Built-in provider that watches for files to be dropped into a directory on the file system. Files must be in XML format, and you must write an XML schema that the SQLXML bulk loader (from SQLXML 3.0) will use to load the events into the event classes.
• SQL Server event provider—Built-in provider that runs the SQL statement of your choice at an interval you configure to determine whether rows in a SQL Server table have changed or merit that an event be submitted. Note that SQL Server–linked server connections are used; this data could be in a non-SQL Server database.
• Analysis Services event provider—New for SQL Server 2005, this will produce events based on Multidimensional Expressions (MDX), such as those used for key performance indicators.
• SQLNS stored procedures—You can use SQLNS-defined stored procedures, which are generated on a per–event class basis, to insert batches of events. This is not an event provider in itself but is an alternative way to generate event batches.
• IEventProvider and IScheduledEventProvider—You can write classes that implement one or the other of these interfaces to produce a custom provider.
These are the content formatters:
• XML/XSLT formatter—This built-in provider formats notifications into XML and then passes them through the XSLT transform that you specify.
• IContentFormatter—You can write classes that implement this interface to produce a custom provider.
These are the delivery protocol providers:
• SMTP—This built-in delivery provider delivers notifications using any SMTP-compliant mail server.
• File—This built-in delivery provider writes notifications to the file of your choice and is used mostly for debugging applications.
• IDeliveryProtocol—You can write classes that implement this interface to produce a custom provider.
• HTTP—You can write classes that implement IHttpProtocol-Provider. SQLNS comes with a class, HttpExtension, that encapsulates sending through HTTP. It’s simpler to implement this interface if your custom protocol provider uses HTTP.
• Microsoft Alerts provider—This provider comes with the .NET Alerts 6.0 toolkit and is used to send notifications to the Microsoft Alerts service. From there, Microsoft Alerts can deliver them to instant messenger, e-mail, or SMS-compliant cell phones. Providers for older versions of the .NET Framework Alerts toolkit are also available.
• SMS providers—There are providers to send notifications to cell phones through the major commercial SMS aggregators.
• Third-party products—There are third-party products, released or in development, for fax delivery, SMS, and the BlackBerry Server.
When you start up a Notification Services instance, the command that is run is NSService.exe. If your instance is called StockTrader, for example, the whole command line actually looks like this:
[fullpath to NSService]NSService.exe "StockTraderInstance"
When NSService.exe starts up, it reads that command-line parameter and looks for an application called StockTraderInstance in the Registry (SQLNS keeps a list of all of its instances as Registry subkeys of HKLMSoftwareMicrosoftMicrosoft SQL ServerServices Notification ServicesInstances). One parameter of this subkey holds the location of the SQL Server where the metadata for this instance is stored. It will connect to that instance of SQL Server, using integrated security or an SQL Server user ID and password, and read the metadata it needs to control the instance. This metadata includes information such as:
• What event, subscription, and notification classes are defined.
• Which providers should be loaded, including custom providers.
• Parameters for the service’s generator and distributor subsystems. (We’ll discuss the intricacies of these systems later in this chapter.)
We should mention a couple of details before we go on. First, multiple instances of SQLNS can run on a single machine, much as multiple instances of SQL Server itself can run on a single machine. A single instance of NSService.exe can run a single Notification Services application or more than one application. We’ll define applications in more detail later, but we’ll mention here that each application has its own application database separate from the instance database. Usually, one instance means one set of subscribers, because subscribers and subscriber delivery vehicles (known as subscriber devices) are stored in tables in the instance database. Subscriptions (to an application), events, and notifications are stored in the application database. This is shown in Figure 16-2.
Figure 16-2. One application per instance versus multiple applications per instance
Finally, the generator and distributor subsystems can be spread across multiple machines. Each machine would run its own instance of NSService.exe, and the SQLNS work would be split among multiple machines. This is similar in concept to running federated SQL Servers. This is an advanced deployment concept not explained in detail in this chapter.
Notification Applications Design Patterns
Notification applications follow a few common patterns. The first distinction between application types is the distinction between event-driven subscriptions and scheduled subscriptions. To use an application that reports weather information to a cell phone, as an example, if we are interested in receiving a Portland, Oregon, weather forecast each day at 8 a.m., this is a scheduled notification. If we’re interested in receiving notification when the temperature in Portland, Oregon, goes below 10 degrees, however, this is an event-driven notification. This difference demonstrates the different types of subscriptions and how the generator process handles event-based and schedule-based subscriptions in SQLNS.
With an event-driven notification pattern, events are written into event tables on a fairly continuous basis, as configured by the application administrator. Every so often, the SQLNS generator wakes up and looks for batches of events to process. It will run the algorithm (written in T-SQL and known as a match rule) for each batch processed, matching current events against subscriptions to produce notifications. In addition, each time the generator wakes up, it will obtain all the scheduled subscriptions that are due at that time and match these subscriptions against data to produce notifications. The data used in generating event-driven notifications typically is the raw event batches themselves. The data used in generating scheduled subscriptions is kept in history tables, known as chronicle tables. There are two flavors of chronicle tables: event chronicles and subscription chronicles.
A few refinements of these patterns are useful in making the notifications more meaningful to the consumer. Another variation of the scheduled subscription is the trend-based subscription. In this subscription type, we don’t want to retrieve static information on a schedule (such as the weather forecast at a certain time), but a historical trend, such as the highest and lowest temperature of the day. This is accomplished by adding functionality to the T-SQL statement that updates the event chronicle table, known as the chronicle rule.
A second refinement might be based on the fact that we want to know when the temperature in Portland goes below 10 degrees at most once a day. Although temperature-change events are reported every 5 minutes, we don’t want to be reminded every 5 minutes once the temperature goes below 10 degrees! This is known as duplicate removal and is accomplished by keeping historical subscription data known as subscription chronicle files.
Notification Services Delivery Features
Digest delivery is an option that makes for a better consumer experience. Let’s say we subscribe to a stock subscription service that notifies us by e-mail when the stocks of our choice reach a certain threshold. We’re interested in ten different stocks. If four of them go over the threshold price that we’ve set at the same time, we don’t want to get four individual messages but a single message that has notifications about the four stocks. This is known as digest delivery.
One final feature is used not to enhance the consumer experience but to make the notification formatting (and possibly delivery) more efficient. If 1,000 people each register to receive the weather forecast for Portland by cell phone, and the notification message is the same for each user, the formatter doesn’t need to format the same message 1,000 times. In addition, a Microsoft Alerts–based application that delivers cell phone messages may accept a single message with 1,000 different destinations. Producing a single message for multiple users and delivering it through a list is known as multicast delivery.
Terms Used in Notification Services
Notification Services uses quite a few overloaded terms—that is, terms that you might be familiar with in a different context with a different meaning. Before we go any further, we should define the vocabulary we’re using and what these terms mean in a SQL Server Notification Services environment:
• Events—Events are external items that trigger notifications. In our example, it is changes of stock prices. The events can come from a variety of sources: stock-ticker feeds, changes of rows in database tables, news feeds, file directory changes, and so on. Event providers capture events and store them in the appropriate database tables. To improve scalability, SQLNS processes events in batches.
• Subscribers—Subscribers are the end users of the application. Subscribers are not exclusive to a specific SQLNS application but can be shared among applications. A subscriber can have a locale specified (language and possibly dialect) and a time zone.
• Devices—Devices are what the notifications are delivered to (e-mail, cell phones, Web Services). A subscriber can have several subscriber devices.
• Subscriptions—The subscriptions define what events a subscriber wants to be notified of. Depending on the events, a single application may use one or more subscription styles.
• Chronicle tables—Chronicle tables are used to store event history. The chronicle tables are useful for the different subscription styles mentioned earlier.
• Generator—The notification pipeline consists of the event processor, the generator, and the distributor. The generator runs T-SQL match rules to generate notifications.
• Distributor—The distributor’s job is to format and distribute notifications.
• Quantum—Both the generator and the distributor fire every so often; that duration is defined in seconds. This is known as the quantum.
• Formatters—Formatters are used to shape the raw notification data into a format the subscriber and the device understand. This can be based on device, locale, or both.
• Delivery channels—The delivery channel is the logical delivery mechanism, and a delivery channel targets one or several devices. It is mapped to delivery parameters: server name, user ID, and so on. Delivery channels are also mapped to delivery protocols—however, not in a one-toone relation. Several delivery channels can use the same protocol.
• Delivery protocols—The delivery protocol is the physical delivery mechanism. Typical protocols are SMTP and HTTP.
Designing, Coding, and Generating a Notification Services Application
Because much of the application infrastructure is provided in the box in SQLNS applications, planning the application will be much more involved than designing or coding the applications. Following are two lists of steps that outline the process.
Planning Steps
- Decide which notifications to expose:
• One or many notification classes
• Scheduled or event-driven subscriptions
• Digest (consolidated) or individual notifications
• Whether many subscribers will get the same notification
- Tie notifications to subscriptions:
• Category of notifications versus categories of subscriptions
• Locale-driven notifications
• Items of subscriber information used to personalize
- Decide how events cause notifications:
• Which criteria must be specified (SQL
JOINrules)• Whether users should be able to specify their own
WHEREclauses for rules (known as condition actions)• Whether notifications are event-driven or scheduled
• What history needs to be kept using chronicles or other supplemental data tables
- Decide where subscribers and subscriptions come from:
• Existing applications (for example, an existing line-of-business application or extracted from an Active Directory hierarchy)
• Specially written Web application
- Pick your providers:
• Event provider(s)
• Content formatter
• Delivery protocol(s)—one per delivery channel
Coding Steps
- Code your instance and application definition files:
• Decide where you will store event schema files if using the File Watcher event provider.
• Decide on a directory to watch for files of events to arrive if using the File Watcher event provider.
• Decide where you will store XSLT transforms for formatting if using the XSLT formatter.
• Code these locations into your application definition files or using NMO.
• Code event, subscription, and notification classes in the application definition files or using NMO.
• Code subscription rules in the application definition files or using NMO. These can be event rules, scheduled rules, and condition actions.
• Code information needed for delivery formatting and locations.
• Code machine locations where the SQL Server holding the databases will live, along with locations for the generator and the distributor.
• Code generator quantum duration and other generator and distributor behavior parameters.
• Code a schedule for the vacuum utility, which will run every so often to dispose of stale data.
- Code your subscription management application:
• It may be written in ASP.NET, ASP, or any tool that supports .NET Framework or COM-based components.
• It will use the subscription application object model in the SQLNS DLL.
- Code your event XML schemas if using the File Watcher event provider.
- Code your XSLT transforms, one for each combination of locale and device supported.
Application Generation Steps
- Use SQL Server Management Studio or NMO, or run
NSControl Createto create the instance and application databases. - Use SQL Server Management Studio or NMO, or run
NSControl Registerto create the Registry entries and the Windows service process. - Use SQL Server Management Studio or NMO, or run
NSControlto start (or stop) the various services used for the Notification Services instance and applications.
Some of these steps may refer to terms that haven’t been discussed in detail yet. We’ll delve into the details in our sample application.
A Sample Notification Application
The rest of this chapter consists of a series of tutorials that show developing a sample Notification Services application step by step. We begin with the minimalist application to keep from printing many pages of large configuration files and add functionality to the application incrementally, so you can see the changes to the minimalist configuration needed to implement different application functionality. On the way, we’ll discuss the options available in an SQLNS application by looking at the options of the control file XML elements. The step-by-step application code and the final application are available as part of the samples on the book’s Web site.
Imagine for a moment that you are a developer for a stock trading application. Apart from letting the clients enter trades over the Web, the clients should also be notified when certain stocks reach a specified price (the client has specified the price). The stock prices come in through a feed from a vendor of financial information. The prices are fed into tables in a SQL Server. As soon as the prices change, the application should notify the client if the price matches the preset price. Figure 16-3 shows what it looks like.
Figure 16-3. Financial application
This example is one pattern: an event-driven notification application matching stock code and price from the event feed, with the stock code and a trigger price specified in the subscription. In addition, the customer may want to be notified at a certain time of the highest or lowest price at which a particular stock has traded at during a trading day. The same application now contains two kinds of notification patterns: event-driven notifications and scheduled notifications.
Instance and Application Configuration Files
In Notification Services, one instance of the notification server service can consist of one or more applications. As we mentioned earlier, the instance database1 holds the subscriber information and control tables. The various application databases store the information specific to the application (stock, news, weather) and control tables. In SQL Server 2005 Notification Services, you can also choose to use an existing database. When you use an existing database, special schemas (not the dbo schema) are used as containers for the instance and application tables. We mentioned earlier that the instance is responsible for collecting events and so on. You may be asking yourself how the instance knows about the applications. The answer to this is the configuration files, which exist for both the instance and the applications.
These configuration files define the state and behavior of instances and applications. The configuration files are in XML format and are schema based. In SQL Server 2005, the schema for both the instance configuration file and the application configuration file can be located in the SQL Server directory under 90NotificationServices<version>XML Schemas. The schema for the instance file is called ConfigurationFileSchema.xsd, and the application schema is named ApplicationDefinitionFileSchema.xsd.
The instance configuration file contains control information and lists the applications. The application configuration file lists the applications schema and the actions. Now let’s focus more closely on these two files, starting with the instance file.
Instance Configuration File
The instance configuration file describes a single instance of Notification Services. It holds metadata information about the applications that the instance hosts. The metadata information for applications consists of the application name, base directory path, and name of the application configuration file. The configuration file also contains metadata about the instance, database server, delivery protocols, and delivery channels.
A sample instance configuration file is shown in Listing 16-1. The file contains only the required sections and elements for a valid instance configuration file.
Listing 16-1. Instance configuration file
<?xml version="1.0" encoding="utf-8" ?>
<NotificationServicesInstance
xmlns="http://www.microsoft.com/MicrosoftNotificationServices/
ConfigurationFileSchema">
<InstanceName>Stock</InstanceName>
<SqlServerSystem>SERVNB02</SqlServerSystem>
<Applications>
<Application>
<ApplicationName>StockPrice</ApplicationName>
<BaseDirectoryPath>
C:MyPath
</BaseDirectoryPath>
<ApplicationDefinitionFilePath>
appADF.xml
</ApplicationDefinitionFilePath>
</Application>
</Applications>
<DeliveryChannels>
<DeliveryChannel>
<DeliveryChannelName>FileChannel</DeliveryChannelName>
<ProtocolName>File</ProtocolName>
</DeliveryChannel>
</DeliveryChannels>
</NotificationServicesInstance>
When you inspect the file in Listing 16-1, you can see the metadata entries for the instance, as follows:
• InstanceName—The logical name for the instance of the Notification Services
• SqlServerSystem—The name of a SQL Server instance that hosts the instance database and its application databases
• DeliveryChannels—Contains <DeliveryChannel> nodes that describe all the delivery channels used by all applications hosted by the instance (more about delivery channels later in this chapter)
In the file, you can also see the metadata for one particular application. It is contained within an <Application> node under the <Applications> section:
• ApplicationName—Is the logical name of the application. It must be unique within the configuration file.
• BaseDirectoryPath—Is the application directory path. It is used to locate files specific to the application. These files typically are XML and XSLT files.
• ApplicationDefinitionFilePath—Is the name of, and optionally the path to, the application definition file (ADF).
As we mentioned before, the file in Listing 16-1 contains the bare minimum of information. You’ll probably want to include more information in your configuration file, and SQL Server Books Online lists the complete set of elements and nodes available. You may want to enter version information through the <Version> node, for example. If you create custom delivery protocols, you register them with <Protocol> nodes in the <Protocols> section.
You could also do this by using NMO in SQL Server 2005. Here’s a short equivalent example in NMO:
using System;
using Microsoft.SqlServer.Management.SMO;
using Microsoft.SqlServer.Management.NMO;
public static void Main()
{
Server theServer = new Server();
// create the SQLNS Instance
Instance theInstance =
new Instance(theServer.NotificationServices, "MyInstanceName");
theInstance.DatabaseName = "SERVNB02";
theInstance.Name = "Stock";
// Add in the FileChannel
DeliveryChannel fileChannel =
new DeliveryChannel(theInstance, "FileChannel");
fileChannel.ProtocolName = "File";
DeliveryChannelArgument fileNameArg =
new DeliveryChannelArgument(fileChannel, "FileName");
fileNameArg.Value = @"c:FileNotifications.txt";
fileChannel.DeliveryChannelArguments.Add(fileNameArg);
theInstance.DeliveryChannels.Add(fileChannel);
// Call another method to construct the Application
Application theApp = ConfigureApp(theInstance, "StockPrice");
theInstance.Applications.Add(theApp);
}
When you look at the configuration file in Listing 16-1, you may notice that all information is hard-coded into the file. For a production system, this is not ideal. For this reason, both the instance configuration file and the application configuration file have a <ParameterDefaults> node containing one or more <Parameter> nodes. The parameters are symbolic names appearing as name–value pairs. The application configuration file can inherit parameters from the instance file. Listing 16-2 shows an example of the configuration file in Listing 16-1 using parameters.
Listing 16-2. Configuration file using parameters
<?xml version="1.0" encoding="utf-8" ?>
<NotificationServicesInstance
xmlns="http://www.microsoft.com/MicrosoftNotificationServices/
ConfigurationFileSchema">
<ParameterDefaults>
<Parameter>
<Name>Instance</Name>
<Value>Stock</Value>
</Parameter>
<Parameter>
<Name>DBSystem</Name>
<Value>%COMPUTERNAME%</Value>
</Parameter>
<Parameter>
<Name>BasePath</Name>
<Value>C:MyPath</Value>
</Parameter>
<Parameter>
<Name>AppName</Name>
<Value>StockPrice</Value>
</Parameter>
</ParameterDefaults>
<InstanceName>%Instance%</InstanceName>
<SqlServerSystem>%DBSystem%</SqlServerSystem>
<Applications>
<Application>
<ApplicationName>%AppName%</ApplicationName>
<BaseDirectoryPath>
%BasePath%
</BaseDirectoryPath>
<ApplicationDefinitionFilePath>
appADF.xml
</ApplicationDefinitionFilePath>
<Parameters>
<Parameter>
<Name>_App_</Name>
<Value>%AppName%</Value>
</Parameter>
<Parameter>
<Name>_NSHost_</Name>
<Value>%COMPUTERNAME%</Value>
</Parameter>
</Parameters>
</Application>
</Applications>
<DeliveryChannels>
<DeliveryChannel>
<DeliveryChannelName>FileChannel</DeliveryChannelName>
<ProtocolName>File</ProtocolName>
</DeliveryChannel>
</DeliveryChannels>
</NotificationServicesInstance>
Compare the file in Listing 16-2 with the one in Listing 16-1, and you’ll see that we have a <ParameterDefaults> section, where we define different variables that are used farther down in the file. Within the <Application> section, we have a <Parameters> section, where we redefine some of the variables and give them new names. These names are inherited by the application configuration file.
You should also notice that we use systemwide environment variables—for example, %COMPUTERNAME%. We are not restricted to system environment variables, but we can set up our own variables in scripts and use the scripts when we build the SQLNS applications. This is very useful if you use Visual Studio 2005 to build your applications.2
By now, you may be asking yourself how we go about building databases, tables, and stored procedures from the configuration files. To build these items, you run the executable NSControl.exe and give it the name of your instance configuration file. We’ll cover this topic later in the chapter. First, we need to look at the configuration file for the application: the application definition file.
Application Definition File
The ADF stores all metadata about a particular SQLNS application. The file must conform to the schema in the ApplicationDefinition-FileSchema.xsd schema. Also, the nodes and elements in the file must be provided in the same order as in the schema. Listing 16-3 shows a minimal ADF file.
Listing 16-3. Minimal ADF file
<?xml version="1.0" encoding="utf-8" ?>
<Application xmlns="http://www.microsoft.com/
MicrosoftNotificationServices/ApplicationDefinitionFileSchema">
<SubscriptionClasses>
<SubscriptionClass>
<SubscriptionClassName></SubscriptionClassName>
<Schema>
<Field>
<FieldName></FieldName>
<FieldType></FieldType>
</Field>
</Schema>
</SubscriptionClass>
</SubscriptionClasses>
<NotificationClasses>
<NotificationClass>
<NotificationClassName></NotificationClassName>
<Schema>
</Schema>
<ContentFormatter>
<ClassName></ClassName>
</ContentFormatter>
</NotificationClass>
</NotificationClasses>
<Generator>
<SystemName></SystemName>
</Generator>
<Distributors>
<Distributor>
<SystemName></SystemName>
</Distributor>
</Distributors>
</Application>
The ADF file has four required nodes:
• SubscriptionClasses—This is used to define the subscription classes you use in your application. The classes are defined by a name and a schema. The schema indicates what fields of data you store for the subscription and what the data type of each field is. Optionally, you can define attributes for the fields. These attributes are SQL Server field attributes, such as nullability and default values.
• NotificationClasses—This is used to define the classes your application uses for notifications. As with subscription classes, you enter a name and a schema. In addition to the subscription class, you need to define what formatter is used to format the content of a notification.
• Generator—The generator manages the rule processing for the application. This node holds information about what system runs the process. You specify how often the Generator runs (Quantum-Duration) as a separate element at the same level at the Generator element, not as a child of the Generator element. Optionally, you can set how many operating system threads the generator can use. Read more about this in Books Online.
• Distributor—The distributor manages formatting and delivery of notifications. The node allows you to specify what systems run the distributor and, optionally, the thread pool size and how often the distributor runs (the QuantumDuration).
We mentioned before that the file in Listing 16-3 contains the minimal requirements. Although you can write an SQLNS application without defining EventClasses, EventClasses are also part of most applications. Later in this chapter, we will cover the required nodes in more detail, as well as what more we need to create a fully functional application.
Listing 16-4 shows an ADF file for our stock price application meeting the minimum requirements. The file is based on the file in Listing 16-3 and the instance configuration file in Listing 16-2. Notice how the parameters defined in the file in Listing 16-2 are used in the ADF file.
Listing 16-4. ADF file for the stock price application
<?xml version="1.0" encoding="utf-8" ?>
<Application xmlns="http://www.microsoft.com/
MicrosoftNotificationServices/ApplicationDefinitionFileSchema">
<SubscriptionClasses>
<SubscriptionClass>
<SubscriptionClassName>
%_App_%Subscriptions
</SubscriptionClassName>
<Schema>
<Field>
<FieldName>StockSymbol</FieldName>
<FieldType>varchar(10)</FieldType>
</Field>
</Schema>
</SubscriptionClass>
</SubscriptionClasses>
<NotificationClasses>
<NotificationClass>
<NotificationClassName>
%_App_%Notifications
</NotificationClassName>
<Schema/>
<ContentFormatter>
<ClassName>XsltFormatter</ClassName>
</ContentFormatter>
</NotificationClass>
</NotificationClasses>
<Generator>
<SystemName>%_NSHost_%</SystemName>
</Generator>
<Distributors>
<Distributor>
<SystemName>%_NSHost_%</SystemName>
</Distributor>
</Distributors>
</Application>
We can read from the file in Listing 16-4 that we have one subscription class and that we store only one field of data for the subscription. That field is called StockSymbol (although technically, other helpful fields, like DeviceName, should be stored). To format the notifications, we use the XsltFormatter, which comes out of the box with the SQLNS framework. Finally, the local system generates and distributes the notifications.
Having the files in Listings 16-2 and 16-4, we can now build the stock price Notification Services application. The NSControl.exe tool is used for this.
NSControl
NSControl is a tool for administering SQLNS. You use it to deploy, configure, monitor, and control SQLNS instances and applications. It is run from the command prompt, and Table 16-1 presents the available commands. SQL Server Books Online covers these commands and their syntax in detail.
Table 16-1. Commands for NSControl
You use the Create command to create your new SQLNS instance. The syntax for NSControl Create follows:
nscontrol create
[-help] |
-in configuration_filename
[-sqlusername login_ID -sqlpassword password]
[-argumentkey key]
[parameter_name=value [,...n] ]
[-nologo]
Here is a description of the arguments:
• -help—Displays the command syntax.
• -in configuration_filename—The pathname and filename of the instance configuration file. The path is not required if the file is in the current directory.
• -sqlusername login_ID—If you are using SQL Server authentication to log in to SQL Server, this is the SQL Server login ID. Best practice, however, is to use Microsoft Windows authentication to log in to SQL Server. In that case, do not use the -sqlusername and -sqlpassword arguments.
• -sqlpassword password—The password for the -sqlusername login ID.
• -argumentkey key—The value used to encrypt the delivery channel and event provider arguments that are stored in Notification Services databases.
• parameter_name=value—Aname–value pair used to pass parameters to the configuration file from the command line.
• -nologo—Suppresses the product and version statement that appears when you run an NSControl command.
Now that you know the syntax for NSControl Create, it is time to compile the application. You need two XML files: appConfig1.xml and appADF1.xml. They represent the files in Listings 16-2 and 16-4, and you can use these two files to create your application. Copy them to a directory on your hard drive, and change the BasePath parameter in the appConfig1. xml file to the directory you copied the files to. Before you can run the NSControl Create command, you need to make sure that NSControl.exe is on the path. Alternatively, you can run NSControl Create with a fully qualified path.
The following code creates an SQLNS instance together with the stock price application. It is run from the directory where the configuration files are. It assumes that the NSControl exists on the path, and it uses integrated security to log on to SQL Server:
NSControl Create –in appConfig1.xml
When you run this, you will see something like this in your Command window:
Microsoft Notification Services Control Utility (Enterprise) 9.0.242
Copyright (C) Microsoft Corporation 2004. All rights reserved.
Creating instance
Stock
Creating application
Application name: StockPrice
Create successful.
If you now log on to the SQL Server that is defined in the DBSystem parameter, you can see two new databases: the instance database, named StockNSMain, and the application database, called StockStockPrice. You should never alter the data in these databases directly, but it’s helpful to have a look at what’s inside them at this point.
So far, the databases do not contain that much application data. The instance database has a table called NSApplicationNames, which consists of one record: the StockPrice application. Notice, however, that in the instance database, there are tables for time zones. These tables make it really easy to create time zone–aware applications. The application database does not have that much more interesting information, either. You can see the notification and subscription classes in the NSNotificationClasses and NSSubscriptionClasses tables. Although EventClasses aren’t technically required, most applications will use one or more event classes and event providers. We’ll add them next.
Events
Events can come from a multitude of sources, and they are the occurrences that cause notifications. The events are processed by event providers, which send the event data to the application, where it is inserted into the event table.
We discussed the built-in providers and customization hooks available at the beginning of the chapter. Apart from standard and customized event providers, SQLNS also differentiates between hosted and nonhosted (independent) event providers. Hosted event providers run inside SQLNS, and they either run continuously or fire based on a schedule. The schedule for a hosted scheduled provider is defined in the ADF file for the application. The hosted providers are controlled by a component in Notification Services, the event provider host. An independent event provider runs as an external application, and it submits events according to its own schedule.
Events in SQLNS are handled in batches in order to improve performance. Let’s assume that the event provider picks up three events since it last fired. The provider can create a batch with a batch ID and insert each event into the event table together with the batch ID. The events are processed by the generator the next time the generator fires. This allows the generator to compare multiple events with subscriptions at one time, instead of doing it on an event-by-event basis. This is done because it gives good scalability for the tradeoff of real-time processing. If a little latency is allowed, it scales to huge numbers.
Event Classes
To catch the events you are interested in, you have to define what events to accept. This is accomplished by using event classes and event class definitions in the ADF file. Listing 16-5 shows the required elements and nodes for one event class definition.
Listing 16-5. Event class definition in the ADF file
<EventClasses>
<EventClass>
<EventClassName></EventClassName>
<Schema>
<Field>
<FieldName></FieldName>
<FieldType></FieldType>
</Field>
</Schema>
</EventClass>
</EventClasses>
You can see that it looks very much like the definition for the subscription class and notification class in Listing 16-4. Both the EventClass node and the Schema node support quite a few optional nodes and elements, and they are listed in Books Online.
In our stock price example, we are interested in the stock code (MSFT, IBM, and so on), the exchange the stock trades on, and the price.3 Listing 16-6 shows how the event class definition looks in our stock price application. Notice that there are a couple of nonrequired elements in the file: the FieldTypeMods element, to set attributes on the fields in SQL Server, and the IndexSqlSchema node, where in the SqlStatement you create indexes on the table.
Listing 16-6. Event class definition in the ADF file
<?xml version="1.0" encoding="utf-8" ?>
<Application xmlns="http://www.microsoft.com/
MicrosoftNotificationServices/ApplicationDefinitionFileSchema">
<EventClasses>
<EventClass>
<EventClassName>StockEvt</EventClassName>
<Schema>
<Field>
<FieldName>StockCode</FieldName>
<FieldType>varchar(10)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>ExchangeCode</FieldName>
<FieldType>varchar(15)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>Price</FieldName>
<FieldType>decimal(18,5)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
</Schema>
<IndexSqlSchema>
<SqlStatement>
Create Index StockStockIndex on StockEvt
(StockCode, ExchangeCode)
</SqlStatement>
<SqlStatement>
Create Index StockStockIndex2 on StockEvt
(ExchangeCode)</SqlStatement>
</IndexSqlSchema>
</EventClass>
</EventClasses>
<SubscriptionClasses>
<!-Rest of file omitted
...
</Application>
There is still something missing from the ADF file in Listing 16-6: the information about what event provider to use. Remember that earlier, we discussed hosted versus independent providers? Initially, we will use an independent provider to collect the events.
Event providers are entered and named in the ADF file. They are located under the <Providers> node. This node contains <HostedProvider> and <NonHostedProvider> nodes. First, as we mentioned earlier, we’ll use a nonhosted provider. Although some event providers need to be entered in the ADF to provide supporting information, others (like nonhosted providers) are entered mostly for internal documentation. We add to the ADF file in Listing 16-6 the <Providers> node, as shown in Listing 16-7. Notice that the <Providers> node needs to be placed after the <NotificationClasses> node but before the <Generator> node.
Listing 16-7. Providers node in the ADF file
</NotificationClasses>
<Providers>
<NonHostedProvider>
<ProviderName>SqlStockEvents</ProviderName>
</NonHostedProvider>
</Providers>
<Generator>
With these updates to the ADF file, the SQLNS application can be updated. For updates, the NSControl Update command is used, according to the following syntax:
nscontrol update
[-help] |
-in configuration_filename
[-verbose]
[-force]
[-sqlusername login_id-sqlpassword password]
[-argumentkey key]
[parameter_name=value [,...n] ]
[-nologo]
The Update command looks almost like the Create command. The only differences are the verbose and force flags. The verbose flag displays the information that has changed in the configuration file and the ADF file as it is found. The force flag forces the update to proceed without prompting for approval after displaying the actions that will occur. To set up the event classes in the SQLNS application, you run NSControl Update against the configuration file and the changed ADF file.4 Running NSControl Update -in appConfig2.xml causes some changes to the application database:
• Additional tables have been created: NSCurrentStockEvtEvent-Batches, NSCurrentStockEvtEvents, NSStockEvtEventBatches, and NStockEvtEvents. As the names of the tables indicate, they are used to store event and event batch information.
• Data has been inserted into the NSEventClasses and NSEvent-Fields tables.
• Among the new stored procedures are procedures for writing events and event batches.
In the ADF file, we indicated that we would use a nonhosted provider, and we gave it the name SqlStockEvents. With a nonhosted provider, the only element that needs to be defined is the name of the provider. We will see how the name is used when events are submitted through the SQLNS stored procedures.
Event Stored Procedures
These are the stored procedures that have been created to write events to the database:
• NSBeginEventBatch<EventClassName>—Creates a new event batch and returns an ID for the batch.
• NSEventWrite<EventClassName>—Submits one event to the events table for the event. The NSBeginEventBatch<EventClassName> must be run first to obtain the batch ID.
• NSEventFlushBatch<EventClassName>—Closes the batch and marks it complete. When the batch is marked complete, the generator can process the events.
• NsEventSubmitBatch<EventClassName>—The previous procedures in this list are used mostly to write to the event table when the event is a single event from an outside source. When the events to collect are multiple entries in a database, we use this procedure, which allows us to specify a query to retrieve a batch of events to collect.
In this stage of our application, we are submitting only single events, so we start the event collecting process by starting a batch. We do this by using the NSBeginEventBatch<EventClassName> procedure. The syntax follows:
NSEventBeginBatchEventClassName
[@ProviderName =] 'event_provider_name',
[@EventBatchId =]event_batch_variable OUTPUT
The EventClassName is picked from the EventClassName element in the EventClass node. The procedure takes a provider name as the input parameter. This has to be the same name that appears in the ProviderName element. It returns a batch ID as the output parameter.
The batch ID is used in the NSEventWrite<EventClassName> stored procedure. This procedure also uses the event class name. The syntax looks like this:
NSEventWriteEventClassName
[@EventBatchId =] event_batch_ID ,
[@event_class_field_name =] event_class_field_value [, ...n]
The @EventBatchId parameter is the parameter received from the NSEventBeginBatch procedure. Name–value pairs follow the batch ID parameter. These name–value pairs correspond to the FieldName element in the EventClass node and its value. In the stock price database, the signature for the procedure looks like this:
NSEventWriteStockEvt
@EventBatchId bigint,
@StockCode varchar(10) = NULL,
@ExchangeCode varchar(15) = NULL,
@Price decimal(18,5) = NULL
You close the batch with the NSEventFlushBatch<EventClassName> procedure when the event is submitted. This procedure takes the batch ID and, optionally, the number of events submitted as parameters:
NSEventFlushBatchEventClassName
[@EventBatchId =]event_batch_ID
[, [@EventCount =]number_of_events]
The code to create an event is shown in Listing 16-8. Because the application has just been created (and updated), it needs to be enabled and registered first. NSControl is used for this, and the syntax is
NSControl Enable -name instancename -server servername
where -name is the Notification Services instance name, and -server is the name of the server where the instance is installed.
Listing 16-8. Code to create an event
DECLARE @ProviderName varchar(255)
DECLARE @EventBatchId bigint
DECLARE @EventCount bigint
DECLARE @RicCode varchar(6)
DECLARE @ExchangeCode varchar(6)
DECLARE @Price float(10)
DECLARE @EventClassName varchar(255)
SET @ProviderName = 'SqlStockEvents'
EXECUTE NSEventBeginBatchStockEvt @ProviderName,
@EventBatchId OUTPUT
SET @RicCode = 'MSFT'
SET @ExchangeCode = 'NYSE'
SET @Price = 53.33
EXECUTE NSEventWriteStockEvt @EventBatchId,
@RicCode,
@ExchangeCode, @Price
SET @EventCount = 1
EXECUTE NSEventFlushBatchStockEvt @EventBatchId, @EventCount
To view information about the batch and the events in the batch, we can use a stored procedure named NSEventBatchDetails. The syntax for the procedure follows:
NSEventBatchDetails
[@EventClassName =] 'event_class_name' ,
[@EventBatchId =] event_batch_id
It takes the name of the event class and the ID of the batch you want the information about, and it produces two resultsets. The first resultset contains general information about the batch, such as the provider, how many events are in the batch, when the batch started, when it ended, and the total collection time in milliseconds. The second resultset contains information about the individual events in the batch: the event ID and the individual event fields of the event. Apart from getting information about batches and events, there are stored procedures for information about event providers and event classes:
• NSDiagnosticEventClass—This procedure produces information about event collection and processing of events by the application.
• NSDiagnosticEventProvider—This procedure contains information about the events collected through a specified event provider.
Event Providers
We started this whole section about events by discussing event providers. We also mentioned briefly the event providers that are part of the SQLNS framework: the File Watcher event provider and the SQL Server event provider. Now let’s take a close look at them, starting with the File Watcher event provider (FS).
The File Watcher event provider monitors a directory. The provider fires when an XML file is added to the directory. It reads the content of the file into memory and writes the event data to the event table. Internally, the provider uses the FileSystemWatcher class from the .NET Framework. It is being run as a hosted nonscheduled provider, and it is set up in the ADF with these three mandatory arguments in addition to the provider name, class name, and system name:
• WatchDirectory—Full path and name of the directory that the event provider monitors
• EventClassName—Name of the event class that defines the events
• SchemaFile—Full path to a SQL-annotated schema file that describes the schema for the events
Listing 16-9 shows the provider part of the stock price application’s ADF file when we have added the FS as a hosted provider.
Listing 16-9. ADF file with hosted file system watcher provider
<Providers>
<HostedProvider>
<ProviderName>StockEP</ProviderName>
<ClassName>FileSystemWatcherProvider</ClassName>
<SystemName>%_NSHost_%</SystemName>
<Arguments>
<Argument>
<Name>WatchDirectory</Name>
<Value>%_BasePath_%TestEvents</Value>
</Argument>
<Argument>
<Name>SchemaFile</Name>
<Value>%_BasePath_%EventsSchema.xsd</Value>
</Argument>
<Argument>
<Name>EventClassName</Name>
<Value>StockEvt</Value>
</Argument>
</Arguments>
</HostedProvider>
<NonHostedProvider>
<ProviderName>SqlStockEvents</ProviderName>
</NonHostedProvider>
</Providers>
The provider is named StockEP; the class name has to be FileSystemWatcherProvider; and the system name refers to the _NSHost_ parameter. In the arguments node, we point the directory to watch to the TestEvents directory underneath the base directory. The schema is located in the EventsSchema.xsd file in the base directory and is shown in Listing 16-10. Finally, we tell the provider that the event class to collect events for is the StockEvt class.
Listing 16-10. Schema for the stock price events
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="event" sql:relation="StockEvt">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="StockCode" type="xsd:string"/>
<xsd:element name="ExchangeCode" type="xsd:string"/>
<xsd:element name="Price" type="xsd:decimal" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
NSControl Update can now be run again against the configuration file. The configuration file’s ApplicationDefinitionFilePath element needs to point to an ADF file containing the Providers section in Listing 16-9. Notice, however, that the _BasePath_ parameter is not defined at the application level. It needs to be defined first either in the configuration file or in the ADF file. We leave it up to you to figure out how to do it (or you can look in the appConfig3.xml and appADF3.xml files in the book’s Web site samples). Before any updates can take place, the instance needs to be disabled. Use NSControl Disable to do this with a -name parameter and a -server parameter. These parameters are the same as those used for NSControl Enable. After the update has taken place, run NScontrol Enable again, as you did before you submitted events to the application in Listing 16-8.
The FS provider is a hosted provider and, as such, runs under the SQLNS service. So far, we haven’t registered the service, but we have run SQLNS in nonservice mode. Unless we start the service, several parts of SQLNS won’t run. Among them are the generator of events and notifications and the distributor of notifications.
To register the instance, we use NSControl Register with the name of the instance; the name of the server SQLNS runs on; and the -service argument, which registers this instance of SQLNS as a Windows service. It looks like this from the command line:
NSControl Register -name instance-name -server server-name -service
Notice that the previous command does not start the service explicitly. It needs to be started from Start > Administrative Tools > Services. The name of the service is NS$ followed by the instance name. Therefore, if the instance name is Stock, the service name is NS$Stock.
When the instance is registered and the service is started, the FS is ready to look for events in the watch directory. When a new file with an .xml extension is placed in the watch directory, the FS loads the file into memory. Then it uses an SQLNS EventLoader object to write the event information into the event table. When the batch has been processed and closed, the XML source file is renamed to indicate that it has been processed. The new filename is the original filename, to which the date and time of its processing are appended. A counter value is also appended to differentiate files processed at the same time, as well as a .done extension. If FS could not process the file, it will be renamed as described earlier, but with an .err extension. In the Notification Services sample code on the book’s Web site is an XML file, EventsData.xml, that can be used to test the FS provider.
The second provider that is part of the SQLNS framework is the SQL Server event provider (SEP). This provider uses a user-defined query to query a database table for events. After retrieving the events, it uses the SQLNS event stored procedures to write the events to the event table. Queries can be defined to do pre- and postprocessing of the data. As opposed to the FS provider, the SEP runs as a scheduled provider, and the developer of the SQLNS application needs to define the schedule under which it should be run.
Listing 16-11 shows an example of an entry for the SQL Server event provider in the ADF file. By now, you should be familiar with the schema of the file, so let’s look at what is new in it. There is a <Schedule> node that has two children elements: the <StartTime> and the <Interval>. The <StartTime> element is optional and defines at what time the provider should start running after the application has been set up. The <Interval> defines at what interval it should run. The <Interval> in Listing 16-11 indicates that the provider runs every minute (60 seconds).
Listing 16-11. ADF file with an entry for a scheduled SQL Server event provider
<Providers>
<HostedProvider>
<ProviderName>SQLStockPrice</ProviderName>
<ClassName>SQLProvider</ClassName>
<SystemName>%_NSHOST_%</SystemName>
<Schedule>
<StartTime>15:00:00</StartTime>
<Interval>P0DT00H00M60S</Interval>
</Schedule>
<Arguments>
<Argument>
<Name>EventsQuery</Name>
<Value>SELECT StockSymbol, StockPrice
FROM StockPriceTable</Value>
</Argument>
<Argument>
<Name>EventClassName</Name>
<Value>StockEvents</Value>
</Argument>
</Arguments>
</HostedProvider>
</Providers>
The format of the <Interval> entry is according to the XML duration data type. This data type is defined as PODTOOHOOMOOS, which specifies the interval as follows:
• P—Defines the duration data type
• OD—Number of days
• T—Defines the time portion of the type
• OOH—Number of hours
• OOM—Number of minutes
• OOS—Number of seconds
The <Arguments> node has two children nodes. One defines the query to run, through the EventsQuery name; this is a required entry. The other argument is the EventClassName, which also is required. There is a possible third, optional argument, PostQuery, that defines a query to run after the events have been collected.
Chronicles
We have seen in this events section how events are collected. The model for events collection is based on the presumption that each event can cause a notification. They will not necessarily do that, but potentially, they can. For a scenario where the user is interested only in being notified at a certain time, regardless of whether an event occurred at that time, the model does not work that well. These are some possible scenarios:
• The user wants to get a traffic report at a certain time.
• The user wants to see the highest price of a particular stock during the day.
• The user wants to see historical event information.
To accomplish this, SQLNS uses chronicle tables and chronicle rules to store event data from event batches. Chronicle tables are defined in the /EventClasses/EventClass/Chronicles section of the ADF file, as Listing 16-12 shows.
Listing 16-12. Chronicle table creation in the ADF file
<EventClass>
<!-- other EventClass elements -->
<Chronicles>
<Chronicle>
<ChronicleName>StockEvtChron</ChronicleName>
<SqlSchema>
<SqlStatement>
-- drop table before creating it
IF EXISTS(SELECT name FROM sys.tables
WHERE name = 'StockEvtChron')
DROP TABLE dbo.StockEvtChron
CREATE TABLE StockEvtChron (
[StockCode] nvarchar(6),
[ExchangeCode] nvarchar(6),
[StockPrice] decimal(18,5))
</SqlStatement>
</SqlSchema>
</Chronicle>
</Chronicles>
<!-- other EventClass elements -->
</EventClass>
The chronicle tables are populated from the corresponding event class through chronicle rules. The rules are defined by SQL statements, and they can be defined in two places: in the <ChronicleRule> node of an event class or in the <EventRules> node of a subscription class. The decision where to place the rules depends on when the chronicle data should be affected. If the data is to be affected before notifications are generated, the rule should be defined in the <ChronicleRule>. If the chronicle data should be affected after the notifications have been generated, the rule should be placed in the <EventRules> node. The following code shows a chronicle rule that updates entries in the chronicle table if the price in the events table is greater than the price in the chronicle table. Notice that the developer of the ADF file has to use the entity references of the greater-than (>) and less-than (<) signs because those are reserved characters in XML:
<EventClass>
<!-- other EventClass elements -->
<ChronicleRule>
<RuleName>StockEventsChronRule</RuleName>
<Action>
-- Update value in the chronicle
UPDATE StockEvtChron
SET StockPrice = e.StockPrice
FROM StockEvt e
JOIN StockEventsChron c
ON e.StockCode = c.StockCode
AND e.ExchangeCode = c.ExchangeCode
WHERE e.StockPrice > c.StockPrice
</Action>
</ChronicleRule>
<Chronicles>...</Chronicles>
</EventClass>
Events and chronicles are not interesting in themselves; we need someone to be interested in the events. In other words, we need subscribers and subscriptions.
Subscribers and Subscriptions
To send subscriptions, the SQLNS needs information about the following:
• Who to send information to—The subscribers
• What information to send—The subscriptions
• What devices the information should be sent to—The devices
The setup of this is done through using subscription management objects in SQLNS. Information about the subscribers and their devices is stored in the SQLNS instance database, whereas information about subscriptions is stored in the SQLNS application database. The developer of the SQLNS application also needs to define information about subscriptions in the ADF file by subscription classes and device information in the configuration file (see Listing 16-1 or 16-2, where there is an entry for a <DeliveryChannel>).
Figure 16-4 tries to give a graphical illustration of how subscription information is entered through a subscription management application. The subscription management application uses the management objects to create subscriber information in the instance database and subscription information in the application database.
Figure 16-4. Subscription management
In Figure 16-4, you can see references to various objects (subscriber, subscription, and devices). These objects are part of the SQLNS management objects mentioned earlier, and for subscription management, we specifically use the objects in the following list:
• NSInstance—Represents a specific SQLNS instance. Several Notification Services classes require a reference to this object to be initialized.
• Subscriber—Represents a Notification Services subscriber. Subscriptions and subscriber devices in the Notification Services system are associated with a subscriber by means of the subscriber ID.
• SubscriberDevice—Represents a device belonging to a subscriber that can be used to receive notifications.
• NSApplication—Represents a specific SQLNS application. As with the NSInstance, many SQLNS classes require a reference to this class.
• Subscription—Represents a single subscription within an SQLNS application.
All these classes live in the Microsoft.SqlServer.NotificationServices namespace, and they are the main classes for developing subscription applications. These are not the only classes available for subscription management, however; there are classes for enumeration/information of subscribers, devices, subscriptions, and so on. The NotificationServices namespace is not just for subscription management; it contains all the SQLNS classes and interfaces. All the classes in the NotificationServices namespace are written in managed code, but they also have COM wrappers, so they can be used from COM automation languages. In addition to the classes related to instances, applications, and subscription management in general, the namespace holds classes and interfaces related to the following:
• Submitting events to the Notification Services system
• Developing custom Notification Services components
Looking at Figure 16-4, we can see the steps to create subscription management applications: create subscribers, create devices, and finally create subscriptions. Before the subscriptions can be created, the subscriptions schema needs to be defined in the ADF file through SubscriptionClasses.
Subscription Classes
One or more subscription classes in the ADF file decide the types of subscriptions that the SQLNS application accepts. A subscription class generally holds information about the device, information about the event, and what constitutes an event. You can add further information, such as locale. SQLNS use some subscription fields automatically for formatting and delivery. Examples of such fields are the subscriber ID and the device name, which are used by the formatter and the delivery mechanism; the formatter uses the locale field.
An example of an ADF file with a subscription class is shown in Listing 16-13. Perusing the code in Listing 16-13, we see that there are subscription fields defined for the device, the locale, the event information, and at what point an event triggers. The subscription class also has an SQL statement for dropping and creating indexes on the subscription table.
Listing 16-13. Subscription class information in the ADF file
<SubscriptionClasses>
<SubscriptionClass>
<SubscriptionClassName>
%_App_%Subscriptions
</SubscriptionClassName>
<Schema>
<Field>
<FieldName>DeviceName</FieldName>
<FieldType>varchar(255)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>SubLocale</FieldName>
<FieldType>varchar(10)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>StockCode</FieldName>
<FieldType>varchar(15)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>ExchangeCode</FieldName>
<FieldType>varchar(15)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>TriggerVal</FieldName>
<FieldType>decimal(18, 5)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
</Schema>
<IndexSqlSchema>
<SqlStatement>
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = 'StockSubIndex')
DROP INDEX %_App_%Subscriptions.StockSubIndex
</SqlStatement>
<SqlStatement>
CREATE INDEX StockSubIndex
ON %_App_%Subscriptions (SubscriberID)
</SqlStatement>
</IndexSqlSchema>
</SubscriptionClass>
</SubscriptionClasses>
For event-driven subscriptions, the subscription class also needs to define event rules. The event rules define how notifications are generated for the subscriptions. These rules are executed each time a new event batch is processed. The event rules are entered in the EventRules node in the subscription class; we cover more about event rules in the section about notifications. For schedule-driven notifications, the rules are created in a ScheduledRules section in the ADF file.
The ADF file can now be updated with the section in Listing 16-13. To update the application database, disable the SQLNS instance, run NSControl Update, and then enable the instance again.
Now let’s look at the users of the subscription class: the subscribers and their subscriptions.
Subscribers
Subscribers are instance specific and are added to the instance database. The main class for subscribers is the Subscriber class mentioned earlier, which has methods for adding and deleting subscribers to the instance. From the Subscriber class, you can also get information about what subscriptions a particular subscriber has.
Listing 16-14 shows an example of a managed console application used for adding subscribers to an instance. In a production application, it is unlikely that a console application would be used. Instead, the application probably would be an ASP.NET WebForms application. With the support for COM, it could also be an ASP application. Notice the using statement for the Microsoft.SqlServer.NotificationServices namespace. When compiling, the application needs a reference to the microsoft.sqlserver. notificationservices.dll file, which can be found in the C:Program FilesMicrosoft SQL Server90NotificationServices<version> bin directory. The code shows how we first create an instance of the NSInstance class. The NSInstance class has an overloaded constructor where one constructor method takes the instance name as a parameter and initializes the SQLNS instance. If the constructor method that does not take an instance name as a parameter is used, the instance can be set by a property. In that case, the SQLNS instance needs to be initialized separately by the Initialize method.
Then the instance is used as a constructor parameter for the initialization of the Subscriber class. The ID of the subscriber is set on the SubscriberId property, and Add is called.
Listing 16-14. Code to add subscriber
using System;
using Microsoft.SqlServer.NotificationServices;
class nssub {
static void Main(string[] args) {
string subId = "NielsB";
string inst = "Stock";
AddSubscriber(inst, subId);
}
static void AddSubscriber(string inst, string subId) {
NSInstance nsInst = new NSInstance(inst);
Subscriber sub = new Subscriber(nsInst);
sub.SubscriberId = subId;
try {
sub.Add();
}
catch(Exception e) {
Console.WriteLine(e.Message);
}
}
After successful completion of the methods, there will be an entry in the NSSubcribers table in the instance database. After subscribers have been added, devices need to be added, to define on what devices a subscriber can receive notifications. A device is tied to a delivery channel, which is tied to a delivery protocol. We haven’t yet discussed delivery channels or protocols much; they are covered later in this chapter. For now, what we need to remember is that the SQLNS framework comes with a couple of predefined delivery protocols. You may remember that in the configuration file in Listing 16-2, we defined a delivery channel named FileChannel, which used the file protocol. The file protocol is one of the predefined protocols in the SQLNS framework. It is mainly used for debugging, and we will use it for the time being until we discuss delivery channels and protocols in greater detail later on.
The reason we go on about this is that when we define the devices, we also need to define the delivery channel to use, as the code in Listing 16-15 shows. Be aware that there is a FOREIGN KEY CONSTRAINT between the NSDeliveryChannels table and the NSSubscribersDevices table on the DeliveryChannelName columns. In other words, you need to make sure that the DeliveryChannelName property on the SubscriberDevice instance exists in the NSDeliveryChannels table. The records in the NSDeliveryChannels table depend on what has been entered in the configuration file under the <DeliveryChannels> node.
Listing 16-15. Adding a subscriber device
static void AddSubscriberDevice(string inst, string subId) {
NSInstance nsInst = new NSInstance(inst);
SubscriberDevice dev = new SubscriberDevice(nsInst);
dev.DeliveryChannelName = "FileChannel";
dev.DeviceAddress = "";
dev.DeviceName = "FileDevice";
dev.DeviceTypeName = "File";
dev.SubscriberId = subId;
try {
dev.Add();
}
catch(Exception e) {
Console.WriteLine(e.Message);
}
}
The code in Listing 16-15 shows the following properties on the SubscriberDevice class:
• DeliveryChannelName—The name of the delivery channel used by the device
• DeviceAddress—The address of the device
• DeviceName—The name of the device
• DeviceTypeName—The name of the device type that describes the subscriber device
• SubscriberId—The ID of the subscriber
Subscriptions
The subscriptions node defines what information each subscriber gets and on what device he gets them. The class to use to add subscriptions is the Subscription class. This class has methods to add and delete subscriptions for a specific subscriber, as well as methods and properties to get and set information about the subscription record.
The code in Listing 16-16 shows how the Subscription class is used to add a subscription for an existing subscriber. The NSInstance class is instantiated first, followed by the NSApplication class. The constructor of the application class takes the instance and the name of the application we want to instantiate as constructor parameters. The Subscription class is created with the instance and the name of the subscription class. The subscription class name is needed because there can be several subscription classes in the application. Now the Subscription class can be used, and we call the SetFieldValue on the class. SetFieldValue is a method that takes a name–value pair as separate parameters. The name parameter corresponds to a subscription field in the aforementioned subscription class, and the value part sets the value of that field. From the code, you can see how we set the device the subscriber wants the notifications on, what locale to format the notifications with, the event information (StockCode and ExchangeCode), and when to trigger a notification (TriggerVal). We set the SubscriberId property to indicate who this subscription is for, and finally, we call Add.
Listing 16-16. Adding a subscription
static void AddSubscription(string inst, string appName, string subId)
{
NSInstance nsInst = new NSInstance(inst);
//create an instance of the NSApplication class
NSApplication app = new
NSApplication(nsInst, appName);
Subscription sub =
new Subscription(app, "StockPriceSubscriptions");
//set the value of the fields
//in the subscription record
sub.SetFieldValue("DeviceName", "FileDevice");
sub.SetFieldValue("SubLocale", "en-US");
sub.SetFieldValue("StockCode", "SUNW");
sub.SetFieldValue("ExchangeCode", "NASDAQ");
sub.SetFieldValue("TriggerVal", 7);
sub.SubscriberId = subId;
try {
sub.Add();
}
catch(Exception e){
Console.WriteLine(e.Message);
}
}
The code in Listing 16-16 inserts the subscription information into the NS<SubscriptionClassName>Subscriptions table. The SQLNS framework, based on the schema in the subscription class, creates this table. The table is used when the framework generates notifications.
To allow subscribers to specify their own conditions in SQL Server 2005, condition actions were introduced. Condition actions are specified in the ADF but also must be specified (or entered by the user) as part of a subscription. The Subscription class now contains two additional fields, Condition and RuleName, that are used to specify the userdefined conditions. The Condition class is actually an abstract base class, and there are several subclasses that allow you to specify a single condition or a complex tree structure of conditions. These subclasses include
• ConditionTree
• AndCondition, OrCondition
• LeafCondition
• BetweenLeafCondition
• BooleanLeafCondition
• IsNullLeafCondition
• LinkLeafCondition
• SimpleLeafCondition
• NotCondition
If, for example, I wanted to allow the user-defined conditions instead of always looking for stock prices greater than a certain amount, I would change my user-interface and reprogram the AddSubscription method to use subclasses of Condition based on the user’s input.
Notifications
Notifications are generated by finding matching information in event and subscription tables by joining those tables. The notification data is the event information that meets the subscription requirements, plus any additional information the developer wants to include. The data to include in a notification is defined in one of several notification classes in the ADF file. Information required in the notification class are the event data, what content formatter to use for the notifications, what delivery protocols this notification class uses, and how long notifications should be re-sent before being deemed out of date. In addition to this information, the notification class can define whether the notification class uses the following:
• Digest delivery—Digest delivery groups all notifications to one subscriber during one notification generation and sends them as one notification.
• Notification batch size—Normally, the generator creates one batch per firing. This setting defines how many notifications should be included in one batch. If this is set, the generator breaks the batch into smaller sizes that meet the set size.
• Multicast delivery—Notifications that share identical data and are in the same distributor work item are formatted once and sent to all subscribers.
Listing 16-17 shows one NotificationClass, which defines the data in a notification. The class uses the XSLT formatter, and the file protocol is used to send the notifications. There is an expiration time of two hours. If a notification has not been sent successfully after two hours, the notification is considered to be out of date.
Listing 16-17. Notification class in the ADF file
<NotificationClasses>
<NotificationClass>
<NotificationClassName>%_App_%Notifications</NotificationClassName>
<Schema>
<Fields>
<Field>
<FieldName>StockCode</FieldName>
<FieldType>varchar(15)</FieldType>
</Field>
<Field>
<FieldName>ExchangeCode</FieldName>
<FieldType>varchar(15)</FieldType>
</Field>
<Field>
<FieldName>Price</FieldName>
<FieldType>decimal(18, 5)</FieldType>
</Field>
</Fields>
</Schema>
<ContentFormatter>
<ClassName>XsltFormatter</ClassName>
</ContentFormatter>
<Protocols>
<Protocol>
<ProtocolName>File</ProtocolName>
</Protocol>
</Protocols>
<ExpirationAge>PT2H</ExpirationAge>
</NotificationClass>
</NotificationClasses>
Running NSControl Update updates the NS<NotificationClassName> Notifications table with the fields from the notification class. In addition, it alters a SQL Server user-defined function (UDF), which was originally created when NSControl Create ran.
Remember that in the discussions about subscription classes earlier in this chapter, we mentioned that we needed event generation rules to generate notifications. Event rules are SQL queries defining the contents of a notification and what constitutes a notification. When a notification is created, the raw data is inserted into the notifications table, and the only way that can happen in an SQLNS application is through the notification function. In other words, the SQL query for the event generation rule needs to use the function in some way.
In pre–SQL Server 2005 versions of SQLNS, this was accomplished by executing an SQLNS-generated UDF in the SQL query, sending in the necessary parameters. The parameters to the UDF were based on the fields defined in the notification class schema plus some generic parameters. The notify function looked somewhat like this:
StockPriceNotificationsNotify(@SubscriberId NVARCHAR(255),
@DeviceName NVARCHAR(255), @SubscriberLocale NVARCHAR(10),
@StockCode varchar(15), @ExchangeCode varchar(15),
@Price decimal(18, 5))
One change in SQLNS between pre–SQL Server 2005 versions and the SQL Server 2005 version is the replacement of the notify function with an INSERT INTO / SELECT statement. This is an optimization, because in previous versions, the notify function had to be executed for each notification; the new mechanism is executed only once and is an optimizable T-SQL JOIN. In addition, you can add information from tables other than the subscription table and the event and event chronicle tables. This makes the notification generation mechanism more extensible.
Notice that the first columns in the INSERT statement are subscriber ID, device, and locale. These always need to be included, because the framework uses this information when generating and formatting the notifications. In addition to these fields, event data and other subscription data can be included.
Listing 16-18 shows the EventRules entry needed in the subscription class. In the event rule, we send in to the function the ID of the subscriber, the device, and the locale. This information is taken from the subscription table. The event information used are the stock code, the exchange code, and the price, all of which come from the event table. Finally, it is matched on the stock code and exchange code, and we want only data where the price is greater than the trigger value set in the subscription table. The EventRule also indicates in the EventClassName element which event it is for.
Listing 16-18. Event rule
</Schema>
<EventRules>
<EventRule>
<RuleName>EvtRule</RuleName>
<Action>
INSERT INTO %_App_%Notifications(
SubscriberId, DeviceName,
SubscriberLocale, RicCode,
ExchangeCode, Price)
SELECT s.SubscriberId, s.DeviceName, s.SubLocale,
e.RicCode, e.ExchangeCode, e.Price
FROM StockEvt e, %_App_%Subscriptions s
WHERE e.RicCode = s.RicCode
AND e.ExchangeCode = s.ExchangeCode
AND e.Price > s.TriggerVal
</Action>
<EventClassName>StockEvt</EventClassName>
</EventRule>
</EventRules>
<!-- Index schema information follows -->
<IndexSqlSchema/>
Note that in the EventRule element above, we are defining a specific condition to look for: stock prices greater than a user-specified amount. But what if some users want to be notified if prices are less than a certain amount? We would need to write another event rule. Perhaps some users would like to be notified only when two stock prices go above a certain amount as an indication of a market trend? Over time, this would lead to a proliferation of event rules, each of which would be run separately. In SQL Server 2005, there is support for user-specified conditions. Having a series of user-specified conditions may not be as efficient as a single event rule, but depending on how complex the user-specifications can get, it may be more efficient than having a plethora of separate event rules, as shown in Listing 16-19.
Listing 16-19. Condition action
</Schema>
<EventRules>
<EventRule>
<RuleName>StockCondition</RuleName>
<ConditionAction>
<SqlLogin>LowPrivLogin</SqlLogin>
<SqlUser>LowPrivUser</SqlUser>
<InputName>StockEvt</InputName>
<InputSchema>StockTraderApp</InputSchema>
<SqlExpression>INSERT INTO %_App_%Notifications(
SubscriberId, DeviceName,
SubscriberLocale, RicCode,
ExchangeCode, Price)
SELECT r.[Subscription.SubscriberId],
r.[Subscription.DeviceName],
r.[Subscription.SubLocale],
r.[Input.RicCode], r.[Input.ExchangeCode],
r.[Input.Price]
FROM [StockTraderApp].StockCondition AS r
</SqlExpression>
</ConditionAction>
<EventClassName>StockEvt</EventClassName>
</EventRule>
</EventRules>
Note that because the evaluation of user-defined rules involves dynamic SQL, the condition action evaluation should run as a low-privileged user. In addition, when constructing your subscription management application, you should never allow users to key in freeform expressions to be evaluated, but only to select expressions from a well-defined list in a drop-down list box.
In addition to writing the notifications to the notifications table, the notification function participates in creating notification batches. When the function is called for the first time during firing of a rule, the system creates a new notification batch record. All the notifications that the function creates are part of this batch unless a batch size has been defined, in which case multiple batches would be generated. When the batches are closed, the notifications are ready for formatting and distribution.
Distributor and Formatters
The part of the SQLNS framework that is responsible for both formatting and distributing notifications is the distributor. The distributor runs continuously and partitions the batches ready for distribution into smaller work items. This allows the system to take advantage of parallel processing, whereas multiple distributors can run in parallel and/or a single distributor can process multiple work items in parallel.
Part of the processing of the work items is formatting the notification data. The distributor is responsible for the formatting and routes the notifications to a content formatter.
The content formatter is a managed class that implements the IContentFormatter interface. The developer of an SQLNS application does not have to develop a customized formatter but can use the XSLT formatter that is part of the SQLNS framework. The XSLT formatter allows you to specify an XSL transform to be applied to the raw notification data. This XSL transform makes all the formatting changes that are required to prepare the notification data for display. The XSL transform does not have to be the same for a given notification class. The transform can be different, depending on locale and device.
The XSLT formatter reads the transform from a directory and creates an intermediate XML document in memory. This XML document contains the notification data. Then it applies the XSL transform to the document, and the result is the final formatted notification. The directory that the formatter reads the transform from is based upon whether the formatting is dependent on locale or device. Each type of device and locale supported should have an individual transform file placed in its own directory.
To define the XSLT formatter, an entry in the ADF file is needed in the NotificationClass section under the ContentFormatter node. Listing 16-20 shows the necessary entries in the ADF file for the XSLT formatter. The name of the class that is used for formatting is defined in the ClassName node. For a custom formatter, the assembly name needs to be defined, as well in an AssemblyName element. Following the ClassName comes the Arguments section, with one or more Argument nodes. When the XSLT formatter is used, the base directory needs to be defined, as well as the name of the transform file.
Listing 16-20. Content formatter information in the ADF file
</Schema>
<!--Schema information above -->
<ContentFormatter>
<ClassName>XsltFormatter</ClassName>
<Arguments>
<Argument>
<Name>XsltBaseDirectoryPath</Name>
<Value>%_BaseDirectoryPath_%</Value>
</Argument>
<Argument>
<Name>XsltFileName</Name>
<Value>NoOp.xslt</Value>
</Argument>
</Arguments>
</ContentFormatter>
<!-- Protocol information below -->
<Protocols>
The NoOp.xslt transform file in Listing 16-20 is a transform that comes with the sample applications of the framework. It is mostly used for debugging, and it outputs the raw notification data XML format. A slightly more functional transform is shown in the following code snippet:
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="notifications">
<html>
<body>
<xsl:apply-templates/>
<i>Thank you for using StockTrader and
SQL Server Notification Services.</i><br/><br/>
</body>
</html>
</xsl:template>
<xsl:template match="notification">
<b><xsl:value-of select="StockCode" /></b>
at
<b><xsl:value-of select="ExchangeCode" /></b>
is trading at: $
<b><xsl:value-of select="Price" /></b>
<br/><br/>
</xsl:template>
</xsl:stylesheet>
The XSLT transform matches the notifications and the notifications’ elements. The <xsl:apply-templates/> creates the header of the notification, and the <xsl:value-of select=""/> produces the body information. When the notifications are formatted, they are handed to the delivery channels and protocols for delivery to the subscribers.
Delivery
Formatted notifications are distributed through delivery channels to delivery services. Delivery channels are an abstraction consisting of two concrete parts: the delivery protocol and the configuring/addressing information necessary to identify an endpoint. It is the protocol’s responsibility to assemble formatted notifications into protocol packets that are sent through an external delivery system such as Simple Mail Transfer Protocol (SMTP).
The configuring/addressing information allows interaction with the delivery channel by including non–application-specific information. This information can be what gateway to use, authentication information, and so on. It is the distributor’s job to figure out what delivery channel should handle a notification by looking at the device targeted for the notification (remember that device information is part of a notification record). By looking at the device record in the subscriber devices table, the distributor determines which delivery channel to use. From there, the distributor decides on the protocol to use by looking up the channel in the delivery channels table and matching the channel with the protocol.
Delivery Protocols
Delivery protocols are one concrete part of delivery channels, and several channels can use the same protocol. The protocols used can be custom-developed protocols or either of the two protocols that are part of the framework:
• File—Writes text data to a file specified in the configuration file
• SMTP—Creates and routes messages through SMTP mail systems
Information about delivery channels and protocols is located in both the application configuration file and the ADF file. For the file protocol, the only thing that needs to be added to the configuration file in Listing 16-2 is an entry for what file the output should be written to. The following code shows the relevant part of the configuration file:
<DeliveryChannels>
<DeliveryChannel>
<DeliveryChannelName>FileChannel</DeliveryChannelName>
<ProtocolName>File</ProtocolName>
<Arguments>
<Argument>
<Name>FileName</Name>
<Value>%BasePath%NotifStockNotification.txt</Value>
</Argument>
</Arguments>
</DeliveryChannel>
</DeliveryChannels>
In the Argument node is an argument called FileName, which points to the actual file that receives the output. That is the only required argument for the file protocol. An additional, optional argument named Encoding defines what encoding to use in the output file. By default, this is utf-8. Every protocol used needs an entry in the Protocols section in each of the notification classes using that particular protocol. For the file protocol, the only entry needed is the ProtocolName, and it must be File. Listing 16-17 shows an example of this. Other protocols may need specific information in the header of a notification, and for this purpose, you use the Fields section with Field nodes (more about this later). Each protocol can also have different execution settings: the interval between retries for a failed delivery, how many times a delivery can fail before an error is reported in the event log, and so on. This information is recorded in the ProtocolProtocolExecutionSettings node.
Arguably, the file protocol is a “bare minimum” protocol, used mostly for debugging. The developer uses the file protocol to make sure that notifications are generated, formatted, and distributed as expected. For a production application, another protocol would be used, such as the SMTP protocol, which sends notifications to any SMTP mail system for delivery.
For SQLNS to be set up to work with the SMTP protocol, the entry in the configuration file’s DeliveryChannel section should point to the actual SMTP server used. As with the file protocol, the encoding style can also be specified, as the following code snippet from a configuration file shows:
<DeliveryChannels>
<DeliveryChannel>
<DeliveryChannelName>EmailChannel</DeliveryChannelName>
<ProtocolName>SMTP</ProtocolName>
<Arguments>
<Argument>
<Name>SmtpServer</Name>
<Value>SERVNB01</Value>
</Argument>
<Argument>
<Name>BodyEncoding</Name>
<Value>utf-16</Value>
</Argument>
</Arguments>
</DeliveryChannel>
</DeliveryChannels>
Notice that the argument name for the encoding type is different between the file protocol (Encoding) and the SMTP protocol (BodyEncoding). The ProtocolName element must be SMTP for the SMTP protocol.
Compared with the file protocol, the SMTP protocol needs some more entries in the /NotificationClasses/NotificationClass/Protocols/ Protocol section of the ADF file. The additional entries are the protocol fields we mentioned earlier. The use of protocol fields is to specify protocolspecific header information, such as Subject for an e-mail message. Protocol fields are named expressions that operate on raw notification data and subscriber information, and they are defined as FieldReference elements or SqlExpression elements in the ADF file’s Fields node of the /NotificationClasses/NotificationClass/Protocols/Protocol section. A FieldReference element references fields in the NotificationClass/ Schema/Fields, whereas a SqlExpression refers to arbitrary T-SQL expressions. An example of SqlExpression fields is shown in Listing 16-21. It shows the fields that the SMTP protocol uses. There are three required fields: Subject, From, and To. It also has two optional fields: Priority and BodyFormat. Listing 16-21 also shows how arbitrary string constants can be used from the SqlExpression element, by enclosing the constant in apostrophes. The apostrophes are defined by their entity reference, '.
Listing 16-21. SMTP protocol defined in the ADF file
<Protocols>
<Protocol>
<ProtocolName>SMTP</ProtocolName>
<Fields>
<Field>
<FieldName>Subject</FieldName>
<SqlExpression>
'Stock Notification: '+
CONVERT(varchar(30), GetDate())
</SqlExpression>
</Field>
<Field>
<FieldName>From</FieldName>
<SqlExpression>
'[email protected]'
</SqlExpression>
</Field>
<Field>
<FieldName>To</FieldName>
<SqlExpression>DeviceAddress</SqlExpression>
</Field>
<Field>
<FieldName>Priority</FieldName>
<SqlExpression>'Normal'</SqlExpression>
</Field>
<Field>
<FieldName>BodyFormat</FieldName>
<SqlExpression>'html'</SqlExpression>
</Field>
</Fields>
</Protocol>
</Protocols>
Customization
In the last part of this section about delivery, we want to mention briefly that when existing protocols do not meet the requirements for the application, customized delivery protocols can be developed and used. Examples of custom protocols are protocols for MSMQ and Microsoft Instant Messenger. To develop a custom delivery protocol, developers need to implement one of two interfaces from the Microsoft.SqlServer.NotificationServices namespace:
• IHttpProtocolProvider—This interface makes it easy to create an HTTP-based protocol. All HTTP-related functionality already exists, so developers only have to provide the code for formatting the envelope and processing responses.
• IDeliveryProtocol—Developers can use this interface for non–HTTP-based protocols or where more flexibility is required from the IHttpProtocolProvider.
When a custom protocol is developed, it needs to be defined in the Protocols section of the configuration file, with information about the protocol name, the class name, and what assembly the class can be found in. Also, a delivery channel that uses the protocol needs to be defined in the DeliveryChannels node of the configuration file.
Customization is beyond the scope of this chapter; for more information, look in SQL Server Books Online.
Where Are We?
SQL Server Notification Services is a platform for developing and deploying applications that generate and send notifications to users. It uses Windows services and SQL Server as the foundation for the framework. A series of commercial applications that uses .NET Alerts (most use it in conjunction with Notification Services) is available for subscribers at http://www.microsoft.com/alert.