SQL Server Replication from 6.5 to 2000

  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.


Leave a comment

Your email address will not be published.