|
|
You're in the
sql server replication
group:Replication parameters question
sql server replication:
I have three questions here regarding replication parameters: 1. SubscriptionStreams: It seems I cannot put this parameter into a user created profile, so if I want to add/change this parameter for distribution agent, I can only do this by going the distributor agent JOB (step 2 Run agent), am I right? 2. Assuming I am right for question 1, after I added this parameter, for example, -SubscriptionStreams 4, does this mean this distribution agent will have four sessions running on the distributor side (assuming we have push type transactional subscription)? 3. Final question, have any of you experienced a scenario that distribution agent becomes a bottleneck Other Than a bottleneck caused by cpu/memory/hard-disk? For example, the replication workload is too huge, in terms of 100 to 200 GB per day? I am asking this question because according to http://blogs.technet.com/lzhang/archive/2006/05/12/428178.aspx, sql server replication agent can be a bottleneck itself. Thanks for your help in advance! Jeff
Please, Hilary, publish your paper asap, I hope that can be replication
"bible" reference paper for me. Jeff [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:%23IbD$TB7HHA.4660@TK2MSFTNGP02.phx.gbl... > 1) yes > 2) no > 3) yes. If you do a large batch update on your publisher the distribution > agent will become the bottleneck as it processes all the singletons in > this batch. Also the replication agent parameters if not set correctly > will throttle throughput. Conversely well tuned and appropriate settings > will result in better performance. > > I am writing a white paper for simple-talk which should be published > shortly which addresses this problem. > > -- > RelevantNoise.com - dedicated to mining blogs for business intelligence. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "jeff_y" <jeffrey_ya@abc.com> wrote in message > news:eXoiOc$6HHA.2208@TK2MSFTNGP06.phx.gbl... >> Hi replication gurus, >> I have three questions here regarding replication parameters: >> 1. SubscriptionStreams: It seems I cannot put this parameter into a user >> created profile, so if I want to add/change this parameter for >> distribution agent, I can only do this by going the distributor agent JOB >> (step 2 Run agent), am I right? >> >> 2. Assuming I am right for question 1, after I added this parameter, for >> example, -SubscriptionStreams 4, does this mean this distribution agent >> will have four sessions running on the distributor side (assuming we have >> push type transactional subscription)? >> >> 3. Final question, have any of you experienced a scenario that >> distribution agent becomes a bottleneck Other Than a bottleneck caused by >> cpu/memory/hard-disk? For example, the replication workload is too huge, >> in terms of 100 to 200 GB per day? I am asking this question because >> according to >> http://blogs.technet.com/lzhang/archive/2006/05/12/428178.aspx, sql >> server replication agent can be a bottleneck itself. >> >> Thanks for your help in advance! >> >> Jeff >> > >
Hi Hilary,
Since you answered "No" to my 2nd question, I just found something that may support me for saying "Yes" to the 2nd question. Please take a look at http://support.microsoft.com/kb/934188/en-us Specially under "More Information" section, it seems to IMPLY that by using SubscriptionStreams (> 1), we should see multiple distribution agent process? Is this the right implication or am I just wildly guessing? Thanks, Jeff [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:%23IbD$TB7HHA.4660@TK2MSFTNGP02.phx.gbl... > 1) yes > 2) no > 3) yes. If you do a large batch update on your publisher the distribution > agent will become the bottleneck as it processes all the singletons in > this batch. Also the replication agent parameters if not set correctly > will throttle throughput. Conversely well tuned and appropriate settings > will result in better performance. > > I am writing a white paper for simple-talk which should be published > shortly which addresses this problem. > > -- > RelevantNoise.com - dedicated to mining blogs for business intelligence. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > "jeff_y" <jeffrey_ya@abc.com> wrote in message > news:eXoiOc$6HHA.2208@TK2MSFTNGP06.phx.gbl... >> Hi replication gurus, >> I have three questions here regarding replication parameters: >> 1. SubscriptionStreams: It seems I cannot put this parameter into a user >> created profile, so if I want to add/change this parameter for >> distribution agent, I can only do this by going the distributor agent JOB >> (step 2 Run agent), am I right? >> >> 2. Assuming I am right for question 1, after I added this parameter, for >> example, -SubscriptionStreams 4, does this mean this distribution agent >> will have four sessions running on the distributor side (assuming we have >> push type transactional subscription)? >> >> 3. Final question, have any of you experienced a scenario that >> distribution agent becomes a bottleneck Other Than a bottleneck caused by >> cpu/memory/hard-disk? For example, the replication workload is too huge, >> in terms of 100 to 200 GB per day? I am asking this question because >> according to >> http://blogs.technet.com/lzhang/archive/2006/05/12/428178.aspx, sql >> server replication agent can be a bottleneck itself. >> >> Thanks for your help in advance! >> >> Jeff >> > >
1) yes
2) no 3) yes. If you do a large batch update on your publisher the distribution agent will become the bottleneck as it processes all the singletons in this batch. Also the replication agent parameters if not set correctly will throttle throughput. Conversely well tuned and appropriate settings will result in better performance. I am writing a white paper for simple-talk which should be published shortly which addresses this problem. -- RelevantNoise.com - dedicated to mining blogs for business intelligence. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "jeff_y" <jeffrey_ya@abc.com> wrote in message news:eXoiOc$6HHA.2208@TK2MSFTNGP06.phx.gbl... > Hi replication gurus, > I have three questions here regarding replication parameters: > 1. SubscriptionStreams: It seems I cannot put this parameter into a user > created profile, so if I want to add/change this parameter for > distribution agent, I can only do this by going the distributor agent JOB > (step 2 Run agent), am I right? > > 2. Assuming I am right for question 1, after I added this parameter, for > example, -SubscriptionStreams 4, does this mean this distribution agent > will have four sessions running on the distributor side (assuming we have > push type transactional subscription)? > > 3. Final question, have any of you experienced a scenario that > distribution agent becomes a bottleneck Other Than a bottleneck caused by > cpu/memory/hard-disk? For example, the replication workload is too huge, > in terms of 100 to 200 GB per day? I am asking this question because > according to > http://blogs.technet.com/lzhang/archive/2006/05/12/428178.aspx, sql server > replication agent can be a bottleneck itself. > > Thanks for your help in advance! > > Jeff >
its the wrong implication. Try this. Configure a publication of several
tables. Create a subscription. Stop SQL Server agent. Reconfigure the distribution agent with a subscriptionstreams value of 4. Do a bunch of inserts which look like this insert into tablea GO insert into tableb GO insert into tablec GO insert into tablea select * From tableb GO update tablea set col1=col1 GO update tableb set col1=col1 Then start up the log reader agent. When it has finished, stop it. Then open up task manager. Start up the distribution agent and watch how many instances of distrib you see in task manager. There will only be one. Threading works by having a single binary spawn multiple threads which are not separate binaries, but merely threads. You can also see the thread count for the distrib binary increase. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "jeff_y" <jeffrey_ya@abc.com> wrote in message news:%238fbn1B7HHA.484@TK2MSFTNGP06.phx.gbl... > Hi Hilary, > Since you answered "No" to my 2nd question, I just found something that > may support me for saying "Yes" to the 2nd question. > Please take a look at http://support.microsoft.com/kb/934188/en-us > > Specially under "More Information" section, it seems to IMPLY that by > using SubscriptionStreams (> 1), we should see multiple distribution agent > process? > > Is this the right implication or am I just wildly guessing? > > Thanks, > Jeff > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:%23IbD$TB7HHA.4660@TK2MSFTNGP02.phx.gbl... >> 1) yes >> 2) no >> 3) yes. If you do a large batch update on your publisher the distribution >> agent will become the bottleneck as it processes all the singletons in >> this batch. Also the replication agent parameters if not set correctly >> will throttle throughput. Conversely well tuned and appropriate settings >> will result in better performance. >> >> I am writing a white paper for simple-talk which should be published >> shortly which addresses this problem. >> >> -- >> RelevantNoise.com - dedicated to mining blogs for business intelligence. >> >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> "jeff_y" <jeffrey_ya@abc.com> wrote in message >> news:eXoiOc$6HHA.2208@TK2MSFTNGP06.phx.gbl... >>> Hi replication gurus, >>> I have three questions here regarding replication parameters: >>> 1. SubscriptionStreams: It seems I cannot put this parameter into a user >>> created profile, so if I want to add/change this parameter for >>> distribution agent, I can only do this by going the distributor agent >>> JOB (step 2 Run agent), am I right? >>> >>> 2. Assuming I am right for question 1, after I added this parameter, for >>> example, -SubscriptionStreams 4, does this mean this distribution agent >>> will have four sessions running on the distributor side (assuming we >>> have push type transactional subscription)? >>> >>> 3. Final question, have any of you experienced a scenario that >>> distribution agent becomes a bottleneck Other Than a bottleneck caused >>> by cpu/memory/hard-disk? For example, the replication workload is too >>> huge, in terms of 100 to 200 GB per day? I am asking this question >>> because according to >>> http://blogs.technet.com/lzhang/archive/2006/05/12/428178.aspx, sql >>> server replication agent can be a bottleneck itself. >>> >>> Thanks for your help in advance! >>> >>> Jeff >>> >> >> > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |