SQL Server – SERVERPROPERTY(‘HadrManagerStatus’) Shows 1 Even if Always On is Not Configured

availability-groupshigh-availabilitysql serversql-server-2017

We have a SQL Server Enterprise 2017. I would like to get insights as to why our SERVERPROPERTY('HadrManagerStatus')returns 1 even if Always On is not configured in this instance.

I have a local instance in my work machine that is a stand alone. When I run SELECT SERVERPROPERTY('HadrManagerStatus') it returns 1 even if I have not set up Always On on this machine. Never have attempted to check the "Enable Always ON" box on the Service in the SQL server Configuration Manager.

I have setup this instance to be a publisher and a distributor for Replication. I have restored databases on this instance that the database were part of the availability databases.

I guess my question is, how does this SERVERPROPERTY('HadrManagerStatus') evaluates? What conditions does it check?

Best Answer

As mentioned in a comment by Kin, if you want to check on whether the "Enable Always On" checkbox is checked in SQL Server Configuration Manager, you should be using this property instead:

SELECT SERVERPROPERTY('IsHadrEnabled');

From the docs on IsHadrEnabled:

Always On availability groups is enabled on this server instance.

Regarding the other setting (HadrManagerStatus), here's what docs has to say:

Indicates whether the Always On availability groups manager has started.

I don't really know what the "Availability Groups manager" is, but it sounds like probably an internal component to SQL Server.

Tony helpfully pointed out that this value was non-zero on non-AG instances prior to 2017. I just verified this on a SQL Server 2016 instance where AGs are not enabled and set up, and I get "2" ("Not started and failed").

I'm speculating here, but the fact that the "AG manager" component is started by default on SQL Server 2017 probably has something to do with the clusterless, "read-scale" AG feature introduced in that version. So on 2017 this component is able to start up and run, whereas previous versions noticed the lack of WSFC and failed to start.

Anyway, that's just a guess. Maybe someone from Microsoft will come by and confirm one way or the other. The real answer is what Kin pointed out - if you want to check for AG's being enabled, use IsHadrEnabled.

Related Question