SQL Server

Troubleshooting SQL Server 2016 Merge Replication

Troubleshooting SQL Server 2016 Merge Replication

If you have problems with SQL Server 2016 merge replication, review this troubleshooting
checklist to find potential solutions.

1. Install the latest SQL Server 2016 service pack.

Because some SQL Server 2016 Merge replication bugs were fixed in SQL Server
service packs, you should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2016 service pack was
service pack 1. You can download the SQL Server 2016 service pack 1 at here:
https://support.microsoft.com/en-us/kb/3182545

2. 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 configure merge 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. If you change the startup account of the SQL Server Agent service to a domain
account using the Windows Service Control Manager the replication agent jobs
may fail.

This problem occurs because the Windows Service Control Manager does not grant
the required permissions to the new domain account. To work around this problem,
you can use SQL Server Configuration Manager to change the startup account to a
domain account.

4. 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 merge replication.

5. Increase the value of the -LoginTimeOut property of the Snapshot Agent and the
Merge Agent, if the login fails on timeout.

The -LoginTimeOut property indicates the number of seconds before the login times out.
The default value is 15 seconds. Consider increasing this property if the login fails
on timeout.

6. You can maximize the history logging by selecting 3 for the -HistoryVerboseLevel
property of the Snapshot Agent and for the Merge Agent.

This property specifies the amount of history logged during a snapshot operation
(for a Snapshot Agent) or during a merge operation (for a Merge Agent). The more
log information you will have the easy will be find out and identify the merge
replication problems.

7. Perform the rowcount or rowcount and checksum validation to avoid the problems
with data consistency.

You can use the sp_table_validation system stored procedure to return or compare
the rowcount or checksum information with the specified table or indexed view.
To perform the rowcount or rowcount and checksum validation, you should run this
stored procedure at the Publisher on the publication database and at the Subscriber
on the subscription database.

8. Choose appropriate agent profile for the replication agents.

Agent profiles provide a convenient way to manage the runtime parameters for each
replication agent. For example, if the merge replication will work through the slow
link, you can set the “Slow link agent” profile.

9. Increase the value of the -QueryTimeout property of the Snapshot Agent,
if the Snapshot Agent fails on timeout.

The -QueryTimeout property of the Snapshot Agent indicates the number of seconds
before the queries issued by the agent times out.

10. Consider setting the -OutputVerboseLevel parameter of the Snapshot Agent to 2.

This parameter specifies whether the output should be verbose. There are three
available values:
0 – only error messages are printed
1 – all of the progress report messages are printed
2 – all error messages and progress report messages are printed
The default value of the -OutputVerboseLevel parameter is 1. The more output
information you will have the easy will be find out and identify the merge
replication problems. So, consider setting the -OutputVerboseLevel parameter
of the Snapshot Agent to 2.

11. The error message occurs when you try to perform a batch insert into the
table that contains an identity column on a publisher of a merge replication.

This is the error message text: “Msg 548, Level 16, State 2, Line 1. The insert
failed”. This problem occurs because the current identity value of the table on
the publisher is approaching the maximum allowable value of the identity range
check constraint. To resolve this problem, you can start the merge agent to
automatically adjust the identity range to new values.

12. A non-convergence occurs when you run a merge replication publication
that uses web synchronization if an article in the publication has a table that
contains a calculated column.

This problem occurs when you use an update statement to update the value of a column
to NULL on the subscriber. To work around this problem, you can move the calculated
column to the end of the table in the article.

13. The error 8189 occurs when you try to delete a row from a filtered table part
of a merge publication in SQL Server 2016.

This problem occurs when you don’t have the ALTER TRACE permission. This bug was
fixed in Cumulative update package 1 for SQL Server 2016. You can download the
Cumulative Update package 1 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

14. The error occurs when you use Replication Management Objects (RMO) to run Web
Synchronization for Merge Replication in SQL Server 2016.

This is the error message text: “(0x80004002) No such interface supported”. This
bug was fixed in Cumulative update package 5 for SQL Server 2016. You can download
the Cumulative Update package 5 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/4013105/cumulative-update-5-for-sql-server-2016

15. The synchronization fails when a conflict is detected when you configure the
merge publication to use a Microsoft COM-based resolver for resolving conflicts.

In this case, you receive the following error message: “The Custom resolver for
this article requires OLEAUT32.DLL with a minimum version of 2.40.4276.” This bug
was fixed in Cumulative update package 6 for SQL Server 2016. You can download
the Cumulative Update package 6 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/4019914/cumulative-update-6-for-sql-server-2016

16. 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. The Replication Conflict Viewer displays conflicts that occur in
merge replication during replication synchronization when the same data is modified
at a Publisher and Subscriber or at two different Subscribers.

Facebooktwittergoogle_plusredditpinterestlinkedinmail