David.Poole (3/30/2010)
If you have everything hammering through one database you are talking about one transaction table having to deal with all transactions. Ditto the commands table.
But if you have two, three, four, etc. distribution databases that doesn't change the number of commands and transactions that have to be written. The IO is still the same.
Yes you can have the different distribution databases on different drives with obvious benefits but separate databases have at least got separate files. It's a myth that separate files get separate threads but you are dealing with far smaller files and each distribution database has fewer records to deal with.
Yes, that is a myth. The CSS team has a great blog post about it here.
I think the discussion has more merit if we put some metrics out for comparison. My busiest distributor is a PowerEdge 2950 running SQL 2008 standard edition (x86) on Windows 2003 (x86) with 6 local drives (2 in RAID 1 for OS and transaction logs and the other 4 in RAID 10 (with 64KB partition offset, 64 KB block size, and 64 BK stripe size) for data) and 4 GB of RAM. It's responsible for 82 publications, 240 subscriptions, and based on distribution agent history for the last 48 hours it's pushing through just shy of 80 million commands per day. All distribution agents run continuously and there are rarely any latency issues that need to be dealt with.
How do those numbers and hardware compare to your distributor? (Please don't take this the wrong way - it's not a "prove it" tone I'm taking here...I'm just trying to understand how our environments compare to each other so I can better understand your reasoning for using multiple distribution DBs)