Failover manuale

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

Aprire la console SQLPS ed eseguire il failover nella replica di disponibilità di destinazione in modo che quest'ultima diventi la nuova replica primaria del gruppo di disponibilità.

Element properties:

Source Code:

<ConsoleTask ID="Microsoft.SQLServer.2016.AlwaysOn.ManualFailoverForAvailabilityReplica" 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)

$avg = (Get-Item ('..\..\..\{0}' -f $agPathName))

try

{

  Switch-SqlAvailabilityGroup -InputObject $avg -Confirm;

}

catch

{

  Write-Host -ForegroundColor Red $_.Exception.Message;

}

Write-Host "Press any key to exit...";

$key=[Console]::ReadKey($true);}</Parameter>

  </Parameters>

  <WorkingDirectory/>

</ConsoleTask>