Post reply

Scale out

  • November 20, 2006 at 10:00 am

    #116342

    Anyone have any experience with scale out of SQL2k sp3?

  • November 21, 2006 at 5:37 am

    #673164

    How about qualifying what you're asking a little more so we can help?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • November 21, 2006 at 7:55 am

    #673213

    The only option I am aware of for this in SQL Server 2000 is the "federated databases". And that boils down to distributed partitioned views. And that implies you split your data over several databases sitting on separate servers and joining them together with a view when needed. Since our company is highly aware of uptime this concept introduces multiple points of failure. We currently run in an active-passive cluster for redundancy and to apply failover clustering to multiple instance running on multiple server is not an option.
     
    Charles Deaton

  • November 21, 2006 at 11:07 am

    #673269

     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.  

  • November 21, 2006 at 11:23 am

    #673272

    We do use transaction replication with our cluster acting as the publisher and distributor of 3 separate articles that are pushed. I have not seen a measurable difference in processor when replication is running.

  • November 21, 2006 at 2:15 pm

    #673330

    We will probably end up doing one of the replication scenarios.  Thanks for your help.

  • November 22, 2006 at 5:33 am

    #673420

    You can get performance improvements by disk partitioning, assuming that you still have a resonable amount of physical i/o?

    w2k3 will go to 64Gb ram so that might be one improvment.  Where is your bottleneck on the system? Can I ask why you're still running in 6.5 mode - I can't see that helping the situation at all.

    Partitioned views can be applied in the same database but on different filegroups to give you i/o partitioning - I'd only suggest federating to achieve data tiers or if your box is absolutely maxed out. What sort of performance do you have and what size of data etc.

    I have a slight interest as I too have a badly performing crm/erp app I'm trying to tune.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply