Top 10 Enhancements in SQL 2005 Notification Services

When Microsoft created SQL Server Notification Services (SSNS) as an add-on to SQL Server 2000, they endeavored to create a robust framework for developing and hosting scalable notification applications using technologies already familiar to many developers – XML, Transact-SQL, the .NET Framework, and of course SQL Server 2000. In SQL Server 2005, Microsoft has fully integrated SSNS into the product and has extended its intrinsic capabilities. It offers improvements in such areas as performance, manageability, and flexibility.

In the Part I of this article, I described how to upgrade an existing SSNS v2.0 instance to SSNS 2005. In this article, I want to examine some of the best new features and characteristics available in the 2005 version and how they may be leveraged to improve an existing notification application or to develop a new notification application with enhanced capabilities.Hopefully, it will also supply the “thread” that will help stitch together the disparate SSNS resources available on MSDN Books Online, and elsewhere, and so help you exploit SSNS to its fullest potential.

Improved performance

For most database professionals, whether they are a database administrator or a database developer, performance is constantly at the forefront of their minds. Before making any change to a system, they ask themselves: “How will this modification affect the overall performance of the system?” Perceived performance, along with its counterparts of throughput and concurrency, are key factors in gauging the success of database system.

Because SSNS was designed from the ground up to process events and notifications in set-based batches rather than processing each subscription as a separate query, it is very efficient and highly scalable. SSNS v2.0 boasted of “internet scalability”. SSNS 2005 carries forth, purporting to offer even better performance through a tighter integration into the SQL Server product and through support for the 64-bit platform.

Improved subscription processing

A SSNS application collects event data through its event providers; the event providers submit event data to the SSNS application where it is stored in the application database to await processing by the Generator component. Periodically the Generator executes the matching rules to combine event data with the subscription data and produce notifications.

The process is the same for all versions of SSNS, but SSNS 2005 is more efficient at it than its predecessors, largely due to the way in which the match rules are defined. In prior versions of SSNS, the only supported means of writing an event rule was to use the provided Notify() function. The Notify() function made use of several extended stored procedures, use of which tends to degrade overall system performance.

The newer version of SSNS requires that the match rules be written using Transact-SQL to insert data directly into a notification view rather than calling the Notify() function. This change not only makes developing the match rules much more intuitive, but it also improves the match rule performance.

For more information about how the SSNS v2.0 must be altered under SSNS 2005, see Part I of this article.

 More efficient vaccuuming

As a SSNS application runs, it amasses event data, subscription data, notification data, and distribution data in its underlying control tables. Over time, the amount of data can grow quite voluminously, increasing the size of the application database and adversely affecting system performance.

Fortunately, SSNS includes a mechanism for automatically and routinely cleaning up obsolete data in its underlying tables. This built-in mechanism, available in all versions of SSNS, is known as the vacummer. In the newer version of SSNS, the vacummer operates more efficiently than in the prior versions of the product, approximately 150% more efficiently according to SQL Server Books Online.

