SQL server command to create server alias?

new9mtju  于 2023-04-10  发布在  SQL Server
关注(0)|答案(2)|浏览(176)

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

  1. In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Aliases, and then click New Alias.
  2. In the Alias Name box, type the name of the alias. Client applications use this name when they connect.
  3. In the Server box, type the name or IP address of a server. For a named instance append the instance name.
  4. 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?

qlzsbp2j

qlzsbp2j1#

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;
g9icjywg

g9icjywg2#

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()

相关问题