New KB Article - Merge replication does not support centralized subscriber topologies

Question

I am a little confused with this article http://support.microsoft.com/kb/2750005

Under the More Information Section this Bullet point is made

•Two or more publications inside the same publisher database at the same publishing server instance.

This statement confuses me as I interperet it to mean that you cannot have more than publication inside a published database, even if the different publications reference different articles which I believe is common practice.

Should this not read

•Two or more publications inside the same publisher database [referencing the same article] at the same publishing server instance.

Note: I understand the Central Subscriber model with Tran Replication and multiple publishers but this is different.

Any help with clarification appreciated

GW

---

"Everything in the world should be made as simple as possible, but NOT simpler" A.Einstein

Answer 1

The article is badly written.

You can have many publications inside the same publication database, and the same table can be published in multiple publications - BUT - some of the article options will force you to use these options in other publications (for example identity range management, and row or column level tracking).

The author is very confused here.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Many Thanks, that does clarify things for us.

It does look like the KB was written after our prolonged experience with Premier Support who eventually said our Topology is unsopported because it is a "Centralized Subscriber Model" (whatever that means).

We have 550 UK pull Subscribers to a Filtered Publication & 1 Head Office push Subscriber to an un filtered publication against the same articles (x3 i.e. on each of 3 published databases + the distributor all on one dedicated central box) and have been experiencing approx 50 Deadlocks & (10 Sporadic) unexplainable UPDATE-UPDATE Conflicts per day for years.

I was hoping that now our topology is effectively unsupported by MS the Business would give me the resources (including load testing) to Drop & Recreate our Replication Topology (running for 4 years & suspect corrupted metadata) but it looks like they've had enough and are going to Architect out Replication and try and replace with something else at the application layer (some of their ideas scare me).

Has anyone any idea what a "Centralized Subscriber Model" actually is? our requests for clarification have not been very productive.

I hope this makes sense, Replication can be a very confusing topic when something goes wrong.

GW

---

"Everything in the world should be made as simple as possible, but NOT simpler" A.Einstein

Answer 3

Let me see if I understand the req. You have 500 UK Subscribers with a filtered publication from one publisher which in turn does an unfilterd publication to another subscriber.

The optimal configuration for this is a filter which looks like this:

WHERE [ServerName] =Host_Name() or Host_Name()='X'

In this case your hostname for the unfiltered publication would be X, in which case it would get unfiltered data (or data for all hostnames) and you would have a single publisher.

Your deadlocks are another issue altogether due to contention and are best solved by  rebuilding the indexes on your merge tables and the keys involved in the merge tables (ie the filtering keys on your join heireachy). Then you need to figure out where the deadlocks are occuring - are they user processes deadlocking with user processes, user processes deadlocking with merge processes, or merge processed deadlocking with merge processes. If it is merge with merge you need to limit the number of concurrent subscribers.

Now getting back to your question, a central subscriber is where multiple servers (publishers) publish the same tables to a single subcriber, and to a single table there.

This is normally setup for transactional replication, not merge.

Think of transactional replication as data coming from a single source and going to one or more destinations. Merge replication is sort of like a clearing house for data. The publisher (which can also be the source or a source), gets data from all subscribers (sources) and then figures out what data goes where.

HTH

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Thanks Hilary

You understood correctly

I never thought about using an OR in the Filter, what a great idea. Hopefully the increased overhead of the filter will be more than compensated for by having only one publication ;-) Thank You.

Re: the Deadlocks

A common example between “sp_MSupdategenhistory” and “sp_MSmakegeneration” was analysed down to the following detail By a very helpful MS Tech (not sure I should mention his name)

