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

If you have an existing instance of Notification Services 2.0 running on Microsoft SQL Server 2000, you can upgrade that instance to Microsoft SQL Server 2005. To perform the upgrade, you must upgrade the databases and upgrade the instance of Notification Services. The following procedure shows how to upgrade an instance using command-prompt utilities.

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. On one server where the instance is registered, disable the instance of Notification Services by using the Notification Services 2.0 version of the nscontrol command-prompt utility:

    1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, 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. Open a Notification Services 2.0 command prompt window and 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.

    Installations of Notification Services 2.0 are not altered during the upgrade process.

To migrate the instance of Notification Services

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

    1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click Notification Services Command Prompt.
    2. Use the nscontrol register command to register the instance. The following command shows how to register the instance and create a Windows service that connects to the database server using Windows authentication:
      nscontrol register -nameinstanceName-serverdatabaseServer -service****-serviceusername** username -servicepassword password
      For more information, type nscontrol register -? or see nscontrol register Command.
  2. On one server, type the following command to repair instance metadata:

    nscontrol repair -nameinstance_name-databaseinstanceDatabaseName-schemainstanceDatabaseSchema

  3. Type the following command to upgrade instance and application data:

    nscontrol upgrade -nameinstanceName

  4. 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.

  5. Type the following command to update the instance of Notification Services:

    nscontrol update -inICFPath\ICFName.xml

    Exact arguments vary depending on the authentication mode.

  6. Type the following command to enable the instance of Notification Services:

    nscontrol enable -nameinstanceName

    Exact arguments vary depending on the authentication mode.

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

  8. 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.

  9. When you are ready to start the instance, type the following command on each server that runs the Notification Services engine components:

    **net start NS$**instanceName

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 (SQL Server Management Studio)

Other Resources

nscontrol Utility

Help and Information

Getting SQL Server 2005 Assistance