SQL Server PowerShell

Microsoft.SQLServer.2016.AlwaysOn.RunPowerShellForAvailabilityReplica (ConsoleTask)

Ouvrez la console SQLPS et établissez une connexion au réplica de disponibilité cible.

Element properties:

Source Code:

<ConsoleTask ID="Microsoft.SQLServer.2016.AlwaysOn.RunPowerShellForAvailabilityReplica" Accessibility="Internal" Target="AlwaysOnDisc!Microsoft.SQLServer.2016.AlwaysOn.AvailabilityReplica" RequireOutput="false" Category="MonitoringObject">

  <Application>powershell.exe</Application>

  <Parameters>

    <Parameter>-NoExit -NoLogo -NoProfile -Command Invoke-Command {$l = 0;$ag='$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2016.AlwaysOn.AvailabilityReplica"]/AvailabilityGroupName$';

          $l = 0;$serverName = '$Target/Property[Type="System!System.Entity"]/DisplayName$';

          $l = 0;$dbReplicaName = ([regex]::Replace('$Target/Property[Type="System!System.Entity"]/DisplayName$', '\\', '%5C'));

          #AlwaysOnCommon.ps1



#Extracts computer address from Sql Mirroring endpoint Uri

#Uri format: TCP://&lt;system-address&gt;:&lt;port&gt;

function GetAddressFromUri{

	param(

		[Parameter(Mandatory=$true)]

		[string] $uri

	)

    $match = [regex]::Match($uri, '^TCP://(?:(?&lt;address&gt;[^:/]+)|(?:\[(?&lt;address&gt;[^/\]]+\]))):\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 } 

}





          $rp = ('{0}' -f (p($serverName))); cd ('sqlserver:\SQL\{0}\AvailabilityGroups\{1}\AvailabilityReplicas\{2}\' -f $rp, $ag, $dbReplicaName);}</Parameter>

  </Parameters>

  <WorkingDirectory/>

</ConsoleTask>