Share via

Upgrading Replicated Databases

  • Article

SQL Server 2005 supports upgrading replicated databases from previous versions of SQL Server; it is not required to stop activity at other nodes while a node is being upgraded. Ensure that you adhere to the rules regarding which versions are supported in a topology:

  • SQL Server version 7.0 service pack 4 (SP4) is the minimum version required to participate in a replication topology with SQL Server 2005. If you use SQL Server 2000, service pack 3 (SP3) or greater is required.
  • A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher).
  • A Publisher can be any version as long as it less than or equal to the Distributor version.
  • Subscriber version depends on the type of publication:
    • A read-only Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server version 7.0 Publisher running can have SQL Server 2005 Subscribers; and a SQL Server 2005 Publisher can have SQL Server version 7.0 Subscribers.
    • An updatable Subscriber to a SQL Server 2005 transactional publication can be any version equal to or greater than SQL Server 2000 SP3.
    • A Subscriber to a merge publication can be any version less than or equal to the Publisher version.

Note

This topic is available in the Setup Help documentation and in SQL Server 2005 Books Online. Topic links that appear as bold text in the Setup Help documentation refer to topics that are only available in Books Online.

Upgrading to Standard Edition, Workgroup Edition, or Express Edition

Before upgrading from one edition of SQL Server 2005 to another, verify that the functionality you are currently using is supported in the edition to which you are upgrading. For more information, see the section "SQL Server 2005 Replication Features" in the topic Features Supported by the Editions of SQL Server 2005.

New Replication Agent Security Model

In previous versions of SQL Server, agents ran, by default, under the context of the SQL Server Agent service account. SQL Server 2005 allows fine-grained control over each account under which the replication agents run and make Windows Integrated connections to databases and other resources; a different account can be specified for each agent. For more information, see Security Considerations for Replication and Replication Agent Security Model.

The new security model has the following implications for upgrading and running more than one version of SQL Server in a topology:

  • Replication scripts created from SQL Server 2000 or SQL Server 7.0 should be upgraded for SQL Server 2005 in order to take advantage of security enhancements. For more information, see How to: Upgrade Replication Scripts (Replication Transact-SQL Programming).

  • A Distributor or Subscriber upgraded from a previous version of SQL Server to SQL Server 2005 continues to run under the SQL Server Agent account and likely has more privileges than it requires. After upgrade we recommend that you specify separate accounts for the agents with the appropriate minimum privileges. To specify separate accounts:

    1. Script out the publication and subscriptions.
    2. Make changes to the scripts. For more information, see How to: Upgrade Replication Scripts (Replication Transact-SQL Programming).
    3. Drop the publication and subscriptions. For more information, see Publishing Data and Database Objects and Subscribing to Publications.
    4. Recreate them using the modified scripts.
      For information on the privileges required by agents, see Replication Agent Security Model; for information on managing logins and passwords, see Managing Logins and Passwords in Replication. New replication configurations created after an upgrade requires specific account configuration for each replication agent.

    Note

    Any agents configured to use SQL Server Authentication for local database connections is changed to use Windows Authentication. Local connections are those connections made by an agent to an instance of SQL Server running on the same computer as the agent. For example, the Merge Agent for a pull subscription runs at the Subscriber, so the connections it makes to the Subscriber are local connections.

  • Participants in a replication topology running previous versions of SQL Server retain the previous replication security model unchanged. For example:

    • A pull subscription to a Subscriber running SQL Server 2000 or SQL Server version 7.0 does not use the new security model because the Merge Agent or Distribution is created at the Subscriber.
    • A push subscription from a Distributor running SQL Server 2005 to a Subscriber running SQL Server 2000 or SQL Server version 7.0 uses the new security model because the Merge Agent or Distribution is created at the Distributor.
    • A Publisher running SQL Server 2000 or SQL Server version 7.0 with a Distributor running SQL Server 2005 does not use the new security model (for the Snapshot Agent, Log Reader Agent, or Queue Reader Agent) because the agents are created in the context of the publication database.

Running Agents After Upgrade for Merge Replication

After upgrade, run the Snapshot Agent for each merge publication and the Merge Agent for each subscription to update replication metadata. You do not have to apply the new snapshot, because it is not necessary to reinitialize subscriptions. Subscription metadata is updated the first time the Merge Agent is run after upgrade. This means that the subscription database can remain online and active during the Publisher upgrade.

Merge replication stores publication and subscription metadata in a number of system tables in the publication and subscription databases. Running the Snapshot Agent updates publication metadata and running the Merge Agent updates subscription metadata. It is only required to generate a publication snapshot. If a merge publication uses parameterized filters, each partition also has a snapshot. It is not necessary to update these partitioned snapshots. (In SQL Server 2000, parameterized filters were referred to as dynamic filters, and partitioned snapshots were referred to as dynamic snapshots).

Run the agents from Microsoft SQL Server Management Studio, Replication Monitor, or from the command line. For more information about running the Snapshot Agent, see the following topics:

For more information about running the Merge Agent, see the following topics:

After upgrading SQL Server in a topology that uses merge replication, change the publication compatibility level of any publications if you want to use new features. For more information, see Using Multiple Versions of SQL Server in a Replication Topology.

Web Synchronization for Merge Replication

The Web synchronization option for merge replication requires that the SQL Server Replication Listener (replisapi.dll) be copied to the virtual directory on the Internet Information Services (IIS) server used for synchronization. When you configure Web synchronization, the file is copied to the virtual directory by the Configure Web Synchronization Wizard. If you upgrade the SQL Server components installed on the IIS server, you must manually copy replisapi.dll from the COM directory to the virtual directory on the IIS server. For more information about configuring Web synchronization, see Configuring Web Synchronization.

Restoring a Replicated Database from a Previous Version

To ensure replication settings are retained when restoring a backup of a replicated database from a previous version: restore to a server and database with the same names as the server and database at which the backup was taken.

See Also

Concepts

Replication Backward Compatibility
Using Multiple Versions of SQL Server in a Replication Topology

Other Resources

Administering Replication
Replication Enhancements
Version and Edition Upgrades

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Added a statement that subscription databases may remain online and active during a Publisher upgrade.