Hello All, I have a weird issue that has sprung up on a not so common configuration

cliffs
started a new job recently
a lot of the stuff set up was done a fair amount of time ago
still exploring configurations / setups
come into work today to find merge replication failing and peeling back the layers of the onion have let to one road block after another.


Basics
Publisher / distributor = 2008r2 sp3
subscriber = 2012 sp2

subscriber is also part of an availability group

This past weekend there was a failover (windows patching) and now the merge replication is pointing to the new primary and failing.

After discussing with the other DBA he enlighted me to the following (which goes through the steps of fixing the broken subscription)

https://msdn.microsoft.com/en-us/library/hh882436.aspx

Going through the steps listed, I find another issue when using the GUI ("for merge publications, the subscriber must not exceed the version of the publisher).

SO...that was a little shock, since this has been apparently functioning for quite some time, and I have no idea how it was originally set up as that wasn't documented and the person doesn't work here (ever heard that one before!!!)

I can, however add the subscription via t-sql without error and it looks like it starts working again...but here is the weird part.


I add the subscription, then run the snapshot agent, then start the synchronization...replication starts the application of the snapshot, and it looks like it makes it all the way through the process...the row counts match at each end...

Then it appears at the end of the sync tasks, it has an issue and blows up with the following error


2015-09-08 15:42:26.194 The merge process was unable to deliver the snapshot to the Subscriber.
If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.

shortly followed by:

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

it is a push subscription, I cannot for the life of me figure out if this is a windows permissions error or something database related...or now the fact that this current set up shouldn't have ever worked based on the GUI error I got.


Any help or comments are appreciated.

Thanks in advance,

Lee

Going through the steps listed, I find another issue when using the GUI ("for merge publications, the subscriber must not exceed the version of the publisher).

I don't think this is true and I believe its reverse.

Did you checked the Replication monitor status ? Synchronization states ?

It looks like most of your snapshot has been applied, but one of the conflict triggers/procs is failing. So we know that you do not have a permissions issue. I suspect there is a network issue, or your snapshot is being regenerated.

Does it always fail on this proc?

Hi Hilary,

First thanks for the reply.  From what I can tell base on the profiler trace, yes...it fails on the same step each time regardless.

The publication has 8 articles.  The snapshot is generated, then applied.

We have attempted to totally drop the subscription and publication and start from scratch and that hasn't changed the outcome.

From the trace on the subscriber it appears to work successfully until it gets to the following system stored procedure (Example):

exec sys.sp_MSaddmergetriggers N'Unit', N'xxx', null, 0

At this point, it fails, for each article (called for each article).

That is where the following error is generated

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

The trace details the following chain of events on the subscriber

That procedure is called, it fails on this statement:

set @cmd = 'grant update, insert, select, delete on ' + @viewname + ' to public'

which generates the above error

Failed to create merge replication triggers for object '[xxx].[Unit]'.

The statement above that is the actual dynamic code to apparently create the view I believe

set @cmd = '
                create view dbo.' + @viewname + ' as
                select * from dbo.MSmerge_contents where
                trigger_nestlevel(' + convert(nvarchar,@instrigid) + ') > 0 or
                trigger_nestlevel(' + convert(nvarchar,@updtrigid) + ') > 0 or
                trigger_nestlevel(' + convert(nvarchar,@deltrigid) + ') > 0
                with check option'

Hopefully this explanation helps

EDIT:

Also, I also now realize that going through the GUI to recreate the subscription fails with the version error that Jinu has pointed out.

That being said, you can add the subscriber via t-sql so I know it works and it had worked for some time in up until this recent issue...so I don't think that is an issue.

• Edited by Leeland_1 15 hours 14 minutes ago update

Can you try to run this on your subscriber in your subscription database?

                create view dbo.MSmerge_ctsv_PleaseDeleteMe as
                select * from dbo.MSmerge_contents where
                trigger_nestlevel(2046630334) > 0 or
                trigger_nestlevel(2062630391) > 0 or
                trigger_nestlevel(2078630448) > 0
                with check option
                GO
                Grant update, insert, select, delete on MSmerge_ctsv_PleaseDeleteMe to public

Once this has been created - please drop this proc.