How to handle Database Failover from your Application

By | October 11, 2010

There are many ways that you can let your application handle failover situations and here are some suggestions I had used in my past projects.  But before you start with my suggestions make sure that you have the same database names for both and the same goes for user accounts and permissions.  The items here are ordered on how highly I suggest each solution.

1. Modify your connection strings to do Failover

This is the best practice method and it is just as easy as modifying your connection string to handle the failover (yes you can do that),  Because if you connect with ADO.Net or SQL Native Client your application can take advantage of the drivers ability to handle the scenario and redirect the connection when a failover occurs.  To do that you need to use this connection string

Data Source=SQLServerPrimary;Failover Partner=SQLServerMirror;Initial Catalog=DataBase;Integrated Security=True;

Where Data Source is your Primary SQL and Failover Partner will be your SQL Mirror. The above example is for ADO.Net for others use:

OLE DB: FailoverPartner
ODBC: Failover_Partner
JDBC: failoverPartner


If item 1 does not meet your requirement or its imposible to do the next best thing is using this method, you just need to change your config files to point to the assigned CNAME (and thats all you have to do unless there are some hard coded server names), the change of CNAME can be done automatically or manually and its network admins your choice which option they want to do.  What this does your network guys assigns an alias for your Database Server so for example you have a database name called DBServer1which is the primary and DBServer2 which is the mirror, they can create an alias called DBServerDNS which points to DBServer1 then in a case of failover the entry will be then changed to DBServer2 which you dont need to worry as your application connects to DBServerDNS all the time.

3. Manual Modifications

Finally, if both of above will not work in your situation then this is your last resort, a manual approach (not really recommended) as this cant happen on real time.  Now once the database is running on another server you can either:

a. Create a SQL Server alias just go to SQL Server Configuration Manager and add a SQL Server Alias setting.  With this you dont need to modify any connection strings on your application and when the real server goes back to life just remove the alias setting.

b. Change your config files

Final Note:

Depending on how your applications are written using the solutions above (item 1 and 2) doesn’t mean that your application will not crash as that the network connection to the database server will be lost during a failover and if the exception is not properly handled then issues will arise.  So the best way to do this is to Dispose any commands you are running on the failed SQL Server and Close all the connections so you can reconnect properly on the Mirror Server on the next Execute Command.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.