Sql-server – How to find out when a node was added to an availability group

availability-groupssql server

Ideally, I am looking for T-SQL that returns two columns: the node name, and the date/time that node was added to the availability group, for all nodes in a given availability group.

Best Answer

This is as far as I can see not possible with a single T-SQL statement as individual nodes hold only their own create date.

The sys.availability_replicas page makes reference to a column create_date. This is the datetime value of when the node was added.

To find the create_date for each replica you would need to first connect that replica and query the table for that instance only as below.

SELECT [replica_server_name],
    [create_date]
FROM [master].[sys].[availability_replicas]
WHERE replica_server_name = @@SERVERNAME
Related Question