none
Can't access cluster via SQL Server Mng Studio unless node has ownership

    Question

  • Hi:

    We have Windows and SQL 2008 R2 in a 2 node cluster. Failover and failback work like a dream. All the 'lights' are green. But we can only access the cluster when we're on the node that has ownership. I've deleted all DNS entries and successfully recreated them via ipconfig /registerDns from each node, shutting down, restarting the actual cluster and taking the application and service offline then back on line. All DNS records got recreated perfectly. Cluster error log doesn't show anything when we can't connect either. We can also ping all IPs, Hostnames and Clustername.

    • Edited by g_machuca Wednesday, March 05, 2014 11:20 AM
    Wednesday, March 05, 2014 11:17 AM

All replies

  • Hi g_machuca,

    According to your description, you want to implement failover and failback in SQL Server cluster environment. We need to verify if The cluster group containing SQL Server can be configured for automatic failback to the primary node when it becomes available again. By default, this is set to off. First you set a preferred owner for the SQL Server service in a clustered environment; To ensure the service or application automatically moves to the correct node, select “Allow Failback” under the Failover tab. .  Auto failback saves you from having to make this change manually; however it will restart the SQL Services when it automatically fails back. Or if you don’t want to configure auto failback you’ll want to make sure that you manually fail the services back to the more powerful node after the system has come back online.

    There are details about failover cluster allows automatic failback, you can review the following articles.

    http://www.mssqltips.com/sqlservertip/2672/managing-a-windows-and-sql-server-cluster-using-the-failover-cluster-manager-tool/
    http://blogs.technet.com/b/rob/archive/2008/05/07/failover-clustering.aspx

    For more information about troubleshooting Windows Server 2008 R2 Failover Clusters, you can review the following article.
    http://windowsitpro.com/windows-server-2008/troubleshooting-windows-server-2008-r2-failover-clusters.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Monday, March 10, 2014 1:53 AM
    Moderator
  • When an instance of SQL is running on a cluster, it can only be accessed using the SQL Server Network Name

    The behavior that you are specifying here implies that you have a default instance and are trying to connect using the "."

    Find out the SQL Network name and use that to connect using SSMS on the passive node.

    • Proposed as answer by Feroz R Tuesday, March 18, 2014 1:41 PM
    Sunday, March 16, 2014 10:15 PM
  • Hi,

    I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps? If you would like to, you can post a reply to share your solution and I will mark it as answer. That way, other community members could benefit from your sharing.

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Monday, March 31, 2014 1:53 AM
    Moderator