SQL

How to restore merge replication triggers

Sergey Tunnik

Nov 3, 2015 • 1 min read

Issue

We are using merge replication to provide some filtered data for our clients. Also we use data-tier applications for deploying latest DB schema, stored procedures, etc for our databases. Somehow it turned out that deployment of DACPAC are breaking replication. It took quite a time to troubleshoot it. The root issue was that DACPAC was dropping replication related triggers (MSmerge_(del\ins\upd)_<guid>) during creation of its owns.

Solution

Tell SqlPackage no to drop triggers:

SqlPackage.exe ... /p:DropDmlTriggersNotInSource=False

It is cumbersome to recreate merge replication. I dig into process of merge replication creation - How Merge Replication Initializes Publications and Subscriptions. I played with profiler and cherry-picked that stored procedure sp_MSaddmergetriggers. Run next query to for fix:

use [<database>]

exec sp_MSaddmergetriggers @source_table = '<replicated_table1>'
exec sp_MSaddmergetriggers @source_table = '<replicated_table2>'

It isn't documented nor supported by Microsoft. But it precisely does what it should.