locked
Db fail over in Mirrored environment causing connection attempts from web app to fail until the application pool is restarted RRS feed

  • Question


  • I have a mirrored sql server db environment. A load balanced application hosted in IIS is accessing the databases. In Connection string I have mentioned FailOver partner. When I am performing fail over from db1 to db2 the application fails to connect.

    The error that I received is:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at MyMethodName() at MyMethod() System.ComponentModel.Win32Exception (0x80004005): The network path was not found System.Data.SqlClient.SqlException (0x80131904): The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The network path was not found.
    Once I restart the application pool the application will be able to connect again to the db. My connection string would look like below.

    <add name="SqlString" connectionString="Data Source=12.34.56.79,9100;Failover Partner=12.34.56.78,9100;Initial Catalog=test;Persist Security Info=True;User ID=####;Password=#####" />

    I am using Sql server 2012, IIS 8.5, Windows Server 2012

    Any help on this will be appreciated
    Tuesday, December 13, 2016 10:29 AM

All replies

  • I would look into the way the connections are opened and managed. Usually, connections are pooled, which means that there is a fixed number of connections that are physically opened and saved in a pool. When the application opens a connection, it gets an already open connection from the pool and when it closes the connection, it does nothing but returning it to the pool.

    I suspect that the pooling mechanism here might be interfering with mirroring client redirection.

    Tuesday, December 13, 2016 3:21 PM
  • You might need to add the parameter

    Network Library=DBMSSOCN;

    Your connection string is using named pipes and can't seem to make the connection to the other server.

    Tuesday, December 13, 2016 3:30 PM
  • Hi spaghettidba,

    Thanks for your reply.

    I am not using connection pooling. I am creating SqlConnections on the fly.

    Br

    Ajith

    Tuesday, December 13, 2016 8:55 PM
  • Hi Hilary Cotter,

    Thank you for the answer. I will try this.

    But I was thinking it was able to connect to the db1 with out this change. So it should be able to connect to the other node as well. Is there anything specific when we have fail over partner also specified.

    Wednesday, December 14, 2016 10:27 AM
  • Hi Hilary Cotter,,

    I tried adding Network Library=DBMSSOCN; to connectionstring, but that hasn't fixed the issue.

    Br


    Friday, December 16, 2016 12:15 AM