none
SQL server 2008 R2, 2 node cluster, cluster fails over to each node, other servers loses DB connection across

    Question

  • Hello gurus,

    I created a 2 node failover Windows Server 2008 R2 cluster for SQL Server 2008 R2.  If I shut SQL server B, the the failover takes place, the service and application move to the SQL server A, but then all of my other servers disconnect from the DBs.  The DBs's preferred owner is SQL server B.  Does anyone have any idea or a path to cluster logs to look a little deeper?

    Thanks,

    Joe 

    Friday, May 17, 2013 2:27 PM

All replies

  • but then all of my other servers disconnect from the DBs. 

    Didnt quite really get this. how are the other servers connected? Do you mean to say that when SQL is active in server A, you are not able to connect from other servers? Can you check whether SQL browser service is running and the necessary protocols are enabled?

    To get to the cluster logs check this link http://blogs.technet.com/b/askcore/archive/2010/04/13/understanding-the-cluster-debug-log-in-2008.aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Friday, May 17, 2013 3:04 PM
  • Thanks Ashwin,

    So servers that write to the DBs cannot connect to the DB instance.  So the instances are on SQL B and when I reboot the SQL Server B, the services failover to SQL Server A, but then I have applications that write to the instance that is on SQL server B which lose connection.  The applications try to connect to that instance, but cannot even tho the services are available on the SQL Server A.  Does this make sense?

    Thanks,

    Joe

    Friday, May 17, 2013 3:14 PM
  • Hi,

    Are the applications connecting to the DB instance with the SQL Server Virtualname?


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Friday, May 17, 2013 5:09 PM
  • Yes, they are.
    Friday, May 17, 2013 5:27 PM
  • By definition, a resource group failover is a stop-move-start service process. This means that the SQL Server service will be stopped on ServerB and started on ServerA. Connections to the databases will be disconnected during this process. You either have to manually reconnect the application back to the SQL Server instance or have a built-in reconnection logic to try and reconnect back.

    If you are saying that the SQL Server clustered resource group is online on ServerA yet your applications cannot connect to it, can you try running a PING and TELNET test on the virtual server name that your SQL Server clustered resource group is using? You should get a response if the clustered resource group is running on ServerA


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn

    Saturday, May 18, 2013 12:01 AM
    Moderator
  • Ping and telnet to virtual server name respond.  The services and application within the cluster move or failover to serverA without any issue, but my third party software/applications would not connect at all when everything fails over to NodeA (ServerA).  I do have a few DBs mirrored to a remote SQL server via IPSec Tunnel within our Network.

    Thanks,

    Joe

    • Edited by joelerois Tuesday, May 21, 2013 5:22 PM
    Tuesday, May 21, 2013 5:18 PM
  • If you restart the client applications after failover, do they connect correctly?

    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    Tuesday, May 21, 2013 6:55 PM
    Moderator
  • No they don't, but once the I failover back to ServerB they do.
    Thursday, May 23, 2013 4:43 PM
  • Sounds like the apps are using the node IP, not the cluster instance IP.  Either that or there is an incorrectly configured firewall between the apps and the SQL database.

    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    Thursday, May 23, 2013 7:36 PM
    Moderator
  • Same here, trying to connect to clustered server from Lotus Domino LEI Server, using virtual server name. When Node B is active it can connect, but when I restart B, it fails to connect to Node A. Using cluster instance IP doesn't help.
    Friday, October 11, 2013 8:41 AM