Abra o console do SQLPS e conecte-se às Réplicas Primárias do Grupo de Disponibilidade de destino.
Target | Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroup |
Application | powershell.exe |
WorkingDirectory | |
isRequireOutput | False |
Accessibility | Internal |
Enabled | True |
<ConsoleTask ID="Microsoft.SQLServer.2014.AlwaysOn.RunPowerShellForAvailabilityGroup" Accessibility="Internal" Target="AlwaysOnDisc!Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroup" 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://<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 = '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 '\\/:%<>*?[]|'.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.AvailabilityGroup"]/PrimaryReplicaServerName$
'@
$l = 0
$displayNameParam = @'
$Target/Property[Type="System!System.Entity"]/DisplayName$
'@
$destInstanceName = GetInstancePathName $destInstanceNameParam
$displayName = EncodeSqlName $displayNameParam
if (-Not [string]::IsNullOrEmpty($destInstanceName))
{
$p = '{0}' -f $destInstanceName;
cd SQL\$p\AvailabilityGroups\;
cd $displayName -WarningAction SilentlyContinue;
}
else
{
Write-Host 'There is no primary replica for this Availability Group'
} }
</Parameter>
</Parameters>
<WorkingDirectory/>
</ConsoleTask>