SQL Server 2014 In-Memory OLTP Pool Memory Consumption DataSource

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

DataSource returns memory consumption for SQL Server 2014 In-Memory OLTP Resource

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2014.DataSource.PoolMemoryConsumption" Accessibility="Internal" RunAs="SQL2014Core!Microsoft.SQLServer.2014.SQLProbeAccount">

  <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="ServerName" 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="PoolID" 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="ScriptDelayMsec" type="xsd:int"/>

  </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="ScriptDelayMsec" ParameterType="int" Selector="$Config/ScriptDelayMsec$"/>

  </OverrideableParameters>

  <ModuleImplementation>

    <Composite>

      <MemberModules>

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

          <Scheduler>

            <SimpleReccuringSchedule>

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

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

            </SimpleReccuringSchedule>

            <ExcludeDates/>

          </Scheduler>

        </DataSource>

        <ProbeAction ID="Probe" TypeID="Windows!Microsoft.Windows.PowerShellPropertyBagProbe">

          <ScriptName>GetHKPoolMemoryConsumption.ps1</ScriptName>

          <ScriptBody><Script>#GetHKPoolMemoryConsumption.ps1

param($computerName, $sqlInstanceName, $connectionString, $tcpPort, $delay, $serviceName)





#TODO: Discuss event id

$SCRIPT_EVENT_ID = 4201 

$DEBUG_MODE = 0

$DEBUG_MODULE = "GetHKPoolMemoryConsumption.ps1"

$DEBUG_MSG = ""

$DEBUG_SA = $null

$DEBUG_PWD = $null



#Event Severity values

$INFORMATION_EVENT_TYPE = 0

$ERROR_EVENT_TYPE = 1 



#debug information

function PrintDebug( $api, $error_msg )

{

    if($DEBUG_MODE -eq 1) {

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

        $DEBUG_MSG += [Environment]::NewLine

		$DEBUG_MSG += $error_msg

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

        $DEBUG_MSG	

    }

}



function BuildConnectionString(

    [String] $pHostName, 

    [String] $pInstanceName, 

    [String] $databaseName, 

    [String] $connectionString = $null,

    [String] $user = $null, 

    [String] $password = $null) 

{    

    if (($connectionString.Length -ne 0)) {

        return $connectionString

    } else {

		$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder

		$serverName = ""

		$namespace = "root\Microsoft\SqlServer\ComputerManagement12"

		$class = "ServerNetworkProtocolProperty"

		$serverName =  $pHostName + "\" + $pInstanceName

		$listenAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.PropertyName -eq "ListenOnAllIPs")}

		if($listenAll.PropertyNumVal -eq 1) {

			$tcpipAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {

							($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq "IPAll")-and($_.PropertyName -eq "TcpPort")}

				if($tcpipAll.PropertyStrVal -eq '') {

					$tcpipAll = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {

									($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq "IPAll")-and($_.PropertyName -eq "TcpDynamicPorts")}

				}

				if($tcpipAll.PropertyStrVal -ne '') {

						$serverName =  $serverName + "," + $tcpipAll.PropertyStrVal

				}

			}

			else {

			$ipAddressName = (Get-WmiObject -Namespace $namespace -Class $class | Where-Object {

								($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -ne "")-and($_.PropertyName -eq "Enabled") -and ($_.PropertyNumVal -eq 1)}) | select -first 1 | select -ExpandProperty IPAddressName

			if($ipAddressName -ne $null) {

				$tcp = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {

								($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq $ipAddressName)-and( ($_.PropertyName -eq "TcpPort") -or ($_.PropertyName -eq "TcpDynamicPorts")) -and ($_.PropertyStrVal -ne '')} | select -ExpandProperty PropertyStrVal

				$ip = Get-WmiObject -Namespace $namespace -Class $class | Where-Object {

								($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $pInstanceName) -and($_.IPAddressName -eq $ipAddressName)-and( $_.PropertyName -eq "IpAddress") -and ($_.PropertyStrVal -ne '')} | select -ExpandProperty PropertyStrVal

				if($ip -ne $null) {

					$serverName =  $ip + "," + $tcp

				}

				else {

					$serverName =  $serverName + "," + $tcp

				}

			}

		} 

		

		$builder["Data Source"] = $serverName

		$builder["Initial Catalog"] = $databaseName



		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 SqlTestDestination($connection, $serverName, $instanceName) {

	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

	$DataSet = New-Object System.Data.DataSet

	$SqlCmd.CommandText = "select SERVERPROPERTY('MachineName') as ServerName, @@servicename as InstanceName"

	$SqlCmd.Connection = $SqlConnection

	$SqlAdapter.SelectCommand = $SqlCmd

	$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()

        $serverNameWithoutDomain = $serverName

        $dotPosition = $serverName.IndexOf(".")

        if ($dotPosition -gt -1) {

            $serverNameWithoutDomain = $serverName.Substring(0, $dotPosition)

        }

        if (($serverNameWithoutDomain.ToUpperInvariant() -eq $queryServerName) -and ($instanceName.ToUpperInvariant() -eq $queryInstanceName)) {

            return;

        }

	}

	throw "Connection target check failed: connected to " + $serverName + "\\" + $instanceName + ", but got " + $queryServerName + "\\" + $queryInstanceName + "." 

}



function BuildConnectionStringWithPort(

        [String] $connectionString, 

        [int] $port, 

        [String] $user = $null, 

        [String] $password = $null) {

    $builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder

	if ($port -eq 0){

		$builder["Data Source"] = $connectionString

	} else {

		$builder["Data Source"] = $connectionString + "," + $port

	}

    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 ValidateConnectionString(

        [String] $connectionString, 

        [int] $port, 

		[String] $serverName, 

		[String] $instanceName, 

        [String] $user = $null, 

        [String] $password = $null) 

{

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

	try {

        $SqlConnection.ConnectionString = BuildConnectionStringWithPort $connectionString 0 $user $password

        $SqlConnection.Open()

		SqlTestDestination $SqlConnection $serverName $instanceName

        $SqlConnection.Close()

        return $SqlConnection.ConnectionString

	} catch {

		try	{

			if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {

				$SqlConnection.Close()

			}

			$SqlConnection.ConnectionString = BuildConnectionStringWithPort $connectionString $port $user $password

			$SqlConnection.Open()

			SqlTestDestination $SqlConnection $serverName $instanceName

			$SqlConnection.Close()

			return $SqlConnection.ConnectionString

		} catch {

			if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {

				$SqlConnection.Close()

			}

		}

	}

    return $null

}



function GetValidatedConnection(

        [String] $pHostName, 

		[String] $pInstanceName, 

		[String] $databaseName, 

		[String] $connectionString,

		[int] $port,

		[String] $user = $null, 

		[String] $password = $null) 

{



	$sqlConnectionString = $connectionString

    $sqlConnectionString = ValidateConnectionString $sqlConnectionString $port $pHostName $pInstanceName $user $password



	$connectionString = BuildConnectionString $pHostName $pInstanceName $databaseName $sqlConnectionString



	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

	try

	{

		$SqlConnection.ConnectionString = $connectionString

		$SqlConnection.Open()    

		return $SqlConnection

	}

	catch {

		if ($SqlConnection -ne $null)

		{

			if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {

				$SqlConnection.Close()

			}

			$SqlConnection.Dispose()

		}

	}

    return $null

}



# Parameters should be provided after $SqlConnection and $query

# Parameters should be named in the query text as $p1...$pN

function SqlQueryTables($SqlConnection, $query) {			

	

	$res = $null;

		

	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

	$DataSet = New-Object System.Data.DataSet

	

	#if an error takes place during execution when the function throws exception

    try {

		$SqlCmd.CommandText = $query

		$SqlCmd.Connection = $SqlConnection

		for	($i=0; $i -lt $args.length; $i++) {

            $ParameterName = "@p" + ($i+1)

			$Parameter = New-Object System.Data.SqlClient.SqlParameter($ParameterName, [object]($args[$i]))

			$SqlCmd.Parameters.Add($Parameter)

		}

		$SqlAdapter.SelectCommand = $SqlCmd

		$SqlAdapter.Fill($DataSet)|out-null		

		$res = $DataSet.Tables

		$SqlAdapter.Dispose()

		$SqlCmd.Dispose()

	}

	catch {

        if( $SqlAdapter -ne $null) {

            $SqlAdapter.Dispose()

        }

        if( $SqlCmd -ne $null) {

		    $SqlCmd.Dispose()

        }   

        throw $_.Exception

	}



	return $res

}



function SqlConnQueryTables($SqlConnection, $query, $dbName) {			

	

	$res = $null;

		

	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

	$DataSet = New-Object System.Data.DataSet

	

	#if an error takes place during execution when the function throws exception

    try {

		$safeName = $dbName -replace """", """"""

		$SqlCmd.CommandText = $query -f $safeName

		$SqlCmd.Connection = $SqlConnection

		$SqlAdapter.SelectCommand = $SqlCmd

		$SqlAdapter.Fill($DataSet)|out-null		

		$res = $DataSet.Tables

        $SqlAdapter.Dispose()

		$SqlCmd.Dispose()

    }

	catch {

        if( $SqlAdapter -ne $null)

        {

            $SqlAdapter.Dispose()

        }

        if( $SqlCmd -ne $null)

        {

		    $SqlCmd.Dispose()

        }   

        throw $_.Exception

	}



	return $res

}



function GetTotalPhysicalMemoryMB() {



	[double]$totalPhysicalMemory = 0

	$totalPhysicalMemory = (Get-WmiObject -Class Win32_ComputerSystem).TotalPhysicalMemory / 1024 / 1024

	return $totalPhysicalMemory

}



function GetMaxServerMemoryMB($SqlConnection) {

	

	$query = "SELECT [value_in_use] as [value] FROM sys.configurations WHERE name = 'max server memory (MB)' ORDER BY name OPTION (RECOMPILE);"

	

	$res = SqlQueryTables $SqlConnection $query	

	[double]$maxMemory = $res.Rows[0].value

	

	# if sql max memory isn't configured get system memory

	if ($maxMemory -eq 2147483647) {

		$maxMemory = GetTotalPhysicalMemoryMB

	}

	

	return $maxMemory

}





function GetAvailableHKMemoryMB($memoryMB)

{

    $percent = GetAvailableHKMemoryPercent($memoryMB)

    return [double](($memoryMB/100)*$percent)

}



function GetAvailableHKMemoryPercent($memoryMB)

{

    $percent = 0 

    if ($memoryMB -lt 8*1024) {

        $percent = 70

    }

    elseif ($memoryMB -lt 16*1024) {

        $percent = 75

    } 

    elseif ($memoryMB -lt 32*1024) {

        $percent = 80

    } 

    elseif ($memoryMB -lt 96*1024) {

        $percent = 85

    }

    else {

        $percent = 90

    } 

    return $percent

}



#''' 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($SqlConnection, $DatabaseID)

{

	$isAlwaysOn = -1

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



	$res = SqlConnQueryTables $SqlConnection $query

	$res | foreach {

		$hasAlwaysOn = $_.HasAlwaysOn

		if ($hasAlwaysOn -eq 1) {

			$query = " SELECT d.name, d.database_id, 

							CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica,

							ar.secondary_role_allow_connections 

                     FROM master.sys.databases d 

                     JOIN master.sys.availability_replicas ar on d.replica_id = ar.replica_id 

                     JOIN master.sys.servers s ON s.name = ar.replica_server_name AND s.server_id = 0 /*local server*/

                     WHERE d.database_id = @p1 " 

			$resAv = SqlQueryTables $SqlConnection $query $DatabaseID

			$resAv | foreach {

				$is_replica = $_.is_replica

				if( $is_replica -eq 1) {

					$isAlwaysOn = 1

					$secondary_role_allow_connections = $_.secondary_role_allow_connections

					if ($secondary_role_allow_connections -le 1) {

                        $isAlwaysOn = 0

                    }

					return $isAlwaysOn

				}

			}

		}

	}

	return $isAlwaysOn

}



#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 -Namespace $namespace -ComputerName $server -Class "win32_service" -ErrorAction SilentlyContinue | where {$_.name -like $service } 

		if ($obje -ne $null) {

            return $obje.State

        }

    }

    catch {

    }

    return "Unknown"

}



function Main($ComputerName, $InstanceName, $connectionString, $tcpPort, $delay, $serviceName) {

	#error messege has to be logged duaring execution

	$log_error_msg = $false

	$error_msg = ""

    if($DEBUG_MODE -eq 1)

	{

		$DEBUG_MSG = "ComputerName: $computerName InstanceName: $InstanceName"

        $DEBUG_MSG += " ConnectionString: $connectionString tcpPort: $tcpPort delay: $delay" + [Environment]::NewLine

	}

    # 

	# Prepare MOM API and property bag object

	#

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

    $SqlConnectionMaster = $null  

	#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

	}



    try

    {

        #check if connection string valid

		$SqlConnectionMaster = GetValidatedConnection $computerName $InstanceName 'master' $connectionString $tcpPort $DEBUG_SA $DEBUG_PWD

        #if connection is null when exit the script without error

        if($SqlConnectionMaster -eq $null ) 

        {

            PrintDebug $api "The server was not found or was not accessible."

            return

        }



        #select all database per instance

		$queryDB = "SELECT db.name as dbName

	                    , db.database_id as dbID

	                    , grp.name

	                    , grp.min_memory_percent

	                    , grp.max_memory_percent

	                    , grp.max_memory_kb/1024 AS max_memory_mb

	                    , grp.used_memory_kb/1024 AS used_memory_mb 

	                    , grp.target_memory_kb/1024 AS target_memory_mb

	                    , grp.pool_id AS pool_id

                    FROM master.sys.databases as db

                    join master.sys.dm_resource_governor_resource_pools as grp on grp.pool_id = CASE WHEN db.resource_pool_id IS NULL THEN 2 ELSE db.resource_pool_id END

                    WHERE db.source_database_id IS NULL AND db.state = 0 AND db.is_read_only = 0 AND db.name not in ('master', 'model', 'msdb', 'tempdb')"

	    $res = SqlQueryTables $SqlConnectionMaster $queryDB



        $maxMemorySQL = GetMaxServerMemoryMB($SqlConnectionMaster)

        $maxHekatonPercent = GetAvailableHKMemoryPercent($maxMemorySQL)

		$totalPhysicalMemory = GetTotalPhysicalMemoryMB



        $res | foreach {

            $dbName = $_.dbName

			$dbID = $_.dbID

            $poolID = $_.pool_id                    

            $availableMemoryMB = $_.max_memory_mb

            $usedMemoryMB = $_.used_memory_mb

            $maxPercent = $_.max_memory_percent



	        try {

                $queryFx = "USE ""{0}"";

							SELECT type, filegroup_guid FROM sys.filegroups AS fx WHERE fx.type = 'FX'"                            



                $resGroupFx = SqlConnQueryTables $SqlConnectionMaster $queryFx $dbName

				

                if($resGroupFx -ne $null) {

                    #default pool

                    if ($poolID -eq 2) {

                        #available for in-memory tables before OOM notification

                        $availableMemoryMb = GetAvailableHKMemoryMB $availableMemoryMB

                    }

                    else {

                        if ($maxPercent -igt $maxHekatonPercent) {

                            $sqlServerMemoryMB = 100 * ($availableMemoryMB / $maxPercent);

                            $availableMemoryMB = GetAvailableHKMemoryMB $sqlServerMemoryMB

                        }

                    }                    

                    #calculate metrix

                    $usedMemoryPercent = 100 * $usedMemoryMB/$availableMemoryMB

                    $freeMemoryMB = $availableMemoryMB - $usedMemoryMB

                    $freeMemoryPercent = 100 - $usedMemoryPercent

                    #save to propertybag

                    $bag = $api.CreatePropertyBag()

				    $bag.AddValue("Database", $dbName)             

				    $bag.AddValue("FileGroupGuid", $resPool.filegroup_guid)

                    $bag.AddValue("PoolID", $poolID )

				    $bag.AddValue("PoolUsedMemoryMB", $usedMemoryMB)

                    $bag.AddValue("PoolUsedMemoryPercent", $usedMemoryPercent)

                    $bag.AddValue("PoolAvailableMemoryMB", $availableMemoryMb)

                    $bag.AddValue("PoolFreeMemoryMB", $freeMemoryMB)

                    $bag.AddValue("PoolFreeMemoryPercent", $freeMemoryPercent)

					$bag.AddValue("MemoryForSQLInstanceMB", $maxMemorySQL)

				    $bag.AddValue("TotalMemoryOnTheServerMB", $totalPhysicalMemory)

				    $bag



                    if($DEBUG_MODE -eq 1) {

                        $DEBUG_MSG += "Database: $dbName Pool ID: $poolID Used: $usedMemoryMB MB ( $usedMemoryPercent% ) Available: $availableMemoryMB MB Free: $freeMemoryMB MB ( $freeMemoryPercent% ) " + 

                            "MemoryForSQLInstance $maxMemorySQL MB Total Memory On The Server $totalPhysicalMemory MB" + [Environment]::NewLine

                    }

                }

                Start-Sleep -m $delay

	        }

	        catch {

		        #check if it 

				$flag = AlwaysOnReplicaAllowConnections $SqlConnectionMaster $dbID

				if($flag -ne 0)

				{

					$log_error_msg = $true

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

				}

			}    

        }   

		$SqlConnectionMaster.Close();

    }

    catch {

		if($SqlConnectionMaster.State -ne [System.Data.ConnectionState]::Closed ) {

		    $SqlConnectionMaster.Close()

        }

		$log_error_msg = $true

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

	}

	

	#if an error takes place when message has to be printed

	if($log_error_msg -eq $true) {

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

        $api.LogScriptEvent($header, $SCRIPT_EVENT_ID, $ERROR_EVENT_TYPE, $error_msg)	

    }

	#debug information

	PrintDebug $api $error_msg

}



Main $computerName $sqlInstanceName $connectionString $tcpPort $delay $serviceName

</Script></ScriptBody>

          <SnapIns/>

          <Parameters>

            <Parameter>

              <Name>computerName</Name>

              <Value>$Config/ServerName$</Value>

            </Parameter>

            <Parameter>

              <Name>sqlInstanceName</Name>

              <Value>$Config/SqlInstanceName$</Value>

            </Parameter>

            <Parameter>

              <Name>connectionString</Name>

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

            </Parameter>

            <Parameter>

              <Name>tcpPort</Name>

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

            </Parameter>

            <Parameter>

              <Name>delay</Name>

              <Value>$Config/ScriptDelayMsec$</Value>

            </Parameter>

            <Parameter>

              <Name>serviceName</Name>

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

            </Parameter>

          </Parameters>

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

          <StrictErrorHandling>true</StrictErrorHandling>

        </ProbeAction>

        <ConditionDetection ID="PoolFilter" TypeID="System!System.ExpressionFilter">

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery Type="String">Property[@Name='PoolID']</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value Type="Integer">$Config/PoolID$</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

        </ConditionDetection>

      </MemberModules>

      <Composition>

        <Node ID="PoolFilter">

          <Node ID="Probe">

            <Node ID="Scheduler"/>

          </Node>

        </Node>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.PropertyBagData</OutputType>

</DataSourceModuleType>