Imagine that you have a successful web application that uses ASP.NET and IIS 7. It generates many calls to a SQL Server 2008 database, and is expected to be available to the public with 99.9% uptime (downtime of 8 hours, 45 minutes per year).
Our goals are:
- Install Windows Updates on the server without causing downtime to our customers
- Prevent hardware crashes on the server from slowing down ASP.NET applications because of timeouts
Unlike load-balancing an ASP.NET application, load-balancing SQL seems to be much more difficult. What are your best practices for setting up a simple load-balanced SQL Server 2008 R2 cluster for a Micro-ISV that uses the Microsoft stack?
If it's high availability you need, then Windows/SQL Server Clustering or SQL Server Database Mirroring offer solutions. Clustering does require a lot of planning and familiarisation if you've never done it before, but it will be transparent to the application.
Load balancing is possible with SQL Server, but it's not for the faint hearted. It's a solution that uses Windows Network Load Balancing (NLB) in front of the SQL Servers. The SQL Servers themselves in the NLB are easier to manage if they are read-only, but they can be read-write if you use transactional replication with updateable subscribers. This type of replication is marked for deprecation in a future version though.
One final possibility is Scalable Shared Databases, but they are definitely read-only.
More reading:
Have a look at Allan Hirt's Apress books on SQL Server 2005 High Availability, and Pro SQL Server 2005/2008 Replication from Apress.
Scalable shared databases: http://technet.microsoft.com/en-us/library/ms345392.aspx
Relational database systems are rarely load balanced the same way that web servers are. The problem with the classic approach to load balancing is that all of your databases need to be in constant synchronization. The relational model is worthless if two servers don't have identical state at any point in time.
From your question, it doesn't look like you're even trying to accomplish load balancing, which is primarily a performance measure to ensure that only as many users are hitting each server as that server can handle. It sounds like you want a high availability setup. Since you say that you are using SQL Server, I would look into failovers. This means that, if the primary database is unavailable, clients will attempt to access the failover servers. SQL Server handles keeping the primary instance and each failover in sync and also handles resynchronizing the primary instance to the failover when the primary comes back online from being offline.
Ok, here we go. CAN NOT BE DONE. Not without app changes.
Realize you still need to take the app down for maintenance when you deploy a new copy / make db schema changes.
A cost effective answer here is quite often to buffer the database with a cheap commodity balanced server tier hosting application services that provide logic processing and data storage that would otherwise be tying up database resources. Obviously this requires some thought as to data volatility and caching strategies.
Let me give you a jurassic answer. If your goals include "install Windows updates" you're beyond rescue.
I've got gray in my beard, and can remember a time when an application could run 10 years without having to suffer "operating system updates". The useful lifetime of an app was measured in decades, not years.
So my advice is this: establish a working version of your SQL Server database + application, and ISOLATE THE DATABASE SERVER FROM MICROSOFT UPDATES. If it isn't broken, don't fix it.
Use hot-swappable RAID disks.
Have a mirror image database server (per TomTom's suggestion) at hand in case your hardware craps out.