SQL 2016 ディスク読み取り/書き込み待機時間データ ソース

Microsoft.SQLServer.2016.DBEngine.DiskLatency.DataSource (DataSourceModuleType)

ディスク読み取り/書き込み待機時間データ ソース

Element properties:

TypeDataSourceModuleType
IsolationAny
AccessibilityInternal
RunAsDefault
OutputTypeSystem.PropertyBagData

Member Modules:

ID Module Type TypeId RunAs 
Scheduler DataSource System.Scheduler Default
PS ProbeAction System.CommandExecuterProbePropertyBagBase Default

Overrideable Parameters:

IDParameterTypeSelectorDisplay NameDescription
IntervalSecondsint$Config/IntervalSeconds$間隔 (秒)ワークフローを実行する定期的な実行間隔 (秒)。
TimeoutSecondsint$Config/TimeoutSeconds$タイムアウト (秒)ワークフローが終了して失敗とマークされるまでの、ワークフローの許容実行時間を指定します。
SyncTimestring$Config/SyncTime$同期時刻24 時間形式で指定した同期時刻。省略可能です。

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2016.DBEngine.DiskLatency.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="System!System.CommandExecuterProbePropertyBagBase">
<ApplicationName>%systemroot%\System32\windowspowershell\v1.0\powershell.exe</ApplicationName>
<WorkingDirectory/>
<CommandLine>-NoLogo -NoProfile -Noninteractive "$ep = get-executionpolicy; if ($ep -gt 'RemoteSigned') {set-executionpolicy -Scope Process remotesigned} &amp; '$$file/DBDiskLatencyDataSource.ps1$$' -computerName '$Config/ComputerName$' -sqlInstanceName '$Config/SqlInstanceName$' -connectionString '$Target/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/ConnectionString$' -tcpPort '$Target/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/TcpPort$' -serviceName '$Target/Host/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/ServiceName$'</CommandLine>
<SecureInput/>
<TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>
<RequireOutput>true</RequireOutput>
<Files>
<File>
<Name>DBDiskLatencyDataSource.ps1</Name>
<Contents><Script>#DBDiskLatencyDataSource.ps1
param($computerName, $sqlInstanceName, $connectionString, $tcpPort, $serviceName, $cacheExpirationTime=43200)

$SCRIPT_EVENT_ID = 4211
#debug
$DEBUG_MODE = 0
$DEBUG_MODULE = "DBDiskLatencyDataSource.ps1"
$DEBUG_MSG = ""
$DEBUG_SA = $null
$DEBUG_PWD = $null

#Event Severity values
$INFORMATION_EVENT_TYPE = 0
$ERROR_EVENT_TYPE = 1

#-------------------------------------------------------------------------------


function GetMountPoints($ComputerName) {
$separator = [System.IO.Path]::DirectorySeparatorChar
$extractTextInQuotes = "(?" + [char]60 + "=\"").*?(?=\"")"

$mountPoints = @(,$null)
$rawMountainPoints = Get-WmiObject Win32_MountPoint -ComputerName $ComputerName


$perfCounterNames = Get-RegLocalizedPerfCounterNames

$writeCounters = (Get-LocalizedPerfCounter $perfCounterNames $("\{0}(*)\{1}") $(@( 236 , "LogicalDisk" ), @( 210 , "Avg. Disk sec/Write" ))).CounterSamples
$readCounters = (Get-LocalizedPerfCounter $perfCounterNames $("\{0}(*)\{1}") $(@( 236 , "LogicalDisk" ), @( 208 , "Avg. Disk sec/Read" ))).CounterSamples


