locked
SQL Server Agent fails to connect to DB after enabling mirror on failover cluster RRS feed

  • Question

  • Hello:

    We have multiple databases running in a Failover Cluster instance: SQL 2012SP1 on Server 2008 R2 failover cluster (NOT AlwaysOn). We are trying to add a high-performance mirror in a standalone instance for DR. My understanding is that should be a perfectly normal, supported configuration.

    The mirroring is working properly; however, the clustered SQL Server agent is unable to run jobs that run in the mirrored databases.

    We get the following in the job log: Unable to connect to SQL Server 'VIRTUALSERVERNAME\INSTANCE'.  The step failed.

    There is a partner message in the agent log: [165] ODBC Error: 0, Connecting to a mirrored SQL Server instance using the MultiSubnetFailover connection option is not supported. [SQLSTATE IMH01]

    The cluster is not a mulitsubnet cluster. All hosts are connected to the same subnets and there is no storage replication. I can not find any place where I can adjust the connect string options for SQL Agent.

    Any guidance or suggestions on how to resolve this would be appreciated.

    ~joe

     


    • Edited by Mr. Joe M. _ Monday, June 30, 2014 5:53 PM anonymize servername
    Monday, June 30, 2014 5:52 PM

Answers

  • SQL Team - MSFT:

    Thank you for taking the time to research and provide a clear answer.

    This seems very much a workaround and very unsatisfactory.

    You are correct, there is an IP dependency with OR condition. Moving to an AND condition is not viable for us. The whole point is to provide network redundancy. With an AND condition, if EITHER network interface fails, the service will go offline or fail to come online without manual intervention. This is arguably worse for uptime than having a single interface available.

    We are in process of rewriting all our SQL jobs to start in tempdb before transitioning to the appropriate target database. If this works for all of our jobs, I will mark the above response as answer.

    Again, thank you for the answer.

    Regards,

    Joe M.


    • Edited by Mr. Joe M. _ Friday, August 1, 2014 11:05 PM
    • Marked as answer by Mr. Joe M. _ Thursday, February 26, 2015 10:13 PM
    Monday, July 7, 2014 9:48 PM

All replies

  • Hi Mr.Joe M,

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Wednesday, July 2, 2014 9:21 AM
  • Hello Joe,

    Could you please check if your clustered instance has an "or" dependency on multiple IPs?

    If so, there are two workarounds:

    • set the job step property "database" to a non-mirrored database (e.g. master), then include "use [mirrored database]' in the script
    • Set the dependency of the IP addresses to an AND condition, this way the agent will not issue a connection string with the MultiSbnetFailover option.

    Best regards,

    • Marked as answer by Mr. Joe M. _ Friday, August 1, 2014 11:05 PM
    • Unmarked as answer by Mr. Joe M. _ Thursday, February 26, 2015 10:13 PM
    Friday, July 4, 2014 6:05 AM
  • SQL Team - MSFT:

    Thank you for taking the time to research and provide a clear answer.

    This seems very much a workaround and very unsatisfactory.

    You are correct, there is an IP dependency with OR condition. Moving to an AND condition is not viable for us. The whole point is to provide network redundancy. With an AND condition, if EITHER network interface fails, the service will go offline or fail to come online without manual intervention. This is arguably worse for uptime than having a single interface available.

    We are in process of rewriting all our SQL jobs to start in tempdb before transitioning to the appropriate target database. If this works for all of our jobs, I will mark the above response as answer.

    Again, thank you for the answer.

    Regards,

    Joe M.


    • Edited by Mr. Joe M. _ Friday, August 1, 2014 11:05 PM
    • Marked as answer by Mr. Joe M. _ Thursday, February 26, 2015 10:13 PM
    Monday, July 7, 2014 9:48 PM
  • Thanks, that was the cause. First workaround will be solution.
    Sunday, March 29, 2020 4:08 PM