Locked
Discussion interactions are currently paused.
Alternate method of tracking Availability Group synchronization.
Hello all! I just recently created a monitor to track the amount of time in seconds the secondary replicas are behind the primary and I figured I would share my solution. This will display the span of time in which past transactions could be lost in the event of a fail over. This assumes the secondaries are asynchronous. We are using SQL 2016, I do not know if this will work on earlier versions of SQL.
I created a datasource and set the "Applies to" to the server in question. I set the collector to "SCRIPT" and I set the datasource to multi-instance and enabled active discovery. I used JDBC as the discovery method and Instance List as the discovery type. The details are as follows:
Connection String:
SQL Statement:
(This query gets the name of each secondary replica)
<span class="pln">SELECT CS</span><span class="pun">.</span><span class="pln">replica_server_name
FROM sys</span><span class="pun">.</span><span class="pln">availability_groups_cluster AS C
INNER JOIN sys</span><span class="pun">.</span><span class="pln">dm_hadr_availability_replica_cluster_states AS CSON CS</span><span class="pun">.</span><span class="pln">group_id </span><span class="pun">=</span><span class="pln"> C</span><span class="pun">.</span><span class="pln">group_id
INNER JOIN sys</span><span class="pun">.</span><span class="pln">dm_hadr_availability_replica_states AS RS ON RS</span><span class="pun">.</span><span class="pln">replica_id </span><span class="pun">=</span><span class="pln"> CS</span><span class="pun">.</span><span class="pln">replica_id
WHERE RS</span><span class="pun">.</span><span>Role_desc</span><span class="pln"> </span><span class="pun"><></span><span class="pln"> </span><span class="str">'PRIMARY'</span>
I wrote a script in Powershell that actually pulls the data that I need from the server itself.
Powershell Script:
<span class="pun">[</span><span>System</span><span class="pun">.</span><span>Reflection</span><span class="pun">.</span><span>Assembly</span><span class="pun">]::</span><span>LoadWithPartialName</span><span class="pun">(</span><span class="str">"Microsoft.SqlServer.Smo"</span><span class="pun">)</span><span class="pln"> </span><span class="pun">|</span><span class="pln"> </span><span>Out</span><span class="pun">-</span><span>Null</span><span class="pln">
$SqlServer </span><span class="pun">=</span><span class="pln"> </span><span>New</span><span class="pun">-</span><span>Object</span><span class="pln"> </span><span>Microsoft</span><span class="pun">.</span><span>SqlServer</span><span class="pun">.</span><span>Management</span><span class="pun">.</span><span>Smo</span><span class="pun">.</span><span>Server</span><span class="pun">(</span><span class="str">"##HOSTNAME##"</span><span class="pun">)</span><span class="pln">
$SqlServer</span><span class="pun">.</span><span>AvailabilityGroups</span><span class="pun">[</span><span class="str">"your ag name here"</span><span class="pun">].</span><span>DatabaseReplicaStates</span><span class="pln"> </span><span class="pun">|</span><span class="pln">
</span><span>Where</span><span class="pun">-</span><span>Object</span><span class="pln"> </span><span class="pun">{</span><span class="pln">$_</span><span class="pun">.</span><span>IsLocal</span><span class="pln"> </span><span class="pun">-</span><span class="pln">eq $false </span><span class="pun">-</span><span class="pln">and $_</span><span class="pun">.</span><span>AvailabilityReplicaServerName</span><span class="pln"> </span><span class="pun">-</span><span class="pln">eq </span><span class="str">"##WILDVALUE##"</span><span class="pln"> </span><span class="pun">-</span><span class="pln">and $_</span><span class="pun">.</span><span>AvailabilityDatabaseName</span><span class="pln"> </span><span class="pun">-</span><span class="pln">eq </span><span class="str">"your server name here"</span><span class="pun">}</span><span class="pln"> </span><span class="pun">|</span><span class="pln">
</span><span>Select</span><span class="pln"> </span><span class="pun">-</span><span>ExpandProperty</span><span class="pln"> </span><span>EstimatedDataLoss</span>In our environment there is only one database on the server that we are concerned with for tracking this information so I have not explored monitoring multiple databases. This method pulls the same data that is in the Estimated Data Loss(seconds) column in the Availability Groups dashboard in SQL Management Studio. In the event you wanted to track multiple databases on the secondaries, you will likely have to create a separate datasource for each secondary server you want to track. Additionally you would have to adjust the JDBC query to enumerate the databases on the server and the powershell script accordingly.
I hope this helps someone.
Thanks,
Kyle
kholbrook
OP8 years ago@Andrey Kitsen Thanks man! I just published it and the ID is 4TH3HA. In regards to what LM offers for this, since it involved creating a new database for tracking I figured it would be easier to use this method for the time being.
Andrey_Kitsen
·8 years ago@kholbrook this looks pretty great.
If you've already got this working inside a DataSource, go ahead a publish it and share the locator ID here for others to easily grab and use.
Also, are you by chance running our newest suite of SQL Server monitoring ? We've got some coverage of AlwaysOn Availability Groups, Availability Replicas, Replicas and Replica Clusters.