Why does sys.dm_hadr_cluster return a row on SQL 22 on Linux when AlwaysOn is enabled?

Brandon Tucker 6

Hi folks-

I have a SQL 22 on Windows instance and a SQL 22 on Linux instance. I have enabled AlwaysOn on both and created a read-scale/clusterless AG that spans the two. When I query sys.dm_hadr_cluster on Linux, I receive the following result:

cluster_name = '' (blank)

quorum_type = 0 (Node Majority)

quorum_state = 1 (Normal quorum)

Is this expected or possibly a bug?

Seeya Xi-MSFT 16,436 Reputation points

2023-02-14T08:00:34.3566667+00:00

Hi ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,

Seeya
Brandon Tucker 6 Reputation points

2023-02-14T15:08:17.3866667+00:00

Hi Seeya-

Your responses of "don't use this DMV" do not answer my question. My concern is that a DMV that returns information about clusters is returning information when there is no cluster behind the instance of SQL Server.

For background, I help to create software that monitors SQL Server and I need to be able to explain this behavior.

3 answers

Erland Sommarskog 100.8K Reputation points MVP

2023-02-07T08:11:42.13+00:00

I tried this at two servers at my client, both running Windows Server 2016. None of them are in a Windows cluster as far as I know.

On both, I get one row back from sys.dm_hadr_cluster on the default instance. The test server has a couple of named instances, and interesting the DMV came out empty when I tried the query on a named instance.

I guess that SQL Server just calls a Windows API, and if that API returns something, the information is relayed. Since there is no clustering support on a workstation OS, the API is empty on a workstation OS.
Please sign in to rate this answer.

1 person found this answer helpful.
Brandon Tucker 6 Reputation points

2023-02-07T18:14:14.25+00:00

So on the same host, which isn't a part of a cluster, sys.dm_hadr_cluster returns different information based on whether it's a default or a named instance? That seems...unfortunate.

I did also notice that sys.dm_hadr_cluster_members returns a row on the non-clustered, Windows instance without AO enabled, with a member_type_desc = 'CLUSTER_NODE'. It really feels like a bug, but any additional information would be great.

Erland Sommarskog 100.8K Reputation points MVP

2023-02-07T22:17:15.2633333+00:00

I'm not sure why this would be unfortunate? I assume that if were to actually cluster the instance, something would turn up. I guess that what happens now is due to some quirk of fate. What I don't really understand is why you are concerned about this?
Sign in to comment
Erland Sommarskog 100.8K Reputation points MVP

2023-02-02T22:11:03.0333333+00:00

I see the same thing on a machine running Windows Server 2016 and SQL 2017 on which I have a clusterless AG.

I would say that is quite expected. Normally, with an AG, you do have a cluster, so I guess they display one row here to match regular AGs.
Please sign in to rate this answer.
Erland Sommarskog 100.8K Reputation points MVP

2023-02-03T21:57:57.99+00:00

Which WIndows OS do you have? Is that Windows 10/11, or Windows Server?

In my SQL 2019 instance on Windows 11, I don't have any rows in this DMV. But maybe it is different on a server OS?

Seeya Xi-MSFT 16,436 Reputation points

2023-02-06T06:01:37.1066667+00:00

Hi,

If the Windows Server Failover Clustering (WSFC) node that hosts an instance of SQL Server that is enabled for Always On availability groups has WSFC quorum, sys.dm_hadr_cluster returns a row that exposes the cluster name and information about the quorum. If the WSFC node has no quorum, no row is returned.

If you don't have a cluster, you don't need to use this DMV.

Brandon Tucker 6 Reputation points

2023-02-06T22:21:47.38+00:00

@Erland Sommarskog Windows Server 2019 Standard

Brandon Tucker 6 Reputation points

2023-02-07T18:10:49.3166667+00:00

To follow up on this, I did a fresh install of SQL Server 2019 (Windows) and have not enabled Always On yet. I queried sys.dm_hadr_cluster and received a similar one-row result. The host is not a member of a cluster, so why would there be cluster data from SQL Server?

Erland-

Thanks for confirming on previous versions. I think there's still something at play here...

Seeya-

Thank you for your response. Because there is no cluster behind the scenes, I would not expect any result from a DMV that returns data about the cluster that the SQL instance resides on. From this SQL Docs page, it doesn't seem to match up:

If the Windows Server Failover Clustering (WSFC) node that hosts an instance of SQL Server that is enabled for Always On availability groups has WSFC quorum, sys.dm_hadr_cluster returns a row that exposes the cluster name and information about the quorum. If the WSFC node has no quorum, no row is returned.

I am familiar with setting up a clusterless availability group, so I would like to continue to focus on this item.

Cheers,

-BT
Sign in to comment
Seeya Xi-MSFT 16,436 Reputation points

2023-02-03T09:22:18.75+00:00

Hi @Brandon Tucker ,

Yes. Since there is no cluster behind the scenes, there is no concept of health checks and hence no automatic failovers and there is no problem with your result.

You should focus on something else. Please read this blog: https://www.sqlshack.com/read-scale-availability-group-in-a-clusterless-availability-group/

Best regards,

Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.

0 comments No comments
Sign in to comment