How to handle Database Failover from your Application

There are many ways in which 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

2. DNS CNAME

If item 1 does not meet your requirement or it's impossible 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 that's 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 is your network guys assign 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 be changed to DBServer2, which you don't need to worry as your application connects to DBServerDNS all the time.

3. Manual Modifications

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

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

   

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