Export (0) Print
Expand All

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

SQL Server 2005

Updated: 14 April 2006

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.

ms143756.note(en-US,SQL.90).gifImportant:
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.

  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 -name instance_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 -name instance_name
    2. Close the Command Prompt window.

  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.

  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 -name instance_name -database instanceDatabaseName -schema instanceDatabaseSchema -server databaseServer
  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.

Community Additions

ADD
Show:
© 2015 Microsoft