Parallel Processing in SQL Server 2008 R2

Question

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..

Answer 1

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.