SQL server command to create server alias?

Question

MSDN states that there are 4 steps in creating a server alias:

In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Aliases, and then click New Alias.

In the Alias Name box, type the name of the alias. Client applications use this name when they connect.

In the Server box, type the name or IP address of a server. For a named instance append the instance name.

In the Protocol box, select the protocol used for this alias. Selecting a protocol, changes the title of the optional properties box to Port No, Pipe Name, or Connection String.

But instead of doing it the "UI way", is there a SQL command to do it?

Chris Chilvers · Accepted Answer · 2011-08-15 13:55:25Z

The configuration of server aliases for clients is a client configuration rather than a SQL server configuration. As such there's no SQL command to create one, much the same as there is no SQL command to create an ODBC connection.

You can script the configuration using WMI through powershell, the place to start is: http://msdn.microsoft.com/en-us/library/ms179354.aspx and http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.wmi.aspx

Here is a powershell example using wmi to create an alias

# List existing aliases
Get-WmiObject -Namespace 'root\Microsoft\SqlServer\ComputerManagement10' -Class 'SqlServerAlias' |
    Format-Table -Property 'AliasName', 'ServerName', 'ProtocolName', 'ConnectionString'

# Example script to create an alias
$alias = ([wmiclass] '\\.\root\Microsoft\SqlServer\ComputerManagement10:SqlServerAlias').CreateInstance()
$alias.AliasName = 'bob'
$alias.ConnectionString = '1433' #connection specific parameters depending on the protocol
$alias.ProtocolName = 'tcp'
$alias.ServerName = 'example_server'
$alias.Put() | Out-Null;

Julian Cheslock · Accepted Answer · 2019-06-19 15:53:02Z

0

To edit an existing alias, I would do a delete\insert.

I use this PS to delete the alias entries (SQL 2016):

$alias=Get-WmiObject -namespace 'root\Microsoft\SqlServer\ComputerManagement13' -class 'SqlServerAlias' -filter "AliasName='YourAliasName'"
$alias.Delete()

answered Jun 19, 2019 at 15:53

Julian Cheslock

1

Collectives™ on Stack Overflow

SQL server command to create server alias?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related