Dans le cadre d’un portage de règle de supervision, les trois scripts ci-dessous ont été crées pour remonter l’état de:
- Un Availability Group
- Un Availability Replica
- Un Database Replica
En pratique ils utilisent a peu près la même requête SQL. Ils contiennent des éléments propre a l’api scom mais peuvent bien sur être adapté pour être utilisé indépendamment.
Ci-dessous les 3 scripts et l’exemple du code du premier, Check_SQL_AO_AvailabilityGroupStatus_Query_Version.ps1.
#######################################################################################
#
#
# Script: SQLAlwaysOnAvailGroupStatus.ps1
# Purpose: Shows AlwaysOn Availability Group Status
#
# Parameters:
# $DBServer: ShortName of DB Server
# $InstanceFullName: Name of SQL Instance
# $AvailGroupName: Availability Group Name
#
#
########################################################################################
param (
$Arguments,
[string]$DBServer,
[string]$InstanceFullName,
[string]$AvailGroupName
)
# Create local variables from override value
.([Scriptblock]::Create($Arguments))
$Scriptname = "SQLAlwaysOnAvailabilityReplicaStatus.ps1"
# Name of Instance
$ServerInstance = $InstanceFullName.Split('\')[1]
#Determine TcpPort Used for Instance
try
{
$TcpPort = Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.$ServerInstance\MSSQLServer\SuperSocketNetLib\Tcp\IpAll" | select TcpPort -exp tcpport
}
catch
{
$Message = "Error during Retrieve of TCP Port Used - Check the execution of the script"
write-host -f Yellow $Message
$PropertyBag.AddValue("State","WARNING")
$PropertyBag.AddValue("Message",$Message)
$PropertyBag.AddValue("ReplicaName",$AvailabilityReplicaName)
$PropertyBag
Exit 1
}
# Scom Object and Property Bag
$api = New-Object -comObject “MOM.ScriptAPI”
$PropertyBag = $api.CreatePropertyBag()
# Function GetAODBRepStatus
Function GetAOAvailGroupStatus
{
param(
[string]$TargetComputer=$DBServer,
$global:Source = "$DBServer\$ServerInstance",
[string]$sqlCommand =
$("
SELECT
AO_AG.name as AvailGroupName
,HADR_AO_AGS.synchronization_health as AvailGroup_SyncHealth
,HADR_AO_AGS.synchronization_health_desc as AvailGroup_SyncHealth_Desc
--,SYSDB.name as DBName
--,AO_AVREP.replica_server_name
--,HADR_AVAIL_REP_STATE.synchronization_health as AvailReplica_SyncHealth
--,HADR_AVAIL_REP_STATE.synchronization_health_desc as AvailReplica_SyncHealth_Desc
--,HADR_DB_REP_STATE.synchronization_state as DBReplica_SyncState
--,HADR_DB_REP_STATE.synchronization_state_desc as DBReplica_SyncState_Desc
--,HADR_DB_REP_STATE.synchronization_health_desc as DBReplica_SyncHealth_Desc
FROM
[sys].[availability_databases_cluster] AO_DB_CLUS
--INNER JOIN sys.databases SYSDB on CAST(SYSDB.group_database_id AS VARCHAR(50)) = CAST(AO_DB_CLUS.group_database_id AS VARCHAR(50))
INNER JOIN sys.dm_hadr_database_replica_states HADR_DB_REP_STATE on CAST(HADR_DB_REP_STATE.group_database_id AS VARCHAR(50)) = CAST(AO_DB_CLUS.group_database_id AS VARCHAR(50))
--INNER JOIN sys.dm_hadr_availability_replica_states HADR_AVAIL_REP_STATE on HADR_AVAIL_REP_STATE.group_id = HADR_DB_REP_STATE.group_id
INNER JOIN sys.availability_groups AO_AG on AO_AG.group_id = HADR_DB_REP_STATE.group_id
INNER JOIN sys.dm_hadr_availability_group_states HADR_AO_AGS on HADR_AO_AGS.group_id = AO_AG.group_id
--INNER JOIN [sys].[availability_replicas] AO_AVREP on AO_AVREP.replica_id = HADR_DB_REP_STATE.replica_id
WHERE AO_AG.name = '$AvailGroupName'
GROUP BY
AO_AG.name
,HADR_AO_AGS.synchronization_health
,HADR_AO_AGS.synchronization_health_desc
"
)
)
Try
{
$global:connectionString = "Data Source=$Source,$TcpPort;" +
"Integrated Security=SSPI; " +
"Initial Catalog=master"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$connection.Open()
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
}
catch
{
write-host -F Red $("Error during sql connection - check the credentials used").ToUpper()
#exit 1
}
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$set = New-Object System.Data.DataSet
$adapter.Fill($Set) | Out-Null
$connection.Close()
$Set.Tables
}
# Execute function and get data
try
{
[array]$AvailGroup = GetAOAvailGroupStatus
}
catch
{
$Message = "WARNING - Error during Connection to master database or execution of sql query"
write-host -F Yellow $Message
$PropertyBag.AddValue("State","WARNING")
$PropertyBag.AddValue("Message",$Message)
$PropertyBag.AddValue("DBServer",$DBServer)
$PropertyBag.AddValue("connectstring",$connectionString)
$PropertyBag.AddValue("AvailGroupName",$AvailGroupName)
$PropertyBag.AddValue("AvailGroupStatus","no_data")
$PropertyBag
exit 1
}
if (!($AvailGroup))
{
$Message = "WARNING - Error - No Availability Group have been found"
write-host -F Yellow $Message
$PropertyBag.AddValue("State","WARNING")
$PropertyBag.AddValue("Message",$Message)
$PropertyBag.AddValue("DBServer",$DBServer)
$PropertyBag.AddValue("AvailGroupName","no_data")
$PropertyBag.AddValue("AvailGroupStatus","no_data")
$PropertyBag
exit 1
}
try
{
$AvailGroupState = $AvailGroup | select AvailGroup_SyncHealth_Desc -ExpandProperty AvailGroup_SyncHealth_Desc
}
catch
{
$Message = "Error during Retrieve of Availability Group State"
Write-Host -ForegroundColor Yellow $Message
$PropertyBag.AddValue("State","WARNING")
$PropertyBag.AddValue("Message",$Message)
$PropertyBag.AddValue("DBServer",$DBServer)
$PropertyBag.AddValue("AvailGroupName",$AvailGroupName)
$PropertyBag.AddValue("AvailGroupStatus","no_data")
$PropertyBag
exit 1
}
"AVAILABILITY GROUP: $AvailGroupName"
If ($AvailGroupState -eq "Healthy")
{
$Message = "OK - Status of $AvailGroupName Availability Group is Healthy"
write-host -f Green $Message
$PropertyBag.AddValue("State","OK")
$PropertyBag.AddValue("Message",$Message)
$PropertyBag.AddValue("DBServer",$DBServer)
$PropertyBag.AddValue("AvailGroupName",$AvailGroupName)
$PropertyBag.AddValue("AvailGroupStatus","Healthy")
$PropertyBag
Exit 0
}
ElseIf ($AvailGroupState -eq "Error")
{
$Message = "CRITICAL - Status of $AvailGroupName Availability Group is Error"
write-host -f Red $Message
$PropertyBag.AddValue("State","CRITICAL")
$PropertyBag.AddValue("Message",$Message)
$PropertyBag.AddValue("DBServer",$DBServer)
$PropertyBag.AddValue("AvailGroupName",$AvailGroupName)
$PropertyBag.AddValue("AvailGroupStatus","Error")
$PropertyBag
Exit 0
}
Else {
$Message = "WARNING - Status of $AvailGroupName Availability Group cannot be determined"
write-host -f yellow $Message
$PropertyBag.AddValue("State","WARNING")
$PropertyBag.AddValue("Message",$Message)
$PropertyBag.AddValue("DBServer",$DBServer)
$PropertyBag.AddValue("AvailGroupName",$AvailGroupName)
$PropertyBag.AddValue("AvailGroupStatus","no_data")
$PropertyBag
Exit 1
}
########################################################################################