September 4, 2002 at 7:27 am
There is a problem with SQL 7.0 immediately replication data to more than 100 databases,
Refers to Microsoft KB-Q306457 this problem should be fixed by changing registry to allow more concurrent jobs to run.
I try it by changing its default from 100 to 200, but the concurrent jobs is never run more than 110 jobs, it can starts more 10 jobs than its default.
My local MS support saying that, this is by design. Is it TRUE? He could at least explained me why it is 110 jobs can be run, why not 100 as he claimed it is by design. Have any idea? And how to figure out this problem? (Note: I need to do immediately update data when changes occurred)
September 4, 2002 at 8:44 am
We ran into this a long time ago, increasing the registry value did help for a while but eventually just proved unworkable. Combination of issues with not enough threads and even when they did run, we'd get deadlocking in distribution db along with using a ton of resoures per agent. We finally went with a solution where we cycle through all the publications and replicate one at a time. Increases latency, solves all the other issues. At times I've had as many as 250 db's replicating, trying to run 500 agents is just too much. Couple articles posted here on the site describe the solution.
Andy
September 6, 2002 at 8:28 am
For more info.
I did change Distribution subsystem in registry. We also tested with MS SQL 2000 it was the same result. We then applied latest Service Pack both Windows2000 (SP3) and SQL 7 (SP4), actually we have no point to apply these service packs our applications have never been encountered any problems showing in its fixed lists. But by MS recommended that we should always update SP that is a best practice, and then we have to do it. I don’t like this way if we have no problem with our solution why we have to apply SP to increase more risk to our solution. Other reason to apply SP is that MS like to claim that we may have (this, that, those) problems because of the SP is not updated, but no documents and solution for us to refer. How could he proudly say the word “PROFESSIONAL”?
And you know what? MS told me that this replication solution is a bad design (Centralized with many subscribers). Ah! I’ve just to know SQL product having usage limit on some features as well, now I’m not quite sure we are using Enterprise Edition OR Limited Edition. I think if I have server with high capacity that can handle replication processes as many as I’d like to do. It should allow me to do and let my server telling me how limits I can do, and then I will scale it by adding adequate hardware.
Right now, we have a bit choices for our solution, we have a server which is having 4 CPU Xeon 800 MHz, 4 GB memory, more than 700 GB of hard disk and the replication data is too small but we cannot run more than just 100 and 10 distribution agents. And we have to think about Scalability solution of our replication. What do you think?
Anyway, we still need immediate transactional replication.
Is it possible for these solutions;
1. upgrade SQL 7 on this server to be SQL2000 and let each SQL instance to taking care 100 distribution agents?
2. Buying more publisher servers to taking care for each every 100 distribution agents increased.
3. do as what you say that what I would like to do but we stuck on the solution requirement that needs immediate transactional consistency.
September 6, 2002 at 8:39 am
Possibly #1 will work, but definitely would want to test it before committing. #2 is going to get expensive, but is not a bad strategy. #3 you can split the difference, run your agents in non-continuous mode but strive to keep the reasonable max running at all times. If you can support 100 agents, then you'd cycle in such a way to keep all 100 active, just rotating which db's are being processed. You could also elect to have certain db's continue to replicate continuously and do the ones of lesser importantance on a rotation. If you had 500 db's and able to run 100 agents, you could probably keep your latency to a min or less. I'd push back about the latency requirements, when we started talking about it here they had to have it NOW, turns out they didnt! What we did do was give the users who had a need at certain times of the day (for reporting) to make sure it was caught up was to run the agents outside of the schedule.
Hope that helps.
Andy
September 7, 2002 at 1:46 am
Thanks for all your recommendation. I will have expertiments for all and then let you know which one is suit ours.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply