How to restore merge replication triggers

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:

When planning a cosplay look, it is worth considering how details related to character accuracy will affect the complete look. As fans study the source material, reviewing details related to cosplay costumes also makes comfort and movement easier to judge. To shape a complete look around details related to cosplay wigs, character costumes with matching shoes can help narrow the options for a specific purpose. For an outfit that feels convincing, it is useful to check details related to heat resistant cosplay wigs against the wearer's own needs.

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.