SqlServer Merge Replica push - exec sys.sp_MSaddmergetriggers throws error

Question

i'm stuck trying to solve an issue with a brand-new installation of two sql-server 2017 developer edition, in two different commodity server.

The problem arise with the tentative of merge-replicate one database. Every chanche to sync the subscriber ( in a lot of different combo and scenario ) ends up with the following error:

exec sys.sp_MSaddmergetriggers N'systemunits', N'dbo', null, 0**************deferred cmd exec completed

Cannot find the object 'MSmerge_ctsv_A6203259D69B49709C5BE266A99D0DBC', because it does not exist or you do not have permission.

I have installed the default-instace of sql server on both the server, no rename of the server has been done after the installation.

Any suggestions?

Answer 1

After a whole day , i finally find a solution to the problem! It seems that the replica agent use the Domain-name ( not the ip or the port ) of the subscriber to replicate without issue, every mismatch or change in the DNS ( for example... localhost instead of the hostname , the port number that follows the named instance etc ) cause an error in the sync process.

To avoid any issue, i create sql-ALIAS referencing the destination subscribers, and use those alias in the definition of the subscription.

Example: to point to the server "10.12.12.5\SqlExpress,49170" i create the alias: \SqlExpress port: 49710 protocol: TCP/IP Server: 10.12.12.5\SqlExpress

and use those alias ( i.e. \SqlExpress ) as the host subscriber.