
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

How to troubleshoot replication problems

Alexander Chigrik
chigrik@mssqlcity.com

Should you have problems with replication, review this troubleshooting checklist to find potential solutions.

*****
1. Check the hardware requirements.

For SQL Server 7.0, you should have:

Alpha AXP, Intel or compatible platform

Pentium 166 MHz or higher

64MB RAM or more (recommended)

180MB hard disk space

For SQL Server 2000, you should have:

Intel or compatible platform

Pentium 166 MHz or higher

64MB RAM or more (recommended)

250MB hard disk space

*****
2. Check the software requirements.

To set SQL Server as publisher for merge or snapshot replication, you can have any editions of Windows 9x, Windows NT 4.0 or Windows 2000.

To set SQL Server as publisher for transactional replication, you should have the following operation systems:

Windows NT Server version 4.0 (with Service Pack 4 or later for SQL Server 7.0 and Service Pack 5 or later for SQL Server 2000)
Windows NT Server Enterprise Edition version 4.0 (with Service Pack 4 or later for SQL Server 7.0 and Service Pack 5 or later for SQL Server 2000)
Windows 2000 Server
Windows 2000 Advanced Server
Windows 2000 DataCenter

SQL Server editions have the following restrictions:

SQL Server 7.0 Desktop Edition cannot be used as publisher for transactional replication
SQL Server 2000 Personal Edition cannot be used as publisher for transactional replication
SQL Server 2000 Desktop Engine cannot be used as publisher for transactional replication
SQL Server 2000 Windows CE Edition does not support snapshot or transactional replication, and support only Anonymous Subscriber to merge replication.

*****
3. Check that you use the latest SQL Server service pack.

To check what SQL service pack are you running, see this link:
How can I check what SQL service pack am I running?

*****
4. Check that account the MSSQLServer and SQLServerAgent services runs under belongs to the Administrators local group and is a member of the Domain Users group.

The LocalSystem account does not have network access rights, so this account should not be used if you want to use replication. The account the MSSQLServer and SQLServerAgent service runs under should be a member of the Administrators local group and a member of the Domain Users group.

*****
5. Check that you have sysadmin permissions on the SQL Server.

Only members of the sysadmin server role can configure replication, so if you have not these permissions you cannot set up or configure replication.

*****
6. Check that 'trunc. log on chkpt' option is turned off, if you want to set up Transactional replication.

Transactional replication uses the transaction log to capture changes that were made to data and then sent to Subscribers the INSERT, UPDATE, and DELETE statements in the same order that were made in the Publication database.

*****
7. Check the Agent history to determine which task failed and the reason for failure.

To view the Agent history, you can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Replication Monitor and choose the Agent to view history.
4. Right-click appropriate publication and select Agent History...

*****
8. Choose appropriate Agent profile for your replication model.

For example, if the replication will work through the slow link, choose Slow link agent profile. To choose the Agent profile, you can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Replication Monitor and choose the Agent.
4. Right-click appropriate publication and select Agent Profiles...

Note. You can also create your own Agent profile (click the New Profile button under the Agent profile window).

*****
9. Set the rowcount or rowcount and checksum validation to avoid the problems with data consistency.

You can use sp_table_validation system stored procedure to test for row count or checksum differences.

Here you can find a step-by-step guide illustrating the data validation process for Transactional replication: Data Validation for Transactional Replication

Here you can find a step-by-step guide illustrating the data validation process for Merge replication: Data Validation for Merge Replication

*****
10. Increase the QueryTimeout value in the Merge Agent or Distribution Agent profile, if the Merge Agent or Distribution Agent fails on timeout.

The QueryTimeout value in the Merge Agent or Distribution Agent profile indicate the number of seconds before the queries issued by the agent times out.
To increase the QueryTimeout value in the Merge Agent or Distribution Agent profile, you can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Replication Monitor and choose the Agent.
4. Right-click appropriate publication and select Agent Profiles...
5. Click the New Profile button to create the new profile with the appropriate QueryTimeout value.
6. Choose the newly created profile.

*****
11. If you receive an "Access Denied" error when start Snapshot Agent, make sure the account that SQL Server Agent runs under have default access and launch DCOM permissions.

To check/set it, you can do the following:

1. Run Dcomcnfg.exe.
2. Click the Default Security tab.
3. Check the account that SQL Server Agent runs under have default access and launch DCOM permissions.

*****
12. Make sure that the snapshot folder is shared correctly.

Otherwise, replication agents cannot access the snapshot folder and you will get replication error. For example, on a distributor server running Windows 9x the snapshot folder defaults to using the local path without a share. So, you should change the local path to a network path by sharing the folder manually.

*****
13. If you get "Couldn't deliver schema information." error when synchronize the Internet publications, set up FTP server at the Distributor and set the Merge Agent command line to include an FTP address.

Because UNC path, which is used by default, works only in the Local Area Network (LAN) for Wide Area Networks (WAN) you should use FTP address.

*****
14. If conflict occurs when merging newly inserted rows that contain identity columns, you must assign each Subscriber that will insert new rows containing an identity a unique range of identity values.

Try to avoid using identity columns in the tables that will be replicated.

*****
15. You cannot specify the uniqueidentifier column with the ROWGUIDCOL property as the primary key of the published table when you use merge publishing from SQL Server to Jet 4.0.

To work around this, you can use a composite primary key with two columns (uniqueidentifier column and integer column, for example).

*****
16. Use ALTER TABLE statement instead of using Design Table in SQL Server 7.0 Enterprise Manager to modify a table that has the NOT FOR REPLICATION property.

Otherwise, the NOT FOR REPLICATION property on the IDENTITY column will be lost.

*****
17. Use Replication Conflict Viewer to get more information about conflict details.

Replication Conflict Viewer is a Wzcnflct.exe file that can be executed from the command prompt. You can run Replication Conflict Viewer from the SQL Server Enterprise Manager. To run Replication Conflict Viewer from the SQL Server Enterprise Manager, you can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Replication Monitor and choose the article.
4. Right-click article and select View Conflicts...

*****

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc