Alwayson -- SYS. dm_hadr_instance_node_map returns NULL.

 

 

 

The following is msdn's explanation of SYS. dm_hadr_instance_node_map.

For every instance of SQL Server that hosts an availability replica that is joined to its alwayson availability group, returns the name of the Windows Server failover clustering (wsfc) node that hosts the server instance. this dynamic management view has the following uses:

• This dynamic management view is useful for detecting an availability group with multiple availability replicas that are hosted on the same wsfc node, which is an unsupported configuration that cocould occur after an FCI failover if the availability group is incorrectly configured. for more information, see failover clustering and alwayson availability groups (SQL Server ).

 

• When multiple SQL Server instances are hosted on the same wsfc node, the resource DLL uses this dynamic management view to determine the instance of SQL Server to connect.

 

 

For example

Env

===

Nodes:

Sql1_w2k8r21

Sql1_w2k8r22

Sql1_w2k8r23

 

Default instance

SQL Server 2012 SP1 RTM

 

Availability group: agtest1 and agtest2

 

Agtest1:

Sql1_w2k8r21

Sql1_w2k8r22

 

Sql1_w2k8r23

 

Agtest2:

Sql1_w2k8r21

Sql1_w2k8r22

 

The returned value of SYS. dm_hadr_instance_node_map is as follows:

The corresponding ag_resource_id can be found in SYS. dm_hadr_name_id_map.

 

 

Dmv sys. dm_hadr_instance_node_map will be taken from the registry below

 

 

If your DMV returns NULL, check the registry HKEY_LOCAL_MACHINE \ cluster \ hadragnametoidmap. If the legacy/orphan key value exists, delete it. Back up data in case of any problems.

 

Then run dmv again.