Connection string to mirrored SQL2008 R2 server not working

Posted on 2011-04-29
Medium Priority
Last Modified: 2012-06-27
I have 3 servers, principal, mirrored, and witness. Once I add the failover partner line to my connection string, and shutdown the principle sql server newly created records fail to locate the mirrored server. I have tried all 3 ways of editing the Failover Partner syntax to include an underline (Failover_Partner), a space (Failover Partner), and as one word (FailoverPartner). None of these work. Here is my connection string which works until I add Failover Partner. Any Ideas.

Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;PersistSecurity Info=True;User
ID=logon;PASSWORD=password;Initial Catalog=PCUser;Data Source=server\instance;Failover Partner=server\instance;Network=dbmssocn"
objConnection.ConnectionTimeout = 0
Question by:vhabalnortol
  • 4
LVL 12

Accepted Solution

Chris M earned 2000 total points
ID: 35496581
Please ensure that you specify the database server name (where you have Data Source = server\instance) as the name of the mirror.

Contact your DBA to tell you the name of the database mirror.
One mistake application guys make is connecting to the mirror as if it's a local instance but the way SQL server mirroring works is that the names of the servers that host the mirror will not be used while  connecting to the database. So use the mirror name and you will be able to connect.


Author Comment

ID: 35504642
Hi, I 'm a bit confused.

I have the Data Source = PrincipalServer\instance1 & the failover partner as MirrorServer\instance2.

When I failover the principal server, the mirror server becomes the principal server. When both servers are on-line I can access the principle server, but the mirror server which is in a state of (Mirror, synchronzied/restoring ) will not let me access its database until the principal fails over and makes the mirror the principle. I think that if I were to make the datasource the mirror server, that my connection string probably would not be able to pass data to the mirror. Are you suggesting that I need to make the Data source the mirror server, and the failover partner the principle server? I have a different instance name for the mirror, and principle; does that matter? The mirror server that I have set up seems to be working fine it's just when I try to send data externally through my connection string that it will not add records to the mirror server after it takes the role of principle, after the original principle fails over. Hope I ddn't confuse you.

Assisted Solution

vhabalnortol earned 0 total points
ID: 35689177
Thanks Chris, but I figured out that the problem was a permission issue with the database preventing access to the mirror server. Thanks for your response.

Author Comment

ID: 35689202
The error had to do with login permissions on the mirror database

Author Closing Comment

ID: 35718358
The error had to do with login permissions on the mirror database

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question