Adding a SQL Alias to all the Servers in a SharePoint Farm using PowerShell and Remoting

Today I needed to add a new SQL alias to all the servers in a large SharePoint Farm.

There are 15 servers in this farm (not including the SQL clusters). So I didn't want to logon to each server in the farm to add the alias manually.

I thought it would be great if I could just run a single PowerShell script on one of the SharePoint servers that added the alias to all the servers in the farm.

That's where PowerShell Remoting comes to the rescue! I can execute a script on multiple servers from a single server!

Here's how.

Cooking Time:
5 mins

Ingredients:
1 x Script to execute
1 x Credential (requires administrative permissions)
A handful of Servers

Method:
1. Create a credential object

$account = Read-Host -Prompt "Enter the farm account";            
$password =  Read-Host -Prompt "Enter the farm account password" -AsSecureString
$credentials = New-Object System.Management.Automation.PsCredential($account,$password);

2. Get all the "SharePoint" servers in the farm (a.k.a any server that has the SharePoint binaries installed on it).

I'm filtering the list of servers in the farm based on the Role = Application. This ensures we don't get SQL Servers and email servers.

$farm = Get-SPFarm            
$servers = $farm.Servers | ?{$_.Role -eq "Application"} | Select Name

3. Store the SQL Alias information in some variables

$aliasname = "HR"             
$sqlserver = "sqlserver\hrinstancename"            
$tcpalias = "DBMSSOCN," + $sqlserver

4. Pipe the list of servers to the Foreach-Object (%) cmdlet, and let the magic begin!

In each loop of the for-each block, create a new PSSession, using the server name and the credential object created earlier, to connect to the remote server.

Once you have the new remote PSSession, use the Invoke-Command cmdlet to run the PowerShell script in the remote session.  Pass the $aliasname and $tcpalias variables to Invoke-Command, so that they can be used in the script block.

The PowerShell for actually adding the aliases is a slightly modified version of a script from the guys at Habanero Consulting

Finally, remember to close the PSSession at the end of the block

$servers | %{                
    $rs = New-PSSession -ComputerName $_.Name -Credential $credentials;            
    Write-Host "Adding SQL Aliases to"$_.Name -f green;            
    Invoke-Command -Session $rs -Script {             
  param($AliasName = "", $TCPAlias = "")            
        Write-Host "Working on"$env:COMPUTERNAME;
        $x86 = "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo"            
        $x64 = "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"
        if ((test-path -path $x86) -ne $True){write-host "$x86 doesn't exist";New-Item $x86}            
        if ((test-path -path $x64) -ne $True){write-host "$x64 doesn't exist";New-Item $x64}           
        $p = $null;            
        $p = Get-ItemProperty -Path $x86 -Name $AliasName -ErrorAction:SilentlyContinue
        if($p -eq $null){Write-Host "creating x86 alias" -f Yellow; New-ItemProperty -Path $x86 -Name $AliasName -PropertyType String -Value $TCPAlias}            
                
        $p = $null;            
        $p = Get-ItemProperty -Path $x64 -Name $AliasName -ErrorAction:SilentlyContinue            
        if($p -eq $null){Write-Host "creating x64 alias" -f Yellow;New-ItemProperty -Path $x64 -Name $AliasName -PropertyType String -Value $TCPAlias}            
    }                
    Remove-PSSession -Session $rs;                
} -ArgumentList $aliasname,$tcpalias

And that's it! It's as easy as that!
Kaaaaa PoW!