August 2, 2010 at 9:59 am
I have a vendor whose product uses transactional replication. Currently they are creating one publication per article for replication. It seems to me that it would be more efficient to create one publication with multiple articles. After all, I don't want 27 Sports Illustrated's each week, I just want one with 27 articles in it.
Are there any performance tradeoffs/considerations here? It seems to me that the potential for latency issues would be magnified if one publication doesn't stay in sync with another publication.
"Beliefs" get in the way of learning.
August 2, 2010 at 1:56 pm
I have not seen any performance impact on having one publication for multiple articles. The only thing you try to do is make it manageable and not have way too many articles in one publication.
For instance if you have timestamp column on 10 tables, if I were you, I would create one publication with all the tables with timestamp. This is just an easy way to manage the publications.
The advantage of having multiple publication is that for each publication, it will have its own log reader agent.
-Roy
August 2, 2010 at 2:03 pm
Thanks for your response. I would also think that having too many (whatever "too many" is) log reader agents could potentially cause problems as well. I think, like with most things in SQL Server, the answer is "it depends". I'm curious so I think I'm going to factor this into my test plan, i.e. multiple publications and log reader agents versus one.
"Beliefs" get in the way of learning.
August 2, 2010 at 2:16 pm
Yes, You are right..... But if I had given the answer that "It Depends", I do not think it would have been that helpful.. 🙂 Thus I gave the long answer...
We have 10 Publications. Total of around 300 plus articles. For us that is the optimal set up.
-Roy
August 2, 2010 at 2:26 pm
I'm not that familiar with the internals of replication, yet, so I was hoping someone might know a "best practice" or "rule of thumb". Maybe it's just me but this seems to be an area, i.e. replication, where knowledge is a little thin, even on this site. I know I've posted questions here that weren't ever answered. I'm going to be delving into this pretty heavily in the next six months or so. Perhaps I'll be able to answer some questions instead of asking.
"Beliefs" get in the way of learning.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply