Best way to scale up an IIS/SQL Server application

Question

We have an application that we developed in ASP and SQL Server. We use Rackspace to host it. Each of our "customers" has their own IIS site and SQL database. Each customer could have a dozen or several hundred users hitting the application.

We now have several hundred customers. What we've done up until now is add another pair of servers (one IIS, one SQL) when we need to for performance reasons. We're up to four pairs of servers now.

We're continuing to add new customers, and we're looking at alternative ways to scale up without simply adding pairs of servers.

One approach we're considering is to have a web farm for the IIS side, and a SQL Server cluster and SAN for the database side.

Is this a good approach? We have maybe 400 customers now, each with their own IIS site and SQL database. What if we grow to 1,000? 5,000? Can one SQL cluster handle many thousands of databases?

Each database has several hundred tables. The main table for some of our bigger customers might have several hundred thousand records.

One attraction towards moving towards a big IIS web farm and a big SQL cluster is that if one server goes down, other servers could carry the load. Right now if one of our SQL Servers goes down, or one of our IIS servers goes down, one fourth of our customers cannot use the system. So we want to increase our reliability as well as increasing our capacity for adding new customers.

Answer 1

A SQL Server cluster is not a scalability solution, is strictly a high-availability solution. In a SQL Server cluster only one node is active and handles load, all the other nodes are just passive stand-by, waiting in case the active node has a hardware failure. As such froma SQL Server cluster you obtain, at best, the performance of a single SQL Server instance. You'll find sometimes references to a so called 'Active-Active' deployment, but that is not a SQL Server cluster. The so called 'Active-Active' is two separate clusters that use each other's node in reverse roles (one cluster in active on node A and passive on B, the other is active B and passive on A).

If your application is already partitioned and each customer has a separate database, then you'd be much better continuing to scale out. The problems of administration and maintenance can be addressed, once you automate the administration of 4 servers (ie. now) you can automate the administration of 1000 servers. So are the problem of versionning, deployment and account provisioning.

While right now you may be attracted by a perceived ease of administration, troubleshoot and tunning from a scale up solution, you are going to have new problems, more difficult to solve, culminating with a limit on how high can you scale up.

Updated After you edited the OP with explanation on concerns related to scale out.

Indeed scale up and clustering offer a high availability solution. There are basically two HA solutions for SQL Server: clustering and database mirroring. Since you plan for thousands of individual databases, this pretty much rules out mirroring. What you have to keep in mind though is that SQL Server clusters are supported only on a restricted list of hardware combinations:

Microsoft server clusters are only supported on cluster solutions that are listed in the Windows Server Catalog under Cluster Solutions. To view the Windows Server Catalog, visit the following Microsoft Web site: http://www.windowsservercatalog.com/

That doesn't mean you cannot run a cluster on any hardware pair/group that has a shared SCSI buss. It means you won't be able to request support from Microsoft CSS if you have an issue on a hardware that is not approved.

SQL Server clustering offers protection against hardware failures, except media failure (since the media is shared between nodes). What it doesn't offer protection from is human administration errors, which unfortunately are the primary reason for down-time. With one thousand customer databases on a single cluster, you'll have a very large omelette cooking in one basket...

As for you question about how many databases can a cluster run, ultimately a cluster has only one active instance at any time, so the limits of a single instance apply to a cluster as well. Having one thousand databases attached is not a big thing, the real question is the load, how many users will be running queries simultaneously. To get a ballpark region of numbers, consider that the TPC-C benchmarks published for SQL Server are for some 1.2 mil transactions per minute on a 64 way Superdome, which means roughly 16k transactions per second. You anticipate about 5k clients connected, that gives you a margin of 3 queries per client per second, and to reach that you need a pretty beeffy hardware and an exceptionally well tunned application.

Answer 2

I agree with previous posters. It definately sounds like you are in a situation where you need to start thinking more about scaling. But if I had the environment it sounds like you have now, I would be spending more of my current time working on automating things and ensuring that I have a solid birds eye view on my environment. No matter how you choose to scale out, if you aren't scripting and automating your processes, you are going to run into an administrative nightmare.

In terms of an actual scale strategy, I am thinking a possible setup for you might look something like the following;

1) all customers web files are on all web servers

2) all customers web sites configured in IIS on all server

3) changes to any customers web files are synced to all web servers

4) the following web config

          - - - - - - - - - - - - - -
         |      Load Balancer       |
          - - - - - - - - - - - - - -
                        |
                        |
 - - - -    - - - -     - - - -     - - - -
| web |   | web |   | web |   | web |
 - - - -    - - - -     - - - -     - - - -
    |            |             |            |
          - - - - - - - - - - - - - -
         |      SQL Cluster          |
          - - - - - - - - - - - - - -
                      |
                      |
          - - - - - - - - - - - - - -
         |    Warm SQL Cluster    |
          - - - - - - - - - - - - - -

Then, once I were to started seeing limits on my SQL Cluster, I would create a new SQL Cluster, split my databases among my 2 clusters and change the customers connection strings so they point to their respective clusters.

The idea here is to let the load balancer do what it does best and distribute the traffic among the web tier. As traffic increases, you could always throw a new web server into the rotation to balance out a bit. Keeping all the web servers in sync is the key here.

Also, we are letting the database server(s) do what they do best and once they start getting bogged down, we would add a new server\cluster into the mix to balance out the work. The warm SQL Server give you a degree of redundancy, though there might be some disagreement with me here as to whether or not it would really be needed.

There was recently an article about Foursquare and how they balance their loads. They use an approach where they basically distribute their users across partitions, which in MongoDB are separate servers. This worked pretty well for them until a group of users, who all happen to be heavy users and on the same partition (shard) brought down the shard by making it hit its limits. There was a bit more to it of course, but the jist was;distribute across machines and monitor usage so you know when to split and rebalance the load again.

anyway.... that is my 2 cents.

Answer 3

You might want to consider a cloud solution like Amazon Web Services' EC2 and the complementary database and storage services. It's very easy to scale and it's fully programmable. You could build a custom dashboard to manage all your servers and scale up as you need to. You only pay for what you use and they support Windows and Linux platforms including IIS. You could build your own custom images if you need to scale out to multiple servers or provide geographic redundancy by hosting servers on the east and west coast or even Europe. Rackspace has a similar solution, but I am not sure if they released Windows support yet - they may have. I tried both and found Amazon to be more flexible and comprehensive, but since you are already with Rackspace, it might make more sense. Good luck!