アクティブ接続データソース 2014
Microsoft.SQLServer.2014.DBEngine.ActiveConnections.DataSource (DataSourceModuleType)
コレクション SQL 2014 DB アクティブ接続のデータソース
Element properties: Member Modules:
Overrideable Parameters:
Source Code: <DataSourceModuleType ID="Microsoft.SQLServer.2014.DBEngine.ActiveConnections.DataSource" Accessibility="Internal">
<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="TimeoutSeconds" 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="ComputerName" type="xsd:string"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="SqlInstanceName" type="xsd:string"/>
</Configuration>
<OverrideableParameters>
<OverrideableParameter ID="IntervalSeconds" Selector="$Config/IntervalSeconds$" ParameterType="int"/>
<OverrideableParameter ID="TimeoutSeconds" Selector="$Config/TimeoutSeconds$" ParameterType="int"/>
<OverrideableParameter ID="SyncTime" Selector="$Config/SyncTime$" ParameterType="string"/>
</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="PS" TypeID="Windows!Microsoft.Windows.PowerShellPropertyBagProbe">
<ScriptName>ActiveConnectionsDataSource.ps1</ScriptName>
<ScriptBody><Script> # ActiveConnectionsDataSource.ps1
#
param($computerName, $sqlInstanceName, $connectionString, $tcpPort, $serviceName)
# $InstancePath is the 'Connection String' property of SQL DB Engine Class (Microsoft.SQLServer.DBEngine)
# $InstancePath supposed to be of three types:
# computername
# computername\MSSQLSERVER
# computername\MSSQLSERVER.db1
$DEBUG_MODE = 0
$DEBUG_MODULE = "ActiveConnectionsDataSource.ps1"
$DEBUG_MSG = ""
$DEBUG_SA = $null
$DEBUG_PWD = $null
#TODO: Discuss event id
$SCRIPT_EVENT_ID = 4201
#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 | 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 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(
$computerName,
$sqlInstanceName,
$connectionString,
$tcpPort,
$serviceName
)
#
# Prepare MOM API and property bag object
#
$api = New-Object -comObject "MOM.ScriptAPI"
#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
}
$sqlConnectionString = $connectionString
[int]$sqlTcpPort = $tcpPort
$sqlConnectionString = ValidateConnectionString $sqlConnectionString $sqlTcpPort $computerName $sqlInstanceName $DEBUG_SA $DEBUG_PWD
try
{
$msg = [string]::Empty
$query = "SELECT DB_NAME(dbid) as DBName FROM sys.sysdatabases;
SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NConnections
from sys.sysprocesses as sp
inner join sys.dm_exec_connections as ex
on sp.spid = ex.session_id
WHERE dbid > 0
GROUP BY dbid;"
$connectionString = BuildConnectionString $computerName $sqlInstanceName 'master' $sqlConnectionString
$res = SqlQueryTables $connectionString $query
$dbConections = New-Object 'System.Collections.Generic.Dictionary[String,Double]'
$res[0] | foreach {
$dbConections[$_.DBName] = [double]0
}
$dbCount = 0;
$res[1] | foreach {
$dbConections[$_.DBName] = [double]$_.NConnections
}
$dbConections.GetEnumerator() | foreach {
$bag = $api.CreatePropertyBag()
$bag.AddValue("Name", $_.Key)
$bag.AddValue("Value", $_.Value)
$dbCount++;
$bag
}
$msg = "Active connections were got for {0} DBs on {1}" -f $dbCount, $InstancePath
if($DEBUG_MODE -eq 1) {
$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg)
}
}
catch
{
$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module:{1}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE
$msg += "Error occurred during Active connection 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
$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, $msg)
}
}
Main $computerName $sqlInstanceName $connectionString $tcpPort $serviceName</Script> </ScriptBody>
<SnapIns/>
<Parameters>
<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="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/ConnectionString$</Value>
</Parameter>
<Parameter>
<Name>tcpPort</Name>
<Value>$Target/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/TcpPort$</Value>
</Parameter>
<Parameter>
<Name>serviceName</Name>
<Value>$Target/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/ServiceName$</Value>
</Parameter>
</Parameters>
<TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>
<StrictErrorHandling>true</StrictErrorHandling>
</ProbeAction>
</MemberModules>
<Composition>
<Node ID="PS">
<Node ID="Scheduler"/>
</Node>
</Composition>
</Composite>
</ModuleImplementation>
<OutputType>System!System.PropertyBagData</OutputType>
</DataSourceModuleType>