Listes de ressources partagées du moteur de base de données de la source de données

Microsoft.SQLServer.2014.DataSource.ResourcePool (DataSourceModuleType)

Cette source de données découvre toutes les listes de ressources partagées pour une instance spécifique de moteur de base de données SQL Server 2014.

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2014.DataSource.ResourcePool" Accessibility="Internal" RunAs="Microsoft.SQLServer.2014.SQLDiscoveryAccount">

  <Configuration>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="IntervalSeconds" type="xsd:integer"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="SyncTime" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="ComputerID" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="ComputerName" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="SQLInstanceName" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="TimeoutSeconds" type="xsd:int"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="OnlyInMemoryOLTPPools" type="xsd:string"/>

  </Configuration>

  <OverrideableParameters>

    <OverrideableParameter ID="IntervalSeconds" ParameterType="int" Selector="$Config/IntervalSeconds$"/>

    <OverrideableParameter ID="SyncTime" ParameterType="string" Selector="$Config/SyncTime$"/>

    <OverrideableParameter ID="TimeoutSeconds" ParameterType="int" Selector="$Config/TimeoutSeconds$"/>

    <OverrideableParameter ID="OnlyInMemoryOLTPPools" ParameterType="string" Selector="$Config/OnlyInMemoryOLTPPools$"/>

  </OverrideableParameters>

  <ModuleImplementation>

    <Composite>

      <MemberModules>

        <DataSource ID="DS" TypeID="System!System.Discovery.Scheduler">

          <Scheduler>

            <SimpleReccuringSchedule>

              <Interval>$Config/IntervalSeconds$</Interval>

              <SyncTime>$Config/SyncTime$</SyncTime>

            </SimpleReccuringSchedule>

            <ExcludeDates/>

          </Scheduler>

        </DataSource>

        <ProbeAction ID="Script" TypeID="Windows!Microsoft.Windows.PowerShellDiscoveryProbe">

          <ScriptName>ResourcePoolDataSource.ps1</ScriptName>

          <ScriptBody><Script>#ResourcePoolDataSource.ps1

param( $MapElement, $TargetID, $computerID, $computerName, $sqlInstanceName, $connectionString, $serviceName, $tcpPort, $onlyInMemoryOLTPPools)



#TODO: Discuss event id

$SCRIPT_EVENT_ID = 4201 

$DISCOVERY_ERROR_EVENT_ID = 7105

$DEBUG_MODE = 0

$DEBUG_MODULE = "ResourcePoolDataSource.ps1"

$DEBUG_MSG = ""

$DEBUG_SA = $null

$DEBUG_PWD = $null



#Event Severity values

$INFORMATION_EVENT_TYPE = 0

$ERROR_EVENT_TYPE = 1 



function Main(){

	ModuleAction $MapElement $TargetID $computerID $computerName $sqlInstanceName $connectionString $tcpPort $serviceName $onlyInMemoryOLTPPools

}



function ModuleAction

{

	param(

        $MapElement, 

        $TargetID,

		$computerID,		

		$computerName, 

		$sqlInstanceName,

        $connectionString, 

		$tcpPort,

		$serviceName,

		$onlyInMemoryOLTPPools

	)



    

	$DEBUG_MSG = "MapElement: $MapElement TargetID: $TargetID computerID: $computerID ComputerName: $computerName InstanceName: $sqlInstanceName"

	$DEBUG_MSG += "ConnectionString: $connectionString TcpPort: $tcpPort ServiceName: $serviceName" + [Environment]::NewLine

    # 

	# Prepare MOM API and property bag object

	#

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

    $discoveryData = $api.CreateDiscoveryData(0, $MapElement, $TargetID)

    #if service is not in running state when exit without any error

	$state = GetServiceState $computerName $serviceName $sqlInstanceName

	if(($state -ne "Running") -and ($state -ne "Unknown"))

	{

		return

	}



	$sqlConnection = $null



    try {	       

        $onlyInMemoryOLTPPoolsVal = $null

		if ([bool]::TryParse($onlyInMemoryOLTPPools, [ref]$onlyInMemoryOLTPPoolsVal) -ne $true) {

		   throw "Unable to parse the value of property 'OnlyInMemoryOLTPPools'. The value must be 'true' or 'false'."		  

		} 

		

		$query = "WITH dbrp_cte AS(

					SELECT DISTINCT 

						resource_pool_id

					FROM sys.databases

				)

				SELECT 

					[name],

					pool_id,

					min_cpu_percent,

					max_cpu_percent,

					cap_cpu_percent,

					min_memory_percent,

					max_memory_percent

					,CASE

						WHEN pool_id = 1 THEN 0

						WHEN pool_id = 2 THEN 1

						WHEN dbrp_cte.resource_pool_id IS NOT NULL THEN 1

						ELSE 0

					END AS [ManagesInMemoryOLTP] 

				FROM sys.dm_resource_governor_resource_pools rp

				LEFT JOIN dbrp_cte ON dbrp_cte.resource_pool_id = rp.pool_id"



		$sqlConnection = SmartConnect $connectionString "master" $computerName $sqlInstanceName $DEBUG_SA $DEBUG_PWD

        

	    $res = SqlQueryTables $sqlConnection $query

        

        if($DEBUG_MODE -eq 1) {

            $DEBUG_MSG += $connectionString

            $DEBUG_MSG += [Environment]::NewLine

        }

        

        $res | foreach {

            if( ([string]::IsNullOrEmpty($_.name) -ne $true) -and (($onlyInMemoryOLTPPoolsVal -eq $false) -or ($_.ManagesInMemoryOLTP -eq 1))) {

                $name = $_.name

                $pool_id = $_.pool_id

                $min_cpu_percent = $_.min_cpu_percent

                $max_cpu_percent = $_.max_cpu_percent

                $cap_cpu_percent = $_.cap_cpu_percent

                $min_memory_percent = $_.min_memory_percent

                $max_memory_percent = $_.max_memory_percent

				$ManagesInMemoryOLTP = $_.ManagesInMemoryOLTP

        

                if($DEBUG_MODE -eq 1) {

                    $DEBUG_MSG += "name = " + $name

                    $DEBUG_MSG += " pool_id = " + $pool_id

                    $DEBUG_MSG += " min_cpu_percent = " + $min_cpu_percent

                    $DEBUG_MSG += " max_cpu_percent = " + $max_cpu_percent

                    $DEBUG_MSG += " cap_cpu_percent = " + $cap_cpu_percent

                    $DEBUG_MSG += " min_memory_percent = " + $min_memory_percent

                    $DEBUG_MSG += " max_memory_percent = " + $max_memory_percent

					$DEBUG_MSG += " ManagesInMemoryOLTP = " + $ManagesInMemoryOLTP

                    $DEBUG_MSG += [Environment]::NewLine

                }



                $pool = $null

                if ($pool_id -eq 1) {

                    $pool = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.InternalPool']$")

                    if($DEBUG_MODE -eq 1) {

                        $DEBUG_MSG += "Internal Pool was selected  " + [Environment]::NewLine

                    }

                }

                elseif($pool_id -eq 2 ){

                    $pool = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.DefaultPool']$")

                    if($DEBUG_MODE -eq 1) {

                        $DEBUG_MSG += "Default Pool was selected  " + [Environment]::NewLine

                    }

                }

                else {

                    $pool = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.UserDefinedPool']$")

                    if($DEBUG_MODE -eq 1) {

                        $DEBUG_MSG += "Dedicated Pool was selected  " + [Environment]::NewLine

                    }

                }



                $pool.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $computerID)

                $pool.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.ServerRole']/InstanceName$", $sqlInstanceName)

				$pool.AddProperty("$MPElement[Name='System!System.Entity']/DisplayName$", $name)

				$pool.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.ResourcePool']/PoolID$", $pool_id)

                $pool.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.ResourcePool']/Name$", $name)

                $pool.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.ResourcePool']/MinCpuPercent$", $min_cpu_percent)

                $pool.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.ResourcePool']/MaxCpuPercent$", $max_cpu_percent)

                $pool.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.ResourcePool']/CapCpuPercent$", $cap_cpu_percent)

                $pool.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.ResourcePool']/MinMemoryPercent$", $min_memory_percent)

                $pool.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.ResourcePool']/MaxMemoryPercent$", $max_memory_percent)	

				$pool.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.ResourcePool']/ManagesInMemoryOLTP$", $ManagesInMemoryOLTP)

					

                $discoveryData.AddInstance($pool)

            }

        }   



		$discoveryData



        if($DEBUG_MODE -eq 1) {

            $api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $DEBUG_MSG)	           

        }

    }

    catch {

		$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0}  Module: {1} Version: {2}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE, $MANAGEMENT_PACK_VERSION

		$DEBUG_MSG += "Error occurred during Request count data source executing.{0}Computer:{1} {0}Reason: {2} {0}Position:{3} {0}Offset:{4} {0}Instance:{5}" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message, $_.InvocationInfo.ScriptLineNumber, $_.InvocationInfo.OffsetInLine, $sqlInstanceName

		$DEBUG_MSG += "{0}Detailed error output: {1}" -f [Environment]::NewLine, [String]::Join("{0}--------{0}" -f [Environment]::NewLine, $Error.ToArray())

		$api.LogScriptEvent($header, $DISCOVERY_ERROR_EVENT_ID, $ERROR_EVENT_TYPE, $DEBUG_MSG)	

	}

	finally {

		if($sqlConnection -ne $null) {

			$sqlConnection.Dispose()

		}

	}

}



