SQL Server Alias management with PowerShell and WMI – Update for SQL Server 2012
· Ingo Karstein
In 02/2011 I created a PowerShell script to set / get / remove SQL Server aliases:
Today I updated it for SQL Server 2012 and tested it on Windows Server 2012 and Windows Server 2008 R2.
You can download it here:
http://gallery.technet.microsoft.com/SQL-Server-2008-2012-Alias-baf05737
2 comments
Gavin Jones Jun 6, 2013
Love the script, by the way.
Small tweak needed - using Server 2008r2 and SQL 2008r2 64 bit, was getting "Invalid namespace" errors when connectint calls "$s.connect()". I think you need to try calling connect in your try/catch waterfall to figure out SQL version. e.g.
try {
$s = New-Object System.Management.ManagementScope("\\\\.\\root\\microsoft\\sqlserver\\computermanagement11", $cnnOpt)
$s.connect()
} catch {
try {
$s = New-Object System.Management.ManagementScope("\\\\.\\root\\microsoft\\sqlserver\\computermanagement10", $cnnOpt)
$s.connect()
} catch {
$s = New-Object System.Management.ManagementScope("\\\\.\\root\\microsoft\\sqlserver\\computermanagement", $cnnOpt)
$s.connect()
}
}
Mat Culpepper Apr 8, 2014
I can confirm on a server with SQL2005 and SQL2008R2, the changes mentioned by Gavin Jones are required to get success, but the addition of the $s.Connect in each of the blocks (two try and one catch) does the trick quite nicely.