To learn more about scheduling the vacuumer to remove obsolete SSNS data, visit SQL Server Books Online on the MSDN site (http://msdn2.microsoft.com/en-us/library/ms171064.aspx).

64-bit SQL Server is supported

In April of 2002, Microsoft released a 64-bit version of SQL Server 2000. Some of the advantages of the 64-bit platform include a larger directly-addressable memory space and better caching and busing operations. More information may be found in the Advantages of a 64-bit Environment Microsoft whitepaper.

Unfortunately, SSNS v2.0 was not supported on the new platform but SSNS 2005 now fully supports the 64-bit computing platform and can take full advantage of its benefits.

Improved manageability

Although SSNS v2.0 provides a robust framework for developing and hosting large-scale notification applications, it unfortunately lacks a correspondingly robust set of development and management tools to help database professionals perform their daily tasks. To address these shortcomings, SSNS 2005 provides a new graphical instance management tool that’s integrated in SQL Server Management Studio, and a new management Application Programming Interface (API) that allows developers to programmatically create and manage SSNS instances and applications.

Management Studio integration

Since SSNS v2.0 was created after SQL Server 2000 had been released, true integration into Enterprise Manager was rather impractical. As such the only way to manage a SSNS v2.0 instance is through a rather primitive, yet functional, command line utility called NSControl. The utility can be used to create, register, update, and even delete a SSNS instance on a server.

Now that SSNS 2005 is completely integrated in SQL Server 2005, SSNS instances may be wholly managed from within SQL Server Management Studio. The graphical-based tool may be used to create register, update, and delete a SSNS instance, just as NSControl was used in SSNS v2.0. Additionally, SQL Server Management Studio may be used to start and stop an instance, upgrade an instance, and even generate the XML source code used to create and configure the instance.

The NSControl utility is still available in the current version of SSNS and is still quite useful for scripting SSNS development and management tasks. Figure 1 shows a sample ABCPress instance in SQL Server Management Studio:

Figure 1 – SQL Server Management Studio

For more information on using SQL Server Management Studio to manage a SSNS instance, see Part I of this article.

A new Management API

SSNS 2005 provides a new way to define, create, and manage SSNS instances and applications using the new SSNS Management API, Microsoft.SqlServer.Management.Nmo. Using this API, developers can forego the XML document-based source code of SSNS v2.0 and use object-oriented programming techniques to manipulate SSNS applications.

For example, the following C# code demonstrates how the management API may be used to enable or disable an application from within a checkbox click event:

Creating an Instance Configuration File (ICF) and an Application Definition File (ADF) is certainly still supported.

For more information on the SSNS Management API, visit SQL Server Books Online on MSDN (http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.nmo.aspx).

Improved flexibility

SSNS 2005 offers greater flexibility in terms of how an instance and application may be configured. In the new version, you can:

1. Host the SSNS Engine in your own application
2. Create the SSNS database objects in an existing database
3. Use the new Analysis Services Event Provider to gather event data from an Analysis Services cube
4. Allow users to create subscriber-defined conditions
5. Insert subscriber and subscription information via new views.

Hostable execution engine

SSNS components, such as the Generator and the Distributor, run as part of the SSNS Engine. In prior versions of the product, the SSNS Engine runs in a Windows Service that is created on each server where a SSNS component resides. The Windows Service is created when the SSNS instance is registered on the server. You can enable or disable various components in the SSNS Engine using the NSControl command line utility.

In SSNS 2005, we’re given more flexibility in deploying our applications. If we choose, we can still distribute the SSNS Engine as part of a Windows Services, just as in prior versions. However, we also have the ability, using the SSNS API, to run the SSNS Engine as part of an application that we create. Running the SSNS Engine in a custom application simplifies deployment and makes the application self-contained.

The following C# example demonstrates how a button event handler may be used to start or stop a SSNS instance that is hosted in the application:

 For more information on hosting the SSNS Engine, visit SQL Server Books Online on the MSDN site (http://msdn2.microsoft.com/en-us/library/ms171236.aspx).

Database independence

In SSNS v2.0, each new SSNS instance creates at least two new SQL Server databases:

• An instance database – contains metadata about the instance itself as well as subscriber and subscriber device information.
• An application database – contains information about the application, events, subscriptions, notifications, and distribution of the notifications. An application database is created for each application associated with the instance.

In SSNS 2005, we have ability to create the SSNS database objects in an existing SQL Server 2005 database. By specifying a database name and a unique schema for the objects, they will be created in a database of our choosing. This flexibility may be useful where an existing database already contains information relevant to our notification application, such as subscriber or event data.

To use an existing database as the instance database, supply the DatabaseName element in the Database node of the ICF when creating a new SSNS instance. If a database with that name already exists, the SQLNS instance database objects are created in that database. If a database by that name doesn’t already exist, a new database will be created with the supplied name. The new database will be created in the default location and with the default configuration.

Since a single database can contain more than one SSNS instance, it is a good practice to provide a unique schema name for an instance. This is accomplished using the SchemaName element in the Database node of the ICF as illustrated in the following snippet from a sample ICF:

Books online provides information on both using an existing database for your instance data and using an existing database for your application data.

Analysis Services Event Provider

SSNS v2.0 provides two event providers that can be used to submit event data into an SSNS application:

• The SQL Server Event Provider – periodically queries a SQL Server table, view, or even a linked server, to retrieve event data
• The File System Watcher Event Provider – monitors a specified file system folder for the presence of a new .xml file which contains event data.

If neither of these event providers satisfies the requirements of your application, the SSNS framework allows you to create custom event providers using the SSNS API. These event providers can consume information from a web site, a TCP/IP port, or just about any other source of data that you could programmatically access.

SSNS 2005 adds a new event provider to our repertoire, the Analysis Services Event Provider. Using this event provider, a SSNS application can monitor an Analysis Services cube and retrieve event data using multidimensional expressions (MDX). When using the Analysis Services Event Provider, the provider periodically uses the MDX queries to retrieve a two-dimensional result set, inserting it directly into the event table.

For more information about configuring the Analysis Services Event Provider, visit SQL Server Books Online (http://msdn2.microsoft.com/en-us/library/ms171254.aspx).

 Subscriber-defined conditions

SSNS 2005 expands on the kinds of subscriptions that are available to subscribers. It still supports the scheduled and event-driven subscriptions of prior versions – in fact these are still the recommended types of subscriptions due to their efficiency in processing. However, SSNS 2005 includes a new, more flexible, kind of subscription called subscriber-defined conditions.

Whereas the action of the traditional subscription types are defined by the SSNS developer, and the subscriber simply supplies any associated parameters for the action query, subscriber-defined conditions essentially allow each subscriber to specify the WHERE clause of an action query.

As an example, let’s consider a fictitious automobile manufacturer, ABC Motorworks. Using normal rule-based subscription processing, ABC Motorworks can create an SSNS application that notifies customers whenever a press release is issued for a car of a given model and year. However if the application was developed using subscriber-defined conditions, each customer would be able to further define the circumstances under which they would receive a notification. For example, they may not want to receive a notification where the word “award” is found in the title. Or, perhaps they only want to see press releases where “recall” is found in the title.

However, as is often the case, this additional flexibility comes with some performance-related penalties. Subscriber-defined conditions cannot be processed as efficiently as the traditional subscriptions and hence the overall performance of the application suffers a bit. Whether this is noticeable or not certainly depends on a number of factors and careful consideration is warranted before implementing this new kind of subscription.

Subscriber-defined conditions are described in more detail in SQL Server Books Online (http://msdn2.microsoft.com/en-us/library/ms172509.aspx).

New subscriber and subscription views

As you develop a SSNS application, you’ll want to create testing scenarios along the way to ensure that the system is functioning as intended. Testing a SSNS application is similar to testing other types of application: you’ll subject the SSNS application to a known set of inputs and observe its resulting behavior, looking for the expected outputs.

To test SSNS applications, you must first set up the testing environment. Subscribers, subscriber devices, and subscriptions must be added to the application before you can expect to see any output when event data is submitted into the application.

In SSNS v2.0, the only supported way to create subscribers, subscriber devices, and subscriptions was using the SSNS API. A VBScript, a .NET application, or even a COM-based application was required to insert subscriber data into the instance.

For SSNS 2005 applications, setting up the testing scenarios has been simplified. SSNS 2005 provides new views that allow this information to be inserted directly into the system without having to use the SSNS API. The NSSubscriberView may be used to create subscriber records in an instance database. It has four columns:

• SubscriberId – uniquely identifies a subscriber within this instance
• Enabled – determines whether this subscriber is enabled in the instance
• Created – when the row was originally created
• Updated – when the row was last modified.

The following T-SQL script may be used to insert a new subscriber in a SSNS instance:

Similarly, the NSSubscriberDeviceView may be used to create a device for a subscriber. The T-SQL example below demonstrates how it may be used to create a device for the subscriber created in the prior example:

The SubscriberId column associates this device with a specific subscriber and the Enabled column determines whether this is an active device for the subscriber. The DeviceName names the device; the DeviceTypeName describes the type of destination device that will receive the notification; the DeviceAddress is the address to which the notification is sent; and the DeliveryChannelName specifies the channel used for delivery.

These views are primarily designed to assist with testing and the recommended method for subscription management application is still to access this information using the SSNS API.

For more information on using these new views, visit the SQL Server Books Online on the MSDN web site (http://msdn2.microsoft.com/en-us/library/ms173433.aspx). SQL Server Books Online (http://msdn2.microsoft.com/en-us/library/ms171377.aspx) also contains information about using the SSNS API to manage subscriber and subscription data.

Conclusion

SSNS 2005 provides many new and exciting benefits over prior versions of the product. Some of these features, such as improved operational efficiency and enhanced management tools are immediately available after upgrading from SSNS v2.0. Other features, including the hostable execution engine and the Analysis Services Event Provider will require modifications to your existing SSNS application in order to take full advantage of them. In either case, upgrading to SSNS 2005 is definitely worth considering.