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).