Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I want to run my 2(two) SQL Server 2008 R2 Enterprise Edition as if one server. If one server is too busy, system should direct the requests to other server. How can I do this? Thanks..

share|improve this question
    
What do you need to scale out? Reads? Writes? Both? Reads are far easier to scale out than writes. –  Jon Seigel Jun 3 '12 at 15:27
add comment

1 Answer

up vote 2 down vote accepted

SQL Server does not support scale out functionality out of the box. Doing so is actually quite complex and typically requires a lot of application redesign in order to be done correctly.

Are you sure that the database has been properly tuned so that everything within the database is running at peak efficiency? Tuning the database properly (if it isn't already) will be MUCH less expensive than scaling the applications workload across multiple physical servers. I had a client that thought their application was going to outgrow the database server that it was running on, as the company at the age of 3 years old was already running a 24 core SQL Server to 60%+ during the business day. With some schema changes, and a little bit of code change we were able to drop the CPU workload to about 5% on the exact same hardware.

There are a few different ways that you can scale the application out to multiple servers.

  1. Setup a single server to handle writes, then use SQL Server replication to feed the data to other servers for reads. You then put the read only servers behind a load ballancer so that the read only requests are directed to all the read only servers. This will require a lot of application review and design changes.

  2. Use peer to peer replication to make multiple servers writeable. You then put a load balancer in front of the SQL Servers to spread the workload across the servers. This may require a lot of application review and redesign depending on the current table layouts and how identify values are used and how data is inserted into the database.

  3. Setup views on multiple servers that point to a local copy of the table as well as a remote copy of the table on one of more servers. Constraints are put into place on the physical tables which specifies which portions of the data will exist on which server. Each server holds only a portion of the database. The views are put onto every server so that any user can connect to any server. This requires a LOT of data movement and very careful planning to ensure that the schema rarely changes as schema changes must be done very carefully.

No matter which route you go a LOT of planning needs to be put into place to ensure that the correct option has been selected. I would highly recommend working with a consultant that is experienced in dealing with these sorts of configurations. The up side is that there are a few people out there who have dealt with this sort of thing before, the down side is that there are not very many of them and they are going to be very expensive. If you do decide to go down this route, hit my website and I'll put you in contact with a few consultants (including myself) so that you can be sure that you are getting the right person for the job.

For options 1 and 3 high availability through something like Windows Clustering becomes very important very quickly. If the writeable server in option one goes offline then the entire application goes offline. If any server in option 3 goes offline then the entire application goes offline. With option 2 it's a little more capable of handling a server go down, but if the application is generating this much load you'll need to plan for N+1 nodes for option 2.

When selecting the option to go with you also need to plan ahead for what the workload will be doing in the future as it continues to grow as you need to make sure that you've selected an option which will work in the long term moving forward.

share|improve this answer
    
thanks. So, how could we achive "load balancing" ? Could you please explain this operation step by step or direct me a blog or document? –  user741319 May 30 '12 at 11:13
    
There isn't going to be a blog or document. Basically you turn your current server into the SQL Replication publisher. Then you use transaction replication to several subscribers (you can start with 1 if needed). Put a load ballancer in front of the subscribers. Then modify the application so that all queries which are SELECT statements only connect via the load ballancer's IP. The queries which require INSERT/UPDATE/DELETE need to connect to the current machine. Again you'll want to make very sure that you can't fix this via normal index tuning / schema change first. –  mrdenny May 30 '12 at 19:35
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.