Technical Article

PowerShell script to create SQL Server alias name

,

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"

}

}

}

 

  

Rate

Share

Share

Rate