Connection string to mirrored SQL2008 R2 server not working

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
objConnection.Open
vhabalnortolAsked:
Who is Participating?
 
Chris MConnect With a Mentor Consulting - Technology ServicesCommented:
Hi,
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.

Regards,
Chris.
0
 
vhabalnortolAuthor Commented:
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.
0
 
vhabalnortolConnect With a Mentor Author Commented:
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.
0
 
vhabalnortolAuthor Commented:
The error had to do with login permissions on the mirror database
0
 
vhabalnortolAuthor Commented:
The error had to do with login permissions on the mirror database
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.