Aprire la console SQLPS e sospendere lo spostamento dei dati per la replica di database di destinazione.
Target | Microsoft.SQLServer.2012.AlwaysOn.DatabaseReplica |
Application | powershell.exe |
WorkingDirectory | |
isRequireOutput | False |
Accessibility | Internal |
Enabled | True |
<ConsoleTask ID="Microsoft.SQLServer.2012.AlwaysOn.SuspendDataMovement" Accessibility="Internal" Target="AlwaysOnDisc!Microsoft.SQLServer.2012.AlwaysOn.DatabaseReplica" RequireOutput="false" Category="MonitoringObject">
<Application>powershell.exe</Application>
<Parameters>
<Parameter>-Version 2 -NoExit -NoLogo -NoProfile -Command Invoke-Command {$l = 0;$ag='$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2012.AlwaysOn.DatabaseReplica"]/AvailabilityGroupName$';
$l = 0;$serverName = '$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2012.AlwaysOn.DatabaseReplica"]/AvailabilityReplicaServerName$';
$l = 0;$dbReplicaName = ([regex]::Replace('$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2012.AlwaysOn.DatabaseReplica"]/AvailabilityDatabaseName$', '\\', '%5C'));
#AlwaysOnCommon.ps1
#Extracts computer address from Sql Mirroring endpoint Uri
#Uri format: TCP://<system-address>:<port>
function GetAddressFromUri{
param(
[string] $uri
)
$match = [regex]::Match($uri, '^TCP://(?:(?<address>[^:/]+)|(?:\[(?<address>[^/\]]+\]))):\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,
[string] $retryCount = 3
)
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
}
}
}
}
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
}
# 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
if($comp.Domain -eq '') {
return $comp.DNSHostName
}
return $comp.DNSHostName + '.' + $comp.Domain
}
function SmartGetFqdnAlwaysOn {
param(
[string] $serverName = $null,
[string] $endpointUrl = $null
)
$errMessages = @()
$fqdn = $null
if(![string]::IsNullOrEmpty($serverName)) {
try {
$fqdn = GetFqdnDns $serverName
return $fqdn
}
catch {
$errMessages += 'Unable to get FQDN via DNS using name ''' + $serverName + ''': ' + $_.Exception.Message
}
try {
$fqdn = GetFqdnWmi $serverName
return $fqdn
}
catch {
$errMessages += 'Unable to get FQDN via WMI using name ''' + $serverName + ''': ' + $_.Exception.Message
}
}
else {
$errMessages += 'Server name is empty'
}
if(![string]::IsNullOrEmpty($endpointUrl)) {
$endpointServerName = $null
try {
$endpointServerName = GetAddressFromUri $endpointUrl
$fqdn = GetFqdnWmi $endpointServerName
return $fqdn
}
catch {
$errMessages += 'Unable to get FQDN via WMI using name ''' + $endpointServerName + ''': ' + $_.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 = 'SQLMPSP1\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 = @(
(get-itemproperty -path 'hklm:\Software\Microsoft\Microsoft SQL Server\110\Tools\ClientSetup\' | Select SQLPath).SQLPath ,
(get-itemproperty -path 'hklm:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\110\Tools\ClientSetup\' | Select SQLPath).SQLPath )
if (!(ImportSQLModules $arAssemblies $paths '110\Tools')) {
Import-Module SQLPS -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null
if(!(IsModuleLoaded 'SQLPS'))
{
throw 'Unable to load SQLPS modules'
}
}
}
function IsModuleLoaded([string]$include) {
$modules = Get-Module
foreach($module in $modules){
if($module.Name.Contains($include)) {
return $true
}
}
return $false
}
function ImportSQLModules {
param (
$arAssemblies,
$paths,
[string]$include)
$flRet = $false
foreach($path in $paths){
if(![string]::IsNullOrEmpty($path) -and $path.Contains($include)){
foreach($assemblyName in $arAssemblies){
$filename = $path + $assemblyName
if(Test-Path $filename){
Import-Module $filename -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null
$flRet = $true;
}
}
}
}
return $flRet
}
#AlwaysOnTaskCommon.ps1
#Depends on:
# AlwaysOnCommon.ps1
$arAssemblies = @(,'\PowerShell\Modules\SQLPS\SQLPS.psd1');
ImportModuleActionSqlModules $arAssemblies
function p{
param($v)
if ($v -ilike '*\*') { return $v } else { return '{0}\DEFAULT' -f $v }
}
$rp = ('{0}' -f (p($serverName)));
cd ('sqlserver:\SQL\{0}\AvailabilityGroups\{1}\AvailabilityDatabases\{2}\' -f $rp, $ag, $dbReplicaName);
Suspend-SqlAvailabilityDatabase -Confirm;
Write-Host 'Press any key to exit...';
$key=[Console]::ReadKey($true);
}</Parameter>
</Parameters>
<WorkingDirectory/>
</ConsoleTask>