SQL 2012 Disk Read/Write Latency Data Source

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

Disk Read/Write Latency Data Source

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<DataSourceModuleType ID="Microsoft.SQLServer.2012.DBEngine.DiskLatency.DataSource" Accessibility="Public">

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

          <WorkingDirectory/>

          <CommandLine>

                  powershell.exe -version 2 -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="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$' -tcpPort '$Target/Host/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/TcpPort$' -SQL_WMI_NAMESPACE 'ComputerManagement11' -serviceName '$Target/Host/Property[Type="SQL!Microsoft.SQLServer.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, $SQL_WMI_NAMESPACE, $serviceName,$cacheExpirationTime=43200)



$SCRIPT_EVENT_ID = 4001

#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 $SQL_WMI_NAMESPACE $serviceName

}



function ModuleAction($ComputerName, $sqlInstanceName, $connectionString, $tcpPort, $SQL_WMI_NAMESPACE, $serviceName) {

	

	$msg = [Environment]::NewLine

	$err = [Environment]::NewLine

	

	# 

	# Prepare MOM API

	#

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



    if ($serviceName -eq "MSSQL") {

		$serviceName = "MSSQL`${0}" -f $sqlInstanceName 

	}



    $msg += "Computer Name = '$computerName' WMI = '$SQL_WMI_NAMESPACE' Service Name = '$serviceName' SQL Instance Name = '$sqlInstanceName' Connection String = '$connectionString' Tcp Port = '$tcpPort'"

    $msg += [Environment]::NewLine

	

	#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"))

	{

        if($DEBUG_MODE -eq 1) {

			$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg)

		}

		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 $sqlInstanceName $SQL_WMI_NAMESPACE $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 = ""

			

			$_.group | foreach {						

				$dbFileName = $_.physical_name

				$mountPoint = MatchMountPoint $mountPoints $dbFileName

				

				if ($mountPoint -ne $null) {

					

					if ($mountPoint[3] -gt $maxRead) {

						$maxRead = $mountPoint[3]

						$maxReadFile = $dbFileName

						$maxReadMountPoint = $mountPoint[0]

					}

					

					if ($mountPoint[4] -gt $maxWrite) {

						$maxWrite = $mountPoint[4]

						$maxWriteFile = $dbFileName

						$maxWriteMountPoint = $mountPoint[0]

					}

				} else {

					$err += "Can't find mount point for DB '$dbName' file '$dbFileName'"

					$err += [Environment]::NewLine

				}

			}

			

			[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, $sqlInstanceName

		$msg += $err

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

	} finally {

	    if ($sqlConnection -ne $null) {

	        $sqlConnection.Dispose()

	    }

	    if ($bagCount -gt 0) {

	        $api.ReturnItems(); 

	    }

	}

    if($DEBUG_MODE -eq 1) {

		$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg)		

	}

}

#SQL2012Constants.ps1



$MANAGEMENT_PACK_VERSION = "7.0.2.0"



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

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



#GlobalizationCommon.ps1



#Depends on:

#Common.ps1



$global:UICultureInfoWinApiValue = $null

$StateMPPrefix = 'SQLMPSP1'



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

# 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')

    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 regestry 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=$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 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



# Depends on:

#  Common.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 = "SQLMPSP1\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($namespaceName, $computerName, $instanceName) {

	$protocolsWmi = Get-WmiObject -Namespace "root\Microsoft\SqlServer\$namespaceName" -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, $sqlNamespace){

	$ipSettings = @{}

	

	$settingsWmi = Get-WmiObject -Namespace "root\Microsoft\SqlServer\$sqlNamespace" -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, $sqlNamespace, $debug_user = $null, $debug_password = $null){

	

	try{

		return SmartConnect $connectionDataSource $databaseName $machineName $instanceName $sqlNamespace $debug_user $debug_password

	}

	catch{}



	return $null

}



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

	

	$connectionString = [string]::Empty

	$lastError = $null

	$errorColl = @()

	$targetDataSource = ""

	

	$netBiosHostNameData = GetSqlServerHostNameEx $machineName $instanceName $sqlNamespace

	$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 $sqlNamespace $machineName $instanceName

		if($enabledServerProtocols -contains "sm") {

			$connStr = 'lpc:' + (BuildDataSourceFromParts $netBiosHostName $instanceName '')

		}

	}



	try{

		$targetDataSource = GetDataSource $connStr ""

		return SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 15 $debug_user $debug_password

	}

	catch{

		$lastError = $_.Exception

		$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"

	}

	

	if ($enabledServerProtocols -eq $null){

		$enabledServerProtocols = GetEnabledSqlServerProtocols $sqlNamespace $machineName $instanceName

	}



	if ($enabledServerProtocols -contains "tcp") {

		$tcpIpSettings = GetSqlServerTcpIpSettings $instanceName $sqlNamespace

		$pathArray = $connectionDataSource.Split("{'\'}")

		$targetName = $pathArray[0]

	

		if ($tcpIpSettings.ListenAllIps) {

			foreach($port in $tcpIpSettings.IpSettings["IPAll"]){

				try {

					$targetDataSource = GetDataSource $targetName $port

					return SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password

				}

				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]

						return SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password

					}

					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

						return SqlTryToConnectAndValidate $targetDataSource $databaseName $netBiosHostName $instanceName 10 $debug_user $debug_password

					}

					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

		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.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]

}



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