Load Balancing SQL Server 2005

I am seeking to enlighten myself on the concept of highly available or high performance database architecture.

 

There, now that I got the buzzwords out of the way we can get down to business.  I am dying to understand how large companies with large e-commerce websites deal with database.  I am specifically interested in SQL Server 2005.  Let’s say I have a web farm of a 50 machines all load balanced with my web store application.  Now, I’m incapable of describing this with the proper technical terms, but bear with me:  I would like to setup 10 (the number really matters not, just more than 1) sql server instances that will serve my web farm for my site.

 

Basically I want everything load balanced and available at any moment.  The basic concept is that if I have x number of sql servers all serving queries and recording transactions I will get better overall performance under heavy load.

 

Now, somebody will get on here and rip me a new one in some fashion so have at it.  Have your fun.  In the end I’m still simply curious if this is possible and where might I find some information on this topic.  I found this article which was interesting though I’m not so sure I understood it all or that it describes what I want:

 

http://www.microsoft.com/technet/technetmag/issues/2006/07/InsideMSFT/

 

For example, in the article they go into detail about one very important problem.  If my request to record shipping info is load balanced to SQL Server “A” and then on the next page I show them a page to confirm what they’ve entered and I do that by reading the record from the database how do I get around the fact that I may get load balanced to SQL Server E which hasn’t had the data replicated to it yet?  I suspect this “problem” only gets worse the more sql instances you have.

 

So, any suggestions for reading?  Any comments?  Be gentle in your remarks please, but be firm.  I come seeking wisdom.

Although currently in beta Resonate dbDispatch has a creative solution for SQL Server reliability/redundancy and scalability/load-balancing. See http://www.resonate.com/prod_db_disp.html and http://www.resonate.com/pdf/dbd_whitepaper.pdf.


- Taiwo

well let's see...

first a multi server cluster with all of them active is not easy to build and run... and for 10 servers would cost quite a lot.

generaly a good way is to just build 2 big multi-proc servers and run the x64 server os and sql server.

on that you can loadup a lot of ram and a high-perf disk system.

then run as many sql instances as you need.

even one multi proc server can service a lot of work....

of course good database design helps a lot... a poorly factored database can kill even a good server...
but even huge databases can be queried and managed by one server ...

for example I code and maintain a system that does thousands of orders each day in a few hours.... it runs on one dual proc server with about 3 gigs of ram, most of the time the cpu use is around 2-4%  memory is what is used - around 2 gigs for sql server 1 gig for windows os.

so to expand the server i'd go to x64 and add a huge amount of ram say 8 gigs or 16 gigs  then I could run several times the work on mostly the same hardware.

the second server is your fail-over
you replicate the data to it and ship logs to it.
if the primary goes down the second picks up and keeps going.
then you fix the one thats down.
like mirrored hard drives in a way.

figuerres,

The setup you describe is probably the one I'm most familiar with...not trying to say I'm an expert or anything it is just the only thing in this thread so far I have experience with.  So, I guess one thing I'm wonders is when you talk about moving from a few thousand orders an hour to several hundred thousand transactions an hour or a minute are we expecting a single server to scale vertically?  Is that the only option on the SQL Server platform? 

What I'm getting at is this:  I'm not sure what I'm asking is realistic, just more like is it possible and where could I find info about it.

Currently at my job we have the scenario you discuss.  64 bit servers, 8 gigs RAM, quad processor.  One primary box, one failover.  An active/passive scenario.  Both are 2005 SQL Enterprise.

I agree with you on everything you've stated, but what about WHEN/IF you get to the point that machine cannot handle it?  At lesat not handle it as promptly as someone asks you to make it handle it.

I didn't quite have the time to read through the whole article but it seems to touch on the subject.

http://www.databasejournal.com/features/mssql/article.php/3319481

I know Allan Hirt put together the MS Press book on High Availability for SQL 2000.

The 2005 update appears to be published through APress.

http://www.amazon.co.uk/Pro-Server-2005-High-Availability/dp/159059780X/ref=sr_1_1/202-8206631-9898263?ie=UTF8&s=books&qid=1189687790&sr=8-1

 

