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/>

        <WorkingDirectory/>

        <CommandLine>powershell.exe -NoLogo -NoProfile -Noninteractive "$ep = get-executionpolicy; if ($ep -gt 'RemoteSigned') {set-executionpolicy -Scope Process remotesigned} &amp; '$$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 



$arAssemblies = @(

			"\PowerShell\Modules\SQLPS\Microsoft.AnalysisServices.PowerShell.Provider.dll",

			"\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSProvider.dll",

            "\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll")



function Main(){



	$msg  = [string]::Empty



	#	

	# Prepare MOM API and property bag object

	#

	$api = New-Object -comObject "MOM.ScriptAPI"

	$bag = $api.CreatePropertyBag()



	$sqlConnection = $null



	try

	{

		ImportModuleActionSqlModules $arAssemblies



		$log = @{}

	

		$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	

		$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)

		$ServerObj = New-Object Microsoft.SqlServer.Management.Smo.Server($serverConnection)



		$ServerObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.AvailabilityGroup], $true)

		$ServerObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.AvailabilityReplica], $true)

		$ServerObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.DatabaseReplicaState], $true)



		$serverObj.AvailabilityGroups | foreach  {

			# Availability Group health

			$availabilityGroup = $_

			Test-SqlAvailabilityGroup -NoRefresh -ShowPolicyDetails -InputObject $availabilityGroup | Select Name, Result | foreach {

				$agPropertyName = "{0}-{1}" -f $availabilityGroup.Name, $_.Name

				$bag.AddValue($agPropertyName, $_.Result)

				$log.Add($agPropertyName, $_.Result)

			}



			# Availability Replica health

			$availabilityGroup.AvailabilityReplicas | foreach {

				$replicaName = $_.Name

				Test-SqlAvailabilityReplica -NoRefresh -ShowPolicyDetails -InputObject $_ | Select Name, Result | foreach {

					$arPropertyName = "{0}-{1}-{2}" -f $availabilityGroup.Name, $replicaName, $_.Name

					$bag.AddValue($arPropertyName, $_.Result)

					$log.Add($arPropertyName, $_.Result)

				}

			}

		

			# Database Replica State health

			$availabilityGroup.DatabaseReplicaStates | foreach { 

				$drname = "{0}.{1}" -f $_.AvailabilityReplicaServerName, $_.AvailabilityDatabaseName

			

				Test-SqlDatabaseReplicaState -NoRefresh -ShowPolicyDetails -InputObject $_ | Select Name, Result | foreach {

					$drPropertyName = "{0}-{1}-{2}" -f $availabilityGroup.Name, $drName, $_.Name

					$bag.AddValue($drPropertyName, $_.Result)

					$log.Add($drPropertyName, $_.Result)

				}

			}

		}

		

		$log.GetEnumerator() | %{ 

			$msg += "{0} = {1}{2}" -f $_.Name, $_.Value, [Environment]::NewLine 

		}

		#debug $api.LogScriptEvent("Workflow succeeded! ", $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg)

	}

	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)

}



#SQL2016Constants.ps1



$MANAGEMENT_PACK_VERSION = "6.7.20.0"



$ManagementGroupName = '$Target/ManagementGroup/Name$'

$ManagementGroupID = '$Target/ManagementGroup/Id$'



#AlwaysOnCommon.ps1



#Extracts computer address from Sql Mirroring endpoint Uri

#Uri format: TCP://&lt;system-address&gt;:&lt;port&gt;

function GetAddressFromUri {

	param(

		[string] $uri

	)

    $match = [regex]::Match($uri, '^TCP://(?:(?&lt;address&gt;[^:/]+)|(?:\[(?&lt;address&gt;[^/\]]+\]))):\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 = @( 

		(get-itemproperty -path 'hklm:\Software\Microsoft\Microsoft SQL Server\130\Tools\ClientSetup\' | Select SQLPath).SQLPath ,

		(get-itemproperty -path 'hklm:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\130\Tools\ClientSetup\' | Select SQLPath).SQLPath )

	

	if (!(ImportSQLModules $arAssemblies $paths '130\Tools'))	{

	    Import-Module SQLPS -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null

	    if(!(IsModuleLoaded 'SQLPS'))

	    {

	        throw 'Unable to load SQLPS modules'

	    }

	}

}



function IsModuleLoaded([string]$include) { 

    $modules = Get-Module

    foreach($module in $modules){

		if($module.Name.Contains($include))	{

		    return $true

		}

    }

    return $false

}



function ImportSQLModules {  

    param (

		$arAssemblies,

		$paths,

		[string]$include)   



    $flRet = $false

    foreach($path in $paths){ 

        if(![string]::IsNullOrEmpty($path) -and $path.Contains($include)){

            foreach($assemblyName in $arAssemblies){

                $filename = $path + $assemblyName

				if(Test-Path $filename){

					Import-Module $filename -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null

					$flRet = $true;

				}

            }

        }

    }	

    return $flRet

}



#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){

	

	$connectionString = [string]::Empty

	$lastError = $null

	$errorColl = @()

	$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 ""

		return SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 15 $debug_user $debug_password

	}

	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

					return SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password

				}

				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]

						return SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password

					}

					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

						return SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password

					}

					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

		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.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 | Select -ExpandProperty $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;

    }

	

    $value = Get-ItemProperty -Path $path -Name $key -ErrorAction SilentlyContinue| Select -ExpandProperty $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>