SQL Server 7.0 Replication FAQ and common problems
Last updated: April 14th '01 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New




Receive & Send money over the net easily!
What is Replication Monitor and how it can be used to monitor replication activity and performance?

Replication Monitor is an interface provided by SQL Server 7.0 Enterprise Manager (EM), which allows you to monitor the activity of each replication agent individually AND the activity of all agents at the publication level.

An X mark will be displayed against the replication agents in case of any errors. Different actions of the replication agents are indicated using different icons. For more information on these icons and their meanings, see Replication Icons in Books Online (BOL) at: mk:@MSITStore:C:\MSSQL7\BOOKS\SQLBOL.CHM::/html/r_model_16.htm.

Replication monitor refreshes the results pane and the server tree every 10 seconds by default. To get more real time information, change these settings, so that the contents of replication monitor are refreshed more often. To do this, right click on the Replication Monitor and select ‘Refresh Rate and Settings’. You can always do a manual refresh by right clicking on the nodes in the server tree and selecting ‘Refresh’. You can also press ‘F5’ key to refresh, when the focus is in the results pane.

By right clicking on the Replication Monitor and selecting ‘Select Columns’ you can customize the information that is being displayed about each agent.

You can change the schedules of the agent, add steps to the agent, and set notifications, by right clicking on the agent and selecting ‘Agent Properties’. You can change or edit the profiles of replication agents by right clicking on the agent and selecting ‘Agent History’. You can view the current error messages and history of the replication agent by right clicking on the agent and selecting ‘Error Details’ and ‘Agent History’ respectively. Also you can stop and start agents by right clicking on the agent.

Replication Monitor picks up the agent history from the replication history tables that are stored in the distribution database. You can go through the following tables to view the history:
MSlogreader_history
MSsnapshot_history
MSdistribution_history
MSmerge_history

Sometimes replication monitor shows X marks indicating that there is an error, though there are no errors. Don’t panic! Closing and opening the EM will clear these X marks, in the worst case we need to recycle the server.

We can log the complete activity of a replication agent to an output file. To capture the detailed activity to a file do the following:

- Right click on the agent and select ‘Properties’.
- In the properties dialog box, click on the ‘Steps’ tab.
- Double click on the ‘Run Agent’ step.
- In this tab, you will see a ‘Command’ text box. Append the following to the command string:
-Output C:\AgentOuput.txt –OutputVerboseLevel 3
- Restart the agent.
- Once the agent completes the execution, you can analyze the contents of this file (In this case C:\AgentOutput.txt). In case of push subscriptions, these files will be created on the distributing SQL Server’s hard disk. In case of pull subscriptions, these files will be created on the subscribing SQL Server’s hard disk.

Additionally, you can use Windows NT Performance Monitor to monitor the replication activity on the server. For more information see Monitoring Performance in BOL at: mk:@MSITStore:C:\MSSQL7\BOOKS\SQLBOL.CHM::/html/r_monit_16.htm

None of my replication agents are starting. The error message reads: ‘The step did not generate any output. The step failed’. How to correct this problem?

SQL Server 7.0 installed from SBS Edition OR “Microsoft Backoffice Server Developer Edition 10 client limit” shipped with Enterprise edition of Developer Studio, are known to have this problem. During installation from these CDs, some files required by replication are not getting copied to the target machine. To workaround this problem, manually copy the following files from CD (X86\Binn) to “C:\Program files\Common files\Microsoft Shared\Database replication\” folder on the SQL Server computer:

rdistcom.dll
replerrx.dll
replprov.dll
replrec.dll
replres.dll
sqldistx.dll
sqlmergex.dll

Do not forget to register these DLLs using regsvr32.exe.

While checking for the existence of these files, make sure Windows Explorer is configured to show all files. Make sure, ‘Show all files’ is checked in the View tab of ‘View/Folder Options’ dialog box.

Note: During uninstallation of some programs, you might be prompted to either ‘Keep’ or ‘Delete’ the above shared files. Always chose to ‘Keep’ these files, otherwise replication will break.
Replication agents are not starting and are failing with the error: 'Replication agent is not registered properly. Rerun SQL Server setup'. What's wrong?

Registering replagnt.dll using regsvr32 fixes the problem.

replagnt.dll is present in the MSSQL7\Binn\ folder.

To register it, go to Run command from Start menu and execute:
regsvr32 MSSQL7\Binn\replagnt.dll
Merge agent is failing with the error: 'The process could not initialize "database reconciler". Check to see if the component is registered correctly’. Where to look?

Again this is a problem with missing DLLs. Copy the any of the following missing DLLs from x86\Binn on the CD to “C:\Program files\Common files\Microsoft Shared\Database replication\” folder on the SQL Server computer:

rdistcom.dll
replerrx.dll
replprov.dll
replrec.dll
replres.dll
sqldistx.dll
sqlmergex.dll
How to change the schema of a published table?

In SQL Server 7.0, if you have to change the schema of a replicated table, first you have to unsubscribe to all the publications, which are based on this table. Then, delete all the publications. Do the schema change. Republish the table and resubscribe. You can do NOSYNC, because the data is already there at the subscriber.

You can script the replication in 7.0, so that you don’t have to recreate the publications and subscriptions manually, every time you change the schema. In Enterprise Manager, go to ‘Tools/Replication/Generate Replication Scripts…’ to generate replication scripts.

Note: Pull subscriptions cannot be scripted and can’t be traced in Profiler for security reasons.

The next release of SQL Server is going to support Schema Replication, where you don’t need to do all the above steps if you want to change the schema of a replicated table.

Disclaimer and terms of use
Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved.