Best way to improve performance (and include somehow failover)

Question

We have an application running where IIS and SQL are on the same machine. It's a windows2003standard server, with 4gigs of RAM running on a VM.

Now the numbers of users are rising constantly. There are some huge statistics also, which can be run by the users but has very much impact of the performance for other users. So we need to improve the performance somehow.

I thought of separating IIS and SQL on 2 different machines with windows2008 64bit and at least 6gigs RAM for each machine, but it also should have a failover solution.

Can you recommend some scenarios for how to solve the performance and the failover issue?

Thanks

ps:

Just for info: we are now using inproc state management in IIS, but i think it will be better to change to sqlstatemanagement.

EDIT

I've broadened the question to the point of failover. As our client doesn't want to spend too much money on server and SQL licenses. Would it be "ok" to just have a replication to a second SQL server and use this as a failover? Do you know some better "cheap" solutions?

The application is just for internal use, but now more and more departments are involved in this project.

Answer 1

SQL Server always works best if it is the "ONLY" thing running on a machine. You will have a quick, easy and good benefit from just doing that. It seems to like to take control of everything and is always happier when it can :)

Answer 2

Right now you have 32 bit OS on the VM I assume. Since Standard Edition does not allow AWE the two servers (IIS and SQL) the SQL Server will load up the maximum it can about 1.8 GB and leave plenty of RAM to IIS and OS. But once you move to 64 bit OS things will change as the SQL Server will take all the RAM for its buffer pool (~5GB if 6GB available) and then start giving it back to OS when notified. This behavior can be tweaked by configuring SQL Server memory options. By splitting the IIS and SQL onto separate VMs you leave all the memory on the SQL VM for its buffer pools, and that is good. Ideally you should have enough RAM so that SQL can load the entire database(s) into memory (including tempdb) and only touch the disk for log writes and when it has to checkpoint the database(s). In other words, more RAM means faster SQL. It is by far the most important hardware resource SQL requires for performance and will give the biggest bang for the buck.

Now back to the broad question on 'failover'. In SQL Server the solutions for high availability split into two categories: automatic and manual. For automatic failover you really have only a few solutions:

• Clustering. Traditionally this is rather expensive to implement because of the high cost of hardware that supports clustering, but with VMs this is a different story. Standard Edition SQL supports two node clusters. Clustering is a bit hard to deploy, but is quite easy operate and requires no application changes to support. With clustering the unit of failover is an entire SQL Server instance (ie. every database, including master/tempdb/model and msdb, all logins, all SQL Agent jobs etc etc). A cluster is not a performance solution, as the stand-by server is just sitting idle in case the main one crashes. You can leverage the stand-by VM by deploying the so called 'active-active' cluster. What that mean is that you deploy two clusters, one active on VM1 and stand-by on VM2, the other active on VM2 and stand-by on VM1. In case of failover one of the VMs will have to take the load of both instances, and this is why active-active deployments are sometimes frowned upon. Given that you plan to deploy on VMs not on (expensive) metal, I'd recommend against it since there is no huge cost to 'ammortize'.
• Mirroring. This will keep a hot stand-by mirror of your database (not instance). Mirroring is preferred to clustering because of lower cost to deploy (no special hardware), lower failover time (seconds as opposed to minutes in clustering) and geodistribution capabilities (mirroring supports distribution of nodes on separate continets, clustering only supports a short distance of few hundred meters between nodes). But because the unit of failover is a database, mirroring does not provide the ease of use of clustering. A lot of the resources needed by an application do not reside in the database: logins, Agent jobs, maintenance plans, database mail messages and so on and so forth. Because only the database fails over, the failover has to be carefully planned so that the application continues to work after the failover (eg. logins have to transferred). The application also has to be aware of the mirroring deployment so that it connects properly. With Standard Edition you will only be able to deploy mirroring in high-safety mode.
• Hardware mirroring. I'm not going to enter into details on this one, it requires specialized SAN hardware capable of disk level mirroring.

If you're considering manual failover solutions then there are couple more alternatives:

• Log Shipping. Log shipping is basically out-of-band mirroring. Instead of transferring log records in real-time over a dedicated TCP connection, the log is transferred via file copy operations. There are very few reasons to choose log shipping over mirroring: the stand-by database can be queried for reporting, the stand-by can be located on a location with sporadic connectivity, the stand-by can be housed by a really low powered machine.

• Replication. This is really not a high availability solution. Replication is a solution to provide data copies and/or to exchange data updates between sites. While it can be used to deploy some sort of high-availability make-shift 'solution', there are many problems with that and basically no advantage. Compared to both log shipping and mirroring, it has a number of additional disadvantages because the unit of failover is not even a database, is only slices of data within a database (some of the tables). Metadata like user and security permissions are not failed over, schema changes have to be done in a replication aware mode and some changes cannot even be replicated. By contract both mirroring and log shipping provide stand-by copy identical with the production database that automatically cover any change done to the database.

You mention that you are concerned about license costs: you actually don't need a license for any of the passive servers with any of these technologies except replication. The stand-by servers require a license only if they become active and runs the database for more than 30 days.

Considering you plan to deploy on VMs my choice would be clustering. If you would deploy on metal, I'd recommend mirroing instead because of the cost of clustering hardware.