Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Scaling Out the Distribution Database Expand / Collapse
Author
Message
Posted Wednesday, March 31, 2010 3:21 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, August 23, 2014 6:03 AM
Points: 351, Visits: 1,556
Kendal/David,

Could you also post your CPU configurations please and whether or not you are using Push or Pull subscriptions.

As I'm sure you know, Push subscriptions place more burden on the Distributor server resources.



John Sansom (@sqlBrit) | www.johnsansom.com
Post #893578
Posted Wednesday, March 31, 2010 5:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 2,923, Visits: 1,875
Varies from
Physical 8CPU 64bit W2K8 SQL2008 standard edition with 12GB RAM to
Virtual 4CPU 32bit W2K3 SQL2005 Standard edition with 8 GB RAM.

Mainly push subscriptions but pull used for the larger tables.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #893667
Posted Wednesday, March 31, 2010 9:25 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 3,176, Visits: 797
David,

Thanks for the article. I love finding out new things about replication.

Also, thanks to Kendall and the discussion. I am trying to read the links as I type.

God Bless,
Thomas
http://thesmilingdba.blogspot.com/


Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #893882
Posted Wednesday, March 31, 2010 10:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 12:51 PM
Points: 6,808, Visits: 1,938
Good discussion. David, that is a LOT of records!

One note on the expiration period. I can see why with x billion rows you want to shorten it, but for less extreme environments I've set it to 96 hours, allowing me to survive a replication stoppage over a 3 day weekend without having all my pubs expire. I agree that monitoring should be in place as well, but this saved me some pain a couple times.

Early in my SQL days I had 250 db's replicating into one distribution db. Ran into a lot of contention, ended up solving it by not running all the logreaders at once, didn't think to try the multiple distribution model. The one advantage of my approach (was on SQL 2000) was saving quite a bit of memory by only running a handful of logreaders at once.


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #893934
Posted Wednesday, March 31, 2010 10:06 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 9,928, Visits: 11,207
Hey David,

Assuming the box is licenced per-processor, would you expect any advantage to running the separate distribution databases in separate instances?

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893940
Posted Wednesday, March 31, 2010 11:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 2,923, Visits: 1,875
Paul White NZ (3/31/2010)


Assuming the box is licenced per-processor, would you expect any advantage to running the separate distribution databases in separate instances?


I'm not up to date with licensing requirements on multi-instance installations.

Having multiple instances running on one box strikes me as a good way to detract from the available memory so anything you would gain from instance isolation is lost to the memory requirements of separate instances.

This is outside of my area of expertise.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #893988
Posted Wednesday, March 31, 2010 11:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:35 PM
Points: 362, Visits: 897
John.Sansom (3/31/2010)
Kendal/David,

Could you also post your CPU configurations please and whether or not you are using Push or Pull subscriptions.

As I'm sure you know, Push subscriptions place more burden on the Distributor server resources.


The distributor that I gave details for previously is a 2x Xeon E5430 (quad core, so 8 total cores) @ 2.66 Ghz. I should also mention that the HDDs are 15K RPM SAS drives.

CPU isn't the bottleneck on any of my distributors - it's almost always disk IO.

All of my subscriptions are push.


Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #894011
Posted Wednesday, March 31, 2010 11:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:35 PM
Points: 362, Visits: 897
David.Poole (3/31/2010)
No offence taken.

We have four production distributors two of which are virtual servers.

We put roughly 24 billion transactions through them each day. We have spiked up to 370 billion transactions but this was exceptional.

On the subject of whether this works with SQL2000 distributors the short answer is "I don't know". Some of of publishers are SQL2000 but as the distributors have to be the highest version of SQL that are acting as publishers none of our distributors are SQL2000.


Not sure if that's 24 billion per distributor or total, but either way that's a LOT of transactions to push through! Fair to say that your case is the exception to the norm? I think that while multiple distribution DBs may solve this kind of problem for the typical shop that has < 50 million transactions per day one distribution DB per publication as a standard practice is a bit excessive.

My point isn't to knock your article or to say you're wrong - but to try and put some better idea in place of when it's appropriate to consider multiple DBs as a solution to latency\performance issues.

Just curious - have you looking into replicating stored procedure execution to cut down on the transaction volume?


Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #894014
Posted Wednesday, March 31, 2010 2:29 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 2,923, Visits: 1,875

Just curious - have you looking into replicating stored procedure execution to cut down on the transaction volume?


Absolutely, I was intending to write up an article on it. It cuts down on the number of records flooding through the transaction table dramatically. Particularly if you have heavy purge activity going on.

People forget that insert, updates and deletes still flow through to the distributor even if they don't make it to the commands table.

Having separate distribution databases, even on a weedy virtual box, doesn't do any harm. If you run perfmon you can see the transactions per second going into the distributor. If you monitor it over time then you can get a fair idea when you should add a separate distributor and what should be hived off onto it.

That said we are seeing a massive boost from running SQL2008 64 bit on W2K8.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #894207
Posted Wednesday, March 31, 2010 2:48 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:35 PM
Points: 362, Visits: 897
That said we are seeing a massive boost from running SQL2008 64 bit on W2K8.


I'm guessing that's in part dueto the rewritten network stack in Windows 2008. There's a great whitepaper that talks about that:
Geo-Replication Performance Gains with Microsoft SQL Server 2008 Running on Windows Server 2008


Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #894218
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse