Querying AvailabilityReplica.MemberState can return incorrect values #89
Description
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.