Pools de Recursos do Mecanismo do Banco de Dados de Origem de Dados

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

Esta Fonte de Dados descobre todos os pools de recursos para uma determinada instância do Mecanismo do Banco de Dados do 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"/>

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

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



#TODO: Discuss event id

$SCRIPT_EVENT_ID = 4201 

$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

}



function ModuleAction

{

	param(

        $MapElement, 

        $TargetID,

		$computerID,		

		$computerName, 

		$sqlInstanceName,

        $connectionString, 

		$tcpPort,

		$serviceName

	)



    

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

        $query = "SELECT name, pool_id, min_cpu_percent, max_cpu_percent, cap_cpu_percent, min_memory_percent, max_memory_percent " +

					" FROM sys.dm_resource_governor_resource_pools"



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

                $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

        

                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 += [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)	

					

                $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}" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message, $_.InvocationInfo.ScriptLineNumber, $_.InvocationInfo.OffsetInLine

		$DEBUG_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, $DEBUG_MSG)	

	}

	finally {

		if($sqlConnection -ne $null) {

			$sqlConnection.Dispose()

		}

	}

}



#SQL2014Constants.ps1



$MANAGEMENT_PACK_VERSION = "6.7.0.0"



#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 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 -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] $serverName, 

		[String] $instanceName,

		[string] $timeout = 30,

        [String] $user = $null, 

        [String] $password = $null){

	

	$serverNameWithoutDomain = $serverName

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

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

	        if ($dotPosition -gt -1) {

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

	        }

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

	            return $sqlConnection;

	        }

			throw "Connection target check failed: connected to $serverNameWithoutDomain\$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 = ""

	

	try{

		$targetDataSource = GetDataSource $connectionDataSource ""

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

	}

	catch{

		$lastError = $_.Exception

		$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"

	}

	

	if ((GetEnabledSqlServerProtocols $machineName $instanceName) -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 $machineName $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 $machineName $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 $machineName $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;

}





                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>

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