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 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
if (($res.Count -gt 0) -and ($res[0].Rows.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 = "Management 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
$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)
}
}
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>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>