How to: Migrate Notification Services 2.0 Instances to SQL Server 2005 (SQL Server Management Studio)

  • Article

If you have an existing instance of Notification Services 2.0 running on Microsoft SQL Server 2000, you can upgrade the databases and Notification Services and then migrate the Notification Services instance to Microsoft SQL Server 2005.

The following procedures show how to upgrade an instance using SQL Server Management Studio.

Important

Instances of Notification Services can be deployed in many different configurations. Use the following procedure as a guideline for developing and testing your own migration procedures.

To prepare servers for the upgrade

  1. Disable the instance of Notification Services using the Notification Services 2.0 version of the nscontrol command-prompt utility:

    1. On the Start menu, point to Programs\Microsoft SQL Server Notification Services, and then click Notification Services Command Prompt.
    2. Type the following command to disable the instance:
      nscontrol disable -nameinstance_name

  2. On each server that runs the NS$instanceName Windows service, stop the service:

    1. On the Start menu, point to Programs\Microsoft SQL Server Notification Services, and then click Notification Services Command Prompt.
    2. Type the following command to stop the instance:
      **net stop NS$**instanceName

  3. On each server that has an event provider, generator, distributor, or subscription management interface, unregister the instance.

    1. Type the following in the open Notification Services Command Prompt window:
      nscontrol unregister -nameinstance_name
    2. Close the Command Prompt window.

To upgrade the Database Engine and install Notification Services

  1. Using SQL Server 2005 Setup, upgrade the Database Engine from SQL Server 2000 to SQL Server 2005. For more information, see Upgrading the Database Engine.

  2. On each server that runs an event provider, generator, distributor, or subscription management interface, install SQL Server 2005 Notification Services.

    If Notification Services and the databases are located on the same server you can upgrade the database and install Notification Services at the same time.

To migrate the instance of Notification Services

  1. Open a SQL Server 2005 Notification Services Command Prompt window and add the instance information to the system databases:

    1. On the Start menu, point to All Programs\Microsoft SQL Server 2005\Configuration Tools, and then click Notification Services Command Prompt.
    2. Type the following command to repair instance metadata:
      nscontrol repair -nameinstance_name-databaseinstanceDatabaseName-schemainstanceDatabaseSchema-serverdatabaseServer

  2. Use SQL Server Management Studio to upgrade instance and application database data:

    1. On the Start menu, point to Programs\Microsoft SQL Server, and then click SQL Server Management Studio.
    2. Connect to the instance of SQL Server that hosts the instance and application databases.
    3. In Object Explorer, expand Notification Services.
    4. Right-click the instance, point to Tasks, and then select Upgrade.
      This upgrades the database schema, stored procedures, and version numbers in the instance and application databases.

  3. Modify notification generation rules in the application definition file (ADF) for each application hosted by the instance.

    • Modify all notification generation rules that use the Notify() function to use the INSERT INTO syntax. For example, change the following Notification Services 2.0 rule:

      SELECT dbo.FlightNotificationsNotify(S.SubscriberId, 
S.DeviceName, S.SubscriberLocale, E.Carrier, E.LeavingFrom, 
E.GoingTo, E.Price, E.Conditions)
    FROM FlightEvents E, FlightSubscriptions S
    WHERE E.LeavingFrom = S.LeavingFrom
    AND E.GoingTo = S.GoingTo
    AND ( (E.Carrier = S.Carrier) OR (S.Carrier = '*') )
    AND E.Price < S.Price

      To the following syntax, which no longer uses the Notify() function, but instead selects data and inserts it into a view named after the notification class (FlightNotifications):

      INSERT INTO FlightNotifications(SubscriberId, DeviceName, 
SubscriberLocale, Carrier, LeavingFrom, GoingTo, Price, 
Conditions)
SELECT S.SubscriberId, S.DeviceName, S.SubscriberLocale, 
E.Carrier, E.LeavingFrom, E.GoingTo, E.Price, E.Conditions
    FROM FlightEvents E, FlightSubscriptions S
    WHERE E.LeavingFrom = S.LeavingFrom
    AND E.GoingTo = S.GoingTo
    AND ( (E.Carrier = S.Carrier) OR (S.Carrier = '*') )
    AND E.Price < S.Price

    • Modify version numbers in the ADFs and ICF. (Optional)

    • If you have moved the instance, update the SystemName values in the ADF and the SqlServerSystem value in the ICF. These values might be parameters, which indicates that the values are in a ParameterDefaults node or are provided when creating the instance.

  4. In Object Explorer, right-click the Notification Services instance, point to Tasks, and then select Update.

    Provide the requested information in the Update dialog box. Updating the instance and its applications applies the ADF and ICF changes to the databases.

    Make sure to select Enable instance after it is updated.

  5. On each server where the instance was previously registered, register the instance.

    SQL Server Management Studio registers the instance on the local computer only. To use SQL Server Management Studio to register the instance, right-click the instance, point to Tasks, and then select Register.

    If you have to register the instance on servers where SQL Server Management Studio is not installed, use the SQL Server 2005 nscontrol register command-line utility to register the instance.

  6. Replace any custom components with new components compiled by using the SQL Server 2005 assemblies and the Microsoft .NET Framework 2.0.

  7. If using COM interop, reregister the core Notification Services assembly. For more information, see How to: Register the Core Notification Services Assembly for COM Interop.

  8. In Object Explorer, right-click the instance and select Start.

See Also

Tasks

Migrating Notification Services 2.0 Instances to SQL Server 2005
How to: Migrate Notification Services 2.0 Instances to SQL Server 2005 (Command Prompt)

Other Resources

Managing Instances and Applications How-to Topics (Notification Services)
nscontrol register Command

Help and Information

Getting SQL Server 2005 Assistance