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.

5. Configure Security on Server Server 2000

Add the SQL Server login repl_publisher on the subscribing server if it doesn’t exist, with a blank password (ensure it is blank if it already exists). Add it to the db_owner role in the subscribing database (which needs to be created before replication starts). This means that the subscribing server must be running under Mixed Mode authentication. Also makes sure the domain account under which SQL Server is running on the publisher is an local administrator of the subscribing server.

6. Initiate Replication

Before you do this, it is advisable that all update/insert/delete activity on the source tables is stopped until synchronization is complete. This can be achieved by placing the database in “dbo only” or “single user mode”.

On the publisher, click Manage, Replication, Publications. Select the required publication and click Modify, then click Subscribers. Chose the subscribing server and then click subscribe. Of the three options, chose “No Data Synchronization” (this will be done manually).


Pages: 1 2


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

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |