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, $tcpPort, $serviceName )



#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 BuildConnectionString(

    [String] $pHostName, 

    [String] $pInstanceName, 

    [String] $databaseName, 

    [String] $connectionString = $null,

    [String] $user = $null, 

    [String] $password = $null) 

{    

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

	$serverName = ""



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

        $serverName = $connectionString

    } else {

		$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

	if (($res.Count -gt 0) -and ($res[0].Count -gt 0)) {

		$queryServerName = $res[0][0].ServerName.ToUpperInvariant()

        $queryInstanceName = $res[0][0].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 SqlQueryTables($connectionString, $query) {			

	

	$res = $null;

		

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

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

		$SqlConnection.ConnectionString = $connectionString	 

        $SqlConnection.Open()

		$SqlCmd.CommandText = $query

		$SqlCmd.Connection = $SqlConnection

		$SqlAdapter.SelectCommand = $SqlCmd

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

		$res = $DataSet.Tables

        $SqlConnection.Close()

	}

	catch {

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

		    $SqlConnection.Close()

        }

        throw $_.Exception

	}



	return $res

}



#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

{

	param(

        $MapElement, 

        $TargetID,

		$computerID,		

		$computerName, 

		$sqlInstanceName,

        $connectionString, 

		$tcpPort,

		$serviceName

	)



	$sqlConnectionString = $connectionString

    [int]$sqlTcpPort = $tcpPort

    

    $sqlConnectionString = ValidateConnectionString $sqlConnectionString $sqlTcpPort $computerName $sqlInstanceName $DEBUG_SA $DEBUG_PWD



	$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

	}



    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"

        

		$connectionString = BuildConnectionString $computerName $sqlInstanceName 'master' $sqlConnectionString

        

	    $res = SqlQueryTables $connectionString $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 = "Managegement Group: $Target/ManagementGroup/Name$. Script: {0}  Module: {1} " -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE

		$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

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

	}

}



Main $MapElement $TargetID $computerID $computerName $sqlInstanceName $connectionString $tcpPort $serviceName</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>tcpPort</Name>

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

            </Parameter>

            <Parameter>

              <Name>serviceName</Name>

              <Value>$Target/Host/Property[Type="Microsoft.SQLServer.2014.DBEngine"]/ServiceName$</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>