SQL Server 2014 記憶體內 OLTP 檔案數目資料來源

Microsoft.SQLServer.2014.DataSource.DBFileGroupFx.ActiveAndNonActiveFiles (DataSourceModuleType)

資料來源會傳回每個 SQL Server 2014 記憶體內 OLTP 資料庫的使用中/非使用中檔案數目

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2014.DataSource.DBFileGroupFx.ActiveAndNonActiveFiles" Accessibility="Internal" RunAs="SQL2014Core!Microsoft.SQLServer.2014.SQLProbeAccount">

  <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="ServerName" 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="DatabaseName" type="xsd:string"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="TimeoutSeconds" type="xsd:int"/>

    <xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="ScriptDelayMsec" 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$"/>

    <OverrideableParameter ID="ScriptDelayMsec" ParameterType="int" Selector="$Config/ScriptDelayMsec$"/>

  </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="Probe" TypeID="Windows!Microsoft.Windows.PowerShellPropertyBagProbe">

          <ScriptName>GetHKFilePairs.ps1</ScriptName>

          <ScriptBody><Script>#GetHKFilePairs.ps1

param($computerName, $sqlInstanceName, $connectionString, $tcpPort, $delay, $serviceName)



#TODO: Discuss event id

$SCRIPT_EVENT_ID = 4201 

$DEBUG_MODE = 0

$DEBUG_MODULE = "GetHKFilePairs.ps1"

$DEBUG_MSG = ""

$DEBUG_SA = $null

$DEBUG_PWD = $null



#Event Severity values

$INFORMATION_EVENT_TYPE = 0

$ERROR_EVENT_TYPE = 1 





#debug information

function PrintDebug( $api, $error_msg )

{

    if($DEBUG_MODE -eq 1) {

		$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module: {1}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE

        $DEBUG_MSG += [Environment]::NewLine

		$DEBUG_MSG += $error_msg

        $api.LogScriptEvent($header, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $DEBUG_MSG)

        $DEBUG_MSG	

    }

}



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 GetValidatedConnection(

        [String] $pHostName, 

		[String] $pInstanceName, 

		[String] $databaseName, 

		[String] $connectionString,

		[int] $port,

		[String] $user = $null, 

		[String] $password = $null) 

{



	$sqlConnectionString = $connectionString

    $sqlConnectionString = ValidateConnectionString $sqlConnectionString $port $pHostName $pInstanceName $user $password



	$connectionString = BuildConnectionString $pHostName $pInstanceName $databaseName $sqlConnectionString



	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

	try

	{

		$SqlConnection.ConnectionString = $connectionString

		$SqlConnection.Open()    

		return $SqlConnection

	}

	catch {

		if ($SqlConnection -ne $null)

		{

			if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {

				$SqlConnection.Close()

			}

			$SqlConnection.Dispose()

		}

	}

    return $null

}



# Parameters should be provided after $SqlConnection and $query

# Parameters should be named in the query text as $p1...$pN

function SqlQueryTables($SqlConnection, $query) {			

	

	$res = $null;

		

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

		$SqlCmd.CommandText = $query

		$SqlCmd.Connection = $SqlConnection

		for	($i=0; $i -lt $args.length; $i++) {

            $ParameterName = "@p" + ($i+1)

			$Parameter = New-Object System.Data.SqlClient.SqlParameter($ParameterName, [object]($args[$i]))

			$SqlCmd.Parameters.Add($Parameter)

		}

		$SqlAdapter.SelectCommand = $SqlCmd

		$SqlAdapter.Fill($DataSet)|out-null		

		$res = $DataSet.Tables

		$SqlAdapter.Dispose()

		$SqlCmd.Dispose()

	}

	catch {

        if( $SqlAdapter -ne $null) {

            $SqlAdapter.Dispose()

        }

        if( $SqlCmd -ne $null) {

		    $SqlCmd.Dispose()

        }   

        throw $_.Exception

	}



	return $res

}





function SqlConnQueryTables($SqlConnection, $query, $dbName) {			

	

	$res = $null;

		

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

		$safeName = $dbName -replace """", """"""

		$SqlCmd.CommandText = $query -f $safeName

		$SqlCmd.Connection = $SqlConnection

		$SqlAdapter.SelectCommand = $SqlCmd

		$SqlAdapter.Fill($DataSet)|out-null		

		$res = $DataSet.Tables

        $SqlAdapter.Dispose()

		$SqlCmd.Dispose()

    }

	catch {

        if( $SqlAdapter -ne $null)

        {

            $SqlAdapter.Dispose()

        }

        if( $SqlCmd -ne $null)

        {

		    $SqlCmd.Dispose()

        }   

        throw $_.Exception

	}



	return $res

}



#''' Returns -1: If DB is not in AlwaysOn

#''' Returns  0: If DB is in AlwaysOn and replica allow connections is NO

#''' Returns  1: If DB is in AlwaysOn and replica allow connections is YES

function AlwaysOnReplicaAllowConnections($SqlConnection, $DatabaseID)

{

	$isAlwaysOn = -1

	$query = " SELECT columns.id, CASE WHEN OBJECT_ID('sys.availability_replicas') IS NOT NULL THEN 1 ELSE 0 END AS HasAlwaysOn " +

             " FROM master.sys.syscolumns columns where name = 'replica_id' and id = OBJECT_ID('sys.databases')"



	$res = SqlConnQueryTables $SqlConnection $query

	$res | foreach {

		$hasAlwaysOn = $_.HasAlwaysOn

		if ($hasAlwaysOn -eq 1) {

			$query = " SELECT d.name, d.database_id, 

							CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica,

							ar.secondary_role_allow_connections 

                     FROM master.sys.databases d 

                     JOIN master.sys.availability_replicas ar on d.replica_id = ar.replica_id 

                     JOIN master.sys.servers s ON s.name = ar.replica_server_name AND s.server_id = 0 /*local server*/

                     WHERE d.database_id = @p1 " 

			$resAv = SqlQueryTables $SqlConnection $query $DatabaseID

			$resAv | foreach {

				$is_replica = $_.is_replica

				if( $is_replica -eq 1) {

					$isAlwaysOn = 1

					$secondary_role_allow_connections = $_.secondary_role_allow_connections

					if ($secondary_role_allow_connections -le 1) {

                        $isAlwaysOn = 0

                    }

					return $isAlwaysOn

				}

			}

		}

	}

	return $isAlwaysOn

}



#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,

        $delay,

		$serviceName

	)

	#error messege has to be logged duaring execution

	$log_error_msg = $false

	$error_msg = ""

    if($DEBUG_MODE -eq 1)

	{

		$DEBUG_MSG = "ComputerName: $computerName InstanceName: $sqlInstanceName"

        $DEBUG_MSG += " ConnectionString: $connectionString tcpPort: $tcpPort delay: $delay" + [Environment]::NewLine

    }	

	# Prepare MOM API and property bag object

	$api = New-Object -comObject "MOM.ScriptAPI"

    $SqlConnection = $null

	#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 {

		 #check if connection string valid

		$SqlConnection = GetValidatedConnection $computerName $sqlInstanceName 'master' $connectionString $tcpPort $DEBUG_SA $DEBUG_PWD

        #if connection is null when exit the script without error

        if($SqlConnection -eq $null ) 

        {

            PrintDebug $api "The server was not found or was not accessible."

            return

        }



        $queryDB = "USE ""{0}"";

					SELECT name as dbName, database_id as dbID 

                        FROM sys.databases 

                        WHERE source_database_id IS NULL AND state = 0 AND is_read_only = 0 AND name not in ('master', 'model', 'msdb', 'tempdb');"

        $queryFiles = "USE ""{0}"";

					select 

                        Sum(CASE WHEN  chf.state != 2 THEN 1 Else 0 End) NonActivePairs,

                        Sum(CASE WHEN  chf.state = 2 THEN 1 Else 0 End) ActivePairs

                    from sys.dm_db_xtp_checkpoint_files as chf

                    where chf.file_type = 0;"



	    $res = SqlConnQueryTables $SqlConnection $queryDB 'master'

        

        $res | foreach {

            $dbName = $_.dbName

			$dbID = $_.dbID

            try {

				$resFiles = SqlConnQueryTables $SqlConnection $queryFiles $_.dbName



				$resFiles | foreach {

					if( [string]::IsNullOrEmpty($_.ActivePairs) -ne $true ) {

						$active = $_.ActivePairs

						$nonactive = $_.NonActivePairs

						$nonActivePercent = (100 * ($active + $nonactive)) / $nonactive

						$bag = $api.CreatePropertyBag()              	

						$bag.AddValue("Database", $dbName)          	

						$bag.AddValue("ActivePairs", $active)

						$bag.AddValue("NonActivePairs", $nonactive)

						$bag.AddValue("NonActivePercent", $nonActivePercent)

						$bag



						if($DEBUG_MODE -eq 1) {

							$DEBUG_MSG += "Database=$dbName ActivePairs=$active NonActivePairs=$nonactive"

							$DEBUG_MSG += [Environment]::NewLine

						}

					}

				}

                Start-Sleep -m $delay    

			}

			catch {

				$flag = AlwaysOnReplicaAllowConnections $SqlConnection $dbID

				if($flag -ne 0)

				{

					$log_error_msg = $true

					$error_msg += "Error occurred during Request count data source executing.{0}Computer: {1} {0}Reason: {2} {0}Position:{3} {0}Offset:{4} {0}Database: {5}" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message, $_.InvocationInfo.ScriptLineNumber, $_.InvocationInfo.OffsetInLine, $dbName

				}

			}    

        }   

		$SqlConnection.Close()

        $SqlConnection.Dispose()

    }

    catch {

        if ($SqlConnection -ne $null)

        {

            if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {

		        $SqlConnection.Close()

            }

            $SqlConnection.Dispose()

        }



		$log_error_msg = $true

		$error_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

		$error_msg += "{0}Detailed error output: {1}" -f [Environment]::NewLine, [String]::Join("{0}--------{0}" -f [Environment]::NewLine, $Error.ToArray())

	}

	#if an error takes place when message has to be printed

	if($log_error_msg -eq $true) {

		$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module: {1}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE

        $api.LogScriptEvent($header, $SCRIPT_EVENT_ID, $ERROR_EVENT_TYPE, $error_msg)	

    }

    #debug information

    PrintDebug $api $error_msg

}



Main $computerName $sqlInstanceName $connectionString $tcpPort $delay $serviceName</Script></ScriptBody>

          <SnapIns/>

          <Parameters>

            <Parameter>

              <Name>computerName</Name>

              <Value>$Config/ServerName$</Value>

            </Parameter>

            <Parameter>

              <Name>sqlInstanceName</Name>

              <Value>$Config/SqlInstanceName$</Value>

            </Parameter>

            <Parameter>

              <Name>connectionString</Name>

              <Value>$Target/Host/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/ConnectionString$</Value>

            </Parameter>

            <Parameter>

              <Name>tcpPort</Name>

              <Value>$Target/Host/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/TcpPort$</Value>

            </Parameter>

            <Parameter>

              <Name>delay</Name>

              <Value>$Config/ScriptDelayMsec$</Value>

            </Parameter>

            <Parameter>

              <Name>serviceName</Name>

              <Value>$Target/Host/Host/Property[Type="SQL2014Core!Microsoft.SQLServer.2014.DBEngine"]/ServiceName$</Value>

            </Parameter>

          </Parameters>

          <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>

          <StrictErrorHandling>true</StrictErrorHandling>

        </ProbeAction>

        <ConditionDetection ID="DatabaseFilter" TypeID="System!System.ExpressionFilter">

          <Expression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQuery Type="String">Property[@Name='Database']</XPathQuery>

              </ValueExpression>

              <Operator>Equal</Operator>

              <ValueExpression>

                <Value Type="String">$Config/DatabaseName$</Value>

              </ValueExpression>

            </SimpleExpression>

          </Expression>

        </ConditionDetection>

      </MemberModules>

      <Composition>

        <Node ID="DatabaseFilter">

          <Node ID="Probe">

            <Node ID="Scheduler"/>

          </Node>

        </Node>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <OutputType>System!System.PropertyBagData</OutputType>

</DataSourceModuleType>