Troubleshooting SQL Server 2014 Transactional Replication
If you have problems with SQL Server 2014 transactional replication, review this troubleshooting
checklist to find potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because some SQL Server 2014 Transactional 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 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 transactional 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. 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 setting up or configure transactional replication.
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. The error 20690 may occur when you set up the Publisher identity range for the table.
This is the error message text: “Cannot set up the Publisher identity range for table %s.”
To work around this problem, you should verify that appropriate ranges were specified when
the article was created, and then rerun the Snapshot Agent.
6. Increase the value of the -QueryTimeout property of the Distribution Agent,
if the Distribution Agent fails on timeout.
The -QueryTimeout property of the Distribution Agent indicates the number of seconds before
the queries issued by the agent times out.
7. You can maximize the history logging by selecting 3 for the -HistoryVerboseLevel
property of the Distribution Agent and the Snapshot Agent.
This property specifies the amount of history logged during distribution operation (for a
Distribution Agent) or during a snapshot operation (for a Snapshot Agent). The more log
information you will have the easy will be find out and identify the transactional
replication problems.
8. You can find out that the Distribution Agent does not deliver commands to the
subscriber even if the Distribution Agent is running.
This problem occurs when you specify the Distribution Agent to use multiple subscription
streams by setting the -SubscriptionStreams parameter to a value of 2 or higher. To work
around this problem, you can set the -SubscriptionStreams parameter to a value of 1.
9. 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 transactional replication will work through the slow link, you can
set the “Slow link agent” profile.
10. 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.
11. An access violation may occur in replication Distribution Agent in SQL Server
Transactional Replication.
This problem occurs when there are multiple distribution instances of SQL Server in
the environment. This bug was first fixed in Cumulative Update package 1 for
SQL Server 2014. You can download the Cumulative Update package 1 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/2931693
12. When you enable change data capture (CDC) and transactional replication for some
columns of a table in SQL Server 2014, the Log Reader Agent may fail occasionally.
This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 1
for SQL Server 2014. You can download the Cumulative Update package 1 for SQL Server 2014
at here:
https://support.microsoft.com/en-us/kb/2931693
13. You may experience slow performance during regular workload when you have a
transactional replication publisher set up in SQL Server 2014 and procedures
are being replicated.
To work around this problem, you can stop the transactional replication Log Reader
Agent before you run the maintenance jobs. This bug was first fixed in Cumulative Update
package 1 for SQL Server 2014. You can download the Cumulative Update package 1 for
SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
14. When you use transactional replication in SQL Server 2014, the Log Reader Agent
crashes and returns an access violation during initialization.
This problem occurs only with RTM version of SQL Server 2014. 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. When you apply the snapshot to a new Subscriber, the explicit permissions (GRANT,
DENY, REVOKE) on the published articles cannot be replicated to the Subscriber.
This problem occurs in transaction replication when the Distributor is SQL Server 2014.
This bug was first fixed in Cumulative Update package 4 for SQL Server 2014. You can
download the Cumulative Update package 4 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2999197
16. Check the Snapshot Agent and Distribution Agent history to determine what task
failed and the reason for the failure.
To view the Agent history, you can follow these steps:
1. Run Replication Monitor.
2. Choose the Snapshot Agent or Distribution Agent.
3. Right-click the appropriate transactional publication and then click Agent History.
17. Use Replication Conflict Viewer to get more information about conflict details.
The Replication Conflict Viewer displays conflicts that occur in transactional replication
during replication synchronization when queued updating subscriptions are used.