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

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".