12

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?

4
  • A few comments: What we've done is written ASP.NET applications that can work in "offline" mode without connecting to the database when the connection times out. It degrades to a "basic" service instead of the fully-featured app. I've also read about the Netflix Chaos Monkey approach which is inspirational: readwriteweb.com/cloud/2010/12/…
    – Yakov-MIK
    May 7, 2011 at 6:34
  • What's your circumstance? Are you in a hosted environment with limited servers or are you hosting your own stuff with access to cash and the abiliy to expand your server numbers?
    – Jay
    May 7, 2011 at 8:17
  • @Jay: Like many Micro-ISVs, we have two dedicated servers which are being transitioned to Rackspace Cloud hosting. We could afford two dedicated SQL Server boxes if we needed to, but I'm wondering if there's a better approach.
    – Yakov-MIK
    May 7, 2011 at 16:36
  • Move your database into SQL Azure. You are then freed of the nightmares of server failures, procurements and updates. Your data is duplicated twice in addition to yuor working copy and the load balancing aspect is also handled for you at a fraction of the real cost of implementing/managing a SQL Server, e.g. hardware, software licencing, server-support time, etc etc etc. Aug 23, 2012 at 13:38

5 Answers 5

4

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

2

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.

0

Ok, here we go. CAN NOT BE DONE. Not without app changes.

  • Install updates- use either clustering or mirroring to make sure you have TWO databases that work or can fail over to another computer faster. Mirroring strongly preferred.
  • Rewrite application to handle that (i.e. connections failing, needs to reconnect transparently so it then connects to a copy).

Realize you still need to take the app down for maintenance when you deploy a new copy / make db schema changes.

0

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.

-1

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.

1
  • 3
    Tim, that's like saying why even use a database when you can store results in an Excel file? There are numerous vulnerabilities in the OS, and not installing regular updates / keeping anti-virus / firewall rules up-to-date is poor advice. Not advisable on any level.
    – Yakov-MIK
    May 8, 2011 at 1:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .