Troubleshooting SQL Server 2014 Merge Replication
If you have problems with SQL Server 2014 merge replication, review this troubleshooting checklist
to find potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because some SQL Server 2014 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 2014 service pack was service
pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. 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.
3. 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.
4. 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.
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. 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.
8. 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.
Note. The sp_table_validation stored procedure is not supported for Oracle Publishers.
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 8115 may occur when you call the sp_MScreatemergedynamicsnapshot stored
procedure to create a dynamic snapshot.
This is the error message text: “Arithmetic overflow error converting expression to data
type nvarchar.” This problem occurs when the agent_id value exceeds 99,999 in the
MSdynamicsnapshotjobs table. This bug was first fixed in Cumulative Update package 3 for
SQL Server 2014. You can download the Cumulative Update package 3 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2984923
14. When you use merge replication with SQL Server Compact subscribers and precomputed
partitions, the w3wp.exe process crashes during the synchronization of the subscribers.
In this case, the foolowing error occurs: “ERR:Couldn’t find existing RSCB xxx.”. This bug
was first fixed in Cumulative Update package 3 for SQL Server 2014. You can download the
Cumulative Update package 3 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2984923
15. INSERT performance decreases in merge replication that uses precomputed partitions
with parameterized and join filters in SQL Server 2014.
This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 5 for
SQL Server 2014. You can download the Cumulative Update package 5 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3011055
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.