none
SQL Server Cluster returns 0 for IsClustered and other transact sql queries do not behave as expected.

    Question

  • Hi all

    I am running a two node SQL Cluster (2012) on Windows Server 2012. I can see the availability replicas, groups and availability databases and its behaving as expected but I am getting unexpected results from the transact sql queries to query the cluster.

    select * from sys.dm_os_cluster_nodes gives 0 rows

    SELECT SERVERPROPERTY('IsClustered') returns 0 (Also False in Management Studio)

    SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') always returns the node its run on, not the active node.

    Is something misconfigured?

    Thanks.

    Andrew

    Thursday, December 29, 2016 9:14 PM

Answers

  • Hi Andrew,

    >>Is something misconfigured?

    Nope, the result is expected since you mentioned you are using availability group. 

    >>select * from sys.dm_os_cluster_nodes gives 0 rows
    >>SELECT SERVERPROPERTY('IsClustered') returns 0 (Also False in Management Studio)
    >>SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') always returns the node its run on, not the active node.

    Since you are not running SQL Server Cluster(AKA AlwaysOn Cluster,) the result is expected.

    For AlwaysOn Availability Group configuration, you could go with:
    1. sys.dm_hadr_availability_replica_cluster_nodes 
    2. SELECT SERVERPROPERTY('IsHadrEnabled')
    3. sys.fn_hadr_is_primary_replica

    For more information, please refer to this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by agaskelluk Friday, December 30, 2016 1:56 PM
    Friday, December 30, 2016 7:44 AM
    Moderator
  • To make a bit more clear:

    The "Always On Availability Groups" and "Always On Failover Clustered Instances" are different ways of having high availability and disaster recovery. From my experience with my clients I know that these two are still very confusing for a lot of people out there.

    The features you are querying are about the Always On Failover Clustered Instances. So if you have Always On Availability Groups in your environment, those queries will not return the results you expect.

    For instance when you want to set up an Always On Failover Clustered Instance, you install the SQL Server as a Failover Clustered Instance obviously and only then SELECT SERVERPROPERTY('IsClustered') returns 1, however when you have Always On Availability Groups, you install your SQL Server instances as Stand Alone instances.

    P.S. Of course you can mix these solutions, but that's a different story...


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    • Marked as answer by agaskelluk Friday, December 30, 2016 1:56 PM
    Friday, December 30, 2016 8:22 AM

All replies

  • Hi Andrew,

    >>Is something misconfigured?

    Nope, the result is expected since you mentioned you are using availability group. 

    >>select * from sys.dm_os_cluster_nodes gives 0 rows
    >>SELECT SERVERPROPERTY('IsClustered') returns 0 (Also False in Management Studio)
    >>SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') always returns the node its run on, not the active node.

    Since you are not running SQL Server Cluster(AKA AlwaysOn Cluster,) the result is expected.

    For AlwaysOn Availability Group configuration, you could go with:
    1. sys.dm_hadr_availability_replica_cluster_nodes 
    2. SELECT SERVERPROPERTY('IsHadrEnabled')
    3. sys.fn_hadr_is_primary_replica

    For more information, please refer to this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by agaskelluk Friday, December 30, 2016 1:56 PM
    Friday, December 30, 2016 7:44 AM
    Moderator
  • To make a bit more clear:

    The "Always On Availability Groups" and "Always On Failover Clustered Instances" are different ways of having high availability and disaster recovery. From my experience with my clients I know that these two are still very confusing for a lot of people out there.

    The features you are querying are about the Always On Failover Clustered Instances. So if you have Always On Availability Groups in your environment, those queries will not return the results you expect.

    For instance when you want to set up an Always On Failover Clustered Instance, you install the SQL Server as a Failover Clustered Instance obviously and only then SELECT SERVERPROPERTY('IsClustered') returns 1, however when you have Always On Availability Groups, you install your SQL Server instances as Stand Alone instances.

    P.S. Of course you can mix these solutions, but that's a different story...


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    • Marked as answer by agaskelluk Friday, December 30, 2016 1:56 PM
    Friday, December 30, 2016 8:22 AM