Click here to Skip to main content
12,348,728 members (28,974 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

17.4K views
5 bookmarked
Posted

How to handle Database Failover from your Application

, 10 Oct 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Some suggestions on how to let your application handle failover situations

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.


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/

You may also be interested in...

Comments and Discussions

 
QuestionQuestion with postgres Pin
Member 100964896-Jun-13 6:51
memberMember 100964896-Jun-13 6:51 
GeneralAlternative idea Pin
Tim Corey5-Jan-11 7:02
memberTim Corey5-Jan-11 7:02 
GeneralRe: Alternative idea Pin
Raymund Macaalay5-Jan-11 16:03
memberRaymund Macaalay5-Jan-11 16:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160621.1 | Last Updated 11 Oct 2010
Article Copyright 2010 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid