This script is useful for your environment having multiple always-on with replication setup and your listener value is persisted with RegisterallIP set to 1.
When initiating the always-on failover, It will create/update the alias in the distributor server by pointing to the new primary server without RDP to each distributor server and that makes the log reader agent works uninterruptedly.
$Computers = Get-Content -Path 'T:\StampsDB\computerlist.txt'
foreach ($computer in $Computers) {
If (test-connection -ComputerName $computer -Count 1 -Quiet) {
Try {
Invoke-Command -ComputerName $Computers -ScriptBlock {
#These are the two Registry locations for the SQL Alias
$x86 = "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo"
$x64 = "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"
#if the ConnectTo key doesn't exists, create it.
if ((test-path -path $x86) -ne $True)
{
New-Item $x86
}
if ((test-path -path $x64) -ne $True)
{
New-Item $x64
}
#Name of your SQL Server Alias
$AliasName = ""
# Actual SQL Server Name
$SQLServerName = ""
#TCP Port
$Port = "1433"
#Define SQL Alias
$TCPAliasName = "DBMSSOCN,$SQLServerName,$Port"
function Test-RegistryValue($path, $name)
{
$key = Get-Item -LiteralPath $path -ErrorAction SilentlyContinue
$key -and $null -ne $key.GetValue($name, $null)
}
if ((Test-RegistryValue -path "$x64" -name "$AliasName" ) -ne $True)
{
#Create TCP/IP Aliases
New-ItemProperty -Path $x64 -Name $AliasName -PropertyType String -Value $TCPAliasName
}
else
{
#UpdateTCP/IP Aliases
Set-ItemProperty -Path $x64 -Name $AliasName -Value $TCPAliasName
}
if ((Test-RegistryValue -path "$x86" -name "$AliasName" ) -ne $True)
{
#Create TCP/IP Aliases
New-ItemProperty -Path $x86 -Name $AliasName -PropertyType String -Value $TCPAliasName
}
else
{
#UpdateTCP/IP Aliases
Set-ItemProperty -Path $x86 -Name $AliasName -Value $TCPAliasName
}
}
$status = "Success"
}
Catch {
$status = "Failed"
}
}
}
