Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

ODBC Mirror Screen

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?

share|improve this question

migrated from stackoverflow.com Aug 24 '12 at 12:14

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer

up vote 3 down vote accepted

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]


share|improve this answer
1  
Thanks Max, it turned out that simply supplying the mirror server name in the ODBC client dialog (my screenshot in the question) allowed us to fail over correctly. The issue turned out to be not having user permission on the mirror server. Our database is mirrored but the user accounts are not. Maybe this can be solved by mirroring the system databases? –  Mike S. Oct 4 '12 at 14:39
    
You cannot mirror system databases. The issue of user accounts across instances is a problem solved in SQL Server 2012 through the use of so-called "Contained" databases. The user accounts, etc, can be stored in the actual database itself, negating the need to have Logins on the server itself for your users. When you mirror a "contained" database, you mirror the users automatically. –  Max Vernon Oct 4 '12 at 15:41
    
Ok so the only way to do this on 2008 R2 is to manually create and monitor the accounts on the mirror? –  Mike S. Oct 5 '12 at 18:41
    
Yes. We purchased Idera SQL Admin Toolset to copy logins (among many other uses) to do that. –  Max Vernon Oct 5 '12 at 20:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.