August 3, 2011 at 6:50 am
Hi,
We are planning to upgrade the hardware to our distribution server. Earlier it was having multiple instance handling each publication.
What is a good option:
1. To have multiple distribution databases for handling multiple publication
2. Or to have multiple Instance, separate instance for each publication.
Which do you thing is good option and why? Any article or document to support ?
August 3, 2011 at 8:14 am
Why do you want to separate each publication so distinctly? I wouldn't even have separate distribution databases per publication, let alone separate instances! We do have multiple distribution servers, but only a few, more for logical design reasons than performance reasons, and on each server we just have a single distribution database because that's all we need.
We then use PULL subscriptions, which means all the distribution agents run locally on the subscribers, and not all on the distribution server. This takes a (potentially) huge performance bottleneck away from the distributor.
August 3, 2011 at 8:34 am
my mistake,
read it as publisher server not publication
Just to explain a bit more.
We have three server from which we are publishing data to 10 subscriber.
too many transaction are going on each Publisher. Earlier we were having saperate instance of distribution server for each publisher.
now we are thinking to have multiple distribution Database instaead of multiple instance but not sure which is better.
August 3, 2011 at 9:14 am
I'm still not quite sure where your problem lies.
How many Publications do you have? I know you have 3 publishers, but do they reference one publication each (with each publication containing many articles), or do they reference many publications?
When you say "too many transactions are taking place", what is the result of this? Are your distribution databases growing too large? Are you just seeing latency?
If you have many publications for each publisher, I would seriously look at PULL subscriptions instead of PUSH subscriptions.
If you have, say, 10 publications for each publisher, that is 30 publications in total. For PUSH subscriptions, that will need a total of 300 distribution agents to server your 10 subscribers (assuming they all subscribe to all publications) which would be a very heavy load on your distributor.
By using PULL subscriptions, the distribution agents would run locally at each of the 10 subscribers, meaning a maximum of 30 agents at each subscriber - much more manageable from a resource point of view.
August 3, 2011 at 9:27 am
300 Distribution agent: Correct, thats the load i want to reduce, for Pull we have to change the complete replication scenario of our company(we can still check that later) but for now making multiple instances and using it for each publisher, will it work fine?
10(publication each publisher) X 10 (subscription) =100 distibution agent per instance of distibution, make sense?
what you say?
August 3, 2011 at 9:43 am
Pujain, you need to provide much more info, such as number of publications/publishers. Number of subscribers. Number of articles in the package. Is the distributor a remote that is used solely for this purpose.
Then, you need to evaluate everything that you are currently doing and whether you need to make changes.
Example:
My replication system which we are still building has 72 subscribers currently, with 20 publications. That translates into 736 agents running, including snapshot, log reader and transaction agents. I average 15M transactions per day.
The distribution server is a dedicated 12 core w/32G of memory.
We are still growing. There were many painful moments with a lot of tuning that had to go on to include breaking up some of the packages that going over a T1 took several hours to initialize becasue of the numbers of records.
Additionally are all of your publishers and subscribers on the same version of SS as the distributor? If not, you may lose some functionality as the distributor will operate at the level of the lowest version of SS published on that distributor.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 3, 2011 at 9:47 am
300 Distribution agent: Correct, thats the load i want to reduce, for Pull we have to change the complete replication scenario of our company(we can still check that later) but for now making multiple instances and using it for each publisher, will it work fine?
Sorry, I was typing when you updated.
Are you talking about multiple instances of SQL Server? Remember that each instance takes a portion of the total available resources.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 3, 2011 at 9:59 am
ok, here's the details:
No of Publisher : 3
No of Publication each Publisher: 35
No of Article per Publication: few has 4/5 (very big tables) others 10 / 15
No of Subscription per Publication: 3/4
Memory: 36 GB
Processors(2): 3.07 GHz
question again:
1 distibution server works
3 instance of SQL working as distibution servers.
3 distribution databases
anything else you suggsest?
August 3, 2011 at 10:26 am
If that configuration is working for you, keep it.
Keep an eye out for agents failing. You are going to be hitting a limit in windows soon for non-interactive desktop heap and will have to adlust it.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 4, 2011 at 2:44 am
Alternatively, if that configuration is working for you, take advantage of that fact and plan a more scalable one now whilst you still have the luxury, rather than in a panic when your current one starts failing! 🙂
I know I keep banging on about PULL replication, but your scenario seems to be getting so close to the one we had (where we hit the agent limit that Steve mentions) and switching to PULL subscriptions meant the problem ceased to exist.
August 4, 2011 at 5:16 am
I know I keep banging on about PULL replication
Totally agree, as long as the load of the subscription doesn't impact performance. This will require, in the words of MS, testing to see if there is an impact.
In my case, the sheer volume of data being replicated was an issue at times. But, as long as he has a dedicated distribution system he can increase the non-interactive desktop heap considerably. The default is 768 KB. I am currently running 8MB and expect to get to 10MB before I am done. Replication is one of those things that seems to be tailered to each site.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply