MSSQL 2016: Two State System Policy Monitor Type
Microsoft.SQLServer.2016.AlwaysOn.SystemPolicyScriptMonitorType (UnitMonitorType)
Two state script monitor type with Powershell script Data Source. Used to monitor System Policies.
Element properties: Member Modules:
Overrideable Parameters:
Source Code: <UnitMonitorType ID="Microsoft.SQLServer.2016.AlwaysOn.SystemPolicyScriptMonitorType" RunAs="GPMP!Microsoft.SQLServer.MonitoringAccount" Accessibility="Internal">
<MonitorTypeStates>
<MonitorTypeState ID="HealthState"/>
<MonitorTypeState ID="ErrorState"/>
</MonitorTypeStates>
<Configuration>
<IncludeSchemaTypes>
<SchemaType>Windows!Microsoft.Windows.PowerShellSchema</SchemaType>
</IncludeSchemaTypes>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" minOccurs="1" name="Interval" type="xsd:integer"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" minOccurs="1" name="TimeoutSeconds" type="xsd:integer"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" minOccurs="1" name="PolicyName" type="xsd:string"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" minOccurs="1" name="InstanceName" type="xsd:string"/>
</Configuration>
<OverrideableParameters>
<OverrideableParameter ID="Interval" Selector="$Config/Interval$" ParameterType="int"/>
<OverrideableParameter ID="TimeoutSeconds" Selector="$Config/TimeoutSeconds$" ParameterType="int"/>
</OverrideableParameters>
<MonitorImplementation>
<MemberModules>
<DataSource ID="DSa" TypeID="System!System.CommandExecuterPropertyBagSource">
<IntervalSeconds>$Config/Interval$</IntervalSeconds>
<ApplicationName>%systemroot%\System32\windowspowershell\v1.0\powershell.exe</ApplicationName>
<WorkingDirectory/>
<CommandLine>-NoLogo -NoProfile -Noninteractive "$ep = get-executionpolicy; if ($ep -gt 'RemoteSigned') {set-executionpolicy -Scope Process remotesigned} & '$$file/AvailabilityGroupMonitoring.ps1$$' '$Config/InstanceName$'</CommandLine>
<SecureInput/>
<TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>
<RequireOutput>true</RequireOutput>
<Files>
<File>
<Name>AvailabilityGroupMonitoring.ps1</Name>
<Contents><Script> param($InstanceName)
#TODO: Discuss event id
$SCRIPT_EVENT_ID = 4212
$DEBUG_MODULE = "AvailabilityGroupMonitoring.ps1"
$DEBUG_SA = $null
$DEBUG_PWD = $null
#Event Severity values
$INFORMATION_EVENT_TYPE = 0
$ERROR_EVENT_TYPE = 1
function Main(){
$msg = [string]::Empty
#
# Prepare MOM API and property bag object
#
$api = New-Object -comObject "MOM.ScriptAPI"
$bag = $api.CreatePropertyBag()
$sqlConnection = $null
try
{
$loadedAssemblies = LoadSmoAssemblies
$pathArray = $InstanceName -split "\\"
$ComputerName = ($pathArray[0] -split "\.")[0]
$InstanceName = "MSSQLSERVER"
if ($pathArray.Length -gt 1)
{
$InstanceName = $pathArray[1]
}
#if service is not in running state when exit without any error
$service = GetSQLServiceName $InstanceName
$state = GetServiceState $ComputerName $service $InstanceName
if(($state -ne "Running") -and ($state -ne "Unknown"))
{
$api.CreatePropertyBag()
return
}
$initialDataSource = BuildDataSourceFromParts $ComputerName $InstanceName
$sqlConnection = SmartConnect $initialDataSource "master" $ComputerName $InstanceName $DEBUG_SA $DEBUG_PWD
$srvConnectionWrapped = InitSmoServerConnection $loadedAssemblies $sqlConnection
$srvConnection = ($srvConnectionWrapped).PSObject.BaseObject
$serverObj = InitSmoServer $loadedAssemblies $srvConnection
$policyStore = InitSmoPolicyStore $loadedAssemblies $srvConnection
$agList = GetEmptyListOfSmoObjects $loadedAssemblies
$arList = GetEmptyListOfSmoObjects $loadedAssemblies
$drList = GetEmptyListOfSmoObjects $loadedAssemblies
$serverObj.AvailabilityGroups | foreach {
$ag = $_
$agList.Add($_)
$ag.AvailabilityReplicas | ?{ AvailabilityReplicaIsLocal $_.Name $ComputerName $InstanceName } | %{
$arList.Add($_)
}
$ag.DatabaseReplicaStates | ?{ AvailabilityReplicaIsLocal $_.AvailabilityReplicaServerName $ComputerName $InstanceName } | %{
$drList.Add($_)
}
}
$agTargetsArr = $agList.ToArray()
$arTargetsArr = $arList.ToArray()
$drTargetsArr = $drList.ToArray()
$agEvalResults = $policyStore.Evaluate($agTargetsArr)
$arEvalResults = $policyStore.Evaluate($arTargetsArr)
$drEvalResults = $policyStore.Evaluate($drTargetsArr)
ProcessEvalResults $bag $agTargetsArr $agEvalResults ${function:GetAgBagKey}
ProcessEvalResults $bag $arTargetsArr $arEvalResults ${function:GetArBagKey}
ProcessEvalResults $bag $drTargetsArr $drEvalResults ${function:GetDrBagKey}
}
catch {
$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module: {1} Version: {2}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE, $MANAGEMENT_PACK_VERSION
$msg += "Error occurred during Always On monitoring.{0}Computer:{1} {0}Reason: {2} {0}Position:{3} {0}Offset:{4}" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message, $_.InvocationInfo.ScriptLineNumber, $_.InvocationInfo.OffsetInLine
$msg += "{0}Detailed error output: {1}" -f [Environment]::NewLine, [String]::Join("{0}--------{0}" -f [Environment]::NewLine, $Error.ToArray())
$api.LogScriptEvent($header, $SCRIPT_EVENT_ID, $ERROR_EVENT_TYPE, $msg)
}
finally {
if($sqlConnection -ne $null) {
$sqlConnection.Dispose()
}
}
$api.Return($bag)
}
function GetSqlClrTypesVersions{
$primaryVersion = '13'
$typesVersions = @('13','14','12','11')
$pathTemplates = @('HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\RefCount\SQLSysClrTypes{0}',
'HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\RefCount\SQLSysClrTypes{0}')
$versions = @()
foreach($tv in $typesVersions){
foreach($pt in $pathTemplates){
$path = $pt -f $tv
if(Test-Path $path){
$versions += $tv
break
}
}
}
if($versions.Length -eq 0){
$versions += $primaryVersion
}
return $versions
}
function GetSmoAssemblyFullName{
param(
[string] $shortName,
[string] $version
)
return "{0}, Version={1}.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -f $shortName, $version
}
function LoadSmoAssemblies{
$versions = GetSqlClrTypesVersions
$assemblies = @{Smo = $null; ConnectionInfo = $null; HadrDmf = $null}
$success = $false
foreach($version in $versions){
try {
$assemblies['ConnectionInfo'] = [System.Reflection.Assembly]::Load((GetSmoAssemblyFullName 'Microsoft.SqlServer.ConnectionInfo' $version))
$assemblies['Smo'] = [System.Reflection.Assembly]::Load((GetSmoAssemblyFullName 'Microsoft.SqlServer.Smo' $version))
$assemblies['HadrDmf'] = [System.Reflection.Assembly]::Load((GetSmoAssemblyFullName 'Microsoft.SqlServer.Management.HadrDMF' $version))
$success = $true
break
}
catch{
}
}
if(!$success){
throw 'Unable to load SMO assemblies'
}
return $assemblies
}
function InitSmoServerConnection{
param(
[System.Collections.IDictionary] $assemblies,
[System.Data.SqlClient.SqlConnection] $sqlConnection
)
$srvConnType = $assemblies['ConnectionInfo'].GetType("Microsoft.SqlServer.Management.Common.ServerConnection")
$srvConnCi = $srvConnType.GetConstructor(@($sqlConnection.GetType()))
return $srvConnCi.Invoke(@($sqlConnection))
}
function InitSmoServer {
param(
[System.Collections.IDictionary] $assemblies,
$smoServerConnection
)
$scType = $smoServerConnection.GetType()
if($scType.FullName -ne "Microsoft.SqlServer.Management.Common.ServerConnection"){
throw "Unable to init Smo.Server: invalid ServerConnection object"
}
$smoServerType = $assemblies['Smo'].GetType("Microsoft.SqlServer.Management.Smo.Server")
$smoServerCi = $smoServerType.GetConstructor(@($scType))
$agType = $assemblies['Smo'].GetType("Microsoft.SqlServer.Management.Smo.AvailabilityGroup")
$arType = $assemblies['Smo'].GetType("Microsoft.SqlServer.Management.Smo.AvailabilityReplica")
$drType = $assemblies['Smo'].GetType("Microsoft.SqlServer.Management.Smo.DatabaseReplicaState")
$smoServer = $smoServerCi.Invoke(@($smoServerConnection))
$smoServer.SetDefaultInitFields($agType, $true)
$smoServer.SetDefaultInitFields($arType, $true)
$smoServer.SetDefaultInitFields($drType, $true)
return $smoServer
}
function InitSmoPolicyStore{
param(
[System.Collections.IDictionary] $assemblies,
$smoServerConnection
)
$scType = $smoServerConnection.GetType()
if($scType.FullName -ne "Microsoft.SqlServer.Management.Common.ServerConnection"){
throw "Unable to init HadrDMF.AlwaysOnPolicyStore: invalid ServerConnection object"
}
$policyStoreType = $assemblies['HadrDmf'].GetType('Microsoft.SqlServer.Management.HadrDMF.AlwaysOnPolicyStore')
$policyStoreCi = $policyStoreType.GetConstructor(@($scType))
$policyStore = $policyStoreCi.Invoke(@($smoServerConnection))
$policyStore.LoadAllAlwaysOnPolicies()
return $policyStore
}
function GetEmptyListOfSmoObjects{
param(
[System.Collections.IDictionary] $assemblies
)
$smoObjectType = $assemblies['Smo'].GetType("Microsoft.SqlServer.Management.Smo.SqlSmoObject")
$listType = [Type]::GetType('System.Collections.Generic.List`1')
$genericListType = $listType.MakeGenericType($smoObjectType)
$ci = $genericListType.GetConstructor(@())
$list = $ci.Invoke($null)
return (,$list)
}
function AvailabilityReplicaIsLocal{
param(
[string] $arName,
[string] $localMachineName,
[string] $localInstanceName
)
if([string]::IsNullOrEmpty($arName) -or [string]::IsNullOrEmpty($localMachineName) -or [string]::IsNullOrEmpty($localInstanceName)){
return $false
}
$parts = $arName.Split('\');
$res = ($parts[0] -ieq $localMachineName) -and
(($parts.Length -eq 1 -and $localInstanceName -ieq 'MSSQLSERVER') -or
($parts.Length -eq 2 -and $parts[1] -ieq $localInstanceName))
return $res
}
function GetAgBagKey{
param(
$agObj,
$policyName
)
return "{0}-{1}" -f $agObj.Name, $policyName
}
function GetArBagKey{
param(
$arObj,
$policyName
)
return "{0}-{1}-{2}" -f $arObj.Parent.Name, $arObj.Name, $policyName
}
function GetDrBagKey{
param(
$drObj,
$policyName
)
$drname = "{0}.{1}" -f $drObj.AvailabilityReplicaServerName, $drObj.AvailabilityDatabaseName
return "{0}-{1}-{2}" -f $drObj.AvailabilityGroupName, $drName, $policyName
}
function ProcessEvalResults{
param(
$bag,
$targets,
$evalResults,
$keyGetter
)
for($i = 0; $i -lt $targets.Length; $i++){
foreach($pair in $evalResults[$i].PolicyEvaluationResults.GetEnumerator()){
$policyName = $pair.Key.Name
[string]$bagKey = $keyGetter.Invoke($targets[$i], $policyName) | Select -First 1
$bag.AddValue($bagKey, $pair.Value.HealthState.ToString() -ieq 'Healthy')
}
}
}
#SQL2016Constants.ps1
$MANAGEMENT_PACK_VERSION = "7.0.4.0"
$ManagementGroupName = '$Target/ManagementGroup/Name$'
$ManagementGroupID = '$Target/ManagementGroup/Id$'
#AlwaysOnCommon.ps1
#Extracts computer address from Sql Mirroring endpoint Uri
#Uri format: TCP://<system-address>:<port>
function GetAddressFromUri {
param(
[string] $uri
)
$match = [regex]::Match($uri, '^TCP://(?:(?<address>[^:/]+)|(?:\[(?<address>[^/\]]+\]))):\d{1,5}$', 'IgnoreCase')
if(!$match.Success){
throw 'Provided URI ''' + $uri + ''' has invalid format'
}
return $match.Groups['address'].Value
}
function QueryWmiWithRetry {
param(
[string] $query,
[string] $namespace,
[string] $computerName = $env:COMPUTERNAME,
[int] $retryCount = 3,
[int] $timeoutMs = 100
)
for($i = 1; $i -le $retryCount; $i++) {
try {
return Get-WmiObject -ComputerName $computerName -Namespace $namespace -Query $query -ErrorAction Stop
}
catch {
if($i -ge $retryCount) {
throw
}
else {
Start-Sleep -Milliseconds $timeoutMs
}
}
}
}
function GetFqdnDns {
param([string] $computerName)
$dnsName = [System.Net.Dns]::GetHostByName($serverName).HostName
if($dnsName.IndexOf('.') -lt 0){
throw 'Returned name ''' + $dnsName + ''' is not FQDN'
}
return $dnsName
}
function CombineFqdnName {
param(
[string] $dnsName,
[string] $domainName
)
if ([string]::IsNullOrEmpty($dnsName) -or [string]::IsNullOrEmpty($domainName)) {
return $dnsName
}
return $dnsName + '.' + $domainName
}
# Get FQDN of machine via WMI
# We have to use WMI instead of using DNS
# because we often deal with NetBios host names and Sql Server mirroring endpoint Urls
# and in these cases DNS may fail or returned name may be not FQDN
function GetFqdnWmi {
param(
[string] $computerName
)
$comp = QueryWmiWithRetry 'SELECT DNSHostName, Domain FROM Win32_ComputerSystem' 'ROOT\CIMV2' $computerName | Select -First 1
return CombineFqdnName $comp.DNSHostName $comp.Domain
}
function GetPrincipalNameInfo {
param(
[string] $netbiosName,
[string] $targetHostName,
[Nullable[bool]] $isClustered = $null
)
$connectionHostName = $targetHostName;
$compObj = QueryWmiWithRetry 'SELECT Name, DNSHostName, Domain FROM Win32_ComputerSystem' 'ROOT\CIMV2' $connectionHostName | Select -First 1
$compDomainName = $compObj.Domain
if (($isClustered -eq $null -or !$isClustered.Value) -and $compObj -ne $null){
if($compObj.Name -eq $netbiosName){
return New-Object PSObject -Property @{DnsHostName = $compObj.DNSHostName; DomainName = $compObj.Domain; PrincipalName = (CombineFqdnName $compObj.DNSHostName $compObj.Domain); NetbiosName = $compObj.Name; IsClustered = $false}
}
}
$hostIsClustered = (QueryWmiWithRetry 'SELECT Name FROM __NAMESPACE WHERE Name = ''MSCluster''' 'ROOT' $connectionHostName) -ne $null
if(($isClustered -eq $null -or $isClustered.Value) -and $hostIsClustered){
$searchObj = (QueryWmiWithRetry 'SELECT PrivateProperties FROM MSCluster_Resource WHERE Type = ''Network Name''' 'ROOT\MSCluster' $connectionHostName) | Select -ExpandProperty 'PrivateProperties' | Where {
$_.Name -eq $netbiosName
}
if($searchObj -ne $null){
[string]$domainName = $searchObj.DnsSuffix
if([string]::IsNullOrEmpty($domainName)){
$domainName = $compDomainName
}
return New-Object PSObject -Property @{DnsHostName = $searchObj.DnsName; DomainName = $domainName; PrincipalName = (CombineFqdnName $searchObj.DnsName $domainName); NetbiosName = $searchObj.Name; IsClustered = $true}
}
}
throw 'No Principal name was found for NetBios name ''' + $netbiosName + ''''
}
function SmartGetFqdnAlwaysOn {
param(
[string] $serverName = $null,
[string] $endpointUrl = $null
)
if([string]::IsNullOrEmpty($serverName)) {
throw 'Server name is empty'
}
$errMessages = @()
$fqdn = $null
try {
$fqdn = GetFqdnDns $serverName
return $fqdn
}
catch {
$errMessages += 'Unable to get FQDN via DNS using name ''' + $serverName + ''': ' + $_.Exception.Message
}
try {
$fqdnInfo = GetPrincipalNameInfo $serverName
return $fqdnInfo.PrincipalName
}
catch {
$errMessages += 'Unable to get FQDN via WMI using name ''' + $serverName + ''': ' + $_.Exception.Message
}
if(![string]::IsNullOrEmpty($endpointUrl)) {
$endpointServerName = $null
try {
$endpointServerName = GetAddressFromUri $endpointUrl
$fqdnInfo = GetPrincipalNameInfo $serverName $endpointServerName
return $fqdnInfo.PrincipalName
}
catch {
$errMessages += 'Unable to get FQDN via WMI using host address ''' + $endpointServerName + ''' and server name ''' + $serverName + ''': ' + $_.Exception.Message
}
}
throw ('Unable to get AlwaysOn Replica''s host FQDN. Attempt log:{0} {1}' -f [Environment]::NewLine, ($errMessages -join ([Environment]::NewLine + ' ')))
}
function SmartGetFqdnAlwaysOnEx {
param(
[string] $serverName,
[string] $endpointUrl
)
$statePath = 'SQL2016MP\SqlHostNames'
$fqdnValueName = (EscapeCacheValueName $serverName) + '_fqdn'
$cacheExpirationTime = 43200
$fqdn = TryGetValueFromCache $statePath $fqdnValueName $cacheExpirationTime
if(![string]::IsNullOrEmpty($fqdn)){
return $fqdn
}
$fqdn = SmartGetFqdnAlwaysOn $serverName $endpointUrl
PutValueToCache $statePath $fqdnValueName $fqdn | Out-Null
return $fqdn
}
function GetEmptyNonSnapshotData($api, $mE, $tID) {
$discoveryData = $api.CreateDiscoveryData(0, $mE, $tID)
$discoveryData.IsSnapshot = $false
return $discoveryData
}
function ImportModuleActionSqlModules($arAssemblies){
$paths = GetSqlToolsPaths
if (!(ImportSQLModules $arAssemblies $paths)) {
Import-Module SQLPS -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null
if(!(IsModuleLoaded 'SQLPS'))
{
throw 'Unable to load SQLPS modules'
}
}
}
function GetSqlToolsPaths(){
$pathVersions = @('130','140','120','110')
$pathTemplates = @('HKLM:\Software\Microsoft\Microsoft SQL Server\{0}\Tools\ClientSetup\',
'HKLM:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\{0}\Tools\ClientSetup\')
$includeTemplate = '{0}\Tools'
$paths = @()
foreach($version in $pathVersions){
$checkInclude = $includeTemplate -f $version
foreach($template in $pathTemplates){
$path = $template -f $version
$sqlPathObj = Get-ItemProperty -Path $path -Name 'SQLPath' -ErrorAction SilentlyContinue
if($sqlPathObj -ne $null){
$sqlPath = $sqlPathObj.SQLPath
if(![string]::IsNullOrEmpty($sqlPath) -and $sqlPath.Contains($checkInclude)){
$paths += $sqlPath
}
}
}
}
return $paths
}
function IsModuleLoaded([string]$include) {
$modules = Get-Module
foreach($module in $modules){
if($module.Name.Contains($include)) {
return $true
}
}
return $false
}
function ImportSQLModules($arAssemblies, $paths) {
$assembliesToLoad = New-Object System.Collections.ArrayList ((,$arAssemblies))
foreach($path in $paths){
foreach($assemblyName in $assembliesToLoad.ToArray()){
$filename = $path + $assemblyName
if(Test-Path $filename){
$module = Import-Module $filename -ErrorAction SilentlyContinue -WarningAction SilentlyContinue -PassThru
if($module -eq $null){
continue
}
$assembliesToLoad.Remove($assemblyName)
if($assembliesToLoad.Count -eq 0){
return $true
}
}
}
}
return $false
}
#SqlConnectionCommon.ps1
function SqlTcpPortIsEmpty($tcpPort) {
return [string]::IsNullOrEmpty($tcpPort) -or $tcpPort -eq '0'
}
function GetDataSource($dataSource, $tcpPort) {
$targetDataSource = $dataSource
if (!(SqlTcpPortIsEmpty $tcpPort)){
$nameParts = $dataSource.Split("{\}")
$targetDataSource = $nameParts[0] + "," + $tcpPort
}
return $targetDataSource
}
function GetSqlServerHostName(
[string] $strDNSComputerName,
[string] $instanceName,
[string] $namespace) {
$serviceName = GetSQLServiceName $instanceName
$escapedServiceName = EscapeWmiString $serviceName
$advProperties = Get-WmiObject -ComputerName $strDNSComputerName -Namespace ("ROOT\Microsoft\SqlServer\$namespace") -Class "SqlServiceAdvancedProperty" -Property "PropertyName", "PropertyNumValue", "PropertyStrValue" -Filter "SqlServiceType = 1 AND ServiceName = '$escapedServiceName' AND (PropertyName = 'Clustered' OR PropertyName = 'VSNAME')" -ErrorAction Stop
$isClustered = ($advProperties | Where {$_.PropertyName -eq "CLUSTERED"} | Select -First 1).PropertyNumValue
$hostName = 0
if($isClustered -eq 0){
$hostName = (Get-WmiObject -ComputerName $strDNSComputerName -Namespace ("ROOT\Microsoft\SqlServer\$namespace") -Class "SqlService" -Filter "SQLServiceType = 1 AND ServiceName = '$escapedServiceName'" -ErrorAction Stop | Select -First 1).HostName
}
else{
$hostName = ($advProperties | Where {$_.PropertyName -eq "VSNAME"} | Select -First 1).PropertyStrValue
}
return New-Object PSObject -Property @{HostName = $hostName; IsClustered = $isClustered}
}
function GetSqlServerHostNameEx(
[string] $strDNSComputerName,
[string] $instanceName,
[string] $namespace) {
$statePath = "SQL2016MP\SqlHostNames"
$hostValueName = EscapeCacheValueName $strDNSComputerName
$isClusteredValueName = $hostValueName + "_IsClustered"
$cacheExpirationTime = 7200
$isClustered = $null
$hostName = TryGetValueFromCache $statePath $hostValueName $cacheExpirationTime
if(![string]::IsNullOrEmpty($hostName)){
$isClustered = TryGetValueFromCache $statePath $isClusteredValueName $cacheExpirationTime
}
if(![string]::IsNullOrEmpty($hostName) -and ![string]::IsNullOrEmpty($isClustered) -and '01'.Contains($isClustered)){
return New-Object PSObject -Property @{HostName = $hostName; IsClustered = if($isClustered -eq '0') {$false} else {$true}}
}
$hostNameData = GetSqlServerHostName $strDNSComputerName $instanceName $namespace
PutValueToCache $statePath $hostValueName $hostNameData.HostName | Out-Null
PutValueToCache $statePath $isClusteredValueName "$(if($hostNameData.IsClustered){1} else {0})" | Out-Null
return $hostNameData
}
function BuildDataSourceFromParts($computerName, $instanceName, $tcpPort){
$dataSource = $computerName
if($instanceName -ne "MSSQLSERVER"){
$dataSource = "$computerName\$instanceName"
}
return GetDataSource $dataSource $tcpPort
}
function BuildConnectionString(
[String] $dataSource,
[string] $databaseName,
[String] $timeout = 100,
[String] $user = $null,
[String] $password = $null) {
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$builder["Data Source"] = $dataSource
$builder["Initial Catalog"] = $databaseName
$builder['Connection Timeout'] = $timeout
if (($user.Length -ne 0) -and ($password.Length -ne 0)) {
$builder["User ID"] = $user
$builder["Password"] = $password
} else {
$builder["Integrated Security"] = 'SSPI'
}
return $builder.ConnectionString
}
function GetEnabledSqlServerProtocols($computerName, $instanceName) {
$protocolsWmi = Get-WmiObject -ComputerName $computerName -Namespace "root\Microsoft\SqlServer\ComputerManagement13" -Class 'ServerNetworkProtocol' -Filter "InstanceName = '$instanceName'"
$protocolsArr = @()
if($protocolsWmi -ne $null)
{
foreach($protocol in $protocolsWmi){
if($protocol.Enabled){
$protocolsArr+=($protocol.ProtocolName.ToLower())
}
}
}
return (,$protocolsArr)
}
function GetSqlServerTcpIpSettings([string] $instanceName){
$ipSettings = @{}
$settingsWmi = Get-WmiObject -Namespace 'root\Microsoft\SqlServer\ComputerManagement13' -Class 'ServerNetworkProtocolProperty' -Filter "ProtocolName = 'Tcp' and InstanceName = '$instanceName'"
$listenAllObj = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.PropertyName -eq "ListenOnAllIPs")}
$listenAll = $false
if($listenAllObj.PropertyNumVal -eq 1){
$listenAll = $true
}
if($listenAll) {
$portArr = @()
$tcpIpAll = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and ($_.IPAddressName -eq "IPAll") -and (($_.PropertyName -eq "TcpPort") -or ($_.PropertyName -eq "TcpDynamicPorts")) -and ($_.PropertyStrVal -ne '')}
foreach($port in $tcpIpAll)
{
$splittedPorts = $port.PropertyStrVal.Split("{,}", [System.StringSplitOptions]::RemoveEmptyEntries) | %{$_.Trim()} | ?{-not (SqlTcpPortIsEmpty $_)}
$portArr += $splittedPorts
}
$ipSettings.Add("IPAll", $portArr);
}
else{
$ipAddresses = ($settingsWmi | Where-Object { ($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.IPAddressName -ne "")-and($_.PropertyName -eq "Enabled") -and ($_.PropertyNumVal -eq 1)})
foreach($ipAddress in $ipAddresses){
$ipAddressName = $ipAddress.IPAddressName
$ip = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.IPAddressName -eq $ipAddressName)-and( $_.PropertyName -eq "IpAddress") -and ($_.PropertyStrVal -ne '')} | select -ExpandProperty PropertyStrVal
$ports = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.IPAddressName -eq $ipAddressName)-and( ($_.PropertyName -eq "TcpPort") -or ($_.PropertyName -eq "TcpDynamicPorts")) -and ($_.PropertyStrVal -ne '')}
$portArr = @()
foreach($port in $ports)
{
$splittedPorts = $port.PropertyStrVal.Split("{,}", [System.StringSplitOptions]::RemoveEmptyEntries) | %{$_.Trim()} | ?{-not (SqlTcpPortIsEmpty $_)}
$portArr += $splittedPorts
}
$ipSettings.Add($ip, $portArr);
}
}
return New-Object -TypeName PSObject -Property @{'ListenAllIPs' = $listenAll; 'IpSettings' = $ipSettings }
}
function SqlTryToConnectAndValidate(
[String] $dataSource,
[string] $databaseName,
[String] $hostName,
[String] $instanceName,
[string] $timeout = 30,
[String] $user = $null,
[String] $password = $null){
$connectionString = BuildConnectionString $dataSource $databaseName $timeout $user $password
$query = "SELECT SERVERPROPERTY('MachineName') AS ServerName, @@servicename AS InstanceName"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection ($connectionString)
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter ($query,$sqlConnection)
$dataSet = New-Object System.Data.DataSet
try{
$sqlConnection.Open()
$sqlAdapter.Fill($dataSet) | Out-Null
$res = $dataSet.Tables | Select -First 1
if ($res -ne $null) {
$queryServerName = ($res | Select -ExpandProperty ServerName).ToUpperInvariant()
$queryInstanceName = ($res | Select -ExpandProperty InstanceName).ToUpperInvariant()
if (($hostName.ToUpperInvariant() -eq $queryServerName) -and ($instanceName.ToUpperInvariant() -eq $queryInstanceName)) {
return $sqlConnection;
}
throw "Connection target check failed: connected to $hostName\$instanceName, but got $queryServerName\$queryInstanceName."
}
}
catch{
$sqlConnection.Dispose()
throw
}
}
function SmartConnectNTE($connectionDataSource, $databaseName, $machineName, $instanceName, $debug_user = $null, $debug_password = $null){
try{
return SmartConnect $connectionDataSource $databaseName $machineName $instanceName $debug_user $debug_password
}
catch{}
return $null
}
function SmartConnect($connectionDataSource, $databaseName, $machineName, $instanceName, $debug_user = $null, $debug_password = $null){
$hostNamesPath = "SQL2016MP\SqlHostNames"
$hostValueName = EscapeCacheValueName $machineName
$instanceValueName = EscapeCacheValueName $instanceName
$paramsPath = "SQL2016MP\SmartConnectParams\{0}\{1}" -f $hostValueName, $instanceValueName
$cacheExpirationTime = [int]::MaxValue
$netBiosHostName = TryGetValueFromCache $hostNamesPath $hostValueName $cacheExpirationTime
$targetDataSource = TryGetValueFromCache $paramsPath "TargetDataSource" $cacheExpirationTime
$timeout = TryGetValueFromCache $paramsPath "ConnectionTimeout" $cacheExpirationTime
$lastError = $null
$errorColl = @()
if($netBiosHostName -ne $null -and $targetDataSource -ne $null -and $timeout -ne $null) {
try {
return SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName $timeout $debug_user $debug_password
}
catch {
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
}
$connectionString = [string]::Empty
$targetDataSource = ""
$netBiosHostNameData = GetSqlServerHostNameEx $machineName $instanceName 'ComputerManagement13'
$netBiosHostName = $netBiosHostNameData.HostName
$dnsHostName = $machineName.Split(".")[0]
$enabledServerProtocols = $null
$connStr = $connectionDataSource
if(($netBiosHostName -eq $env:COMPUTERNAME) -and ($netBiosHostName -ne $dnsHostName) -and (!$netBiosHostNameData.IsClustered)) {
$enabledServerProtocols = GetEnabledSqlServerProtocols $machineName $instanceName
if($enabledServerProtocols -contains "sm") {
$connStr = 'lpc:' + (BuildDataSourceFromParts $netBiosHostName $instanceName '')
}
}
try{
$targetDataSource = GetDataSource $connStr ""
SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 15 $debug_user $debug_password
PutValueToCache $paramsPath "TargetDataSource" $targetDataSource | Out-Null
PutValueToCache $paramsPath "ConnectionTimeout" 15 | Out-Null
return
}
catch{
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
if($enabledServerProtocols -eq $null){
$enabledServerProtocols = GetEnabledSqlServerProtocols $machineName $instanceName
}
if ($enabledServerProtocols -contains "tcp") {
$tcpIpSettings = GetSqlServerTcpIpSettings $instanceName
$pathArray = $connectionDataSource.Split("{'\'}")
$targetName = $pathArray[0]
if ($tcpIpSettings.ListenAllIps) {
foreach($port in $tcpIpSettings.IpSettings["IPAll"]){
try {
$targetDataSource = GetDataSource $targetName $port
SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password
PutValueToCache $paramsPath "TargetDataSource" $targetDataSource | Out-Null
PutValueToCache $paramsPath "ConnectionTimeout" 10 | Out-Null
return
}
catch {
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
}
}
else{
$upc = New-Object "System.Collections.Generic.HashSet[string]"
foreach($portArr in $tcpIpSettings.IpSettings.Values){
if(($portArr.Length -gt 0) -and $upc.Add($portArr[0])){
try {
$targetDataSource = GetDataSource $targetName $portArr[0]
SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password
PutValueToCache $paramsPath "TargetDataSource" $targetDataSource | Out-Null
PutValueToCache $paramsPath "ConnectionTimeout" 10 | Out-Null
return
}
catch {
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
}
}
foreach($pair in $tcpIpSettings.IpSettings.GetEnumerator()){
foreach($port in $pair.Value){
try {
$targetDataSource = GetDataSource $pair.Key $port
SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password
PutValueToCache $paramsPath "TargetDataSource" $targetDataSource | Out-Null
PutValueToCache $paramsPath "ConnectionTimeout" 10 | Out-Null
return
}
catch {
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
}
}
}
}
throw [Exception] ("Cannot connect to the target Sql Server instance.`nConnection log:`n" + [string]::Join([Environment]::NewLine, $errorColl)) #, $lastError
}
function SqlQueryTablesCommon($sqlConnection, $inputQuery, [bool]$useDbName, $dbName, $queryParams){
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$dataSet = New-Object System.Data.DataSet
try {
$query = $inputQuery
if($useDbName) {
$query = $inputQuery -f ($dbName -replace '"', '""')
}
$sqlCmd.CommandText = $query
$sqlCmd.Connection = $sqlConnection
$sqlCmd.CommandTimeout = 60000
for ($i = 0; $i -lt $queryParams.Length; $i++) {
$sqlCmd.Parameters.AddWithValue(("@p" + ($i+1)), [object]($queryParams[$i])) | Out-Null
}
$sqlAdapter.SelectCommand = $sqlCmd
$sqlAdapter.Fill($dataSet) | Out-Null
if(($dataSet.Tables.Count -eq 0) -or ($DataSet.Tables[0] -eq $null))
{
throw 'Can not query data from {0} database. Please check read permissions for this db.' -f $SqlConnection.Database
}
}
finally{
$sqlAdapter.Dispose()
$sqlCmd.Dispose()
}
return $dataSet.Tables
}
function SqlQueryTables($sqlConnection, $query) {
return SqlQueryTablesCommon $sqlConnection $query $false $null $args
}
function SqlConnQueryTables($sqlConnection, $query, $dbName) {
return SqlQueryTablesCommon $sqlConnection $query $true $dbName $args
}
function SqlQueryRows($sqlConnection, $query){
return (SqlQueryTablesCommon $sqlConnection $query $false $null $args | Select -First 1).Rows
}
function SqlConnQueryRows($sqlConnection, $query, $dbName){
return (SqlQueryTablesCommon $sqlConnection $query $true $dbName $args | Select -First 1).Rows
}
function SqlQueryScalar($SqlConnection, $query) {
$sqlAdapter = New-Object 'System.Data.SqlClient.SqlDataAdapter' ($query, $SqlConnection)
$dataSet = New-Object System.Data.DataSet
$sqlAdapter.SelectCommand.CommandTimeout = 60000
$SqlAdapter.Fill($dataSet) | Out-Null
if(($dataSet.Tables.Count -eq 0) -or ($DataSet.Tables[0] -eq $null))
{
throw 'Can not query data from {0} database. Please check read permissions for this db.' -f $SqlConnection.Database
}
return ($dataSet.Tables[0]|Select -first 1)[0]
}
#''' Returns -1: If DB is not in AlwaysOn
#''' Returns 0: If DB is in AlwaysOn and replica allow connections is NO
#''' Returns 1: If DB is in AlwaysOn and replica allow connections is YES
function AlwaysOnReplicaAllowConnections([System.Data.SqlClient.SqlConnection]$SqlConnection, $DatabaseID)
{
$query = " SELECT columns.id, CASE WHEN OBJECT_ID('sys.availability_replicas') IS NOT NULL THEN 1 ELSE 0 END AS HasAlwaysOn " +
" FROM master.sys.syscolumns columns where name = 'replica_id' and id = OBJECT_ID('sys.databases')"
$dr = SqlConnQueryTables $SqlConnection $query | Select -First 1
if($dr -eq $null -or $dr.HasAlwaysOn -ne 1) {
return -1
}
$query = "SELECT d.name, d.database_id, drs.is_primary_replica AS db_is_primary_replica
, CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica
, CASE WHEN drs.is_primary_replica = 1 THEN ar.primary_role_allow_connections ELSE ar.secondary_role_allow_connections END AS role_allow_connections
, CASE WHEN drs.is_suspended = 0 THEN -1 ELSE suspend_reason END AS db_suspended_state
FROM sys.databases as d
JOIN sys.dm_hadr_database_replica_states drs ON drs.database_id = d.database_id
JOIN sys.availability_replicas ar on d.replica_id = ar.replica_id
WHERE drs.is_local = 1 AND d.database_id = @p1"
$rdr = SqlQueryTables $SqlConnection $query $DatabaseID | Select -First 1
if($rdr -ne $null -and $rdr.is_replica -eq 1) {
if($rdr.role_allow_connections -le 1) {
return 0
}
else {
if($rdr.db_suspended_state -gt 0 -and (!$rdr.db_is_primary_replica -or ($rdr.db_is_primary_replica -and $rdr.db_suspended_state -ne 5))){
return 0
}
return 1
}
}
return -1
}
#Common.ps1
#-------------------------------------------------------------------------------
#The function returns service or "Unknown" state
#Input:
# server - compute name
# service - system service name
# InstanceName - sql server instance name
#Output:
# service state or "Unknown" state
function GetServiceState($server, $service, $InstanceName)
{
try {
if ($service -eq "MSSQL") {
$service = "MSSQL`${0}" -f $InstanceName
}
$namespace = "root/cimv2"
$obje = Get-WmiObject -Property 'Name,State' -Namespace $namespace -ComputerName $server -Class "win32_service" -Filter "Name = '$service'" -ErrorAction SilentlyContinue
if ($obje -ne $null) {
return $obje.State
}
}
catch {
}
return "Unknown"
}
function GetSQLServiceName($InstanceName)
{
if ($InstanceName -eq "MSSQLSERVER")
{
return "MSSQLSERVER"
}
return 'MSSQL$' + $InstanceName
}
function IsNullOrZero($count)
{
return ($count -eq 0) -or ($count -eq $null)
}
function LogEventDebug($message) {
if($DEBUG_MODE -eq 1) {
$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $message)
}
}
function ConvertToInt($v) {
if ($v -eq $null) {
return $null;
}
if ([int].IsInstanceOfType($v)) {
return $v;
}
$res = $null;
if([int]::TryParse($v.ToString(),[ref] $res)) {
return $res;
}
return $null;
}
function EscapeWmiString($str) {
return $str -replace "\\","\\" -replace "'","\'"
}
function CreatePathRecursive($path) {
if (Test-Path $path) {
return;
}
$parts = $path.Split('\');
$curPath = $parts[0];
for ($i=1;$i -lt $parts.Length;$i++) {
$curPath = $curPath + '\'+$parts[$i];
if (!(Test-Path $curPath)) {
New-Item -Path $curPath -ErrorAction SilentlyContinue|Out-Null
}
}
}
function PutValueToCache($path,$key, $value) {
if ($cacheExpirationTime -eq $null -or $cacheExpirationTime -eq 0) {
return;
}
$registryKey = $api.GetScriptStateKeyPath($ManagementGroupID);
$keyPath = "HKLM:\" + $registryKey + "\" + $path;
CreatePathRecursive $keyPath
try {
$creationKey = $key+"_CreationTime";
$ip = Get-ItemProperty -Path $keyPath -Name $creationKey -ErrorAction SilentlyContinue;
if ($ip -ne $null) {
Remove-ItemProperty -Path $keyPath -Name $creationKey
}
$ip = Get-ItemProperty -Path $keyPath -Name $key -ErrorAction SilentlyContinue;
# recreate key to avoid type problem
if ($ip -ne $null) {
Remove-ItemProperty -Path $keyPath -Name $key
}
# null - delete old entry
if ($value -ne $null) {
New-ItemProperty -Path $keyPath -Name $key -Value $value|Out-Null
$creationDate = [DateTime]::UtcNow.ToString('yyyy-MM-ddTHH:mm:ssZ',[System.Globalization.CultureInfo]::InvariantCulture)
New-ItemProperty -Path $keyPath -Name $creationKey -Value $creationDate|Out-Null
}
} catch {
LogEventDebug "Someone is refreshing data at $keyPath $key or no access"
}
LogEventDebug "Set at $keyPath $key value $value"
return $ip.$key;
}
# Returns all non-expired properties from specified path in cache as hashtable
function GetValuesFromCache($statePath, $cacheExpirationTime) {
function GetActualValue($name, $props) {
$creationKey = $name + "_CreationTime"
if (($props -eq $null) -or ($props[$creationKey] -eq $null)) {
return $null;
}
[ref]$creationDate = [DateTime]::MinValue;
[System.Globalization.CultureInfo]$ci = [System.Globalization.CultureInfo]::InvariantCulture;
$v = [System.DateTime]::TryParseExact($props[$creationKey].Value,'yyyy-MM-ddTHH:mm:ssK',$ci,[System.Globalization.DateTimeStyles]::None,$creationDate);
if (!$v) {
return $null;
}
$diff = [DateTime]::UtcNow - $creationDate.Value;
if ($diff.TotalSeconds -gt $cacheExpirationTime) {
return $null;
}
return $props[$name].Value;
}
$result = @{}
$registryKey = $api.GetScriptStateKeyPath($ManagementGroupID);
$path = "HKLM:\" + $registryKey + "\" + $statePath;
if (!(Test-Path $path) -or $cacheExpirationTime -eq $null -or $cacheExpirationTime -eq 0) {
return $result;
}
$ip = Get-ItemProperty -Path $path
$properties = $ip.psobject.Properties;
$msg = "Got from $path : ";
if($properties -ne $null) {
foreach ($property in $properties) {
$pn = $property.Name;
$pv = GetActualValue $pn $properties;
if ($pv -ne $null) {
$result[$pn] = $pv
$msg += "$pn :$pv ;"
}
}
}
LogEventDebug $msg
return $result;
}
function TryGetValueFromCache($statePath, $key, $cacheExpirationTime) {
$registryKey = $api.GetScriptStateKeyPath($ManagementGroupID);
$path = "HKLM:\" + $registryKey + "\" + $statePath;
if (!(Test-Path $path) -or $cacheExpirationTime -eq $null -or $cacheExpirationTime -eq 0) {
return $null;
}
$creationKey = $key + "_CreationTime"
$regObj = Get-ItemProperty -Path $path -Name $creationKey -ErrorAction SilentlyContinue
if ($regObj -eq $null) {
return $null;
}
$creationDateStr = $regObj.$creationKey
[ref]$creationDate = [DateTime]::MinValue;
[System.Globalization.CultureInfo]$ci = [System.Globalization.CultureInfo]::InvariantCulture;
$v = [System.DateTime]::TryParseExact($creationDateStr,'yyyy-MM-ddTHH:mm:ssK',$ci,[System.Globalization.DateTimeStyles]::None,$creationDate);
if (!$v) {
return $null;
}
$diff = [DateTime]::UtcNow - $creationDate.Value;
if ($diff.TotalSeconds -gt $cacheExpirationTime) {
LogEventDebug "Cache entry at $path key $key got expired"
return $null;
}
$valueObj = Get-ItemProperty -Path $path -Name $key -ErrorAction SilentlyContinue
if ($valueObj -eq $null) {
return $null;
}
$value = $valueObj.$key
LogEventDebug "Got from $path key $key value $value"
return $value
}
function EscapeCacheValueName([string] $name) {
return $name -replace "_", "__"
}
Main
</Script> </Contents>
<Unicode>true</Unicode>
</File>
</Files>
</DataSource>
<ConditionDetection ID="HealthFilter" TypeID="System!System.ExpressionFilter">
<Expression>
<Or>
<Expression>
<Not>
<Expression>
<Exists>
<ValueExpression>
<XPathQuery Type="Boolean">Property[@Name="$Config/PolicyName$"]</XPathQuery>
</ValueExpression>
</Exists>
</Expression>
</Not>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="Boolean">Property[@Name="$Config/PolicyName$"]</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="Boolean">true</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</Or>
</Expression>
</ConditionDetection>
<ConditionDetection ID="ErrorFilter" TypeID="System!System.ExpressionFilter">
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="Boolean">Property[@Name="$Config/PolicyName$"]</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="Boolean">false</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</ConditionDetection>
</MemberModules>
<RegularDetections>
<RegularDetection MonitorTypeStateID="HealthState">
<Node ID="HealthFilter">
<Node ID="DSa"/>
</Node>
</RegularDetection>
<RegularDetection MonitorTypeStateID="ErrorState">
<Node ID="ErrorFilter">
<Node ID="DSa"/>
</Node>
</RegularDetection>
</RegularDetections>
</MonitorImplementation>
</UnitMonitorType>