function GetSqlInstanceNameBySqlServiceName ($sqlServiceName) {
if ($sqlServiceName -eq $null) {
return $null
}
$i = $sqlServiceName.IndexOf('$');
if ($i -eq -1) {
return $sqlServiceName
}
return $sqlServiceName.Substring($i+1);
}
function GetLocalComputerName($computerName) {
if ($computerName -eq $null) {
return $null;
}
return $computerName.Split('.')[0]
}
function GetCoresCountThreshold($connection, $coresCount) {
[int]$maxWorkersCount = SqlQueryScalar $connection "SELECT max_workers_count as value FROM sys.dm_os_sys_info"
if ($maxWorkersCount -eq $null -or $maxWorkersCount -eq 0) {
}
if(IsNullOrZero $logicalCoresCount)
{
throw 'Can not query data from Win32_ComputerSystem WMI namespace. Error persisted after 3 tries.'
}
$perfCounterNames = Get-RegLocalizedPerfCounterNames
$processIdCounter = (Get-LocalizedPerfCounter $perfCounterNames $("\{0}(sqlservr*)\{1}") $(@( 230 , "Process" ), @( 784 , "ID Process" ))).CounterSamples
$processNameById = @{}
$instanceNameByID = GetRunningInstances
foreach ($row in $processIDCounter) {
if ($row.CookedValue -eq $null) {
$msg += "No ID for "+$row.Path + [System.Environment]::NewLine
}
$id = [uint32]$row.CookedValue;
# Get information from SQL Servers for missing instances
foreach ($row in $instanceNameByID.GetEnumerator()) {
$instancePid = $row.Key;
$instanceName = $row.Value;
$sqlConnection = $null;
# 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
$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
$cul = Get-UICultureWinApi
$lcid = $cul.LCID
$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(".")
#get language ID
$language = "{0:X3}" -f $(Get-GetLanguageId($osVersion))
#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 ) )
{
$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);'
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,'(?<=\().*(?=\))').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
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]
}
#''' 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
$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
#-------------------------------------------------------------------------------
#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
}