Environment

-> Requirement will be to create an Always On latency report that will contain multiple database servers that are part of Availability group.

-> Data from multiple database servers will be placed on a centralized server from where the report will be generated.

-> We will use below procedure to create the Always On latency reports.

-> On the centralized database server we should perform below actions.

-> Create a database called JBRepository on the centralized server that will store data for the report.

create database JBRepository 

-> Create a table called AlwaysON_Latency on database JBRepository that will be used to store data related to Always on latency,

USE [JBRepository]
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[AlwaysON_Latency](    
     [Primary_Replica] nvarchar(500) NULL,
     [Secondary_Replica] nvarchar(500) NULL,
     [database_name] nvarchar(500) NULL,
     [Date_Collection] [datetime] NOT NULL,
     [last_commit_time] [datetime] NULL,
     [DR_commit_time] [datetime] NULL,
     [lag_in_seconds] [int] NULL
 ) ON [PRIMARY]
 GO

-> Create a table called AlwaysON_Latency_SQLServer_Instance on database JBRepository that will be used to store SQL Server instances from where Always On latency details will be pulled,

USE [JBRepository]
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[AlwaysON_Latency_SQLServer_Instance](
     [AlwaysON_Latency_ID] [int] IDENTITY(1,1) primary key,
     [SQLServerInstance] nvarchar(500) NULL,   
 ) ON [PRIMARY]
 GO

-> Create stored procedure spINSERT_AlwaysON_Latency_NEW_SQL_Inst that will be used to insert SQL Server instance details to table AlwaysON_Latency_SQLServer_Instance,

USE [JBRepository]
 GO
 CREATE OR ALTER PROCEDURE [dbo].[spINSERT_AlwaysON_Latency_NEW_SQL_Inst]
 @SQLServerInstance nvarchar(100),@Debug bit
 AS
 /*
 -- Object Name: dbo.spINSERT_AlwaysON_Latency_NEW_SQL_Inst
 -- Purpose: Insert a record into table [dbo].[AlwaysON_Latency_SQLServer_Instance]
 -- Database: [JBRepository]
 -- Dependent Objects: None
 -- if @Debug = 0 -> Insert data into table [dbo].[AlwaysON_Latency_SQLServer_Instance]
 -- if @Debug = 1 -> Select data from table [dbo].[AlwaysON_Latency_SQLServer_Instance] to validate the existing data
 -- @SQLServerInstance - Provide the output of @@servername from Alwayson Primary and Secondary replica
 -- Usage -- Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBSAG1',@debug=0
 */
 SET NOCOUNT ON
 if (@Debug = 0)
 begin
     IF EXISTS (select 1 from [dbo].[AlwaysON_Latency_SQLServer_Instance] where [SQLServerInstance] = @SQLServerInstance)
         BEGIN
             Print 'SQL Server Instance already exists in table [dbo].[AlwaysON_Latency_SQLServer_Instance]. Exiting.'
         END
         ELSE
         BEGIN
             INSERT INTO [dbo].AlwaysON_Latency_SQLServer_Instance VALUES (@SQLServerInstance)
         END
 end
 else if (@Debug = 1)
     begin
         select * from [dbo].[AlwaysON_Latency_SQLServer_Instance] where [SQLServerInstance] like '%'+@SQLServerInstance+'%'
     end
 GO

-> Create stored procedure spDELETE_AlwaysON_Latency_NEW_SQL_Inst that will be used to remove unmwated SQL Server instance details from table AlwaysON_Latency_SQLServer_Instance,

USE [JBRepository]
 GO
 CREATE OR ALTER PROCEDURE [dbo].[spDELETE_AlwaysON_Latency_NEW_SQL_Inst]
 @SQLServerInstance nvarchar(100),@Debug bit
 AS
 /*
 -- Object Name: dbo.spDELETE_AlwaysON_Latency_NEW_SQL_Inst
 -- Purpose: Insert a record into table [dbo].[AlwaysON_Latency_SQLServer_Instance]
 -- Database: [JBRepository]
 -- Dependent Objects: None
 -- if @Debug = 0 -> Insert data into table [dbo].[AlwaysON_Latency_SQLServer_Instance]
 -- if @Debug = 1 -> Select data from table [dbo].[AlwaysON_Latency_SQLServer_Instance] to validate the existing data
 -- @SQLServerInstance - Provide the output of @@servername from Alwayson Primary and Secondary replica
 -- Usage -- EXEC [spDELETE_AlwaysON_Latency_NEW_SQL_Inst] 'JBSAG2',@debug=0
 */
 SET NOCOUNT ON
 if (@Debug = 0)
 begin
     IF EXISTS (select 1 from [dbo].[AlwaysON_Latency_SQLServer_Instance] where [SQLServerInstance] = @SQLServerInstance)
         BEGIN
             delete from [dbo].[AlwaysON_Latency_SQLServer_Instance] where [SQLServerInstance] = @SQLServerInstance
             delete from [dbo].[AlwaysON_Latency] where Primary_Replica = @SQLServerInstance
         END
         ELSE
         BEGIN
             Print 'Specified SQL Server Instance does not exist in table [dbo].[AlwaysON_Latency_SQLServer_Instance]. Exiting.'         
         END
 end
 else if (@Debug = 1)
     begin
         select * from [dbo].[AlwaysON_Latency_SQLServer_Instance] where [SQLServerInstance] like '%'+@SQLServerInstance+'%'
     end
 GO

