Problem
You want to read the AOAG replica state change log for the entire cluster, not just from a single node. You could of course read it from one node at the time and concatenate the data manually, but where is the fun in that?
Analysis
We have many possible avenues to explore:
- Central Management Server
- Storing the xel files on a network share
- Linking the servers
- SQLCMD
- PowerShell
After considering my options I landed on PowerShell, more specifically on the dbatools PowerShell module. To be even more specific, I employed the invoke-dbaquery command: Invoke-DbaQuery | dbatools | SQL Server automation with PowerShell.
Why? I hear you ask. Well, because I already have dbatools installed and the other options required more hassle. And who wants that? It is not like I spent a day making this work or anything. That would be stupid. Especially if you have only one cluster. If you have many on the other hand…
The usual caveats and gotchas
I am not responsible for what you do with the code and advice given here. If you do not understand it, do your research and get help before you run this in production. It might not even work at all. Tests have been performed at SQL 2022/25 and Win 2022/2025 only.
A simple solution for simple people, eh simple setups
Let us be completely honest; if you have one or two clusters and need these data once a year, you do not need a fancy solution. Just run the script below on each node, paste the results into excel and perform your further analysis in excel:
WITH exEvents AS (SELECT object_name, CONVERT(XML, event_data) AS data
FROM sys.fn_xe_file_target_read_file('AlwaysOn*.xel', null, null, null)
WHERE object_name = 'availability_replica_state_change'
)
--select * from exEvents
SELECT
data.value('(/event/@timestamp)[1]','datetime') AS [TimeStamp],
data.value('(event/data[@name=''availability_group_name''])[1]','varchar(max)') AS [AGName],
data.value('(event/data[@name=''availability_replica_name''])[1]','varchar(max)') AS [AGReplicaName],
data.value('(event/data[@name=''previous_state'']/value)[1]','varchar(max)') AS [PreviousStateID],
data.value('(event/data[@name=''previous_state'']/text)[1]','varchar(max)') AS [PreviousState],
data.value('(event/data[@name=''current_state'']/value)[1]','varchar(max)') AS [CurrentStateID],
data.value('(event/data[@name=''current_state'']/text)[1]','varchar(max)') AS [CurrentState]
FROM exEvents
ORDER BY TimeStamp DESC
The results should look something like this:
What the script does when it works
- Gathers output files (.xel files) from the AlwaysOn_health session. If you do not have this session, you have to re-create it. The easiest way to do so is to copy it from a cluster node where it has not been tampered wth.
- The data is stored in the temporary table exEvents
- And the fancy select statement parses said xml data into something we can read without getting a headache
A more complex solution for stubborn people
…or people with many clusters and/or more complex needs.
First, let us make a table to store our results. You can create this table in any database on any server reachable from the machine on which you run these commands. You also need access to all the cluster nodes and a recent copy of the dbatools PowerShell module. Then, we need a script file to execute. Let us call it AOAGLog.sql. Or perhaps Bob.sql. The name does not matter, but it is referenced in the next step.
--Results table
USE TestDB;
CREATE TABLE xeData(object_name nVarchar(MAX), data XML);
--AOAGLog.sql, used to collect xel files. Referenced by invoke-dbaquery below
SELECT object_name, CONVERT(XML, event_data) AS data
FROM sys.fn_xe_file_target_read_file('AlwaysOn*.xel', null, null, null)
WHERE object_name = 'availability_replica_state_change'
Here comes the PowerShell part
We need to execute tha AOAGLog script against all the nodes in the cluster. First we create an array with all the node names. Then we run Invoke DbaQuery and store the result in the $results variable.
$Instances= @( 'node1', 'node2', 'node3' ) $results = Invoke-DbaQuery -SqlInstance $Instances -File C:\Tools\aoaglog.sql
Next, we take the results and store them in the exData table. This table does not have to be located on one of the nodes. Remember to empty the table between runs to avoid duplicates.
foreach ($row in $results) {
Invoke-DbaQuery -SqlInstance "xeDataServer" -Database "TestDB" -Query @"
INSERT INTO xeData (object_name, data)
VALUES ('$($row.object_name)', '$($row.data)')
"@
}
Parse the results
Then, all that remains is to parse the results and analyse them. The default is sorted by timestamp, newest first, but you can play around with this as needed. You can also store the results for later reference.
-- Parse results (runs on the server hosting the results table)
USE TestDB;
SELECT
data.value('(/event/@timestamp)[1]','datetime') AS [TimeStamp],
data.value('(event/data[@name=''availability_group_name''])[1]','varchar(max)') AS [AGName],
data.value('(event/data[@name=''availability_replica_name''])[1]','varchar(max)') AS [AGReplicaName],
data.value('(event/data[@name=''previous_state'']/value)[1]','varchar(max)') AS [PreviousStateID],
data.value('(event/data[@name=''previous_state'']/text)[1]','varchar(max)') AS [PreviousState],
data.value('(event/data[@name=''current_state'']/value)[1]','varchar(max)') AS [CurrentStateID],
data.value('(event/data[@name=''current_state'']/text)[1]','varchar(max)') AS [CurrentState]
FROM xeData
ORDER BY TimeStamp DESC
