Querying AvailabilityReplica.MemberState can return incorrect values

The following code may print different values for MemberState when fetched using SMO compared to running the SQL query directly.

using System;
using Microsoft.SqlServer.Management.Smo;
using System.Data;
using Microsoft.Data.SqlClient;

namespace SMOTEST
{
    internal class Program
    {
        class sqlCMD
        {
            public String m_ServerName { get; set; }
            public String m_AGName { get; set; }
            public String cmdMemberState()
            {
                String s = @"exec sp_executesql N'
            select* into #tmpag_availability_groups from master.sys.availability_groups
            select group_id, replica_id, replica_server_name, create_date, modify_date, endpoint_url, read_only_routing_url, primary_role_allow_connections, secondary_role_allow_connections, availability_mode, failover_mode, session_timeout, backup_priority, owner_sid, seeding_mode into #tmpar_availability_replicas from master.sys.availability_replicas

            SELECT
                arrc.replica_server_name, 
                COUNT(cm.member_name) AS node_count,
                SUM(cm.member_state) AS member_state_sum,
                SUM(cm.number_of_quorum_votes) AS quorum_vote_sum
            INTO #tmpar_availability_replica_cluster_info
            FROM
                (SELECT DISTINCT replica_server_name, node_name FROM master.sys.dm_hadr_availability_replica_cluster_nodes) AS arrc
            LEFT OUTER JOIN master.sys.dm_hadr_cluster_members AS cm ON UPPER(arrc.node_name) = UPPER(cm.member_name)
            GROUP BY arrc.replica_server_name

                SELECT

                CASE
                    WHEN arci.member_state_sum IS NULL OR arci.node_count = 0 THEN 3
                    WHEN arci.member_state_sum = 0 THEN 0
                    WHEN arci.member_state_sum < arci.node_count THEN 2
                    WHEN arci.member_state_sum = arci.node_count THEN 1
                    ELSE 3
                END
                       AS[MemberState]
                FROM
                #tmpag_availability_groups AS AG
                INNER JOIN #tmpar_availability_replicas AS AR ON (AR.replica_server_name IS NOT NULL) AND (AR.group_id=AG.group_id)
                LEFT OUTER JOIN #tmpar_availability_replica_cluster_info AS arci ON UPPER(AR.replica_server_name) = UPPER(arci.replica_server_name) 
                WHERE
                (AR.replica_server_name = @_msparam_0)and((AG.name = @_msparam_1))

                      DROP TABLE #tmpar_availability_replicas
    


                      DROP TABLE #tmpar_availability_replica_cluster_info
    


                      drop table #tmpag_availability_groups
    

                ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'" + m_ServerName + "',@_msparam_1=N'" + m_AGName +"'";
                return s;
            }
            public String cmdReplica()
            {
                String s = @"exec sp_executesql N'
                      select * into #tmpag_availability_groups from master.sys.availability_groups
    



                        select group_id, replica_id, replica_server_name,create_date, modify_date, endpoint_url, read_only_routing_url, primary_role_allow_connections, secondary_role_allow_connections, availability_mode,failover_mode, session_timeout, backup_priority, owner_sid, seeding_mode into #tmpar_availability_replicas from master.sys.availability_replicas
      


                      select group_id, replica_id, role,operational_state,recovery_health,synchronization_health,connected_state, last_connect_error_number,last_connect_error_description, last_connect_error_timestamp into #tmpar_availability_replica_states from master.sys.dm_hadr_availability_replica_states
    


                      select * into #tmpar_ags from master.sys.dm_hadr_availability_group_states
                      select ar.group_id, ar.replica_id, ar.replica_server_name, ar.availability_mode, (case when UPPER(ags.primary_replica) = UPPER(ar.replica_server_name) then 1 else 0 end) as role, ars.synchronization_health into #tmpar_availabilty_mode from #tmpar_availability_replicas as ar
                      left join #tmpar_ags as ags on ags.group_id = ar.group_id
                      left join #tmpar_availability_replica_states as ars on ar.group_id = ars.group_id and ar.replica_id = ars.replica_id
                      select am1.replica_id, am1.role, (case when (am1.synchronization_health is null) then 3 else am1.synchronization_health end) as sync_state, (case when (am1.availability_mode is NULL) or (am3.availability_mode is NULL) then null when (am1.role = 1) then 1 when (am1.availability_mode = 0 or am3.availability_mode = 0) then 0 else 1 end) as effective_availability_mode
                      into #tmpar_replica_rollupstate from #tmpar_availabilty_mode as am1 left join (select group_id, role, availability_mode from #tmpar_availabilty_mode as am2 where am2.role = 1) as am3 on am1.group_id = am3.group_id
                      drop table #tmpar_availabilty_mode
                      drop table #tmpar_ags
    


                      CREATE TABLE #ror_list_delimited (
                      replica_id uniqueidentifier,
                      read_only_routing_list nvarchar(max)
                      )
                      select replica_id,read_only_replica_id,routing_priority into #tmp_availability_read_only_routing_lists from sys.availability_read_only_routing_lists

