Snapshot Agent Fails while Generating Snapshot in SQL Server 2014
Replication in SQL Server is most widely used and most often of not the setup and configuration of Replication is quite simple. However there are many issues with the replication which causes problems for DBAs and with each release of newer versions new problems come up.
On such problem with replication comes with setting up replication on SQL Server 2014 instance. When you setup Merge replication and then try to initialize the replication by running the Snapshot agent, it may fail with below error.
There is already an object named “TestConstraint” in the database. Could not create constraint or index. See previous errors.
There is no special configuration with the replication, same database, same tables and same data on SQL Server 2008 R2 or 2012 instance works fine without any errors, this happens only with SQL Server 2014.
After looking at the code where it is failing, we noticed that it is trying to create system table MSmerge_conflict_publication_article where it fails. After digging and research found that in SQL Server 2014 the Snapshot Agent is trying to generate the create script to create the table MSmerge_conflict_publication_article with columns, it is also defining a constraint with same constraint name which already exists in the table as part of user table constraint. Here both the user table constraint name and system table constraint name are same thus causing this error.
Because of this change in SQL Server 2014, the merge replication can fail on tables which have constraints defined on them. This appear to be bug, but not seem to have any solution so far. Post talks about same problem and they have already tested installing SQL Server 2014 CU4, but the issue still persisted.
As of now one of the workaround is to drop all constraints before on the user tables which are replication and create them later. Other options which can be tried include initializing the Subscriber database using other methods such as Backup/Restore where we can avoid the Snapshot agent from running the scripts. Hopefully this issue will be fixed soon.
This is applicable on below versions of SQL Server
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.