Setting up a backup DB server in ASP.NET web.config file

Question

I currently have an asp.net website hosted on two web servers that sit behind a Cisco load balancer. The two web servers reference a single MSSQL database server.

Since this database server is a single point of failure, I'm adding an additional MSSQL server for backup. I would like to setup my web.config files to write everything to both MSSQL servers, but only read from the "primary" database server unless it is unreachable for some reason, at which point the backup MSSQL server would be used.

Is this possible via a web.config file setting, or must this be done in code? Thanks in advance for any help.

New Information: I just wanted to add further information on this topic after researching it for the past several days - Microsoft TechNet has a good article title "Implementing Application Failover with Database Mirroring" (http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx#EMD).

This specifically covers the database mirroring feature in Microsoft SQL Server 2005 and the new new "Failover Partner" connection string keyword that allows you to specify two server/db instances in a single connection string.

The article is well worth a read if your interested in implementing this type of feature.

Answer 1

Its a bit more complex than that. Does your webpage write to the databases? Or just read?

If they write, then you'll have to worry about keeping the 2 databases synchronized, probably using mirroring or log shipping.

But what you are (in essense) talking about doing is setting up a SQL cluster.

Answer 2

What you want is called "failover", where if one database fails your queries are automatically redirected to the other. This is acheived at the database level, not the application. There are a lot of walkthroughs etc for setting up failover clusters: here's one for SQL 2000, and another for SQL 2005. Basically, once you set it up, the primary database communicates all activity to the secondary one. If the primary fails, the secondary is (almost) up to date and takes over.

The servers form a cluster, and look like a single unit - similar to the way your load-balanced web servers look to the outside world. The backup monitors the primary, and if the primary stops responding, the backup takes over receiving queries. If you're Googling, try also looking adding the keywords "database mirroring" and "quorum".

Answer 3

I've written a blog entry that shows how to setup MSSQL Database mirroring as well as how to actually utilize it from a managed code perspective: http://www.improve.dk/blog/2008/03/23/sql-server-mirroring-a-practical-approach

Answer 4

Nice answer from "Rick" but I just wanted to add my 2 cents of information. Normally for a setup with failover without a lot of expensive equipment, I would set it up like that:

You can have your 2 SQL Server box waiting for request and have a third box low-end system with SQL Server 2005 Express as a "health monitoring". What that saves you is 10K$ for the box and one SQL Server licence. SQL Server Express (as in Free) can do the health monitoring between the 2 databases servers without any issues.

That is my setup :)