This is the source of all non-development information about Sql Server:

http://technet.microsoft.com/en-us/sqlserver/default.aspx

Take a look in the right sidebar of the page, there are webcasts available about the "high availability features" of Sql Server 2005.

nosajis wrote:

figuerres,

The setup you describe is probably the one I'm most familiar with...not trying to say I'm an expert or anything it is just the only thing in this thread so far I have experience with.  So, I guess one thing I'm wonders is when you talk about moving from a few thousand orders an hour to several hundred thousand transactions an hour or a minute are we expecting a single server to scale vertically?  Is that the only option on the SQL Server platform? 

What I'm getting at is this:  I'm not sure what I'm asking is realistic, just more like is it possible and where could I find info about it.

Currently at my job we have the scenario you discuss.  64 bit servers, 8 gigs RAM, quad processor.  One primary box, one failover.  An active/passive scenario.  Both are 2005 SQL Enterprise.

I agree with you on everything you've stated, but what about WHEN/IF you get to the point that machine cannot handle it?  At lesat not handle it as promptly as someone asks you to make it handle it.

well first off I will say that I have not read the High Av stuff in about 2 years so I may be slightly out of date.

having said that this is (I think) still true:

if you need to have say 3 or more servers all be "Active - Active" and share the same data store you are looking at some kind of extenral disk system possibly a SAN and that costs $$$ to build and maintain.
then you have to have the servers all share access to that data store and not corrupt the data.
I think this can be done, is done for a few systems but in most cases it's overkill.

for example If I had to handle a high TP rate I might look at a way to deploy several servrs to handle that transaction load and then dump the data into the main server.

not a "cluster" just say 3 "New Order" servers each taking some orders and then the 2 server mirror set.

the new Order servers would each only have about 3-5 tables and each would be puged at least 1 time each day.
create the new orders with a Primary Key of a GUID.
then onn the main server you can append them all into one master file.
when a client returns to the system use sql federation to view all the seperate tables as if they were one.
(partitioned view)

something like that might get all the perf you need but with much less expensive hardware and software setup.

also -- MORE RAM !
SQL loves ram, eats it for breakast, lunch and dinner!
if you can have as much ram as the size of the database then go for it!

when you look at sql perf one of the #1 killers is having to hit the disk for the data it needs.

the other one is to use QUery Analyser and see when you have table scans and index scans...
what you want are index seeks 90% of the time
if you find any index scans thats slower but ok.
if you find a table scan -- Jump on it!

also there are some tweaks you can do for large volumes of inserts to cut way back on page locks and table locks.
they kill insert speed.

btw when I mention # of orders figure like this:
in about 1-2 hours 70% of the days orders go in the system I deal with. for each new order there are at leeast 3-5 inserts, often 50-100 for all the detail lines. and each of them has a sql transaction to keep it all good.
around 8K per day but that only counts one part of the system.
I have 2 other tracks that place otrher orders I have not counted yet.

keep looking though... at the least it's good to check out all the options.

figuerres wrote:


if you need to have say 3 or more servers all be "Active - Active" and share the same data store you are looking at some kind of extenral disk system possibly a SAN and that costs $$$ to build and maintain.
then you have to have the servers all share access to that data store and not corrupt the data.
I think this can be done, is done for a few systems but in most cases it's overkill.

Hey, shared disk, good idea.  I'm gonna have to dig up some info on that.  That sounds like not a bad idea.  I know of several other applications that cluster in this manner.

figuerres wrote:


for example If I had to handle a high TP rate I might look at a way to deploy several servrs to handle that transaction load and then dump the data into the main server.

The concept of splitting the ordering or the high volume processing tables away into another instance is not a bad way either.  I noticed a somewhat similar approach in an application a couple years ago.  That takes some planning.  But I guess all of this would.

figuerres wrote:


something like that might get all the perf you need but with much less expensive hardware and software setup.

I think this is an excellent point and a good reminder to be cost aware.

figuerres wrote:

also -- MORE RAM !
SQL loves ram, eats it for breakast, lunch and dinner!
if you can have as much ram as the size of the database then go for it!

Uh...ya.  This is the one I'm not doing right now.  Need to get on that.