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.

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.

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

(Continued on next part...)