SQL Server 2005 introduced database mirroring, an excellent way of increasing availability of the database by providing (for the sake of argument) an exact copy elsewhere, along with ability for the SQL Server to automatically failover, making the mirror copy the new principle, its also able with ADO.NET 2.0 to redirect to the new server - this will require you to change both your connection string, and each call to the database.
Typically, when issuing statements against the database, you would capture the error and handle it gracefully, presenting the user with an "oops" page. However to accommodate SQL Server Database Mirroring its necessary (and prudent) to retry that last transaction a few times. But how many times is a few?
When SQL Server fails over to the mirror, it doesn't occur instantly, depending on how busy the principle was at the time of failure and whether you have transaction-safety enabled or not, will affect how long the process takes. Which alter in different environments. Because of this, you should determine how long a failover takes and have your code retry the transaction at set intervals, the product of how many retries and the delay between each, should exceed the time it takes to failover. With this, the end use should have an error free experience during potential outage.
Pseudo-Code of Events
START:
TRY (SEND SQL STATEMENT)
ON SUCESS END
ELSE
IF ABORTFLAG != 1 DELAY 1 SECOND GOTO START
IF ABORTFLAG = 1 END
C# Demonstration
sqlmtest.sql
sqlmtest.cs
This simple console application attempts to insert a GUID, DATETIME and @@SERVERNAME into a pre-existing table, it demonstrates survival during a failure by automatically switching its connection to the partner, and continuing its transactions.
You will need both files, sqlmtest.sql shoul be run beforehand on your Test database on the principle, and sqlmtest.cs should be compiled to a console application and executed with the correct parameters.