$rawMountainPoints | foreach {
if ($_.Directory -match $extractTextInQuotes) {

$directory = $matches[0].Replace("\\", "\")

if ($_.volume -match $extractTextInQuotes) {
$volume = $matches[0].Replace("\\", "\")

$diskInstanceName = $directory
if ($diskInstanceName.EndsWith($separator) ) {
$diskInstanceName = $directory.Substring(0, $directory.Length - 1)
}

$writeCounter = $writeCounters | where { $_.InstanceName -eq $diskInstanceName }
$readCounter = $readCounters | where { $_.InstanceName -eq $diskInstanceName }

if ($writeCounter -ne $null -and $readCounter -ne $null) {
$name = $volume + $directory
$mountPoints += , @( $directory, $volume, $name, [double]$readCounter.CookedValue, [double]$writeCounter.CookedValue )
}
}
}
}
return $mountPoints
}

function MatchMountPoint($mountPoints, $fileName) {

return $mountPoints | where {($_ -ne $null) -and ($fileName.StartsWith($_[0], [System.StringComparison]::InvariantCultureIgnoreCase)) } | select -First 1
}

function Main(){
ModuleAction $computerName $sqlInstanceName $connectionString $tcpPort $serviceName
}

function ModuleAction($ComputerName, $InstanceName, $connectionString, $tcpPort, $serviceName) {

$msg = [Environment]::NewLine
$err = [Environment]::NewLine

#
# Prepare MOM API
#
$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
}

try {
$query = "select sys.databases.name, sys.databases.database_id,sys.master_files.physical_name from sys.databases
join sys.master_files on sys.databases.database_id = sys.master_files.database_id
where sys.databases.source_database_id is null"

$sqlConnection = SmartConnect $connectionString "master" $ComputerName $InstanceName $DEBUG_SA $DEBUG_PWD

$dbFiles = SqlQueryRows $sqlConnection $query
$mountPoints = GetMountPoints $ComputerName
$bagCount = 0;
$groupedDBFiles = $dbFiles | group name

if($groupedDBFiles -eq $null){
return
}

$groupedDBFiles | foreach {
$dbName = $_.name

[double]$maxRead = 0.0;
[double]$maxWrite = 0.0;

$maxReadFile = ""
$maxWriteFile = ""
$maxReadMountPoint = ""
$maxWriteMountPoint = ""

$is_mount = $false

$_.group | foreach {
$dbFileName = $_.physical_name
$mountPoint = MatchMountPoint $mountPoints $dbFileName

if ($mountPoint -ne $null) {

if ($mountPoint[3] -gt $maxRead) {
$maxRead = $mountPoint[3]
$maxReadFile = $dbFileName
}

if ($mountPoint[4] -gt $maxWrite) {
$maxWrite = $mountPoint[4]
$maxWriteFile = $dbFileName
}
$maxReadMountPoint = $mountPoint[0]
$maxWriteMountPoint = $mountPoint[0]
$is_mount = $true
} else {
$err += "Can't find mount point for DB '$dbName' file '$dbFileName'"
$err += [Environment]::NewLine
}
}

if ($is_mount -eq $true)
{
[double]$maxReadWrite = 0.0
[string]$maxReadWriteFile = ""
[string]$maxReadWriteMountPoint = ""

if ($maxRead -gt $maxWrite) {
$maxReadWrite = $maxRead
$maxReadWriteFile = $maxReadFile
$maxReadWriteMountPoint = $maxReadMountPoint
} else {
$maxReadWrite = $maxWrite
$maxReadWriteFile = $maxWriteFile
$maxReadWriteMountPoint = $maxWriteMountPoint
}

$maxRead *= 1000
$maxWrite *= 1000
$maxReadWrite *= 1000

$bag = $api.CreatePropertyBag()

$bag.AddValue("Name", $dbName)
$bag.AddValue("MaxRead", $maxRead)
$bag.AddValue("MaxReadFile", $maxReadFile)
$bag.AddValue("MaxReadMountPoint", $maxReadMountPoint)

$bag.AddValue("MaxWrite", $maxWrite)
$bag.AddValue("MaxWriteFile", $maxWriteFile)
$bag.AddValue("MaxWriteMountPoint", $maxWriteMountPoint)

$bag.AddValue("MaxReadWrite", $maxReadWrite)
$bag.AddValue("MaxReadWriteFile", $maxReadWriteFile)
$bag.AddValue("MaxReadWriteMountPoint", $maxReadWriteMountPoint)

$msg += "DB=$dbName MaxRead=$maxRead MaxWrite=$maxWrite MaxReadFile=$maxReadFile MaxWriteFile=$maxWriteFile "
$msg += [Environment]::NewLine

$bagCount++;

$api.AddItem($bag)
}
}
}
catch {
$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module: {1} Version: {2}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE, $MANAGEMENT_PACK_VERSION
$msg += "Error occurred during DB Disk Latency data source executing.{0}Computer:{1} {0}Reason: {2} {0}Position:{3} {0}Offset:{4} {0}Instance:{5}" -f
[Environment]::NewLine, $ComputerName, $_.Exception.Message, $_.InvocationInfo.ScriptLineNumber, $_.InvocationInfo.OffsetInLine, $InstanceName
$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 + $err)
}
finally {
if($sqlConnection -ne $null) {
$sqlConnection.Dispose()
}
if ($bagCount -gt 0) {
$api.ReturnItems();
}
}
if($DEBUG_MODE -eq 1) {
$msg += "Debug occurred during DB Disk Latency data source executing.{0}Computer:{1} {0}Instance:{2} {0}Connection:{3} {0}Port:{4} {0}Service:{5} {0}{6}" -f
[Environment]::NewLine, $ComputerName, $InstanceName, $connectionString, $tcpPort, $serviceName, $err
$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg)
}
}

#SQL2016Constants.ps1

$MANAGEMENT_PACK_VERSION = "7.0.7.0"

$ManagementGroupName = '$Target/ManagementGroup/Name$'
$ManagementGroupID = '$Target/ManagementGroup/Id$'

#GlobalizationCommon.ps1

#Depends on:
#Common.ps1

$global:UICultureInfoWinApiValue = $null
$StateMPPrefix = 'SQL2016MP'

#-------------------------------------------------------------------------------
# localized performance counters
#-------------------------------------------------------------------------------

# Needed to replace cmdlet Get-UICulture because it shows incorrect current UI language
# in the case of installing language packs
function Get-UICultureWinApi
{
if ($global:UICultureInfoWinApiValue -ne $null) {
return $global:UICultureInfoWinApiValue
}
$result = ConvertToInt (TryGetValueFromCache "$StateMPPrefix\Common" 'UserDefaultUILanguage' $cacheExpirationTime)
if ($result -ne $null) {
$global:UICultureInfoWinApiValue = $result
return $result;
}
$code = '[DllImport("Kernel32.dll", CharSet = CharSet.Auto)]public static extern System.UInt16 GetUserDefaultUILanguage();'
$UICultureWinApi = Add-Type -MemberDefinition $code -Name 'UICultureWinApi' -Namespace 'Win32' -PassThru
$result = $UICultureWinApi::GetUserDefaultUILanguage()
PutValueToCache "$StateMPPrefix\Common" 'UserDefaultUILanguage' $result | Out-Null
$global:UICultureInfoWinApiValue = $result
return $result
}

# Retrieve the default language identifier of the current user. For most languages,
# you use the primary language identifier only to retrieve the text. In Windows XP and
# Windows Server 2003, you use the complete language identifier to retrieve Chinese
# text. In Windows Vista, you use the complete language identifier to retrieve Portuguese text.
function Get-GetLanguageId([String[]] $osVersion)
{
$LANG_PORTUGUESE = 0x16
$LANG_CHINESE = 0x04

$lcid = Get-UICultureWinApi

$lcidPrimary = ($lcid -band 0xff)

if (
($LANG_PORTUGUESE -eq $lcidPrimary -and $osVersion[0] -gt 5) -or #Windows Vista and later
($LANG_CHINESE -eq $lcidPrimary -and ($osVersion[0] -eq 5 -and $osVersion[1] -ge 1)) #XP and Windows Server 2003
)
{
return $lcid
}

return $lcidPrimary
}

# Build a hash array of offsets into the counter buffer. Use the index
# values from the performance data queries to access the offsets.
function Get-RegLocalizedPerfCounterNames($computerName = $env:COMPUTERNAME)
{
$path = ""
#get OS version
$osvi = Get-WmiObject -class Win32_OperatingSystem -computerName $computerName
if(($osvi -eq $null) -or ($osvi.Version -eq $null)) {
return $null
}
[int[]]$osVersion = $osvi.Version.split(".")

#if OS windows 7, 8, 2008R2, 2012, 10
if(( $osVersion[0] -eq 6 -and $osVersion[1] -ge 1 ) -or ($osVersion[0] -gt 6 ) )
{
$path = "hklm:SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\CurrentLanguage"
}
#if OS Windows XP, 2003, Vista, 2008
if(($osVersion[0] -eq 6 -and $osVersion[1] -eq 0 ) -or ($osVersion[0] -eq 5 -and $osVersion[1] -ge 1 ) )
{
#get language ID
$language = "{0:X3}" -f (Get-GetLanguageId $osVersion)
$path = "hklm:SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\{0}" -f $language
}
#if no data in the registry when return null
$val = Get-ItemProperty -Path $path -Name "Counter" -ErrorAction SilentlyContinue
if (($val -eq $null) -or ($val.Length -eq 0))
{
return $null
}
#return registry values
$hashArray = @{}
for($i=0; $i -lt $val.Counter.Count; $i+=2)
{
[UInt32] $key = [UInt32]$val.Counter[$i]
if ($hashArray.ContainsKey($key) -ne $true)
{
$hashArray.Add($key, $val.Counter[$i+1])
}
}
return $hashArray
}

#Returns the performance object name or counter name corresponding to the specified index.
function Get-PdhLookupPerfNameByIndex([UInt32] $ID, $computerName = $env:COMPUTERNAME)
{
$code = '[DllImport("pdh.dll", SetLastError=true, CharSet=CharSet.Unicode)] public static extern UInt32 PdhLookupPerfNameByIndex(string szMachineName, uint dwNameIndex, System.Text.StringBuilder szNameBuffer, ref uint pcchNameBufferSize);'

$Buffer = New-Object System.Text.StringBuilder(1024)
[UInt32]$BufferSize = $Buffer.Capacity

$t = Add-Type -MemberDefinition $code -PassThru -Name PerfCounter -Namespace Utility
$rv = $t::PdhLookupPerfNameByIndex($computerName, $id, $Buffer, [ref]$BufferSize)

if ($rv -eq 0)
{
return $Buffer.ToString().Substring(0, $BufferSize-1)
}
else
{
Throw 'Get-PdhLookupPerfNameByIndex : Unable to retrieve localized name for performance counter ID ({0}) on computer "{1}".' -f $ID, $ComputerName
}
}

function Get-LocalizedPerfCounter($arrayCounters, [string] $format, $parameters, $ignoreErrors = $false)
{
$lcid = Get-UICultureWinApi
#English language
if(($lcid -band 0xff) -ne 0x09)
{
#try to get localized values
if ( $arrayCounters -ne $null)
{
#from the registry
for($i=0; $i -lt $parameters.Count; $i++)
{
[UInt32] $id = [UInt32]$parameters[$i][0]
if ($arrayCounters.ContainsKey($id) -eq $true)
{
$parameters[$i][1] = $arrayCounters[$id]
}
else
{
Throw 'Get-PerfCounter : Unable to retrieve localized name. (Index : {0} Name : {1})' -f $id,$parameters[$i][1]
}
}
}
else
{
#win32 api
for($i=0; $i -lt $parameters.Count; $i++)
{
[UInt32] $id = [UInt32]$parameters[$i][0]
$parameters[$i][1] = Get-PdhLookupPerfNameByIndex $id
}
}
}
#get counter
$counter = $format -f $parameters[0][1], $parameters[1][1]
if ($ignoreErrors) {
return Get-Counter $counter -ErrorAction SilentlyContinue
} else {
try {
return Get-Counter $counter -ErrorAction Stop
}
catch {
throw New-Object Exception("Unable to get counter ""$counter"": $($_.Exception.Message)", $_.Exception)
}
}
}

function Convert-ToHashtable ($list, $keyProperty) {
$result = @{}
foreach ($item in $list) {
$result[[string]$item.$keyProperty] = $item;
}
return $result
}

#Converts result of Get-Counter to hashtable with instanceName (indexed) as key and cookedValue as value
function Convert-PerfCounterToHashtable ($table) {
if ($table.CounterSamples -ne $null) {
$table = $table.CounterSamples
}
$result = @{}
foreach ($row in $table) {
$instanceName = [regex]::match($row.Path,'(?&lt;=\().*(?=\))').Groups[0].Value
if ($row.CookedValue -ne $null) {
$result[$instanceName] = $row.CookedValue
} else {
$result[$instanceName] = -1
}
}
return $result
}

#SqlConnectionCommon.ps1

function SqlTcpPortIsEmpty($tcpPort) {
return [string]::IsNullOrEmpty($tcpPort) -or $tcpPort -eq '0'
}

function GetDataSource($dataSource, $tcpPort) {
$targetDataSource = $dataSource

if (!(SqlTcpPortIsEmpty $tcpPort)){
$nameParts = $dataSource.Split("{\}")
$targetDataSource = $nameParts[0] + "," + $tcpPort
}
return $targetDataSource
}

function GetSqlServerHostName(
[string] $strDNSComputerName,
[string] $instanceName,
[string] $namespace) {

$serviceName = GetSQLServiceName $instanceName
$escapedServiceName = EscapeWmiString $serviceName

$advProperties = Get-WmiObject -ComputerName $strDNSComputerName -Namespace ("ROOT\Microsoft\SqlServer\$namespace") -Class "SqlServiceAdvancedProperty" -Property "PropertyName", "PropertyNumValue", "PropertyStrValue" -Filter "SqlServiceType = 1 AND ServiceName = '$escapedServiceName' AND (PropertyName = 'Clustered' OR PropertyName = 'VSNAME')" -ErrorAction Stop

$isClustered = ($advProperties | Where {$_.PropertyName -eq "CLUSTERED"} | Select -First 1).PropertyNumValue

$hostName = 0
if($isClustered -eq 0){
$hostName = (Get-WmiObject -ComputerName $strDNSComputerName -Namespace ("ROOT\Microsoft\SqlServer\$namespace") -Class "SqlService" -Filter "SQLServiceType = 1 AND ServiceName = '$escapedServiceName'" -ErrorAction Stop | Select -First 1).HostName
}
else{
$hostName = ($advProperties | Where {$_.PropertyName -eq "VSNAME"} | Select -First 1).PropertyStrValue
}

return New-Object PSObject -Property @{HostName = $hostName; IsClustered = $isClustered}
}

function GetSqlServerHostNameEx(
[string] $strDNSComputerName,
[string] $instanceName,
[string] $namespace) {

$statePath = "SQL2016MP\SqlHostNames"
$hostValueName = EscapeCacheValueName $strDNSComputerName
$isClusteredValueName = $hostValueName + "_IsClustered"
$cacheExpirationTime = 7200

$isClustered = $null
$hostName = TryGetValueFromCache $statePath $hostValueName $cacheExpirationTime
if(![string]::IsNullOrEmpty($hostName)){
$isClustered = TryGetValueFromCache $statePath $isClusteredValueName $cacheExpirationTime
}

if(![string]::IsNullOrEmpty($hostName) -and ![string]::IsNullOrEmpty($isClustered) -and '01'.Contains($isClustered)){
return New-Object PSObject -Property @{HostName = $hostName; IsClustered = if($isClustered -eq '0') {$false} else {$true}}
}

$hostNameData = GetSqlServerHostName $strDNSComputerName $instanceName $namespace

PutValueToCache $statePath $hostValueName $hostNameData.HostName | Out-Null
PutValueToCache $statePath $isClusteredValueName "$(if($hostNameData.IsClustered){1} else {0})" | Out-Null

return $hostNameData
}

function BuildDataSourceFromParts($computerName, $instanceName, $tcpPort){
$dataSource = $computerName
if($instanceName -ne "MSSQLSERVER"){
$dataSource = "$computerName\$instanceName"
}
return GetDataSource $dataSource $tcpPort
}

function BuildConnectionString(
[String] $dataSource,
[string] $databaseName,
[String] $timeout = 100,
[String] $user = $null,
[String] $password = $null) {
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder

$builder["Data Source"] = $dataSource
$builder["Initial Catalog"] = $databaseName
$builder['Connection Timeout'] = $timeout
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 GetEnabledSqlServerProtocols($computerName, $instanceName) {
$protocolsWmi = Get-WmiObject -ComputerName $computerName -Namespace "root\Microsoft\SqlServer\ComputerManagement13" -Class 'ServerNetworkProtocol' -Filter "InstanceName = '$instanceName'"
$protocolsArr = @()

if($protocolsWmi -ne $null)
{
foreach($protocol in $protocolsWmi){
if($protocol.Enabled){
$protocolsArr+=($protocol.ProtocolName.ToLower())
}
}
}

return (,$protocolsArr)
}

function GetSqlServerTcpIpSettings([string] $instanceName){
$ipSettings = @{}

$settingsWmi = Get-WmiObject -Namespace 'root\Microsoft\SqlServer\ComputerManagement13' -Class 'ServerNetworkProtocolProperty' -Filter "ProtocolName = 'Tcp' and InstanceName = '$instanceName'"

$listenAllObj = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.PropertyName -eq "ListenOnAllIPs")}

$listenAll = $false
if($listenAllObj.PropertyNumVal -eq 1){
$listenAll = $true
}

if($listenAll) {
$portArr = @()
$tcpIpAll = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and ($_.IPAddressName -eq "IPAll") -and (($_.PropertyName -eq "TcpPort") -or ($_.PropertyName -eq "TcpDynamicPorts")) -and ($_.PropertyStrVal -ne '')}

foreach($port in $tcpIpAll)
{
$splittedPorts = $port.PropertyStrVal.Split("{,}", [System.StringSplitOptions]::RemoveEmptyEntries) | %{$_.Trim()} | ?{-not (SqlTcpPortIsEmpty $_)}
$portArr += $splittedPorts
}
$ipSettings.Add("IPAll", $portArr);
}
else{
$ipAddresses = ($settingsWmi | Where-Object { ($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.IPAddressName -ne "")-and($_.PropertyName -eq "Enabled") -and ($_.PropertyNumVal -eq 1)})

foreach($ipAddress in $ipAddresses){
$ipAddressName = $ipAddress.IPAddressName

$ip = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.IPAddressName -eq $ipAddressName)-and( $_.PropertyName -eq "IpAddress") -and ($_.PropertyStrVal -ne '')} | select -ExpandProperty PropertyStrVal

$ports = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.IPAddressName -eq $ipAddressName)-and( ($_.PropertyName -eq "TcpPort") -or ($_.PropertyName -eq "TcpDynamicPorts")) -and ($_.PropertyStrVal -ne '')}

$portArr = @()
foreach($port in $ports)
{
$splittedPorts = $port.PropertyStrVal.Split("{,}", [System.StringSplitOptions]::RemoveEmptyEntries) | %{$_.Trim()} | ?{-not (SqlTcpPortIsEmpty $_)}
$portArr += $splittedPorts
}
$ipSettings.Add($ip, $portArr);
}
}
return New-Object -TypeName PSObject -Property @{'ListenAllIPs' = $listenAll; 'IpSettings' = $ipSettings }
}

function SqlTryToConnectAndValidate(
[String] $dataSource,
[string] $databaseName,
[String] $hostName,
[String] $instanceName,
[string] $timeout = 30,
[String] $user = $null,
[String] $password = $null){

$connectionString = BuildConnectionString $dataSource $databaseName $timeout $user $password
$query = "SELECT SERVERPROPERTY('MachineName') AS ServerName, @@servicename AS InstanceName"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection ($connectionString)
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter ($query,$sqlConnection)
$dataSet = New-Object System.Data.DataSet
try{
$sqlConnection.Open()
$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()
if (($hostName.ToUpperInvariant() -eq $queryServerName) -and ($instanceName.ToUpperInvariant() -eq $queryInstanceName)) {
return $sqlConnection;
}
throw "Connection target check failed: connected to $hostName\$instanceName, but got $queryServerName\$queryInstanceName."
}
}
catch{
$sqlConnection.Dispose()
throw
}
}

function SmartConnectNTE($connectionDataSource, $databaseName, $machineName, $instanceName, $debug_user = $null, $debug_password = $null){

try{
return SmartConnect $connectionDataSource $databaseName $machineName $instanceName $debug_user $debug_password
}
catch{}

return $null
}

function SmartConnect($connectionDataSource, $databaseName, $machineName, $instanceName, $debug_user = $null, $debug_password = $null){

$hostNamesPath = "SQL2016MP\SqlHostNames"

$hostValueName = EscapeCacheValueName $machineName
$instanceValueName = EscapeCacheValueName $instanceName

$paramsPath = "SQL2016MP\SmartConnectParams\{0}\{1}" -f $hostValueName, $instanceValueName

$cacheExpirationTime = [int]::MaxValue

$netBiosHostName = TryGetValueFromCache $hostNamesPath $hostValueName $cacheExpirationTime
$targetDataSource = TryGetValueFromCache $paramsPath "TargetDataSource" $cacheExpirationTime
$timeout = TryGetValueFromCache $paramsPath "ConnectionTimeout" $cacheExpirationTime

$lastError = $null
$errorColl = @()

if($netBiosHostName -ne $null -and $targetDataSource -ne $null -and $timeout -ne $null) {
try {
return SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName $timeout $debug_user $debug_password
}
catch {
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
}

$connectionString = [string]::Empty
$targetDataSource = ""

$netBiosHostNameData = GetSqlServerHostNameEx $machineName $instanceName 'ComputerManagement13'
$netBiosHostName = $netBiosHostNameData.HostName
$dnsHostName = $machineName.Split(".")[0]

$enabledServerProtocols = $null
$connStr = $connectionDataSource

if(($netBiosHostName -eq $env:COMPUTERNAME) -and ($netBiosHostName -ne $dnsHostName) -and (!$netBiosHostNameData.IsClustered)) {
$enabledServerProtocols = GetEnabledSqlServerProtocols $machineName $instanceName
if($enabledServerProtocols -contains "sm") {
$connStr = 'lpc:' + (BuildDataSourceFromParts $netBiosHostName $instanceName '')
}
}

try{
$targetDataSource = GetDataSource $connStr ""
SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 15 $debug_user $debug_password
PutValueToCache $paramsPath "TargetDataSource" $targetDataSource | Out-Null
PutValueToCache $paramsPath "ConnectionTimeout" 15 | Out-Null
return
}
catch{
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}

if($enabledServerProtocols -eq $null){
$enabledServerProtocols = GetEnabledSqlServerProtocols $machineName $instanceName
}

if ($enabledServerProtocols -contains "tcp") {
$tcpIpSettings = GetSqlServerTcpIpSettings $instanceName
$pathArray = $connectionDataSource.Split("{'\'}")
$targetName = $pathArray[0]

if ($tcpIpSettings.ListenAllIps) {
foreach($port in $tcpIpSettings.IpSettings["IPAll"]){
try {
$targetDataSource = GetDataSource $targetName $port
SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password
PutValueToCache $paramsPath "TargetDataSource" $targetDataSource | Out-Null
PutValueToCache $paramsPath "ConnectionTimeout" 10 | Out-Null
return
}
catch {
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
}
}
else{
$upc = New-Object "System.Collections.Generic.HashSet[string]"
foreach($portArr in $tcpIpSettings.IpSettings.Values){
if(($portArr.Length -gt 0) -and $upc.Add($portArr[0])){
try {
$targetDataSource = GetDataSource $targetName $portArr[0]
SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password
PutValueToCache $paramsPath "TargetDataSource" $targetDataSource | Out-Null
PutValueToCache $paramsPath "ConnectionTimeout" 10 | Out-Null
return
}
catch {
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
}
}
foreach($pair in $tcpIpSettings.IpSettings.GetEnumerator()){
foreach($port in $pair.Value){
try {
$targetDataSource = GetDataSource $pair.Key $port
SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password
PutValueToCache $paramsPath "TargetDataSource" $targetDataSource | Out-Null
PutValueToCache $paramsPath "ConnectionTimeout" 10 | Out-Null
return
}
catch {
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
}
}
}
}
throw [Exception] ("Cannot connect to the target Sql Server instance.`nConnection log:`n" + [string]::Join([Environment]::NewLine, $errorColl)) #, $lastError
}

function SqlQueryTablesCommon($sqlConnection, $inputQuery, [bool]$useDbName, $dbName, $queryParams){
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$dataSet = New-Object System.Data.DataSet
try {
$query = $inputQuery
if($useDbName) {
$query = $inputQuery -f ($dbName -replace '"', '""')
}
$sqlCmd.CommandText = $query
$sqlCmd.Connection = $sqlConnection
$sqlCmd.CommandTimeout = 60000
for ($i = 0; $i -lt $queryParams.Length; $i++) {
$sqlCmd.Parameters.AddWithValue(("@p" + ($i+1)), [object]($queryParams[$i])) | Out-Null
}
$sqlAdapter.SelectCommand = $sqlCmd
$sqlAdapter.Fill($dataSet) | Out-Null
if(($dataSet.Tables.Count -eq 0) -or ($DataSet.Tables[0] -eq $null))
{
throw 'Can not query data from {0} database. Please check read permissions for this db.' -f $SqlConnection.Database
}
}
finally{
$sqlAdapter.Dispose()
$sqlCmd.Dispose()
}
return $dataSet.Tables
}

function SqlQueryTables($sqlConnection, $query) {
return SqlQueryTablesCommon $sqlConnection $query $false $null $args
}

function SqlConnQueryTables($sqlConnection, $query, $dbName) {
return SqlQueryTablesCommon $sqlConnection $query $true $dbName $args
}

function SqlQueryRows($sqlConnection, $query){
return (SqlQueryTablesCommon $sqlConnection $query $false $null $args | Select -First 1).Rows
}

function SqlConnQueryRows($sqlConnection, $query, $dbName){
return (SqlQueryTablesCommon $sqlConnection $query $true $dbName $args | Select -First 1).Rows
}

function SqlQueryScalar($SqlConnection, $query) {
$sqlAdapter = New-Object 'System.Data.SqlClient.SqlDataAdapter' ($query, $SqlConnection)
$dataSet = New-Object System.Data.DataSet
$sqlAdapter.SelectCommand.CommandTimeout = 60000
$SqlAdapter.Fill($dataSet) | Out-Null
if(($dataSet.Tables.Count -eq 0) -or ($DataSet.Tables[0] -eq $null))
{
throw 'Can not query data from {0} database. Please check read permissions for this db.' -f $SqlConnection.Database
}
return ($dataSet.Tables[0]|Select -first 1)[0]
}

#''' 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([System.Data.SqlClient.SqlConnection]$SqlConnection, $DatabaseID)
{
$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')"
$dr = SqlConnQueryTables $SqlConnection $query | Select -First 1

if($dr -eq $null -or $dr.HasAlwaysOn -ne 1) {
return -1
}

$query = "SELECT d.name, d.database_id, drs.is_primary_replica AS db_is_primary_replica
, CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica
, CASE WHEN drs.is_primary_replica = 1 THEN ar.primary_role_allow_connections ELSE ar.secondary_role_allow_connections END AS role_allow_connections
, CASE WHEN drs.is_suspended = 0 THEN -1 ELSE suspend_reason END AS db_suspended_state
FROM sys.databases as d
JOIN sys.dm_hadr_database_replica_states drs ON drs.database_id = d.database_id
JOIN sys.availability_replicas ar on d.replica_id = ar.replica_id
WHERE drs.is_local = 1 AND d.database_id = @p1"
$rdr = SqlQueryTables $SqlConnection $query $DatabaseID | Select -First 1

if($rdr -ne $null -and $rdr.is_replica -eq 1) {
if($rdr.role_allow_connections -le 1) {
return 0
}
else {
if($rdr.db_suspended_state -gt 0 -and (!$rdr.db_is_primary_replica -or ($rdr.db_is_primary_replica -and $rdr.db_suspended_state -ne 5))){
return 0
}
return 1
}
}
return -1
}

#Common.ps1

#-------------------------------------------------------------------------------
#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 -Property 'Name,State' -Namespace $namespace -ComputerName $server -Class "win32_service" -Filter "Name = '$service'" -ErrorAction SilentlyContinue
if ($obje -ne $null) {
return $obje.State
}
}
catch {
}
return "Unknown"
}

function GetSQLServiceName($InstanceName)
{
if ($InstanceName -eq "MSSQLSERVER")
{
return "MSSQLSERVER"
}
return 'MSSQL$' + $InstanceName
}

function IsNullOrZero($count)
{
return ($count -eq 0) -or ($count -eq $null)
}

function LogEventDebug($message) {
if($DEBUG_MODE -eq 1) {
$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $message)
}
}

function ConvertToInt($v) {
if ($v -eq $null) {
return $null;
}
if ([int].IsInstanceOfType($v)) {
return $v;
}
$res = $null;
if([int]::TryParse($v.ToString(),[ref] $res)) {
return $res;
}
return $null;
}

function EscapeWmiString($str) {
return $str -replace "\\","\\" -replace "'","\'"
}

function CreatePathRecursive($path) {
if (Test-Path $path) {
return;
}
$parts = $path.Split('\');
$curPath = $parts[0];
for ($i=1;$i -lt $parts.Length;$i++) {
$curPath = $curPath + '\'+$parts[$i];
if (!(Test-Path $curPath)) {
New-Item -Path $curPath -ErrorAction SilentlyContinue|Out-Null
}
}
}

function PutValueToCache($path,$key, $value) {
if ($cacheExpirationTime -eq $null -or $cacheExpirationTime -eq 0) {
return;
}
$registryKey = $api.GetScriptStateKeyPath($ManagementGroupID);
$keyPath = "HKLM:\" + $registryKey + "\" + $path;
CreatePathRecursive $keyPath
try {
$creationKey = $key+"_CreationTime";
$ip = Get-ItemProperty -Path $keyPath -Name $creationKey -ErrorAction SilentlyContinue;
if ($ip -ne $null) {
Remove-ItemProperty -Path $keyPath -Name $creationKey
}

$ip = Get-ItemProperty -Path $keyPath -Name $key -ErrorAction SilentlyContinue;
# recreate key to avoid type problem
if ($ip -ne $null) {
Remove-ItemProperty -Path $keyPath -Name $key
}

# null - delete old entry
if ($value -ne $null) {
New-ItemProperty -Path $keyPath -Name $key -Value $value|Out-Null
$creationDate = [DateTime]::UtcNow.ToString('yyyy-MM-ddTHH:mm:ssZ',[System.Globalization.CultureInfo]::InvariantCulture)
New-ItemProperty -Path $keyPath -Name $creationKey -Value $creationDate|Out-Null
}
} catch {
LogEventDebug "Someone is refreshing data at $keyPath $key or no access"
}
LogEventDebug "Set at $keyPath $key value $value"
return $ip.$key;
}

# Returns all non-expired properties from specified path in cache as hashtable
function GetValuesFromCache($statePath, $cacheExpirationTime) {

function GetActualValue($name, $props) {
$creationKey = $name + "_CreationTime"

if (($props -eq $null) -or ($props[$creationKey] -eq $null)) {
return $null;
}

[ref]$creationDate = [DateTime]::MinValue;

[System.Globalization.CultureInfo]$ci = [System.Globalization.CultureInfo]::InvariantCulture;

$v = [System.DateTime]::TryParseExact($props[$creationKey].Value,'yyyy-MM-ddTHH:mm:ssK',$ci,[System.Globalization.DateTimeStyles]::None,$creationDate);

if (!$v) {
return $null;
}
$diff = [DateTime]::UtcNow - $creationDate.Value;

if ($diff.TotalSeconds -gt $cacheExpirationTime) {
return $null;
}
return $props[$name].Value;
}


$result = @{}

$registryKey = $api.GetScriptStateKeyPath($ManagementGroupID);
$path = "HKLM:\" + $registryKey + "\" + $statePath;

if (!(Test-Path $path) -or $cacheExpirationTime -eq $null -or $cacheExpirationTime -eq 0) {
return $result;
}

$ip = Get-ItemProperty -Path $path
$properties = $ip.psobject.Properties;

$msg = "Got from $path : ";
if($properties -ne $null) {
foreach ($property in $properties) {
$pn = $property.Name;
$pv = GetActualValue $pn $properties;
if ($pv -ne $null) {
$result[$pn] = $pv
$msg += "$pn :$pv ;"
}
}
}
LogEventDebug $msg
return $result;
}

function TryGetValueFromCache($statePath, $key, $cacheExpirationTime) {
$registryKey = $api.GetScriptStateKeyPath($ManagementGroupID);
$path = "HKLM:\" + $registryKey + "\" + $statePath;

if (!(Test-Path $path) -or $cacheExpirationTime -eq $null -or $cacheExpirationTime -eq 0) {
return $null;
}

$creationKey = $key + "_CreationTime"
$regObj = Get-ItemProperty -Path $path -Name $creationKey -ErrorAction SilentlyContinue

if ($regObj -eq $null) {
return $null;
}

$creationDateStr = $regObj.$creationKey

[ref]$creationDate = [DateTime]::MinValue;

[System.Globalization.CultureInfo]$ci = [System.Globalization.CultureInfo]::InvariantCulture;

$v = [System.DateTime]::TryParseExact($creationDateStr,'yyyy-MM-ddTHH:mm:ssK',$ci,[System.Globalization.DateTimeStyles]::None,$creationDate);

if (!$v) {
return $null;
}
$diff = [DateTime]::UtcNow - $creationDate.Value;

if ($diff.TotalSeconds -gt $cacheExpirationTime) {
LogEventDebug "Cache entry at $path key $key got expired"
return $null;
}

$valueObj = Get-ItemProperty -Path $path -Name $key -ErrorAction SilentlyContinue

if ($valueObj -eq $null) {
return $null;
}

$value = $valueObj.$key
LogEventDebug "Got from $path key $key value $value"
return $value
}

function EscapeCacheValueName([string] $name) {
return $name -replace "_", "__"
}
Main
</Script></Contents>
<Unicode>true</Unicode>
</File>
</Files>
<OutputType>System.PropertyBagData</OutputType>
<DefaultEventPolicy/>
<EventPolicy/>
</ProbeAction>
</MemberModules>
<Composition>
<Node ID="PS">
<Node ID="Scheduler"/>
</Node>
</Composition>
</Composite>
</ModuleImplementation>
<OutputType>System!System.PropertyBagData</OutputType>
</DataSourceModuleType>