SQL 2014 Stolen Server Memory datasource
Microsoft.SQLServer.2014.DBEngine.StolenServerMemory.DataSource (DataSourceModuleType)
Stolen Server Memory datasource
Element properties: Member Modules:
Overrideable Parameters:
Source Code: <DataSourceModuleType ID="Microsoft.SQLServer.2014.DBEngine.StolenServerMemory.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"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="PerformanceCounterObject" 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>StolenServerMemory2014.ps1</ScriptName>
<ScriptBody><Script> #StolenServerMemory2014DataSource.ps1
param($computerName, $sqlInstanceName, $PerformanceCounterObject, $connectionString, $tcpPort, $serviceName)
$SCRIPT_EVENT_ID = 4201
#debug
$DEBUG_MODE = 0
$DEBUG_MODULE = "StolenServerMemory2014DataSource.ps1"
$DEBUG_MSG = ""
$DEBUG_SA = $null
$DEBUG_PWD = $null
#Event Severity values
$INFORMATION_EVENT_TYPE = 0
$ERROR_EVENT_TYPE = 1
$ManagementGroupID = '$Target/ManagementGroup/Id$'
function BuildConnectionString(
[String] $pHostName,
[String] $pInstanceName,
[String] $databaseName,
[String] $connectionString = $null,
[int] $port = 0,
[String] $user = $null,
[String] $password = $null)
{
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$serverName = ""
if (($connectionString.Length -ne 0) -and ($port -ne 0)) {
$serverName = $connectionString + "," + $port
}
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 ValidateConnectionString(
[String] $connectionString,
[int] $port,
[String] $user = $null,
[String] $password = $null)
{
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$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'
}
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
try {
$SqlConnection.ConnectionString = $builder.ConnectionString
$SqlConnection.Open()
$SqlConnection.Close()
return $true
}
catch {
if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {
$SqlConnection.Close()
}
}
return $false
}
function SqlQueryRows($connectionString, $query) {
$res = "";
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
try
{
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$DataSet = New-Object System.Data.DataSet
$SqlConnection.ConnectionString = $connectionString
$SqlCmd.CommandText = $query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)|out-null
$res = ($DataSet.Tables[0])
}
finally
{
$SqlConnection.Close()
}
return $res
}
function GetMaxServerMemoryMB($connectionString) {
$query = "SELECT [value_in_use] as [value] FROM sys.configurations WHERE name = 'max server memory (MB)' ORDER BY name OPTION (RECOMPILE);"
$res = SqlQueryRows $connectionString $query
[double]$maxMemory = $res.value
# if sql max memory isn't configured get system memory
if ($maxMemory -eq 2147483647) {
$maxMemory = (Get-WmiObject -Class Win32_ComputerSystem).TotalPhysicalMemory / 1024 / 1024
}
return $maxMemory
}
#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($ComputerName, $InstanceName, $PerformanceCounterObject, $connectionString, $tcpPort, $serviceName) {
#
# Prepare MOM API and property bag object
#
$api = New-Object -comObject "MOM.ScriptAPI"
$bag = $api.CreatePropertyBag()
#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
}
$msg = [Environment]::NewLine
$err = [Environment]::NewLine
$sqlConnectionString = $connectionString
[int]$sqlTcpPort = $tcpPort
$isConnected = ValidateConnectionString $sqlConnectionString $sqlTcpPort $DEBUG_SA $DEBUG_PWD
if ($isConnected -eq $false) {
$sqlConnectionString = $null
$sqlTcpPort = 0
}
#try {
if ($PerformanceCounterObject -eq "MSSQL")
{
$PerformanceCounterObject = "MSSQL`${0}" -f $InstanceName
}
$connectionString = BuildConnectionString $computerName $sqlInstanceName 'master' $sqlConnectionString $sqlTcpPort
$maxMemoryMB = GetMaxServerMemoryMB $connectionString
$counterName = "\{0}:Memory Manager\Stolen Server Memory (KB)" -f $PerformanceCounterObject
$stolenMemoryKB = (Get-Counter ($counterName)).CounterSamples[0].CookedValue
[double]$stolenMemoryMB = $stolenMemoryKB / 1024
[double]$stolenMemoryPercent = 0
if($maxMemoryMB -gt 0){
[double]$stolenMemoryPercent= 100.0 * $stolenMemoryMB / $maxMemoryMB
}
$bag.AddValue("StolenMemoryMB", $stolenMemoryMB)
$bag.AddValue("StolenMemoryPercent", $stolenMemoryPercent)
$msg += "StolenMemoryMB=$stolenMemoryMB StolenMemoryPercent=$stolenMemoryPercent"
$msg += [Environment]::NewLine
#debug $api.LogScriptEvent("SQL 2014 Stolen Server Memory data source", $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg + $err)
if($DEBUG_MODE -eq 1) {
$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg + $err)
}
#}
#catch {
# $header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module: {1}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE
# $msg += "Error occurred during SQL 2014 Stolen Server Memory data source executing.{0}Computer:{1} {0}Reason: {2}" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message
# $api.LogScriptEvent($header, $SCRIPT_EVENT_ID, $ERROR_EVENT_TYPE, $msg + $err)
#}
$bag
}
main $computerName $sqlInstanceName $PerformanceCounterObject $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>PerformanceCounterObject</Name>
<Value>$Config/PerformanceCounterObject$</Value>
</Parameter>
<Parameter>
<Name>connectionString</Name>
<Value>$Target/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/ConnectionString$</Value>
</Parameter>
<Parameter>
<Name>tcpPort</Name>
<Value>$Target/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/TcpPort$</Value>
</Parameter>
<Parameter>
<Name>serviceName</Name>
<Value>$Target/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>