Updating an Application

After you deploy an instance of SQL Server Notification Services, you can update applications hosted by the instance. You update an application by modifying the application definition and then using SQL Server Management Studio or the nscontrol update command to apply the changes.

To update a single application, you do not need to stop or disable the entire instance. You can leave other applications running that are hosted by the instance. However, if you also change values in the instance configuration, you must disable the entire instance before you apply the update.

Before you update an application, consider the following:

• If you change a subscription class in the application definition file (ADF), Notification Services creates new subscriptions tables. Notification Services backs up the existing subscription data; you can use these backups to restore your subscription data. For more information, see "Restoring Subscription Data" below.
• Updating an application usually deletes event and notification data. Plan to resubmit any batches of events that have not been processed before the update, or any batches of events that would have been processed during the application update.
• Make sure that any code in your application definition that creates a SQL Server object checks for and deletes or renames any existing object with the same name. The update process does not automatically delete or rename user-defined SQL Server objects, so by explicitly deleting or renaming SQL Server objects, you avoid a duplicate object error.
• If you are using Notification Services Standard Edition, and you specify options in your instance configuration or application definition that are not supported by Standard Edition, the update process stops without updating the instance. For more information, see Editions of Notification Services.

To update a notification application

• How to: Update One Application in a Multi-Application Instance (SQL Server Management Studio)
• How to: Update One Application in a Multi-Application Instance (Command Prompt)

To update an instance of Notification Services

• How to: Update an Instance of Notification Services (SQL Server Management Studio)
• How to: Update an Instance of Notification Services (Command Prompt)

Restoring Subscription Data

If you change a subscription class in the ADF, Notification Services recreates the subscription tables during the update. When this happens, Notification Services renames the existing tables by appending "Old" to the table name, as follows:

After you update the application, you can restore subscription data by copying the data from the backup tables to the new tables. One way to do this is to use Transact-SQL queries to select data from the backup tables and insert it into the new tables.

If you need to transform data while restoring it, such as adding a conditional value to a new column, consider using SQL Server 2005 Integration Services (SSIS) (SSIS) to move the data between tables.

After you verify the restored subscription data, you should delete or rename the backup tables. If you leave the tables and then later update the subscription class again, the update fails because the backup tables already exist.

Examples

Restoring Event-Triggered Subscriptions

The Flight sample application has a subscription class that uses event-triggered rules to generate notifications. For this subscription class, all subscription data is stored in a table named NSFlightSubscriptionsSubscriptions. If you change the subscription class and then update the Flight application, Notification Services creates a backup of the subscription data in a table named NSFlightSubscriptionsSubscriptionsOld.

When you write a query to copy the data from the backup to the new table, you need to know the column names. You can get this data by running the following two queries:

USE [FlightInstanceFlight];
SELECT * FROM [NSFlightSubscriptionsSubscriptions];
SELECT * FROM [NSFlightSubscriptionsSubscriptionsOld];

When you know the column names, you can write a query to copy data from NSFlightSubscriptionsSubscriptionsOld to NSFlightSubscriptionsSubscriptions.

In the update used for this example, the Carrier column in the subscription class (and rule) was renamed to Airline. This change is reflected in the query below.

USE [FlightInstanceFlight];
SET IDENTITY_INSERT [dbo].[NSFlightSubscriptionsSubscriptions] ON;
INSERT INTO [dbo].[NSFlightSubscriptionsSubscriptions]
( [SubscriptionId], [SubscriberId], [Created], [Updated], [Enabled], 
    [DeviceName], [SubscriberLocale], [LeavingFrom], [GoingTo], 
    [Airline], [Price] )
SELECT [SubscriptionId], [SubscriberId], [Created], [Updated], 
       [Enabled], [DeviceName], [SubscriberLocale], [LeavingFrom], 
       [GoingTo], [Carrier], [Price]
FROM [dbo].[NSFlightSubscriptionsSubscriptionsOld];
SET IDENTITY_INSERT [dbo].[NSFlightSubscriptionsSubscriptions] OFF;

When this query completes successfully, you can delete or rename NSFlightSubscriptionsSubscriptionsOld.

Restoring Scheduled Subscriptions

A Weather application uses scheduled subscriptions. The scheduled subscription data is stored in three tables: NSWeatherSubscriptionsSubscription, NSWeatherSubscriptionsSchedules, NSWeatherSubscriptionsTimeZones. Whenever the subscription class is modified and then the application is updated, you can restore the data in these tables using the following queries.

First, restore the time zone data:

USE [WeatherInstanceWeather];
INSERT INTO [dbo].[NSWeatherSubscriptionsTimeZones]
    ( [TimeZoneId], [UtcOffset] )
SELECT [TimeZoneId], [UtcOffset]
FROM [dbo].[NSWeatherSubscriptionsTimeZonesOld];

Next, restore the schedule data:

SET IDENTITY_INSERT [dbo].[NSWeatherSubscriptionsSchedules] ON
INSERT INTO [dbo].[NSWeatherSubscriptionsSchedules]
    ( [ScheduleId], [UtcTime], [TimeZoneId], [ScheduleType], 
      [ScheduleData], [ScheduleText] )
SELECT [ScheduleId], [UtcTime], [TimeZoneId], [ScheduleType], 
       [ScheduleData], [ScheduleText]
FROM [dbo].[NSWeatherSubscriptionsSchedulesOld]
ORDER BY [ScheduleId];
SET IDENTITY_INSERT [dbo].[NSWeatherSubscriptionsSchedules] OFF;

And finally, restore the subscription data:

SET IDENTITY_INSERT [dbo].[NSWeatherSubscriptionsSubscriptions] ON;
INSERT INTO [dbo].[NSWeatherSubscriptionsSubscriptions]
    ( [SubscriptionId], [SubscriberId], [Created], [Updated], 
      [Enabled], [ScheduleId], [DeviceName], [SubscriberLocale], 
      [City] )
SELECT [SubscriptionId], [SubscriberId], [Created], [Updated], 
       [Enabled], [ScheduleId], [DeviceName], [SubscriberLocale], 
       [City]
FROM [dbo].[NSWeatherSubscriptionsSubscriptionsOld];
SET IDENTITY_INSERT [dbo].[NSWeatherSubscriptionsSubscriptions] OFF;

After you have verified that the data has been transferred to the new tables, you can delete or rename the old tables.

See Also

Concepts

Adding an Application to an Instance
Removing an Application from an Instance

Other Resources

Updating Instances and Applications
SQL Server Integration Services

Help and Information

Getting SQL Server 2005 Assistance