Building Effective SQL Server Notification Applications Part I

SQL Server Notification Services provides a powerful framework for developing and hosting notification applications and the 2005 version comes with a host of new features designed to make our lives, as developers and administrators of notification systems, a bit easier.

Upgrading to SQL Server 2005 Notification Services

By Joe Webb

SQL Server Notification Services provides a powerful framework for developing and hosting notification applications and the 2005 version comes with a host of new features designed to make our lives, as developers and administrators of notification systems, a bit easier. However, the upgrade process isn’t as straightforward as you might expect. Joe Webb shows you exactly what you need to do to get started with SQL Server 2005 Notification Services.

From the new graphical management tool that’s integrated into SQL Server Management Studio, to the management API that provides a way to create and manipulate an instance using a .NET programming language, the enhancements brought forth by SQL Server 2005 Notification Services are a welcome addition to even the most finicky database administrator or developer.

But for those of us who have an existing SQL Server Notification Services (SSNS) instance in production, these enhancements, as compelling as they may be, aren’t instantly available after upgrading to SQL Server 2005. In fact, your existing Notification Services instance won’t even work after upgrading your database engine; some relatively straightforward changes must be made to your instance in order for it to work properly in the new version of SQL Server.

Upgrading your Notifications Services instance

Microsoft has taken great care in trying to ensure that each SQL Server database engine upgrade goes as smoothly as possible. Unfortunately, SSNS 2005 introduces some pretty significant changes to its infrastructure that prevents a seamless and completely automated upgrade of SSNS v2.0 instances to SSNS 2005 instances.

In many respects, this is to be expected since a SSNS instance is not wholly contained within a SQL Server instance. There are other, external components and dependencies that affect an instance’s upgrade path. For example, a SSNS v2.0 instance may be spread among multiple servers and creates at least one Windows service. Additionally, a SSNS instance may have custom components and subscription management applications that work in conjunction with SSNS but that are not entirely contained within SQL Server. Furthermore, SSNS 2005 has made some rather fundamental changes in the way applications are designed that prevent v2.0 instance from working under the new version without modification.

So, what are the changes and how do we need to modify our instances in preparation for an upgrade? Let’s look at a typical upgrade example.

Before you upgrade to the SQL 2005 engine

For the reasons described above, SSNS instances are not upgraded when the engine itself is upgraded. In order to endure a smooth upgrade of your SSNS instances from v2.0 to SSNS 2005, there are three crucial tasks to perform before you upgrade the database engine to 2005:

  1. Backup your NS databases
  2. Uninstall the samples
  3. Stop, disable, and un-register your NS instances

1) Backup your databases

As with any upgrade, you should back up your user and system databases prior to applying the upgrade. A SSNS v2.0 instance database is named <InstanceName>NSMain while the application database is called <InstanceName><ApplicationName>. For example, if you have created an instance called UserGroup and an application entitled NewsLetters, your instance database and application database would be named UserGroupNSMain and UserGroupNewsLetters, respectively. The instance database stores information about subscribers and the devices they use, while the application database contains information about subscriptions, events, and notifications. Both are required by the SSNS instance so you should backup all instance and application databases prior to your upgrade.

2) Uninstall the samples

SSNS v2.0 came with four sample applications that demonstrate how it may be employed in various business situations. SSNS 2005 expands on these sample applications and provides an additional two samples to further illustrate its capabilities.

In preparing to upgrade your instance, you should remove the v2.0 sample applications from your system to prevent any conflicts during the upgrade process. The v2.0 samples won’t work on the upgrade system, anyway. The easiest way to eliminate the samples is to run from the Notification Service command prompt the RemoveSamplesDB.cmd utility located in the v2.0.NNNN.0\Samples folder.

3) Stop, disable, and unregister your NS instances

Finally, you should stop, disable, and un-register each SSNS instance on each server where a SSNS component is installed. This will stop and remove the Windows service associated with the instance. It will also remove the registry entries and performance counters for the instance. It will leave the instance and application databases untouched.

Use the NSControl command line utility in a Notification Services command prompt to stop, disable, and un-register an instance, as follows:

Upgrade the database engine

Now, you’re ready to upgrade the database engine. During the installation, if you elect to upgrade your existing database instance (rather than installing a parallel named instance of SQL Server 2005), then all databases in that instance will be converted to the new SQL Server 2005 format, including the SSNS instance and application databases.

As with previous versions of SQL Server, SQL Server 2005 allows each database to behave as if it were running in a prior version. This is known as the compatibility level of the database. It does not affect the format of the database, only the characteristics of its behavior. For example, if running in a SQL Server 6.5 compatibility level, the TOP clause is not available.

After upgrading the database engine, you can verify a database’s compatibility level using the following T-SQL commands from a query window in SQL Server Management Studio:

If you need to alter the compatibility level for a database, use the following T-SQL commands.

SQL Server Management Studio may also be used to view and change a database compatibility level. Right-click on the database in question, select Properties from the context menu, and view the Options page.

After you upgrade the database engine

You can now walk through the steps that will actually upgrade your NS 2.0 instances to 2005:

  1. Install SQL Server 2005 Notification Services
  2. Recompile your custom components
  3. Update the Notification Services Metadata
  4. Update the Notification Services instance
  5. Register the instance
  6. Enable and start the instance
  7. Review and test the upgrade

1) Install SQL Server 2005 Notification Services

Now that the database engine has been updated, you can install Notification Services. To do this, run the SQL Server 2005 installation program and select the Notification Services option. For the single server deployment scenario depicted in Figure 1, all SSNS components are installed on the database server so installing SSNS on that server is all that is required.


Figure 1 – Single server deployment scenario

