March 29, 2008 at 4:44 am
Hi there,
I'm searching for the best solution to scale out transactional replication with hundreds of publications, but..."I still haven't found what i'm looking for" 🙂
That's the situation: we use transactional replication (push, no update) with almost 100 articles (tables and sp) on sql2k5.
We've the online database, that expose a publication with no filters to the distribution database, which is the remote distributor for that publication . We use that way to not stress the online database with replication log reader of distribution database.
The distrib db itself expose 150 publications (the same base publication, but with different filters on articles - based on subscriber) to 150 subscribers, and he is local distributor of itself.
This seems to work: we've a latency of 2-max 7 sec. in the normal work, but...when whe do large insert/update (almost 100.000 records), the distribution server goes down, with 100% cpu for hours and hours, and the latency goes to the sky.:blink:
The other problem is that it seems whe reached out the hardware resource of the distributor server (150 publications, almost 10-15 mb per distrib.exe), so we're searching for a new scaled solution.
The question is: there is a good way to scale out sql2k5 transactional replication, having more than one distributor server?
Can I have an online database and have a lot of distribution server, each with some publications to load the balance?
I also took a look to log shipping, but..can it work with transactional replication?
Thanks in advance, questions, ideas, criticism and comments are welcome!;)
Maurizio Mangiavacchi.
March 30, 2008 at 3:45 pm
Maurizio,
I'm quite sleepy now, so no idea what I'm writing, but you may find something useful in it. First of all, there's no easy way to happiness 🙂 You can use small tricks to make your life easier, but don't expect too much.
Scale-out can work if you can team your tables into equally loaded groups and replicate one group to one server. However, if you update 10000s of records, it'll still freeze. If you have the opportunity, you can try to run the massive updates as a stored procedure and replicate the stored procedure execution instead of the individual updates. With inserts, it's obviously quite complex. However, if you have any influence on inserts you can try to use smaller batch size. For us, 300 record/transaction was the limit - I should admit, it was a pain updating 16M records this way but at least replication was happy.
Log shipping is not a real option as the secondary database cannot be used for publication as long as it's a secondary. Do you have the opportunity to convert the subscriptions from push to pull?
And you may try to set up more distribution databases (I mean distributor databases) as this way you can shorten the transaction list in each distribution DB and make replication faster.
-- Erik http://blog.rollback.hu
March 30, 2008 at 6:29 pm
Have you looked at republishing?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2008 at 6:32 pm
Here is a link that explains republishing: http://technet.microsoft.com/en-us/library/ms152553.aspx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 3:26 am
Thanks for the reply, Erik.
Yes, I think we should make smallest insert/update, and the problem is in the software that have to be changed 😉
How to run massive update as a stored procedure on the subscriber? Never done...
For log shipping: yes, I've the opportunity to change the subscriptions from push to pull but...how will this change our system? That is: will we have a greater latency?
Another question: so it isn't possibile to assign something like n publications to each distribution?
You're talking about more distribution database, but on the same distribution server?
Thanks in advance,
Maurizio Mangiavacchi
April 1, 2008 at 3:28 am
Thanks for the reply, rbarryyoung.
Yes, we use republishing on the same distribution machine, that is: the online database exposes a publication, and the subscriber is the distribution database. then the distrib exposes n publications (filtered) to the client subscribers.
But there is no other way to scale out transactional replication?
Thans in advance,
Maurizio Mangiavacchi
April 1, 2008 at 8:33 am
Maurizio79 (4/1/2008)
Thanks for the reply, rbarryyoung.Yes, we use republishing on the same distribution machine, that is: the online database exposes a publication, and the subscriber is the distribution database. then the distrib exposes n publications (filtered) to the client subscribers.
But there is no other way to scale out transactional replication?
Thans in advance,
Maurizio Mangiavacchi
I don't understand, why don't you just increase the number of republishers? They would have to be on seperate servers to be effective, but than again, you're not going to "scale-out" anything without more hardware, that's what scale-out means..
Your other options are tuning and optimization. Is that what you are really asking about?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 9:08 am
rbarryyoung (4/1/2008)
I don't understand, why don't you just increase the number of republishers? They would have to be on seperate servers to be effective, but than again, you're not going to "scale-out" anything without more hardware, that's what scale-out means..Your other options are tuning and optimization. Is that what you are really asking about?
Sorry, maybe i didn't use adeguate words.
I want to use more distribution server, that is what i consider "scale out".
But there is that possibility in sql2k5? That is, can I have something like: 100 publications, 5 distributors machine (about 20 publications per distribution)?
So, i'm asking about having more distribution servers in my replication system.
Thanks in advance.
April 1, 2008 at 10:11 am
Maurizio79 (4/1/2008)
Sorry, maybe i didn't use adeguate words.I want to use more distribution server, that is what i consider "scale out".
But there is that possibility in sql2k5? That is, can I have something like: 100 publications, 5 distributors machine (about 20 publications per distribution)?
So, i'm asking about having more distribution servers in my replication system.
Thanks in advance.
Hmm, are you certain that your Distributor is getting overloaded by the Publications? Because my sense is that they are actually being overloaded by their subscriptions.
That is, if you have 150 subscribers, then your Distribution database has to maintain 150 subscription output queues per Publication, and potentially, 150 copies of the replicated data (I am not sure about this, it may only be 150 copies of pointers to the data, but it is still pretty heavy).
You can use Republishers effectively as Re-Distributors to lower this Subscription load: So instead of one Distributor, going to 150 Subscribers; you would have one Distributor going to five Republishers, each going to 20 Subscribers.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 10:31 am
rbarryyoung (4/1/2008)
Hmm, are you certain that your Distributor is getting overloaded by the Publications? Because my sense is that they are actually being overloaded by their subscriptions.That is, if you have 150 subscribers, then your Distribution database has to maintain 150 subscription output queues per Publication, and potentially, 150 copies of the replicated data (I am not sure about this, it may only be 150 copies of pointers to the data, but it is still pretty heavy).
You can use Republishers effectively as Re-Distributors to lower this Subscription load: So instead of one Distributor, going to 150 Subscribers; you would have one Distributor going to five Republishers, each going to 20 Subscribers.
Well, i'm pretty sure that the distributor is getting slower by the number of publications to be updated during massive insert/update time (so not for the big one publication between the online database and the distrib).
And yes, I think the only solution is to re-distribute the database among more than one distribution server, so republishing the data.
April 1, 2008 at 10:57 am
Maybe I'm missing something here: How many Subscribers per Publication do you have?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 11:10 am
rbarryyoung (4/1/2008)
Maybe I'm missing something here: How many Subscribers per Publication do you have?
Just 1 subscriber per publication, 150 and more publications, with articles filter per subscribers.
April 14, 2008 at 11:57 am
Barry raises good question ...
simplistically there are these processes on the Distributor
1. LogReader [reads from PUB.srcdb tranlog, and writes to DIST.distribution]
2. Distribution Agent (DA) [reads from distribution, and writes to SUB.tgtdb]
3. "Distribution clean up: distribution" SQLAgent job (and others) that run every 10 mins
and it is perfectly acceptable to stop [all] the DA before doing your big UPDATE on the PUB
- see if the DIST gets maxxed out
then progressively turn on DA's and see what happens next
- I have seen the clean-up job run solidly to check if changes are all shipped [if so delete]
- peak periods can mean serious locking on the distribution db itself
rather than have 150 pubs (each with varying articles), have you considered one common pub where different subs just pick subset of avail articles ?
best of luck
Dick
May 7, 2008 at 4:36 am
dbaker (4/14/2008)
Barry raises good question ...rather than have 150 pubs (each with varying articles), have you considered one common pub where different subs just pick subset of avail articles ?
best of luck
Dick
this cannot be done because the publications have all the same articles, but with different filters.
May 7, 2008 at 12:04 pm
Maurizio79 (4/1/2008)
I want to use more distribution server, that is what i consider "scale out".
But there is that possibility in sql2k5? That is, can I have something like: 100 publications, 5 distributors machine (about 20 publications per distribution)?
So, i'm asking about having more distribution servers in my replication system.
Thanks in advance.
You can set up separate distribution databases for different servers, os if you have all the data in one DB, then unfortunately you can have only one distribution database.
-- Erik http://blog.rollback.hu
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply