locked
SQL Server 2008 mirroring problem when principal is unavailable RRS feed

  • Question

  • 0

    I have 3 SQL server 2008 servers in a mirrored setup (principal, mirror & witness), using high availability with automatic failover. I'm using Visual Studio 2008 (vb .net) to connect to the server using the following connection string:

    Data Source=TestSvr1; Failover Partner=TestSvr2; Initial Catalog=TestDB;Uid=TempUser;Pwd=TempPassword

    In code I'm using the System.Data.SqlClient namespace with the SqlConnection class.

    If I have everything up and running, with the mirror synchronized, I can connect to the principal server with no problems. If I do a failover, so that the mirror now becomes the principal and the principal now becomes the mirror, I still can connect with no problems.

    However, if the principal server becomes unavailable, due to shutting down, failed network connection, etc, the mirror does become the principal but I can no longer connect to the principal with the connection string using the failover partner. If I swap the datasource and failover partner in the connection string, I connect to the new principal server immediately.

    There was a Microsoft Support article regarding almost this same issue for SQL Server 2005 (see below):

    Article ID: 912151 - Last Review: December 3, 2007 - Revision: 2.4 FIX: The connection may time out before the first try to connect to the failover partner server when you try to connect to a SQL Server 2005 mirrored database by using an ADO.NET 2.0-based application

    The hotfix mentioned will not install on my client (XP Pro SP3, .NET framework 2.0 SP2, 3.0 SP2 and 3.5 SP1 all installed).

    The failover is supposed to be automatic in this setup and it IS, unless the principal server becomes unavailable.

    Has anyone out there come across this problem? How did you resolve the issue?

    Thanks in advance, Harry

    Tuesday, August 3, 2010 3:50 PM

All replies

  • Hi Harry,

    There is less information about this issue in the web.

    However, I recommend that you check the "Application Retry Logic" section in the following link:

    http://msdn.microsoft.com/en-us/library/cc917713.aspx

    • Proposed as answer by Tom Li - MSFT Monday, August 9, 2010 5:48 AM
    Friday, August 6, 2010 11:20 AM
  • Hi, I have the similar issues with MSSQL 2005 servers. Configuration is the same - 3 servers with witness.

    First, I take a PC with .net framework 2.0 installed without any SP, and install 912151 (actually it shows me 916002). In testing I got the same results - connection seems to be timed out. SO 912151 has no use.

    But I found out how to escape of this errors! First, you have to use TCP as network protocol. Add "Network Library=dbmssocn;" to the connection string or/and disable named pipes in servers configuration.

    Second, add "Connect Timeout=0;" to set indefinite connection timeout. Disconnect principal from the lan, check if mirror take his role. Then test. The SQLConnection.open() method should wait some time and return success. If this is not true for you - then, probably you have other kind of error. Set timeout value to nonzero. For me it seems to work with values greater than 20-25.

    And, of cource you should retry connection attempts as connection brokes when failover occurs.

    Hope this will help

     

     

    Thursday, October 21, 2010 10:08 AM