Always use SQL-Alias in your SharePoint farm

Not every farm is set up using SQL-Alias. It’s not clear to me why this is so. Always. Do. SQL-Alias.

I’ve heard excuses like: “That’s much more work”. Yea. Like 5 clicks for each server. Granted.

But that got me thinking on how to automate the creation of SQL-Alias in a farm. I started out by wanting to utilize (Get-SPFarm).Servers. However, the farm is only accessible after is is set up. That would be too late.

Therefore I created a script to set a SQL-Alias for a list of servers:

<#
.SYNOPSIS
Sets SQL-Aliases on multiple machines
.PARAMETER Alias
The Alias to use
.PARAMETER Server
The server to point to
.PARAMETER Port
The port to point to
.PARAMETER Use32Bit
set this flag to add the alias to 32bit only
.PARAMETER Use64Bit
set this flag to add the alias to 64bit only
.PARAMETER Machines
list of machines to set the alias on. Default is local
.EXAMPLE
Set-SqlAlias -Alias "sql1" -Server RealSqlServer
Sets a SQL-Alias from sql1 to point to RealSqlServer for 32 and 64Bit on the local machine only.
.EXAMPLE
Set-SqlAlias -Alias "oldServer\instance" -Server "newServer\Instance" -Use64Bit -Machines ((Get-SPFarm).Servers | ?{ $_.Role -ne "Invalid"} | Select-Object -ExpandProperty Address)
Sets a SQL-Alias from "oldServer\instance" to point to "newServer\Instance" for 64Bit only. This will be set on all machines of a SharePoint-Farm.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]$Alias,
[Parameter(Mandatory=$true)]
[string]$Server,
[Parameter()]
[int]$Port,
[Parameter()]
[switch]$Use32Bit,
[Parameter()]
[switch]$Use64Bit,
[Parameter()]
[string[]]$Machines
)
$ErrorActionPreference="Stop"
if((-not $Use64Bit) -and (-not $Use32Bit)){
# default is 32 and 64
$Use64Bit = $true
$Use32Bit = $true
}
if($Use64Bit -and (-not [Environment]::Is64BitProcess)){
throw "Unable to access 64Bit-Registry from a non-64Bit-Process. Use 64Bit PowerShell."
}
if($Machines -eq $null -or $Machines.Length -eq 0){
$Machines = @($env:COMPUTERNAME)
}
$regViews = @();
if($Use32Bit) {
$regViews += [Microsoft.Win32.RegistryView]::Registry32
}
if($Use64Bit) {
$regViews += [Microsoft.Win32.RegistryView]::Registry64
}
$Machines | % {
$machine = $_
$regViews | % {
$view = $_
Write-Verbose "Accessing '$($machine)' for $($view)"
try {
$regEdit = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("LocalMachine", $machine, $view)
}
catch [IO.IOException] {
throw "Unable to connect to '$($machine)'. Error: $($_.Exception.Message)"
}
$key = $regEdit.OpenSubKey("SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo", $true)
if($key -eq $null) {
$key = $regEdit.OpenSubKey("SOFTWARE\Microsoft\MSSQLServer\Client", $true)
if($key -eq $null) {
throw "SQL-ClientTools not installed on $($machine)"
}
$key = $key.CreateSubKey("ConnectTo", $true)
if($key -eq $null) {
throw "Unable to access HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo on '$($machine)'."
}
}
$val = "DBMSSOCN,$($Server)"
if($Port -gt 0) {
$val += ",$($Port)"
}
$key.SetValue($Alias, $val)
$regEdit.Close()
}
}
view raw Set-SqlAlias.ps1 hosted with ❤ by GitHub

Consequently I also created a script to list the existing SQL-Alises:

<#
.SYNOPSIS
Gets SQL-Aliases on multiple machines
.PARAMETER Use32Bit
set this flag to get 32bit aliases only
.PARAMETER Use64Bit
set this flag to get 64bit aliases only
.PARAMETER Machines
list of machines to connect. Default is local
.EXAMPLE
Get-SqlAlias
gets all 32 and 64 Bit aliases of the local machine.
.EXAMPLE
Get-SqlAlias -Use64Bit -Machines ((Get-SPFarm).Servers | ?{ $_.Role -ne "Invalid"} | Select-Object -ExpandProperty Address)
Gets all 64 Bit aliases for all machines of a SharePoint-Farm.
#>
[CmdletBinding()]
param(
[Parameter()]
[switch]$Use32Bit,
[Parameter()]
[switch]$Use64Bit,
[Parameter()]
[string[]]$Machines
)
$ErrorActionPreference="Stop"
if((-not $Use64Bit) -and (-not $Use32Bit)){
# default is 32 and 64
$Use64Bit = $true
$Use32Bit = $true
}
if($Use64Bit -and (-not [Environment]::Is64BitProcess)){
throw "Unable to access 64Bit-Registry from a non-64Bit-Process. Use 64Bit PowerShell."
}
if($Machines -eq $null -or $Machines.Length -eq 0){
$Machines = @($env:COMPUTERNAME)
}
$regViews = @();
if($Use32Bit) {
$regViews += [Microsoft.Win32.RegistryView]::Registry32
}
if($Use64Bit) {
$regViews += [Microsoft.Win32.RegistryView]::Registry64
}
$Machines | % {
$machine = $_
$regViews | % {
$view = $_
Write-Verbose "Accessing '$($machine)' for $($view)"
try {
$regEdit = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("LocalMachine", $machine, $view)
}
catch [IO.IOException] {
throw "Unable to connect to '$($machine)'. Error: $($_.Exception.Message)"
}
$key = $regEdit.OpenSubKey("SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo", $true)
if($key -eq $null) {
$regEdit.Close()
return
}
$names = $key.GetValueNames()
$names | % {
$parts = $key.GetValue($_).Split(",")
$server = $parts[1]
$port = ""
if($parts.Length -gt 2) {
$port = $parts[2]
}
[pscustomobject]@{
Machine = $machine;
RegistryView = $view;
Alias = $_;
Server = $server;
Port = $port
}
}
$regEdit.Close()
}
}
view raw Get-SqlAlias.ps1 hosted with ❤ by GitHub