How can I use powershell scripting to change SQL Server Aliases?

Question

I have an x64 machine that regularly needs about 10 aliases changed in the SQL Server Client Network Utility (cliconfig) for both the x64 and x86 sides of the registry. Is there a way I could do this with a powershell script?

Answer 1

cliconfig.exe is used in SQL Server 2000, the right tool to be using for SQL Server 2008 is SQL Server Configuration Manager. Nonetheless they both seem to manipulate the same registry keys.

For x86:

HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

For x64:

HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo

To create a new TCP alias with powershell (although you can use any tool that manipulates the registry) use the following:

New-ItemProperty HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo -name alias1 -propertytype String -value "DBMSSOCN,servera,2001"

Where alias1 is the name of the alias, servera is the name of the server and 2001 is the TCP port.

Answer 2

I believe you should use WMI for doing this. In root\Microsoft\SqlServer\ComputerManagement namespace there is an object of type SqlServerAlias which corresponds to server alias. Try using it - as far as I know using WMI is a recommended way of performing such tasks.