Share via

SQL Server Merge Replication - Adding a new article

Duncan, Barry 1 Reputation point
2021-03-05T03:10:49.957+00:00

We have SQL server 2014 with merge replication in place, which currently has several publications and two subscribing servers.

We wanted to add an existing table, which exists on all three servers with quite a few million rows back into a publication that it was removed from, therefore the tables are practically in sync as this is the DEV system.

Reading the MS documentation page on 'sp_addmergearticle' there is a parameter that we always use when adding a new article/table: @pre_creation_cmd

..the documentation states the following:

[ @pre_creation_cmd = ] 'pre_creation_cmd' Specifies what the system is to do if the table exists at the subscriber when applying the snapshot. pre_creation_cmd is nvarchar(10), and can be one of the following values.

Value Description
none If the table already exists at the Subscriber, no action is taken.

Therefore we have the following in our article creation script:

@pre_creation_cmd = N'none'

But the problem is, and I have experienced this before, we add the article, run the publication snap-shot, and then start the subscription merge job, but what happens is that the table gets added OK to the subscription server, but then for all the rows in the publication table there is an update carried out on the subscription server.

I have noticed if I create a new publication and add a filtered table to the publication (not table above), and then add the same table as above with the millions of rows, therefore 2 tables in the publication, run same steps as above, no updates are carried out at the subscription server, in fact, the subscription merge job runs in a couple of minutes, as opposed to days for adding the article to an existing publication.

Can anyone shed some light on what the difference is here?

Thanks,
--Baz

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories


Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.