Just a heads-up on part of the merge replication behavior that you need to be aware of, if you are not already :).
Suppose we have three tables – Region, Company and Sales. These are related by Primary Key-Foreign Key relationships such that Regions have Companies and Companies have Sales.
In the publication we create a filter so that Subscriber “Sub_North” gets the North Region records and subscriber “Sub_South” gets the “South” records.
Suppose we change the ACME company from the North to the South region….what happens? Well first of all subscriber Sub_South will now get the company ACME and all the associated ACME sales. Meanwhile, subscriber sub_North will have the ACME company deleted and all the associated ACME sales will be deleted. It is like all the data is moved from one subscriber to another. If you are new to merge replication this might catch you by surprise and we have to be aware that such a change near the top of a hierarchy can result in the same volume of traffic as a reinitialization.
It is all controlled by a parameter to sp_addmergepublication and sp_changemergepublication “@allow_partition_realignment”.
By default this parameter is set to ‘true’ meaning that deletes are sent to the subscriber when modification of the row on the publisher causes it to change its partition. ‘false’ leaves the data from an old partition on the Subscriber for historical purposes. However this is not any longer possible in practice to set it as ‘false’ as in this case you’ll have to redesign the whole publication and reset all articles to disable uploads.
So – although it isn’t practical to reset we just need to be aware of this behavior in our design :).
When the merge agent synchronizes it might be the case that an insert, update or a delete cannot be applied at the partner. This could be because of a particular constraint or trigger.
What happens in this case. By default there is a conflict reported but no error. At this stage the data is out of sync and until we sort out the conflict it’ll stay like that.
There is an alternative though. The merge agent may send a compensating change back to the source partner to undo the failed change i.e. delete the source record in the case of a failed insert at the partner. This all occurs in the same synchronization session and there is no error.
In sp_addmergearticle there is a special parameter which can enable this behaviour: @compensate_for_errors. By default it is set to ‘false’.
This is not exposed in the SQL Server Management Studio so you’ll need to set it using scripts : either sp_changemergearticle or sp_addmergearticle.
When @compensate_for_errors is set to true, there will be a conflict registered in the conflict viewer. In the case of an insert being compensated by a delete, the conflict viewer will show “A row insert at ‘xxx’ could not be propagated to ‘yyy’. This failure can be caused by a constraint violation. …”. The offending row still exists at the xxx partner. On the next synchronization the row is removed from xxx by a compensating delete. We have data converged at this stage. Slight issue that the conflict still exists in the conflict viewer and needs manually removing but no big deal.
There is an option on the article properties to “keep existing object unchanged”.
This seems a little odd at first. Surely we always drop all subscriber objects when we reinitialize? Well for a particular table we could have something different in mind. A real edge case but there is a scenario this is designed for. It is where we have multiple publishers to a single subscriber. The table is created on the subscriber by the first publisher during initialization and hence has the replication objects are created at the subscriber. Subsequent publishers will send their commands and records there but leave the table in place. This can be used for centralised reporting from several municipal offices to head office. Any ddl changes such as adding a column are fine – we do them on the first publisher (the one which did the initial drop and recreate at the subscriber) so the ddl change goes to the subscriber.
This is an interesting question. In the publication properties there is the option on the snapshot tab to run extra scripts before and after the snapshot is applied. This is the same for both merge and transactional publications (below).
Many DBAs will have scripts which drop all the FKs on the subscriber and readd them after the snapshot is applied so the initialization runs smoothly and we don’t get the following sort of error:
“Could not drop object ‘dbo.tCity’ because it is referenced by a FOREIGN KEY constraint“.
However the snapshot generation is different between Transactional and Merge. In Transactional all the FKs are dropped for you and re-added later on. This doesn’t happen for merge. There’s probably a good reason for it but can’t see why they should run differently at present. Anyway, the message is that you don’t need to roll your own logic to deal with subscriber FKs in Transactional but you still do in merge!
Have you ever noticed that when you add an article to a transactional publication and run the snapshot agent it does a complete snapshot of all tables? This can be a real pain for big publications. Fear not – it is configurable. The setting is visible in the Replication Monitor. Actually 2 settings – shown below. We need both to be False but the observant DBA will notice that the main one of these is greyed out!
A little scripting solves it though. We just run the script below before adding a table and all is well – the snapshot agent then just creates the new article.
@publication = ‘pubTestTransactional’,
@property = ‘allow_anonymous’,
@value = ‘false’
@publication = ‘pubTestTransactional’,
@property = ‘immediate_sync’,
@value = ‘false’
There is a handy system stored procedure sp_showpendingchanges that will provide an “approximation” of the pending changes that need to be replicated at that database. This proc will list inserts/updates/deletes per article and can also filter by the subscriber server. It has been around since SQL 2008 and is a welcome change. Would be nicer if it was represented in the replication monitor like the one for transactional replication though.
You can run the procedure without any arguments in which case it provides a summary.
You can also provide all arguments as below to look at a specific subscriber and table and see the rows waiting to be sent.
exec sp_showpendingchanges @destination_server = ‘DESKTOP-NSONKMO’
, @publication = ‘Testuse_partition_groups’
, @article = ‘tCompany’
, @show_rows = 1
However, if the subscriber database name is the same as the publisher one nothing will get returned. Not great as this is a common setup!
In this case you’ll need to roll your own script using something like the code I created below:
select tCompany.* from tCompany
inner join MSmerge_contents on tCompany.rowguid = MSmerge_contents.rowguid
inner join MSmerge_genhistory on MSmerge_contents.generation = MSmerge_genhistory.generation
where MSmerge_genhistory.genstatus = 0 and MSmerge_genhistory.generation >
(select recgen from sysmergesubscriptions where subscriber_server = ‘DESKTOP-NSONKMO\paulsinst’)
In merge replication we often replicate several related tables. Imagine if we were replicating the following 3 tables:
Now it might be that we declare a filter and join filters as follows in the publication:
Notice that the relationship of the filters is opposite to the direction of the foreign keys. This makes business sense in some cases (another post on this later). Anyway, if this is the sort of scenario you have then beware that some data changes won’t propagate. If for example on the subscriber I delete 3 related records:
delete tStaff where staffname = ‘PaulSmith’
delete tDepartment where departmentname = ‘Finance’
delete tCompany where where CompanyName = ‘IBM’
When synchronizing back to the publisher the tCompany record will not be deleted. No error in the replication monitor but there is mention of a “retry”. Further syncs don’t mention the retry and still the tCompany record remains. This is a bug/issue that has existed from SQL 2005 through to SQL 2016. What we need to do is reset the Precompute partitions option. By default it is set to true and we reset it to false. This will cause a snapshot to be created so if you are going to have this type of filter setup remember to do this at the beginning before initialization.
Suppose there are some rows at the publisher which are not at the subscriber. Or vice versa. This is after a successful synchronization….
Other posts have dealt with how this can occur, and how we can stop it happening, but suppose it has happened – what to do?
Well there is a nice stored procedure which comes to our rescue: sp_mergedummyupdate. We just need the name of the table and the rowguid.
exec sp_mergedummyupdate @source_object = ‘tCity’, @rowguid = ‘724FEE04-F8DB-E611-9BE5-080027A1E9BF’
It is very robust – if we give it the rowguid of a non-missing row there will just be an update propagated and we won’t end up with duplicates. Likewise if we try running it more than once there won’t be an issue.
Just run the proc at the publisher or the subscriber (whichever one has the missing rows) and then synchronize – job done!