I have an application which uses file DSNs to connect to a central database server. This database server is mirrored onto a secondary server, and in the event of a failover, it is my understanding that adding a line in the file DSN "Failover_Partner=" will allow the application to automatically connect to the secondary.
However, this does not appear to work. Below are the contents of my DSNs on the application servers.
[ODBC]
DRIVER=SQL Server Native Client 10.0
UID="username"
failover_partner="secondary server"
Network=DBMSSOCN
DATABASE="database name"
APP=Microsoft Open Database Connectivity
SERVER="primary server"
I have tested failing over the databases, and some of our applications which use connections strings with a failover partner specified in their app.config or web.config files are fine. They automatically pick up the secondary server and continue working. They use the same databases and same usernames, so I know it isn't a permissions issue.
The applications that continue to work are on the same subnet as the ones that don't so I know it isn't a network issue.
I can connect to the relevant databases manually in SSMS with the right usernames / passwords.
I am at a loss here. I can't find any reason why this shouldn't work. I can only assume there is something wrong with the DSN, but I don't know what it is, and it only seems to fail when the failover partner needs to be used. When I fire up the app and the primary is available, its fine.
