7. Synchronize Tables
First, check the distribution task on the publishing server, in the SQL Executive. If it has failed with the error “Login failed: The user: is not associated with a trusted connection”, refer to Knowledge Base Article Q216848. This explains how to check the various ODBC drivers and ensure they are all at the correct version in all places they exist. Important Note: make sure the SQL Server 2000 ODBC Driver has NOT been installed on the 6.5 Server. If it has, regression to the required earlier version means a reinstall of NT Server, sadly. To avoid this, do not install any MDAC versions later than 2.1.
Once the distribution task is working okay, check the destination table has been created on the subscribing server. If it hasn’t, you may have to do this manually (in testing, both situations were encountered). The script for the table will be found in the repldata working folder on the publisher, and will have a *.SCH suffix. Run this on the subscriber to create the table. If the table has been created automatically by SQL Server, it will be owned by repl_publisher. You can leave this if you can live with it, but it is recommended that you run sp_changeobjectowner to make it dbo owned.
Now, you must manually synchronize the data. In testing this was done using bcp, but other methods could easily be used and may be better, such as a DTS Bulk Insert or transfer data task. For tables which have text columns which are not being replicated, the best way is to bcp out of them is to bcp out of a view which you create, which selects all the columns of the table but not the text ones. This avoids the messy use of format files. Once the bcp out and in has been done, replication is ready to go! You should test some simple data modifications on the source, to ensure inserts, updates and deletes are replicating and the changes are being applied to the subscriber.
You can use the sp_distcounters stored procedure to check it is working okay. Any non-zero items in the undelivered jobs columns indicate a problem (although on a heavily updated system, small backlogs are commonplace).
Normal update activity on the publisher can now be enabled again by removing dbo only or single user status from the publisher.
8. Configuration Options to Consider When Replication is Working
The frequency and retention period of the replcleanup task needs to be considered. To change the retention period to anything other that the default 0, just change the final parameter of the job to the number of hours of distributed transactions you want to keep. Remember, the more you keep, the larger the distribution database needs to be.
The batch size and timeout parameters for the distribution tasks may need to be adjusted. Refer to SQL Server 6.5 Books Online for the details. In tests it has been found to be optimal to set the batch size to 10 and the timeout period to 2400 seconds.
Published with the express written permission of the author. Copyright 2001.
]]>