SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scaling Out the Distribution Database


Scaling Out the Distribution Database

Author
Message
John.Sansom
John.Sansom
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1851 Visits: 1558
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
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16976 Visits: 3403
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
www.simple-talk.com
Thomas LeBlanc
Thomas LeBlanc
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4615 Visits: 915
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/
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25721 Visits: 2746
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
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36136 Visits: 11361
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16976 Visits: 3403
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
www.simple-talk.com
Kendal Van Dyke
Kendal Van Dyke
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1652 Visits: 983
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/
Kendal Van Dyke
Kendal Van Dyke
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1652 Visits: 983
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/
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16976 Visits: 3403

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
www.simple-talk.com
Kendal Van Dyke
Kendal Van Dyke
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1652 Visits: 983
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search