SQL Server PowerShell

Microsoft.SQLServer.2017.AlwaysOn.Windows.ConsoleTask.DatabaseReplica.RunPowerShell (ConsoleTask)

Open SQLPS console and connect to Availability Replica of target Database Replica.

Element properties:

TargetMicrosoft.SQLServer.2017.AlwaysOn.Windows.DatabaseReplica
Applicationpowershell.exe
WorkingDirectory
isRequireOutputFalse
AccessibilityInternal
EnabledTrue

Source Code:

<ConsoleTask ID="Microsoft.SQLServer.2017.AlwaysOn.Windows.ConsoleTask.DatabaseReplica.RunPowerShell" Accessibility="Internal" Target="SQL2017AWD!Microsoft.SQLServer.2017.AlwaysOn.Windows.DatabaseReplica" RequireOutput="false">
<Assembly>Res.Microsoft.SQLServer.2017.AlwaysOn.Windows.ConsoleTask.DatabaseReplica.RunPowerShell</Assembly>
<Handler>ShellHandler</Handler>
<Parameters>
<Argument Name="Application">powershell.exe</Argument>
<Argument Name="WorkingDirectory"/>
<Argument>-NoExit -NoLogo -NoProfile -Command Invoke-Command {
# PsTaskCommon.ps1

$arAssemblies = @(,'\PowerShell\Modules\SQLPS\SQLPS.psd1');

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 = @('140','130','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] $sqlModuleName) {
$modules = Get-Module
foreach($module in $modules){
if($module.Name.Contains($sqlModuleName)) {
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
}

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()
}

ImportModuleActionSqlModules $arAssemblies


$p = 0
$destInstanceNameParam = @'
$Target/Property[Type="SQL2017AG!Microsoft.SQLServer.2017.AlwaysOn.Library.DatabaseReplica"]/AvailabilityReplicaServerName$
'@
$p = 0
$agNameParam = @'
$Target/Property[Type="SQL2017AG!Microsoft.SQLServer.2017.AlwaysOn.Library.DatabaseReplica"]/AvailabilityGroupName$
'@
$p = 0
$arNameParam = @'
$Target/Property[Type="SQL2017AG!Microsoft.SQLServer.2017.AlwaysOn.Library.DatabaseReplica"]/AvailabilityReplicaServerName$
'@
$p = 0
$dbNameParam = @'
$Target/Property[Type="SQL2017AG!Microsoft.SQLServer.2017.AlwaysOn.Library.DatabaseReplica"]/AvailabilityDatabaseName$
'@

$destInstanceName = GetInstancePathName $destInstanceNameParam
$agPathName = EncodeSqlName $agNameParam
$dbReplicaPathName = '{0}.{1}' -f (EncodeSqlName $arNameParam), (EncodeSqlName $dbNameParam)

cd ('sqlserver:\SQL\{0}\AvailabilityGroups\{1}\DatabaseReplicaStates\{2}\' -f $destInstanceName, $agPathName, $dbReplicaPathName)
}</Argument>
</Parameters>
</ConsoleTask>