We reviewed our Maint Plan which runs on sunday (an event which causes 150 Deadlocks) Plus I tried restricting concurrent subscribers to a calculated number and some time later it brought the entire estate to it's knee's ;-(

SPID :- 141 à Executing “sp_MSmakegeneration”

SPID :- 76   à Executing “sp_MSupdategenhistory”

 

Statements involved in the deadlocks are as below :- 

 

SPID :- 141 “sp_MSmakegeneration”

===============================

update dbo.MSmerge_genhistory with (rowlock)

            set genstatus = 3,

                coldate = getdate(),

                nicknames = @nickbin

            where generation <= @current_max_gen and genstatus = 4 and 

                coldate not in (select login_time from sys.dm_exec_sessions) and

                subscriber_number not in (select s.subscriber_number from dbo.sysmergesubscriptions s

                                           inner join sys.dm_exec_sessions p on p.program_name = s.application_name collate database_default)        

SPID:- 76 “sp_MSupdategenhistory”

==============================

 

update dbo.MSmerge_genhistory with (rowlock)      

        set genstatus = 2, 

            art_nick = case when isnull(@art_nick,0) <> 0 then @art_nick else art_nick end, 

            coldate= getdate(),

            changecount = @changecount

    where generation = @gen -- and guidsrc = @guidsrc

 

+ Both the SPIDs are waiting on each other on two different Indexes “c1MSmerge_genhistory” and “nc2MSmerge_genhistory” for the same table “MSmerge_genhistory”.

 

2012-07-23 13:02:45.020 spid16s   resource-list

2012-07-23 13:02:45.020 spid16s    keylock hobtid=72057594416529408 dbid=7 objectname=Pawnbroking.dbo.MSmerge_genhistory indexname=c1MSmerge_genhistory id=lock1fba1bd00 mode=X associatedObjectId=72057594416529408

2012-07-23 13:02:45.020 spid16s     owner-list

2012-07-23 13:02:45.020 spid16s      owner id=process54f948 mode=X

2012-07-23 13:02:45.020 spid16s     waiter-list

2012-07-23 13:02:45.020 spid16s      waiter id=process855048 mode=U requestType=wait

2012-07-23 13:02:45.020 spid16s    keylock hobtid=72057594508017664 dbid=7 objectname=Pawnbroking.dbo.MSmerge_genhistory indexname=nc2MSmerge_genhistory id=lock1f3ecd480 mode=U associatedObjectId=72057594508017664

2012-07-23 13:02:45.020 spid16s     owner-list

2012-07-23 13:02:45.020 spid16s      owner id=process855048 mode=U

2012-07-23 13:02:45.020 spid16s     waiter-list

2012-07-23 13:02:45.020 spid16s      waiter id=process54f948 mode=X requestType=wait

 
+ Both these SPIDs belong to two different Merge Agents. 
+ One might argue that if these are two different merge agents , the below 
statement for “sp_MSmakegeneration” should avoid updating rows for the currently 
running agents , thus should not be part of the deadlock at the first place.
 
   subscriber_number not in (select s.subscriber_number from dbo.sysmergesubscriptions s
       inner join sys.dm_exec_sessions p 
	on p.program_name = s.application_name collate database_default)        
 
+ The assumption is valid but when I pulled the execution plan of this update 
statement it was observed that at your end the update statement is using 
 “nc2MSmerge_genhistory” Index to filter out rows with “genstatus=4” followed by 
further filtering on “[generation]<=[@current_max_gen]”, later down the line 
the filter is further evaluated to check if it’s a currently running merge agent or not.

Execution Plans (ommitted here) were analyzed and MS Tech got a different plan their end

In Summary

"+In short the execution plans are bit different , the filtering on check if
it’s a currently running merge agent, comes after we pull the relevant records
from “MSmere_genhistory” based on the two conditions , “genstatus=4” and
“[generation]<=[@current_max_gen]”. Thus there is a high possibility of
running into this deadlock.

 + One way to eliminate this deadlock is to force the update statement to not 
use Index “nc2MSmerge_genhistory”, most probably “nc2MSmerge_genhistory” is
being used because we have high number of “genstatus=4” records while the update
was running which forces optimizer to choose “nc2MSmerge_genhistory” as
preferred Index.

 + “genstatus=4” means either a interrupted generation for a remote replica
(like a network failure) or the remote replica is still processing this
generation at the Publisher. We call “sp_MSinsertgenhistory” just before
“sp_MSupdategenhistory” is called , “sp_MSinsertgenhistory” initially inserts
the data into “MSmerge_genhistory” table with genstatus=4 which is later
converted to genstatus=2 by “sp_MSupdategenhistory”.

 +  The change in execution plan forcing  “nc2MSmerge_genhistory” getting used
might be because of high concurrent merge agents running at the same time and
thus ending up with high number of rows for “genstatus=4” during the initial
upload phase.  Or else it would also be interesting to see how many rows you
have on publisher with “genstatus=4” in “MSmerge_genhistory”, are there any rows
which are stuck with “genstatus=4” and are not getting cleaned up at all !

 + Looking into the profiler traces near the deadlock time, there were at
least 19 different Merge agents for "Pawnbroking" that were active at any point
of time within a span of just 1 minute.

 + On a side note, we have not yet observed any other major bottleneck on the
box which can explain the deadlocks."

All this was fantastic stuff from the very knowledgeable Support Escalation Engineer, but by this time I had run out of time & the will to live.

Along with the final MS Support statement & knowing we could not afford the downtime or resources to drop and rebuild our Topology, everything got put on hold.

Thanks for the clarification of a central subscriber model, it's not something we have a use for at the moment. Seems it was convenient for somene to append "ized" onto the pattern and wipe their hands (we only have one Publisher), can't really blame them.

I am going to Mark your original response as an answer as it was really what I had asked before I digressed, but will be monitoring If you feel you have any further advice to give.

Your one suggestion about the Filter makes this whole exercise worthwhile. Thanks again

GW

---

"Everything in the world should be made as simple as possible, but NOT simpler" A.Einstein

Answer 5

How frequently do you update statistics on this box?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

That would be once a week on Sunday after running the intelligent defrag Job

-- Update all statistics after index rebuilds are complete
exec sp_updatestats

---

"Everything in the world should be made as simple as possible, but NOT simpler" A.Einstein

Answer 7

I am guessing here that you are getting bad execution plans for these merge processes. I would not do an sp_updatestats which just tops off statistics which need to be updated, but rather a upstate statistics TABLENAME with sample 20 percent

You might get better execution plans this way.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 8

Thanks

I'll give it a try & let you know if we see any improvement

---

"Everything in the world should be made as simple as possible, but NOT simpler" A.Einstein

Answer 9

Many Thanks, that does clarify things for us.

It does look like the KB was written after our prolonged experience with Premier Support who eventually said our Topology is unsopported because it is a "Centralized Subscriber Model" (whatever that means).

We have 550 UK pull Subscribers to a Filtered Publication & 1 Head Office push Subscriber to an un filtered publication against the same articles (x3 i.e. on each of 3 published databases + the distributor all on one dedicated central box) and have been experiencing approx 50 Deadlocks & (10 Sporadic) unexplainable UPDATE-UPDATE Conflicts per day for years.

I was hoping that now our topology is effectively unsupported by MS the Business would give me the resources (including load testing) to Drop & Recreate our Replication Topology (running for 4 years & suspect corrupted metadata) but it looks like they've had enough and are going to Architect out Replication and try and replace with something else at the application layer (some of their ideas scare me).

Has anyone any idea what a "Centralized Subscriber Model" actually is? our requests for clarification have not been very productive.

I hope this makes sense, Replication can be a very confusing topic when something goes wrong.

GW

---

"Everything in the world should be made as simple as possible, but NOT simpler" A.Einstein

Had same question about "Centralized subscriber" when solvinn our case with MS Premier. And the same confusing KB article.

http://www.sqlrepl.com/sql-server/central-subscriber-model-explained/      helped for me.. :)

Answer 10

AAAARRRRGGGHHHHHHHH!!!!!  Thanks but AAAARRRRGGGHHHHHHHH!!!!! 

centralIZED is debatedly not the same as central, this is where all the confusion is coming from.

As already stated in my OP "Note: I understand the Central Subscriber model with Tran Replication and multiple publishers but this is different."

we Only have ONE Publisher in our entire Topology, Not at all confusing for us. 

PS Forcing update stats on the publisher daily

UPDATE STATISTICS MSmerge_contents WITH SAMPLE 20 PERCENT
UPDATE STATISTICS MSmerge_genhistory WITH SAMPLE 20 PERCENT

Including 2 or 3 of our larger user tables did not help I'm afraid.

O Well, Bye Bye Replication, the damage is done.

---

"Everything in the world should be made as simple as possible, but NOT simpler" A.Einstein