#SQL2014Constants.ps1



$MANAGEMENT_PACK_VERSION = "7.0.2.0"



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

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



#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 = "SQL2014MP\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\ComputerManagement12" -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\ComputerManagement12' -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 'ComputerManagement12'

	$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.$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></ScriptBody>

          <SnapIns/>

          <Parameters>

            <Parameter>

              <Name>MapElement</Name>

              <Value>$MPElement$</Value>

            </Parameter>

            <Parameter>

              <Name>TargetID</Name>

              <Value>$Target/Id$</Value>

            </Parameter>

            <Parameter>

              <Name>computerID</Name>

              <Value>$Config/ComputerID$</Value>

            </Parameter>

            <Parameter>

              <Name>computerName</Name>

              <Value>$Config/ComputerName$</Value>

            </Parameter>

            <Parameter>

              <Name>sqlInstanceName</Name>

              <Value>$Config/SQLInstanceName$</Value>

            </Parameter>

            <Parameter>

              <Name>connectionString</Name>

              <Value>$Target/Host/Property[Type="Microsoft.SQLServer.2014.DBEngine"]/ConnectionString$</Value>

            </Parameter>

            <Parameter>

              <Name>serviceName</Name>

              <Value>$Target/Host/Property[Type="Microsoft.SQLServer.2014.DBEngine"]/ServiceName$</Value>

            </Parameter>

            <Parameter>

              <Name>tcpPort</Name>

              <Value>$Target/Host/Property[Type="Microsoft.SQLServer.2014.DBEngine"]/TcpPort$</Value>

            </Parameter>

            <Parameter>

              <Name>onlyInMemoryOLTPPools</Name>

              <Value>$Config/OnlyInMemoryOLTPPools$</Value>

            </Parameter>

          </Parameters>

          <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>

          <StrictErrorHandling>true</StrictErrorHandling>

        </ProbeAction>

      </MemberModules>

      <Composition>

        <Node ID="Script">

          <Node ID="DS"/>

        </Node>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.Discovery.Data</OutputType>

</DataSourceModuleType>