SQL Server Replication from 6.5 to 2000

These are the steps needed to successfully establish transactional replication from SQL Server 6.5 to 2000. They has been tested and are known to successfully work. This article assumes you have some previous experience with SQL Server replication.
  1. Prepare publishing server/database
  2. Set subscribing servers
  3. Update sp_articleview in master (6.5)
  4. Publish tables
  5. Configure security on 2000 Server
  6. Initiate replication
  7. Synchronize tables
  8. Configuration Options to Consider When Replication is working.

1. Prepare Publishing Server/Database

This step assumes the SQL Server 6.5 database is not already being published for another reason. If it is, do not perform the following step. In the database which is to be published, ensure that the the tables syspublications, sysarticles and syssubscriptions are all empty. If they are not, delete any rows you find. In order to do this, ad-hoc updates to the system tables must be enabled. This can be done by running:

sp_configure ‘allow updates’, 1

Don’t forget to unset it afterwards by running the same command, but replacing the 1 with a 0.

Create two devices for the distribution database, for its data and log. Then, from Enterprise Manger, click Server, Replication Configuration, Install Publishing, choosing the devices you just created for the distribution database. Set the publishing database, but do not add subscribing servers yet.

2. Set Subscribing Servers

Register the 2000 Server in SQL Server 6.5 Enterprise Manager. This generates an error, but you can ignore it. The click Server, Replication Configuration, Publishing, and enable subscribing to the SQL Server 2000 server.

3. Update sp_articleview in Master (6.5)

This is required to avoid a bug (Q190208) when publishing tables with large numbers of columns. Run this script in the SQL Server 6.5 master database.

4. Publish Tables

Create publications and define articles as required. It is advisable to exclude all text columns. It is also recommended that no indexes are replicated, these should be added manually after synchronization. This makes the data synchronization quicker, because data can be inserted into destination tables without updating indexes. Indexes will therefore need to be scripted and these scripts run after synchronization.


Pages: 1 2


No comments yet... Be the first to leave a reply!