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,

    [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 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

    

    $sqlConnectionString = ValidateConnectionString $sqlConnectionString $sqlTcpPort $ComputerName $InstanceName $DEBUG_SA $DEBUG_PWD



	#try {

		if ($PerformanceCounterObject -eq "MSSQL")

        {

            $PerformanceCounterObject = "MSSQL`${0}" -f $InstanceName 

        }



		$connectionString = BuildConnectionString $ComputerName $InstanceName 'master' $sqlConnectionString

	

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