Ugh! I dont think its feasible to split our ERP db up into a federated configuration. We have a SAN but there are almost 2000 tables with no clear way to partition the data. Uptime is a concern with us too. Deep sigh...
I would like to seperate the inventory transactions off from the rest. They are very ineffiecent. The inventory transactions are basicly a series of queries that only utilize one thread. We have tinkered with the "max threshold for parrallelism" and seem to get the best results at 3 seconds. The software is poorly written and fixing it is impractical. I suppose something could be done with replication/distributed transactions. Is there much cpu overhead with replication? Would the gain be enough to consider this scenario? Ideally the data should be real time but a 15-20 minute latency would probably be acceptable.
We already went the scale up route. I am running a Dell 6850, 32 gig RAM and a Hitachi SAN with Raid 10's. That has worked for a while but we have 2 business acquisitions on the horizon. I know we wont be able to handle the second.
For what its worth, I am running SQL2k sp3, compatiblity mode 6.5.