Wednesday, August 23, 2006

This Document may be of use while trouble shooting problems with replication
1. Check the software requirements.
To set SQL Server as publisher for merge or snapshot replication, you can use any edition of Windows XP, Windows 9x, Windows NT 4.0 or Windows 2000.
To set SQL Server as publisher for transactional replication, you should use one of 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 2003 Server
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 supports only Anonymous Subscriber to merge replication.

2. Ensure that the account the MSSQLServer and SQLServerAgent services run 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.

3. Ensure that you have sysadmin permissions on the SQL Server.
Only members of the sysadmin server role can configure replication, so if you do not have these permissions, you will not be able to set up or configure replication.

4. Ensure that the '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 sends the INSERT, UPDATE, and DELETE statements to Subscribers in the same order they were made in the Publication database.

5. Check the Replication Agent history to determine which task failed and the reason for failure.
To view the Replication Agent history, you can do the following:
i. Run SQL Server Enterprise Manager.
ii. Expand a server group, then expand a server.
iii Expand Replication Monitor and choose the Agent to view history.
iiii. Right-click appropriate publication and select Agent History...

6. Choose the 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...



7. If the Merge Agent or Distribution Agent fails on timeout, increase the QueryTimeout value in the Merge Agent or Distribution Agent profile.
The QueryTimeout value in the Merge Agent or Distribution Agent profile indicates the number of seconds before the queries issued by the agent time 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.

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

9. 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.
Set Different identity seed values on both the subscriber and publisher. Eg set publisher at 1 and subscriber at 99,000,000. You should try to avoid using identity columns in the tables that will be replicated.

10. Use a ALTER TABLE statement instead of using the Design Table in SQL Server 7.0 Enterprise Manager tool to modify a table that has the NOT FOR REPLICATION property.
Otherwise, the NOT FOR REPLICATION property on the IDENTITY column will be lost.

11. Use the 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, 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...

No comments: