Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Same article in multiple publications Expand / Collapse
Author
Message
Posted Friday, July 20, 2012 7:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 9:26 AM
Points: 26, Visits: 65
We have a merge replication scenario with pull subscriptions. Currently there is only one publication but we are adding a new publication, where some articles are going ot be common. Some of the common articles are going to be download only. but some are updateable on both sides. With this information, can someone please point me the right direction to achieve this? Obvious goal is to do it right the first time even if the solution seems eccentric. Thank you.
Post #1332970
Posted Tuesday, July 31, 2012 7:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:32 PM
Points: 1,618, Visits: 1,550
the best practice here is to not put an article in multiple pulbications. It's bad for performance. Breal it up into smaller publications if you have to and send multiple publications to the same subscribers, but don't do what you propose.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1337908
Posted Tuesday, July 31, 2012 4:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 9:26 AM
Points: 26, Visits: 65
So for a bidirectional(but always inserted at the subscriber) article, across publications, even if it is guaranteed that a uniquely identified row exists at only one subscriber (other than the publisher of course), you think it still might cause performance bottleneck? This is a filtered article in both publications.

As an example, let's say this is a 'UserLog' table. only subscribers log and not the publishers. There are two different publications, but only one 'UserLog' table, so we got to include it in both publications. We filter subscriptions by userid, so all datasets are disjoint wrt userid.

Do you think this still is a performance bottleneck?
Post #1338242
Posted Tuesday, July 31, 2012 5:13 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:32 PM
Points: 1,618, Visits: 1,550
When it an article is in multiple publications, it is tracked separately for each one. So every update to the table will be tracked multiple times. This will cause there to be more records in the tracking tables. You are better off putting that table into a publication by itself and subscribing both subscriptions to it.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1338249
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse