Aprire la console SQLPS e connettersi alle repliche primarie del gruppo di disponibilità di destinazione.
Target | Microsoft.SQLServer.2012.AlwaysOn.AvailabilityGroup |
Application | powershell.exe |
WorkingDirectory | |
isRequireOutput | False |
Accessibility | Internal |
Enabled | True |
<ConsoleTask ID="Microsoft.SQLServer.2012.AlwaysOn.RunPowerShellForAvailabilityGroup" Accessibility="Internal" Target="AlwaysOnDisc!Microsoft.SQLServer.2012.AlwaysOn.AvailabilityGroup" RequireOutput="false" Category="MonitoringObject">
<Application>powershell.exe</Application>
<Parameters>
<Parameter>-Version 2 -NoExit -NoLogo -NoProfile -Command Invoke-Command {$l = 0;$pr='$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2012.AlwaysOn.AvailabilityGroup"]/PrimaryReplicaServerName$';
$l = 0;$displayName = '$Target/Property[Type="System!System.Entity"]/DisplayName$\';
#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,
[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 = '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 }
}
if (-Not [string]::IsNullOrEmpty($pr)) { $p = '{0}' -f (p($pr));
cd SQL\$p\AvailabilityGroups\;
cd $displayName -WarningAction SilentlyContinue; }
else { Write-Host 'There is no primary replica for this Availability Group' }}
</Parameter>
</Parameters>
<WorkingDirectory/>
</ConsoleTask>