For the remote database deployment and scale-out deployment scenarios, you’ll need to install SSNS on each server where a SSNS component will reside. Figure 2 and Figure 3 illustrate the remote database deployment and the scale-out deployment scenarios, respectively.


Figure 2 – Remote database deployment scenario


Figure 3 – Scale-out deployment scenario

2) Recompile your custom components

The first publicly available version of SSNS was v2.0; it was developed in C# using v1.0 of the .Net Framework. The next full release of SSNS, v2.0 SP1, brought with it a new version of the Notification Services DLL that was built using v1.1 of the .Net framework. Now, as you might expect, SQL Server 2005 comes with and uses v2.0 of the .Net framework. There is no version of the SSNS DLL that works with a prior version of the .Net framework.

This means that any custom components that that you have developed for use with a prior version of SSNS will not work with SQL Server 2005 without some modification, since they reference the previous version of the Notification Services DLL. You’ll need to modify and recompile each custom event provider, custom delivery protocol, and custom content formatter so that it references the newer version of the Notification Services DLL. The Notification Services DLL may be found in the …\Program Files\Microsoft SQL Server\90\NotificationServices\n.n.nnn\bin folder, where n.n.nnn is the build number for the installed version.

Any subscription management applications you may have built using a prior version of the Notification Services DLL will also have to be updated to the newer version.

3) Update the Notification Services Metadata

SSNS 2005 stores information about each instance in the msdb system database; prior versions of SSNS did not store information in there. So, after installing SSNS 2005 we need to ensure that the appropriate metadata for each SSNS instance is created. This can be done using the NSControl command line utility:

After running the utility, your SSNS instance will appear in SQL Server Management Studio as shown in Figure 4.


Figure 4 – SQL Server Management Studio

Although, your SSNS instance now appears in SQL Server Management Studio, it is not ready for production; it must be modified, upgraded, and updated to work with SSNS 2005.

4) Update the Notification Services instance

In the prior two versions of SSNS, we developed our subscription rules using the provided Notify() User Defined Function. When the Generator executed according to its schedule, it invoked the appropriate matching rules. The Notify() function was used to create notification rows in the Notifications table. This was the only supported way of creating notifications. A typical match rule would look similar to the following excerpt from an Application Definition File (ADF).

One of the really nice enhancements in SSNS 2005 is the addition of a new view that allows match rules to be written without calling the Notify() function. This not only makes developing our match rules a bit more straightforward, but it also has a positive impact on overall system performance since the Notify() function invoked an extended stored procedure to accomplish its work.

When upgrading your SSNS v2.0 instance to SQL Server 2005, you’ll need to modify your subscription rules so that they make use of the newly available Notifications view rather than using the now-obsolete Notify() function. Our prior match rule example would be rewritten as follows:

Once the match rules have been modified, the instance must be upgraded and updated. The following NSControl commands maybe used to upgrade and update your instance:

You may also use SQL Server Management Studio to upgrade and update your instance by right-clicking on the instance, selecting Tasks from the context menu, and choosing the appropriate task as show in Figure 5:


Figure 5 – Using SQL Server Management Studio to upgrade an instance

After upgrading and updating your instance, you can register the instance.

5) Register the instance

As in prior versions of the product, the SSNS instance should be registered on each server where a component of the instance will reside. Registering an instance creates the appropriate registry entries and performance counters on the server. It can also create a Windows service for the instance, if needed. For a single server deployment scenario, registering is done on the sole server participating in the deployment. For the remote database and scale-out deployment scenarios, you should register the instance on each of the servers.

Use the following command, issued from a Notification Services command prompt, to register an instance. Other command line arguments may also be required depending on your implementation. See Books Online for a full list of command line arguments for NSControl register.

SQL Server Management Studio may be used to register an instance by right-clicking on your instance, selecting Tasks from the context menu, and choosing Register. Figure 6 depicts the dialog box where you can supply additional information, such as the service account used by the Windows service and the database login used to connect to the SQL Server instance.


Figure 6 – Registering your instance using SQL Server Management Studio

6) Enable and start the instance

You’re now ready to start the instance. Use the following commands in the Notification Services command prompt to prepare the instance for use.

Figure 7 illustrates how SQL Server Management Studio may be used to enable an instance. Once enabled, you can start the instance from the same menu.


Figure 7 – Using SQL Server Management Studio to enable (and start) an instance

7) Review and test the upgrade

To ensure that the migration has gone as expected, you should review the Applications page of the Instance Properties as illustrated in Figure 8.


Figure 8 – Reviewing the instance properties

You should also ensure that the Windows service has successfully started by reviewing the properties page for the Windows Services and the Associated Components as illustrated in Figure 9.


Figure 9 – Reviewing the windows service properties

If all has gone smoothly to this point, you likely have a new SSNS 2005 instance ready for testing. You’ll want to make sure that the application performs as expected by submitting to it a series of known inputs and ensuring that the correct outputs are produced. You’ll also want to stress test the upgraded instance; you’ll likely find that it performs even better than its SSNS v2.0 predecessor.


Unlike upgrading the SQL Server database engine, upgrading to SQL Server 2005 Notification Services from a prior version is not a truly automated and transparent process. The new version requires that each SSNS instance be modified and upgraded individually so that it adheres to the new design specifications inherent in the latest version of the product. Fortunately those modifications, although significant in their impact, are not difficult in practice. And the benefits of upgrading are great! Next month, we’ll examine the rich new set of features that are available after the upgrade.

Joe Webb, a Microsoft SQL Server MVP, serves as Chief Operating Manager for WebbTech Solutions, a Nashville-based IT consulting company. He has over 13 years of industry experience and has consulted extensively with companies in the areas of software development, database design, and technical training. He can be reached at