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 replicasare 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 secondariesare 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" andIset 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: jdbc:sqlserver://##HOSTNAME##;IntegratedSecurity=true; SQL Statement: (This query gets the name of each secondary replica) SELECT CS.replica_server_name FROM sys.availability_groups_cluster AS C INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS CSON CS.group_id = C.group_id INNER JOIN sys.dm_hadr_availability_replica_states AS RS ON RS.replica_id = CS.replica_id WHERE RS.Role_desc <> 'PRIMARY' I wrote a script in Powershell that actually pulls the data that I need from the server itself. Powershell Script: [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("##HOSTNAME##") $SqlServer.AvailabilityGroups["your ag name here"].DatabaseReplicaStates | Where-Object {$_.IsLocal -eq $false -and $_.AvailabilityReplicaServerName -eq "##WILDVALUE##" -and $_.AvailabilityDatabaseName -eq "your server name here"} | Select -ExpandProperty EstimatedDataLoss 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 theserver and the powershell script accordingly. I hope this helps someone. Thanks, Kyle28Views1like2CommentsI wrote this "how to" article on alert when a MS-SQL 'job' fails
DESCRIPTION: This will alert you via LogicMonitor when a MS-SQL ‘job’ fails INSTRUCTIONS: Create a LogicMonitor “EventSource”. Named “SQL job failures” or similar. I suggest that you clone the default “SQL eventsource”. Set these settings: LogName = “Application” SourceName = “SQLSERVERAGENT” Level more urgent than “Information” Use Microsoft’s SQL Management Studio to set each job on each of your SQL servers so that when a job fails, it will write a message to the Windows Application event log (screenshot below) Right click on job and click “Properties” Click on “Notifications” on left pane Click on “Write to Windows event logs” checkbox and click “When the job fails” Test. I suggest you create a job with a bogus query and run it. Look in the Windows Event viewer and you should notice an EventID 208 at “warning” severity. Optional: In LogicMonitor you can create an Alert rule that notifies a specific person or team. Do this by selecting the datasource name of the eventSource you named above Note: I developed this and tested it with SQL version 2008-R2 but it will probably also work with newer versions. Below is screenshot showing how to create/clone a LogicMonitor EventSource Below shows how to set “Write to event log” on each SQL Job Below shows what the alert looks like in LogicMonitor.23Views1like2Comments