如何查看可用性組中主節點的歷史記錄？

如何查看集群主節點的故障轉移歷史記錄？我試圖弄清楚輔助節點在什麼時候開始充當主節點。

我們正在執行帶有 SQL Server 2012 和可用性組的 Windows Server 2012。

這是我提取 Aaron 提到的事件 (availability_replica_state_change) 的首選查詢。此腳本還可以將 UTC 時間轉換為您的本地時間：

declare @xel_path varchar(1024);
declare @utc_adjustment int = datediff(hour, getutcdate(), getdate());

-------------------------------------------------------------------------------
------------------- target event_file path retrieval --------------------------
-------------------------------------------------------------------------------
;with target_data_cte as
(
   select  
       target_data = 
           convert(xml, target_data)
   from sys.dm_xe_sessions s
   inner join sys.dm_xe_session_targets st
   on s.address = st.event_session_address
   where s.name = 'alwayson_health'
   and st.target_name = 'event_file'
),
full_path_cte as
(
   select
       full_path = 
           target_data.value('(EventFileTarget/File/@name)[1]', 'varchar(1024)')
   from target_data_cte
)
select
   @xel_path = 
       left(full_path, len(full_path) - charindex('\', reverse(full_path))) + 
       '\AlwaysOn_health*.xel'
from full_path_cte;

-------------------------------------------------------------------------------
------------------- replica state change events -------------------------------
-------------------------------------------------------------------------------
;with state_change_data as
(
   select
       object_name,
       event_data = 
           convert(xml, event_data)
   from sys.fn_xe_file_target_read_file(@xel_path, null, null, null)
)
select
   object_name,
   event_timestamp = 
       dateadd(hour, @utc_adjustment, event_data.value('(event/@timestamp)[1]', 'datetime')),
   ag_name = 
       event_data.value('(event/data[@name = "availability_group_name"]/value)[1]', 'varchar(64)'),
   previous_state = 
       event_data.value('(event/data[@name = "previous_state"]/text)[1]', 'varchar(64)'),
   current_state = 
       event_data.value('(event/data[@name = "current_state"]/text)[1]', 'varchar(64)')
from state_change_data
where object_name = 'availability_replica_state_change'
order by event_timestamp desc;

引用自：https://dba.stackexchange.com/questions/76016