Ressourcenpools für das Datenquellen-Datenbankmodul
Microsoft.SQLServer.2014.DataSource.ResourcePool (DataSourceModuleType)
Diese Datenquelle erkennt alle Ressourcen für eine angegebene Instanz des SQL Server 2014-Datenbankmoduls.
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 > -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>