SQL Server PowerShell

Microsoft.SQLServer.2014.AlwaysOn.RunPowerShellForDatabaseReplica (ConsoleTask)

Open SQLPS console and connect to Availability Replica of target Database Replica.

Element properties:

TargetMicrosoft.SQLServer.2014.AlwaysOn.DatabaseReplica
Applicationpowershell.exe
WorkingDirectory
isRequireOutputFalse
AccessibilityInternal
EnabledTrue

Source Code:

<ConsoleTask ID="Microsoft.SQLServer.2014.AlwaysOn.RunPowerShellForDatabaseReplica" Accessibility="Internal" Target="AlwaysOnDisc!Microsoft.SQLServer.2014.AlwaysOn.DatabaseReplica" RequireOutput="false" Category="MonitoringObject">
<Application>powershell.exe</Application>
<Parameters>
<Parameter>-Version 2 -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 = 'SQL2014MP\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 = @('120','140','130','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) {
$assembliesToLoad = New-Object System.Collections.ArrayList ((,$arAssemblies))
foreach($path in $paths){
foreach($assemblyName in $assembliesToLoad.ToArray()){
$filename = $path + $assemblyName
if(Test-Path $filename){
$module = Import-Module $filename -ErrorAction SilentlyContinue -WarningAction SilentlyContinue -PassThru
if($module -eq $null){
continue
}
$assembliesToLoad.Remove($assemblyName)
if($assembliesToLoad.Count -eq 0){
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="AlwaysOnDisc!Microsoft.SQLServer.2014.AlwaysOn.DatabaseReplica"]/AvailabilityReplicaServerName$
'@

$l = 0
$agNameParam = @'
$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2014.AlwaysOn.DatabaseReplica"]/AvailabilityGroupName$
'@

$l = 0
$arNameParam = @'
$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2014.AlwaysOn.DatabaseReplica"]/AvailabilityReplicaServerName$
'@

$l = 0
$dbNameParam = @'
$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2014.AlwaysOn.DatabaseReplica"]/AvailabilityDatabaseName$
'@

$destInstanceName = GetInstancePathName $destInstanceNameParam
$agPathName = EncodeSqlName $agNameParam
$dbReplicaPathName = '{0}.{1}' -f (EncodeSqlName $arNameParam), (EncodeSqlName $dbNameParam)
cd ('sqlserver:\SQL\{0}\AvailabilityGroups\{1}\DatabaseReplicaStates\{2}\' -f $destInstanceName, $agPathName, $dbReplicaPathName); }</Parameter>
</Parameters>
<WorkingDirectory/>
</ConsoleTask>