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