Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What exists today to do Microsoft SQL Server Load Balancing on Windows Server and being transparent to any programatic access. Is this built in or does a solution have to be purchased?

I'm trying to find out if there is a solution today that makes this very easy from the view point of the webservers or other database access to SQL Server.

share|improve this question

migrated from stackoverflow.com Nov 1 '11 at 2:49

This question came from our site for professional and enthusiast programmers.

add comment

5 Answers 5

up vote 17 down vote accepted

There is no "standard" load balancing set up for MS SQL Server that you can run via a wizard.

This would be a database architecture decision and implemented at the database level not the server level. Techniques would be:

  • Scaling out/federating the database servers
  • Partitioning
  • Offload your reporting requirements
  • Perhaps replication

If anyone disagrees, then I'd like to see an article by a respected known MS SQL figure saying clustering is load balancing. The articles quoted above do not mention load balancing. For example, A Microsoftie (Chas Boyd) says it is not here.

My question to the OP would be what kind of load do you expect?

Database servers are usually IO and memory bound, so proper disk configuration (with appropriate filegroups) and as much RAM as possible will go a lot further than any solution above.

Don't forget: SQL Server 2005/Windows 2003 Enterprise 32-bit goes to 32GB RAM (of which you'd have 26-28GB data cache) and you are not limited by drive letters because of NTFS mount points. As for x64...

share|improve this answer
3  
+1: I agree here and pretty much said that same thing but you beat me to the submit button :-) –  John Sansom Apr 17 '09 at 18:43
add comment

First I would like to clear a few things up if I may….

SQL Server Clustering is an availability technology that is built on top of Windows Clustering. It provides redundancy at the hardware level and has no relation to the technique known as load balancing, i.e. distribution of a processing load.

Further to this, Database Mirroring and Log Shipping are also technologies for primarily implementing availability of varying forms.

Now onto the original question…..

Unfortunately there is no ready to go “out of the box” solution for load balancing in SQL Server.

You can use SQL Server Replication Technologies to implement a distributed database environment that also factors in distribution of transaction processing load however, your application needs to be “aware” of the underlying architecture.

This approach requires development and customisation of a given application in order to deliver a service that is load balanced.

I hope what I have detailed is clear and makes sense but of course please feel free to fire your queries over to me directly.

share|improve this answer
add comment

Check out the Native SQL Load balancing solution from Cirtrix NetScaler or this one.

share|improve this answer
add comment

If you're running on a POSIX operating system you might want to consider memcached. It's not a total load balancing solution, but most DB load-balancing solutions include it to lessen the number of hits to the DBs.

Different DBMSs usually have different solutions. The authors of the DBMS often offer load balancing solutions.

share|improve this answer
1  
Thanks but I'm specifically asking about MS SQL Server on Windows Server. I edited my original question to make that more clear. Sorry about that :) –  Robert Kozak Apr 17 '09 at 17:59
    
Then my first recommendation is to get away from Windows Server :P –  Ben S Apr 17 '09 at 18:01
1  
Not helpful, really is it? if you knew SQL Server you'd know that is has a respectable data caching engine –  gbn Apr 17 '09 at 18:43
    
I answered before knowing he used MS SQL Server (before his edit). I then joked about ditching the Windows platform once I found out. I'm sorry if you took offence to that. –  Ben S Apr 17 '09 at 18:58
1  
I thought it was funny. The :P signaled to me that you were being cheeky. :) –  Robert Kozak Apr 17 '09 at 20:28
add comment

If you don't have a very high write database say 10/90 where only 10% of your transactions are write you can use sql 2005 and above peer-to-peer replication on top of a hardware load balancer to achive your needs. Nothing out of the box.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.