                      INSERT INTO #ror_list_delimited
                      SELECT DISTINCT ars.replica_id, a.delimited
                      FROM #tmpar_availability_replicas AS ars
                      CROSS APPLY
                      (
                        SELECT STUFF(final_list.read_only_routing_list,1,1,'''') FROM (
                        SELECT CASE 
                        WHEN CHARINDEX('','', STUFF(priority_grouped_list.priority_group, 1, 1, '''')) = 0 THEN priority_grouped_list.priority_group
                        ELSE '',('' + STUFF(priority_grouped_list.priority_group, 1, 1, '''') + '')'' 
                          END 
                        FROM (
                        SELECT (SELECT  '',N''''''+ ars2.replica_server_name +''''''''
                        FROM #tmp_availability_read_only_routing_lists AS rrlists
                        INNER JOIN #tmpar_availability_replicas AS ars2
                        ON ars2.replica_id = rrlists.read_only_replica_id
                        WHERE rrlists.routing_priority = prioritylist.routing_priority
                        AND rrlists.replica_id = ars.replica_id
                        FOR XML PATH('''') ) AS priority_group
                        FROM
                        (SELECT  distinct(routing_priority)
                        FROM #tmp_availability_read_only_routing_lists where replica_id = ars.replica_id) AS prioritylist(routing_priority)) priority_grouped_list
                        FOR XML PATH('''') ) AS final_list(read_only_routing_list) ) AS a(delimited)
    


                      select replica_id,join_state into #tmpar_availability_replica_cluster_states from master.sys.dm_hadr_availability_replica_cluster_states
    


                SELECT
                AR.replica_server_name AS [Name],
                AR.replica_id AS [UniqueId],
                ISNULL(arstates.role, 3) AS [Role],
                ISNULL(arstates.operational_state, 6) AS [OperationalState],
                ISNULL(arstates.recovery_health, 2) AS [RollupRecoveryState],
                (case when arrollupstates.sync_state = 3 then 3 when (arrollupstates.effective_availability_mode = 1 or arrollupstates.role = 1) then arrollupstates.sync_state when arrollupstates.sync_state = 2 then 1 else 0 end) AS [RollupSynchronizationState],
                ISNULL(arstates.connected_state, 2) AS [ConnectionState],
                ISNULL(AR.create_date, 0) AS [CreateDate],
                ISNULL(AR.modify_date, 0) AS [DateLastModified],
                ISNULL(AR.endpoint_url, N'''') AS [EndpointUrl],
                ISNULL(AR.read_only_routing_url, '''') AS [ReadonlyRoutingConnectionUrl],
                ISNULL(rorlists.read_only_routing_list, '''') AS [ReadonlyRoutingListDelimited],
                ISNULL(AR.primary_role_allow_connections, 4) AS [ConnectionModeInPrimaryRole],
                ISNULL(AR.secondary_role_allow_connections, 3) AS [ConnectionModeInSecondaryRole],
                ISNULL(AR.availability_mode, 2) AS [AvailabilityMode],
                ISNULL(AR.failover_mode, 3) AS [FailoverMode],
                ISNULL(AR.session_timeout, -1) AS [SessionTimeout],
                ISNULL(arstates.last_connect_error_number,-1) AS [LastConnectErrorNumber],
                ISNULL(arstates.last_connect_error_description, N'''') AS [LastConnectErrorDescription],
                ISNULL(AR.backup_priority, -1) AS [BackupPriority],
                ISNULL(arstates.last_connect_error_timestamp, 0) AS [LastConnectErrorTimestamp],
                suser_sname(AR.owner_sid) AS [Owner],
                ISNULL(arcs.join_state, 99) AS [JoinState],
                ISNULL(AR.seeding_mode, 1) AS [SeedingMode]
                FROM
                #tmpag_availability_groups AS AG
                INNER JOIN #tmpar_availability_replicas AS AR ON (AR.replica_server_name IS NOT NULL) AND (AR.group_id=AG.group_id)
                LEFT OUTER JOIN #tmpar_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id
                LEFT OUTER JOIN #tmpar_replica_rollupstate AS arrollupstates ON AR.replica_id = arrollupstates.replica_id
                LEFT OUTER JOIN #ror_list_delimited AS rorlists ON AR.replica_id = rorlists.replica_id
                LEFT OUTER JOIN #tmpar_availability_replica_cluster_states AS arcs ON AR.replica_id = arcs.replica_id
                WHERE
                (AR.replica_server_name=@_msparam_0)and((AG.name=@_msparam_1))

                      DROP TABLE #tmpar_availability_replicas
    


                      DROP TABLE #tmpar_availability_replica_states
    


                      DROP TABLE #tmpar_replica_rollupstate
    


                      DROP TABLE #ror_list_delimited
                      DROP TABLE #tmp_availability_read_only_routing_lists
    


                      DROP TABLE #tmpar_availability_replica_cluster_states
    


                      drop table #tmpag_availability_groups
    

                ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'"+ m_ServerName+"',@_msparam_1=N'" + m_AGName + "'";
                return s;
            }
        }
        static void Main(string[] args)
        {

           sqlCMD m_sqlCMD = new sqlCMD();

            Console.WriteLine("Enter Server Name :");
            String sName = Console.ReadLine();

                Server m_server = new Server(sName);
                m_server.ConnectionContext.LoginSecure = true;
                try
                {
                    int iNumber = m_server.BuildNumber;
                    Console.WriteLine("Build Number : " + iNumber);
                } catch (Exception e)
                {
                    Console.WriteLine("Cannot connect to server");
                    return;
                }
                
                
                AvailabilityGroupCollection AGs = m_server.AvailabilityGroups;
                
                if (AGs.Count > 0)
                {
                    //AvailabilityReplicaCollection replicas = AGs["A"].AvailabilityReplicas;
                    
                    foreach (AvailabilityGroup AG in AGs)
                    {
                    Console.WriteLine("AG Name : " + AG.Name);
                    m_sqlCMD.m_AGName = AG.Name;
                    
                        foreach (AvailabilityReplica replica in AG.AvailabilityReplicas)
                        {
                        m_sqlCMD.m_ServerName = replica.Name;
                            Console.WriteLine("Name : " + replica.Name);
                            Console.WriteLine("State :  " + replica.MemberState);
                            Console.WriteLine("Role : " + replica.Role);
                            
                            SqlDataReader rdr = m_server.ConnectionContext.ExecuteReader(m_sqlCMD.cmdMemberState());
                            while (rdr.Read())
                            {
                                Console.WriteLine(" Query State : " + rdr["MemberState"].ToString());
                            }
                            rdr.Close();
                            rdr = m_server.ConnectionContext.ExecuteReader(m_sqlCMD.cmdReplica());
                            while (rdr.Read())
                            {
                                Console.WriteLine("Query Role : " + rdr["Role"].ToString());
                            }
                            rdr.Close();
                        }
                    }

                }
            //}
            Console.WriteLine("Finished... press any key");
            Console.ReadKey();
            
        }
    }
}

The workaround is to call m_server.SetDefaultInitFields(typeof(AvailabilityReplica), nameof(AvailabilityReplica.MemberState) before accessing the collections.

提供された C# の再現コードから始め、同じサーバーと可用性グループに対する SMO の AvailabilityReplica.MemberState と直接の SQL クエリを比較します。AvailabilityReplica のクエリパスと、再現コードに示されている MemberState の計算を追跡します。完了条件は、報告されたシナリオで両方のアクセスパスが同じ値を返すことです。