SQL Server ODBC Mirror/Failover Partner

Question

Background/Context

We are in the process of moving our SQL Server 2000 databases to a mirrored SQL Server 2008 R2 server with a witness and automatic failover. We have both .Net and Access applications running off of these databases, and are testing client redirection on a server failover event.

The .Net apps respond correctly after I added a "Failover Partner" to the connection string. On a failover the app will fail on the next DB request, then move to the mirror server when we restart the app.

Question

For the Access applications going through ODBC we are trying to use the SQL Server Native Client 10.0 driver (it looks like the standard driver doesn't support mirroring?) and supplying a mirror server in the settings.

I'm specifying the database and the failover server name here. On a failover event the Access application does not seem to be redirected to the mirror server even after an application restart. Is there anything else I should be doing here to force ODBC to switch to the mirror server, or am I not specifying the mirror server name correctly?

Answer 1

I maintain an Access 2003 application that connects to a pair of SQL Server 2005 servers setup with mirroring. I use the following connection string which allows the application to connect to the mirror after an application restart:

Dim ws As Workspace
Dim db As Database
Dim a As Long

Set ws = DBEngine.CreateWorkspace("MyODBCWorkspace", "admin", "", dbUseJet)
Set db = ws.OpenDatabase("C:\[pathtodatabase]", True, False, "MS Access;")
For a = 0 To db.TableDefs.Count - 1
    Me.ProgressBar1.Value = a
    DoEvents
    If db.TableDefs(a).Connect  "" Then
        db.TableDefs(a).Attributes = TableDefAttributeEnum.dbAttachSavePWD
        db.TableDefs(a).Connect = "ODBC;DRIVER=SQL Native Client;SERVER=[PrimaryServerName];Trusted_Connection=Yes;APP=[MyAppName];WSID=[MyWorkstationName];DATABASE=[SQLServerDatabase];Failover_Partner=[MirrorSQLServerName];"
        db.TableDefs(a).RefreshLink
    End If
Next a
db.Close
ws.Close
Set db = Nothing
Set ws = Nothing

I use this code in a VB launcher app I created that the user double-clicks to launch the Access MDB/MDE.

Alternatively, this is the content of a DSN file we sometimes use to accomplish the same thing:

[ODBC]
DRIVER=SQL Native Client
APP=Microsoft Data Access Components
Trusted_Connection=Yes
SERVER=[ServerFullyQualifiedDomainName]
FAILOVER_PARTNER=[MirrorFullyQualifiedDomainName]
DATABASE=[DatabaseName]