December 1, 2008 at 3:00 pm
I am in need of the collective brain trust. I have a system that is in development. For the database backend, I have at the ready 4 Dell M600 blade servers. Each server has dual Quad-Core 2.23G proc with 24GB RAM. We have 14TB (raw) SAN for storage. The whole system runs on fiber. In addition, I have four licenses for SQL Server 2005 Enterprise and Windows Server 2003 Enterprise is the OS. The database is large (over 800mb, not including indexes, and growing by ~10mb per day). I need to maximize query speed and performance. Failover security is less important for reasons that are unimportant.
Clearly, indexing is one aspect of this project--which I've worked out. The thing I am having an issue with is the db cluster and SAN configuration. For the servers, which is better Active/Passive or Active/Active? I have some experience setting up Active/Passive servers, but none with Active/Active. Is there a guide/book that explains how to do it and what are the best practices? Or does anyone have any suggestions or designs that I might consider?
December 1, 2008 at 3:51 pm
Whether to go Active/Active or Active/Passive is really a decision that you need to make based upon the available hardware, uptime requirements, performance, load, and licensing. If I were you I think Active/Passive would likely be the best option. It requires more hardware, but also allows for better performance since you can allocate more hardware resources, etc.
That being said, if you are having big performance issues on a single 800 MB database, you may want to investigate some basic performance tuning types of things to improve performance. A 800 MB database is tiny, and with proper indexes, coding standards, etc, there's no reason you couldn't get all the performance you need out of much less hardware.
A.J.
DBA with an attitude
December 1, 2008 at 5:50 pm
shane.barney (12/1/2008)
...The database is large (over 800mb, not including indexes, and growing by ~10mb per day)....
800 MB is a very small database. Did you actually mean 800 GB?
December 2, 2008 at 1:21 am
Active/Active implies that there will be a second SQL instance on the second node that has its own databases. Is that what you're planning?
Clustering isn't a performance enhancement. Active/Active doesn't mean a load-balanced database that's running on both nodes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 5:22 am
As Gail has pointed out, Active/Active is not a load balancing solution. An Active/Active cluster is actually two Active/Passive instances. If you only have a single database, you cannot set up an Active/Active cluster.
Assuming a generally good database design, an 800GB database is not all that big these days.
December 2, 2008 at 6:11 am
Thank you for your responses and suggestions. I guess the really question than is should I split up the database so that I could run Active/Active and is there any advantage to doing so. The database has five primary tables in a star schema. I haven’t seen a lot of information on the pros/cons of an Active/Active setup. What are the advantages and disadvantages of such a setup? More to the point, given the resources I listed above and what little you know of the database, what setup would you recommend?
Sorry for the “newbie” questions, I am just a little out of my element here.
December 2, 2008 at 6:20 am
How would you split up the database?
Do you intend to have you application layer determine which instance to query based on some criteria?
Your data is in a star schema, do you intend to use Analysis Services for high-speed querying?
Ignore the Active/Active clustering name and think of it as two entirely different SQL servers (because that is what it really is). How do you intend to split your database over two servers and still have the application function? It is not impossible, or even that hard to do, but doing it in a way that will improve performance rather than hurt it can be pretty tricky. The real key is to make sure your application can figure out where to get data and most requests do not require some kind of a distributed query. If you find yourself writing a lot of queries that use 4 part names, you can end up in trouble quickly.
December 2, 2008 at 6:36 am
Linked servers and remote queries are an excellent way to slow things down.
Why do you want to split the DB up? If the hardware is correct, the design is good (star schema - cubes?), the indexes are well designed and the queries well written, a single database will perform very well.
SQL doesn't scale out (yet)1 and most attempts at doing that cause far more problems than they solve.
Is your DB 800 MB or 800 GB? Growing by 10 MB a day?
1) See the upcoming Kilimanjaro (eta first half of 2010) which includes scale-out for datawarehouses (Star/snowflake schemas). That's for LARGE datawarehouses. Like the 150 TB datawarehouse that they queried live during the PASS keynote (no, that is not a typo. 150 terabytes with a trillion row fact table)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 7:28 am
You all rule! Thanks for the advice; you have saved me from many hair-pulling hours of work. I am going to stick with an Active/Passive configuration and go from there. If things become ugly, I'll look to another solution. Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply