Replication and Upgrading
When upgrading to Microsoft® SQL Server™ 2000, you can upgrade servers in your organization one at a time; however, when servers are used for replication, you must upgrade the Distributor first, the Publisher second, and then Subscribers. Upgrading servers one at a time following this sequence is recommended when a large number of Publishers and Subscribers exist because you can continue to replicate data even though servers are running different versions of SQL Server. You can create new publications and subscriptions with servers running instances of SQL Server 2000, and still maintain subscriptions created in SQL Server 6.5 or SQL Server 7.0.
When using transactional replication, you can upgrade Subscribers before the Publisher. If you are using immediate updating with snapshot replication or transactional replication, there are additional upgrade recommendations in this topic under Upgrading and Immediate Updating.
You can upgrade replication servers running SQL Server 6.5 or SQL Server 7.0 to SQL Server 2000. If the server is running SQL Server 6.5, you do not need to upgrade it to SQL Server 7.0 before upgrading to SQL Server 2000.
Important When upgrading servers configured for replication to SQL Server 2000, the database compatibility level must be set to 70 (version 7.0 compatibility) or later. If you have servers running in 65 (version 6.5) or an earlier compatibility level, temporarily change them to 70 or later during the upgrade process.
When the Publisher or Subscriber is running in 65 or an earlier compatibility level during upgrade to SQL Server 2000, error 15048 will be raised stating that the operation is supported only on SQL Server version 7.0 or SQL Server 2000.
For more information about setting the backward compatibility level, see SQL Server 2000 and SQL Server version 6.5.
If you are upgrading replication on a failover cluster, you must uncluster the previous installation before upgrading. Unclustering the previous installation means that you must delete all publications, remove replication, and reconfigure it after upgrading to SQL Server 2000. This will not be a requirement when upgrading SQL Server 2000 to future releases.
Upgrading and Immediate Updating
If you are using immediate updating with snapshot replication or transactional replication, changes to that feature in SQL Server 2000 will affect how you upgrade. Rows in immediate updating articles now use a uniqueidentifier column to identify versions, whereas in SQL Server 7.0, a timestamp column was used. In addition, the triggers generated for immediate updating have been changed, and the trigger generation code has been modified to accommodate queued updating. Because of these changes, additional upgrade steps are necessary.
If using immediate updating:
- Upgrade both the Publisher and Subscriber before replicating data.
- Drop the publication and all subscriptions to the publication.
- Use an ALTER TABLE DROP COLUMN Transact-SQL statement to drop the timestamp column from the tables on the Publisher and from the tables on the Subscriber that allow Subscriber updates.
- Re-create the publication and subscriptions. The system adds a uniqueidentifier column to the published table. That column is used for row versioning (to detect conflicts when receiving updates from the Subscriber).
Although it is recommended you upgrade both the Publisher and the Subscriber and then drop and re-create the existing publications, the Publisher and Subscribers can be upgraded in any order. If you need to reinitialize a Subscriber or add a new Subscriber, you need to drop and re-create the publication.
Upgrading and File Transfer Protocol
If using File Transfer Protocol (FTP), you should follow the recommended upgrade path, which ensures that Subscribers are able to obtain the necessary FTP information from the Distributor.
SQL Server 2000 stores FTP parameters as Publication Properties; you no longer need to administer them at the Subscriber for each subscription. When upgrading to SQL Server 2000, the FTP option in the Publication Properties is turned off, and you need to open the properties for each publication that uses FTP, and then reset the FTP parameters.
SQL Server 7.0 Subscribers will continue to locate FTP files using the FTP parameters stored in the Subscription Properties when using a Distributor running an instance of SQL Server 2000. However, Subscribers running an instance of SQL Server 2000 will not be able to obtain FTP information from Distributors running earlier versions of SQL Server.
Existing subscriptions using merge replication or transactional replication will be unaffected by this change unless you need to reinitialize or connect to the FTP site. The FTP parameters need to be specified before snapshot replication occurs, or replication agents will not be able to locate the snapshot files.
For more information about changing the FTP parameters, see Using TCP/IP and FTP and How to specify FTP information (Enterprise Manager).
Troubleshooting and Replication Upgrades
If errors occur while upgrading replication servers, they might be related to the database being offline or unavailable or a script may have failed. For more information about troubleshooting errors that occur when upgrading replication, see Help with Replication.
It is recommended that you stop all data modifications at the replication server while it is being upgraded. When upgrading from SQL Server 6.5, you must run the Log Reader Agent and Distribution Agent before upgrading to make sure there are no replicated commands pending delivery to Subscribers.
Because you can upgrade servers running instances of Microsoft® SQL Server™ 2000 one at a time, you may have circumstances where servers in your replication topology are running different versions of SQL Server. You can replicate between different versions of SQL Server, but you are often limited to the functionality of the earliest version used.
Important When upgrading from SQL Server 6.5 or 7.0 to SQL Server 2000, SQL Server Setup runs several *.sql replication scripts. Although the upgrade process can take several minutes and does not display progress notifications, you can view error messages in the *.out and *.err files located in the SQL Server Install directory.
See Also
Publishing Data Over the Internet Using TCP/IP and FTP
Replication Between Different Versions of SQL Server