Connection string to mirrored SQL2008 R2 server not working

Posted on 2011-04-29
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
    LVL 12

    Accepted Solution

    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

    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

    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

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

    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now