打开 SQLPS 控制台并且连接到目标可用性组的主副本。
Target | Microsoft.SQLServer.2016.AlwaysOn.AvailabilityGroup |
Application | powershell.exe |
WorkingDirectory | |
isRequireOutput | False |
Accessibility | Internal |
Enabled | True |
<ConsoleTask ID="Microsoft.SQLServer.2016.AlwaysOn.RunPowerShellForAvailabilityGroup" Accessibility="Internal" Target="AlwaysOnDisc!Microsoft.SQLServer.2016.AlwaysOn.AvailabilityGroup" RequireOutput="false" Category="MonitoringObject">
<Application>powershell.exe</Application>
<Parameters>
<Parameter>-NoExit -NoLogo -NoProfile -Command Invoke-Command {$l = 0;$pr='$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2016.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(
[Parameter(Mandatory=$true)]
[string] $uri
)
$match = [regex]::Match($uri, '^TCP://(?:(?<address>[^:/]+)|(?:\[(?<address>[^/\]]+\]))):\d{1,5}$', 'IgnoreCase')
if(!$match.Success){
throw 'Provided URI is in incorrect format'
}
return $match.Groups['address'].Value
}
#Get FQDN of machine
#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 GetFQDN{
param(
[Parameter(Mandatory=$true)]
[string] $computerName
)
$comp = Get-WmiObject -ComputerName $computerName -Class 'Win32_ComputerSystem' -ErrorAction Stop | Select -first 1
if($comp.Domain -eq ''){
return $comp.DNSHostName
}
return $comp.DNSHostName + '.' + $comp.Domain
}
function GetFQDNEx{
param(
[Parameter(Mandatory=$true)]
[string] $compAddress
)
$statePath = 'SQL2016MP\SqlHostNames'
$fqdnValueName = (EscapeCacheValueName $compAddress) + '_fqdn'
$cacheExpirationTime = 7200
$fqdn = TryGetValueFromCache $statePath $hostValueName $cacheExpirationTime
if(![string]::IsNullOrEmpty($fqdn)){
return $fqdn
}
$fqdn = GetFQDN $compAddress
PutValueToCache $statePath $fqdnValueName $fqdn | Out-Null
return $fqdn
}
function ImportModuleActionSqlModules($arAssemblies){
$paths = @(
(get-itemproperty -path 'hklm:\Software\Microsoft\Microsoft SQL Server\130\Tools\ClientSetup\' | Select SQLPath).SQLPath ,
(get-itemproperty -path 'hklm:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\130\Tools\ClientSetup\' | Select SQLPath).SQLPath )
if (!(ImportSQLModules $arAssemblies $paths '130\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($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>