-> Use below query to insert required SQL Server instances into table AlwaysON_Latency_SQLServer_Instance for which Alwayson Latency details are required. In my case I am inserting 6 SQL Server instances,

Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBPUB-PRIMARY',@debug=0
 Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBPUB-DR',@debug=0
 Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBSUB-PRIMARY',@debug=0
 Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBSUB-DR',@debug=0
 Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBDIST-PRIMARY',@debug=0
 Exec [spINSERT_AlwaysON_Latency_NEW_SQL_Inst] 'JBDIST-DR',@debug=0

-> Execute below query to verify inserted SQL Server instances,

use [JBRepository]
select * from AlwaysON_Latency_SQLServer_Instance

-> Save below query on the centralized server to a location. In my case I am placing it in location F:\Automation\AlwaysOn_Latency\.

WITH DR_CTE ( replica_server_name, database_name, last_commit_time)
 AS
 (
 select ar.replica_server_name, database_name, rs.last_commit_time from master.sys.dm_hadr_database_replica_states  rs inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id where replica_server_name != @@servername
 )
 select  ar.replica_server_name as 'Primary_Replica', DR_CTE.replica_server_name as 'Secondary_Replica',dcs.database_name, getdate() as Date_Collection ,rs.last_commit_time, DR_CTE.last_commit_time 'DR_commit_time', abs(datediff(ss,  DR_CTE.last_commit_time, rs.last_commit_time)) 'lag_in_seconds'
 from master.sys.dm_hadr_database_replica_states  rs 
 inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id
 inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id
 inner join DR_CTE on DR_CTE.database_name = dcs.database_name 
 where ar.replica_server_name = @@servername
 order by lag_in_seconds desc

-> Save below power shell script that will fetch Alwayson Latency data from all SQL Server instances and place the latency data in table AlwaysON_Latency in location F:\Automation\AlwaysOn_Latency\.

<#Script to pull Alwayson on Latency data from mutiple SQL Databases from multiple Server#>
  <# Ver 1.0 #>
  Import-Module SqlServer
  <#Variables - details of the connection, tsqlcommand and parameters #>
  $RepositorySQLInstance = "JBPUB-PRIMARY"
  $RepositoryDB = "JBRepository"
  $connectionString = "server=$RepositorySQLInstance;database=$RepositoryDB;trusted_connection=true;"; 
  $tsqlcommand = "select [SQLServerInstance] from [dbo].[AlwaysON_Latency_SQLServer_Instance]"; 
  <#SQL Connection - connection to SQL server #>
  $sqlConnection = new-object System.Data.SqlClient.SqlConnection; 
  $sqlConnection.ConnectionString = $connectionString; 
  <#SQL Command - set up the SQL call #>
  $sqlCommand = New-Object System.Data.SqlClient.SqlCommand; 
  $sqlCommand.Connection = $sqlConnection; 
  $sqlCommand.CommandText = $tsqlcommand; 
  <#SQL Adapter - get the results using the SQL Command #>
  $sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter 
  $sqlAdapter.SelectCommand = $sqlCommand 
  $dataSet = new-object System.Data.Dataset 
  $recordCount = $sqlAdapter.Fill($dataSet) 
  <#Close SQL Connection #>
  $sqlConnection.Close(); 
  <#Get single table from dataset #>
  $data = $dataSet.Tables[0] 
  <#Provide the name of the Local Instance to store the data#>
  $LocalInstance = $RepositorySQLInstance 
  <#Provide the name of the database to store the data#>
  $Repositorydb=$RepositoryDB
  <#Looping through each Instance#> 
  foreach ($SqlInstance in $data) 
  { 
  Invoke-Sqlcmd -inputfile "F:\Automation\AlwaysOn_Latency\Always_On_Latency.sql" -ServerInstance $SqlInstance.SQLServerInstance -Database master| Write-SqlTableData -ServerInstance $LocalInstance -DatabaseName $Repositorydb -SchemaName dbo -TableName AlwaysON_Latency -Force 
  Write-Host "Always on Latency details collected from" $SqlInstance.SQLServerInstance 
  }
  $purge_table_AlwaysON_Latency = "delete from [dbo].[AlwaysON_Latency] where Date_Collection < (getdate()-90)"
  Invoke-Sqlcmd -ServerInstance $RepositorySQLInstance -Database $RepositoryDB -Query $purge_table_AlwaysON_Latency

-> Remember to change below details before executing the Power shell query,

Line 5 – Change $RepositorySQLInstance to your centralized SQL server instance.

Line 6 – Change $RepositoryDB to appropriate database.

Line 32 – Change -inputfile to the correct location where Always_On_Latency.sql is placed.

-> Try executing the script manually,

-> Use below query to check the Always ON latency data,

use [JBRepository]
select * from AlwaysON_Latency

-> I have configured a SQL Server agent job to execute every 5 mins that will execute this powershell script. This in turn will collect Always on latency data for all applicable SQL server instance.

-> I use below Power BI file to check this as a report,

-> Below is what I see in my lab machine.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.