SQL Server PowerShell

Microsoft.SQLServer.2016.AlwaysOn.RunPowerShellForAvailabilityReplica (ConsoleTask)

Open SQLPS console and connect to target Availability Replica.

Element properties:

Source Code:

<ConsoleTask ID="Microsoft.SQLServer.2016.AlwaysOn.RunPowerShellForAvailabilityReplica" Accessibility="Internal" Target="AlwaysOnDisc!Microsoft.SQLServer.2016.AlwaysOn.AvailabilityReplica" RequireOutput="false" Category="MonitoringObject">

  <Application>powershell.exe</Application>

  <Parameters>

    <Parameter>-NoExit -NoLogo -NoProfile -Command Invoke-Command {

#AlwaysOnCommon.ps1



#Extracts computer address from Sql Mirroring endpoint Uri

#Uri format: TCP://&lt;system-address&gt;:&lt;port&gt;

function GetAddressFromUri {

	param(

		[string] $uri

	)

    $match = [regex]::Match($uri, '^TCP://(?:(?&lt;address&gt;[^:/]+)|(?:\[(?&lt;address&gt;[^/\]]+\]))):\d{1,5}$', 'IgnoreCase')

    if(!$match.Success){

        throw 'Provided URI ''' + $uri + ''' has invalid format'

    }

    return $match.Groups['address'].Value

}



function QueryWmiWithRetry {

    param(

        [string] $query, 

        [string] $namespace, 

        [string] $computerName = $env:COMPUTERNAME, 

        [int] $retryCount = 3,

        [int] $timeoutMs = 100

    )



	for($i = 1; $i -le $retryCount; $i++) {

		try {

			return Get-WmiObject -ComputerName $computerName -Namespace $namespace -Query $query -ErrorAction Stop 

		}

		catch {

			if($i -ge $retryCount) {

				throw

			}

            else {

                Start-Sleep -Milliseconds $timeoutMs

            }

		}

	}

}



function GetFqdnDns {

	param([string] $computerName)

	

	$dnsName = [System.Net.Dns]::GetHostByName($serverName).HostName

	if($dnsName.IndexOf('.') -lt 0){

		throw 'Returned name ''' + $dnsName + ''' is not FQDN'

	}

	return $dnsName

}



function CombineFqdnName {

    param(

        [string] $dnsName, 

        [string] $domainName

    )

    

    if ([string]::IsNullOrEmpty($dnsName) -or [string]::IsNullOrEmpty($domainName)) {

        return $dnsName

    }

    return $dnsName + '.' + $domainName

}



# Get FQDN of machine via WMI

# We have to use WMI instead of using DNS 

# because we often deal with NetBios host names and Sql Server mirroring endpoint Urls

# and in these cases DNS may fail or returned name may be not FQDN

function GetFqdnWmi {

	param(

		[string] $computerName

	)



    $comp = QueryWmiWithRetry 'SELECT DNSHostName, Domain FROM Win32_ComputerSystem' 'ROOT\CIMV2' $computerName | Select -First 1

	return CombineFqdnName $comp.DNSHostName $comp.Domain

}



function GetPrincipalNameInfo {

    param(

        [string] $netbiosName, 

        [string] $targetHostName, 

        [Nullable[bool]] $isClustered = $null

    )



	$connectionHostName = $targetHostName;

	

    $compObj = QueryWmiWithRetry 'SELECT Name, DNSHostName, Domain FROM Win32_ComputerSystem' 'ROOT\CIMV2' $connectionHostName | Select -First 1



	$compDomainName = $compObj.Domain

	

	if (($isClustered -eq $null -or !$isClustered.Value) -and $compObj -ne $null){

		if($compObj.Name -eq $netbiosName){

			return New-Object PSObject -Property @{DnsHostName = $compObj.DNSHostName; DomainName = $compObj.Domain; PrincipalName = (CombineFqdnName $compObj.DNSHostName $compObj.Domain); NetbiosName = $compObj.Name; IsClustered = $false}

		}

	}

	

    $hostIsClustered = (QueryWmiWithRetry 'SELECT Name FROM __NAMESPACE WHERE Name = ''MSCluster''' 'ROOT' $connectionHostName) -ne $null



	if(($isClustered -eq $null -or $isClustered.Value) -and $hostIsClustered){

        $searchObj = (QueryWmiWithRetry 'SELECT PrivateProperties FROM MSCluster_Resource WHERE Type = ''Network Name''' 'ROOT\MSCluster' $connectionHostName) | Select -ExpandProperty 'PrivateProperties' | Where {

            $_.Name -eq $netbiosName

		}

		

		if($searchObj -ne $null){

			[string]$domainName = $searchObj.DnsSuffix

			if([string]::IsNullOrEmpty($domainName)){

				$domainName = $compDomainName

			}

			return New-Object PSObject -Property @{DnsHostName = $searchObj.DnsName; DomainName = $domainName; PrincipalName = (CombineFqdnName $searchObj.DnsName $domainName); NetbiosName = $searchObj.Name; IsClustered = $true}

		}

	}	

	

	throw 'No Principal name was found for NetBios name ''' + $netbiosName + '''' 

}



function SmartGetFqdnAlwaysOn {

	param(

		[string] $serverName = $null,

		[string] $endpointUrl = $null

	)

	

	if([string]::IsNullOrEmpty($serverName)) {

		throw 'Server name is empty'

	}



	$errMessages = @()

	$fqdn = $null



	try {

		$fqdn = GetFqdnDns $serverName

		return $fqdn

	}

	catch {

		$errMessages += 'Unable to get FQDN via DNS using name ''' + $serverName + ''': ' + $_.Exception.Message

	}

		

	try {

		$fqdnInfo = GetPrincipalNameInfo $serverName

		return $fqdnInfo.PrincipalName

	}

	catch {

		$errMessages += 'Unable to get FQDN via WMI using name ''' + $serverName + ''': ' + $_.Exception.Message

	}

	

	if(![string]::IsNullOrEmpty($endpointUrl)) {

		$endpointServerName = $null

		try {

			$endpointServerName = GetAddressFromUri $endpointUrl

			$fqdnInfo = GetPrincipalNameInfo $serverName $endpointServerName

			return $fqdnInfo.PrincipalName

		}

		catch {

			$errMessages += 'Unable to get FQDN via WMI using host address ''' + $endpointServerName + ''' and server name ''' + $serverName + ''': ' + $_.Exception.Message

		}

	}

	

	throw ('Unable to get AlwaysOn Replica''s host FQDN. Attempt log:{0} {1}' -f [Environment]::NewLine, ($errMessages -join ([Environment]::NewLine + ' ')))

}





function SmartGetFqdnAlwaysOnEx {

	param(

		[string] $serverName,

		[string] $endpointUrl

	)

	$statePath = 'SQL2016MP\SqlHostNames'

	$fqdnValueName = (EscapeCacheValueName $serverName) + '_fqdn'

	$cacheExpirationTime = 43200



	$fqdn = TryGetValueFromCache $statePath $fqdnValueName $cacheExpirationTime



	if(![string]::IsNullOrEmpty($fqdn)){

		return $fqdn

	}



	$fqdn = SmartGetFqdnAlwaysOn $serverName $endpointUrl



	PutValueToCache $statePath $fqdnValueName $fqdn | Out-Null



	return $fqdn

}



function GetEmptyNonSnapshotData($api, $mE, $tID)	{

	$discoveryData = $api.CreateDiscoveryData(0, $mE, $tID)

	$discoveryData.IsSnapshot = $false

	return $discoveryData

}



function ImportModuleActionSqlModules($arAssemblies){

	$paths = GetSqlToolsPaths

	if (!(ImportSQLModules $arAssemblies $paths))	{

	    Import-Module SQLPS -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null

	    if(!(IsModuleLoaded 'SQLPS'))

	    {

	        throw 'Unable to load SQLPS modules'

	    }

	}

}



function GetSqlToolsPaths(){

	$pathVersions = @('130','140','120','110')

	$pathTemplates = @('HKLM:\Software\Microsoft\Microsoft SQL Server\{0}\Tools\ClientSetup\',

					   'HKLM:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\{0}\Tools\ClientSetup\')

	$includeTemplate = '{0}\Tools'

	$paths = @()

	

	foreach($version in $pathVersions){

		$checkInclude = $includeTemplate -f $version

		foreach($template in $pathTemplates){

			$path = $template -f $version

			$sqlPathObj = Get-ItemProperty -Path $path -Name 'SQLPath' -ErrorAction SilentlyContinue

			if($sqlPathObj -ne $null){

				$sqlPath = $sqlPathObj.SQLPath

				if(![string]::IsNullOrEmpty($sqlPath) -and $sqlPath.Contains($checkInclude)){

					$paths += $sqlPath

				}

			}

		}

	}

	return $paths

}



function IsModuleLoaded([string]$include) { 

    $modules = Get-Module

    foreach($module in $modules){

		if($module.Name.Contains($include))	{

		    return $true

		}

    }

    return $false

}



function ImportSQLModules($arAssemblies, $paths) {  

    foreach($path in $paths){ 

        foreach($assemblyName in $arAssemblies){

            $filename = $path + $assemblyName

			if(Test-Path $filename){

				Import-Module $filename -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null

				return $true;

			}

        }

    }

	return $false

}



#AlwaysOnTaskCommon.ps1



#Depends on:

# AlwaysOnCommon.ps1



$arAssemblies = @(,'\PowerShell\Modules\SQLPS\SQLPS.psd1');

ImportModuleActionSqlModules $arAssemblies



function GetInstancePathName([string] $name)

{

    if ($name -ilike '*\*')

    {

        return $name

    }

    else

    {

        return '{0}\DEFAULT' -f $name

    }

}



function EncodeSqlName([string] $name)

{

    $sb = New-Object 'System.Text.StringBuilder'

	

	for($ind = 0; $ind -lt $name.Length; ++$ind)

    {

        if([char]::IsControl($name[$ind]) -or [int]$name[$ind] -eq 46 -or '\\/:%&lt;&gt;*?[]|'.Contains($name[$ind].ToString()))

        {

			[void]$sb.Append(('%{0:X2}' -f [byte]$name[$ind]))

		}

		else

        {

            [void]$sb.Append($name[$ind])

		}

	}

	return $sb.ToString()

}







$l = 0

$destInstanceNameParam = @'

$Target/Property[Type="System!System.Entity"]/DisplayName$

'@



$l = 0

$agNameParam = @'

$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2016.AlwaysOn.AvailabilityReplica"]/AvailabilityGroupName$

'@



$l = 0

$replicaNameParam = @'

$Target/Property[Type="System!System.Entity"]/DisplayName$

'@



$destInstanceName = GetInstancePathName $destInstanceNameParam

$agPathName = EncodeSqlName $agNameParam

$replicaPathName = EncodeSqlName $replicaNameParam

cd ('sqlserver:\SQL\{0}\AvailabilityGroups\{1}\AvailabilityReplicas\{2}\' -f $destInstanceName, $agPathName, $replicaPathName) }</Parameter>

  </Parameters>

  <WorkingDirectory/>

</ConsoleTask>