by
Yan Pan
Manage SQL Server client alias
SQL Server client aliases make user connections easier,
faster and more convenient. Each alias saves all of the information you need to
connect to a SQL Server, such as the server name and the client protocol used
to connect to a server. By using an alias, you do not need to enter the
information each time you connect. You can also use an easy-to-remember
nickname that is different from the actual server name.
SqlServerAlias is the class for alias. To view a list of
aliases defined on the local computer, type:
PS > Get-WmiObject `
-namespace root\Microsoft\SqlServer\ComputerManagement `
-class SqlServerAlias
__GENUS : 2
__CLASS : SqlServerAlias
__SUPERCLASS :
__DYNASTY : SqlServerAlias
__RELPATH : SqlServerAlias.AliasName="ALIAS1"
__PROPERTY_COUNT : 4
__DERIVATION : {}
__SERVER : DPP-PANYAN01
__NAMESPACE : root\Microsoft\SqlServer\ComputerManagement
__PATH : \\DPP-PANYAN01\root\Microsoft\SqlServer\
ComputerManagement:SqlServerAlias.AliasName="ALIAS1"
AliasName : ALIAS1
ConnectionString : 7001
ProtocolName : tcp
ServerName : SERVER1
In this example, only an alias ALIAS1 is defined on the
local computer. For a remote computer, use the –computerName parameter to
specify the remote computer name.
The properties and methods that can be applied to the SqlServerAlias
class are shown below.
PS > Get-WmiObject `
-namespace root\Microsoft\SqlServer\ComputerManagement `
-class SqlServerAlias | Get-Member -memberType ScriptMethod
TypeName: System.Management.ManagementObject#root\Microsoft\
SqlServer\ComputerManagement\SqlServerAlias
Name MemberType Definition
---- ---------- ----------
ConvertFromDateTime ScriptMethod System.Object
ConvertFromDateTime();
ConvertToDateTime ScriptMethod System.Object
ConvertToDateTime();
Delete ScriptMethod System.Object Delete();
GetType ScriptMethod System.Object GetType();
Put ScriptMethod System.Object Put();
The Delete method can be used to delete an existing alias.
For example, if we want to delete the Alias ALIAS1 we have above, we can run
this script.
$oldalias=Get-WmiObject `
-namespace root\Microsoft\SqlServer\ComputerManagement `
-class SqlServerAlias `
-filter "AliasName='ALIAS1'"
$oldalias.Delete()
Get-WmiObject can only be used to manage the
existing SQL Server client aliases. To create a new alias, we need to use
New-Object to create objects from the SMO classes. Before PowerShell can call any .NET class, the assembly containing
the .NET class must be loaded first. By default, PowerShell loads a small list
of assemblies. This command gets the list of loaded assemblies.
PS > [System.AppDomain]::CurrentDomain.GetAssemblies() |
ForEach-Object { split-path $_.Location -leaf } | Sort-Object
Microsoft.PowerShell.Commands.Management.dll
Microsoft.PowerShell.Commands.Utility.dll
Microsoft.PowerShell.ConsoleHost.dll
Microsoft.PowerShell.Security.dll
mscorlib.dll
System.Configuration.Install.dll
System.Data.dll
System.DirectoryServices.dll
System.dll
System.Management.Automation.dll
System.Management.dll
System.Xml.dll
As you can see, the Microsoft.SqlServer.Smo.dll assembly is
not loaded by default. To load the assembly, run this command.
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
After the assembly is loaded, we first need to create an
object that represents the WMI installation on an instance of a Microsoft SQL
Server client because an alias must reside on a SQL Server client instance.
This object will be the parent of the alias we will create.
$strComputer='.' # '.' or 'localhost' for local computer.
$objComputer=New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
$strComputer
Then we create an alias object from the Microsoft.SqlServer.Management.Smo.Wmi.ServerAlias
class. This alias is under the ManagedComptuer object we created.
$newalias=New-Object ("Microsoft.SqlServer.Management.Smo.Wmi.ServerAlias")
$newalias.Parent=$objComputer
Each alias has many properties, such as the name of the
server to connect to and the client protocol, that need to be populated before
it can be created. In this example, we create an alias that connects to the
named instance remoteComputer\Instance1 using the TCP/IP protocol.
$newalias.Name='myalias' # myalias is the new alias name
# remoteComputer\Instance1 is the SQL Server instance the alias points to
$newalias.ServerName='remoteComputer\Instance1'
# 7001 is the port the SQL Server instance remoteComputer\Instance1 is listening on
$newalias.ConnectionString=7001
$newalias.ProtocolName='tcp'
$newalias.Create()
The complete script is in CreateServerAlias.ps1.
Manage server network protocols
The ServerNetworkProtocolProperty class represents the
server network protocols. To view the list of properties associated with each
network protocol, type:
PS > Get-WmiObject `
-namespace root\Microsoft\SqlServer\ComputerManagement `
-class ServerNetworkProtocolProperty `
-filter "InstanceName = 'MSSQLSERVER'" | Select ProtocolName, PropertyName
, IPAddressName
ProtocolName PropertyName IPAddressName
------------ ------------ -------------
Sm Enabled
Np PipeName
Np Enabled
Tcp KeepAlive
Tcp Enabled
Tcp ListenOnAllIPs
Tcp NoDelay
Tcp IpAddress IP1
Tcp TcpPort IP1
Tcp TcpDynamicPorts IP1
Tcp Enabled IP1
Tcp Active IP1
Tcp IpAddress IP2
Tcp TcpPort IP2
Tcp TcpDynamicPorts IP2
Tcp Enabled IP2
Tcp Active IP2
Tcp IpAddress IP3
Tcp TcpPort IP3
Tcp TcpDynamicPorts IP3
Tcp Enabled IP3
Tcp Active IP3
Tcp IpAddress IP4
Tcp TcpPort IP4
Tcp TcpDynamicPorts IP4
Tcp Enabled IP4
Tcp Active IP4
Tcp IpAddress IP5
Tcp TcpPort IP5
Tcp TcpDynamicPorts IP5
Tcp Enabled IP5
Tcp Active IP5
Tcp TcpPort IPAll
Tcp TcpDynamicPorts IPAll
Via DefaultServerPort
Via ListenInfo
Via Enabled
As we can see from the above, besides the common property
Enabled, each protocol has its own properties. The Named Pipe protocol has a
property called PipeName, which specifies the named
pipe on which SQL Server listens. The TCP/IP protocol is associated with
all of the IP addresses on the computer, including
the loopback address, 127.0.0.1, in the format IP1, IP2, up to IPAll. IPAll denotes
all of the IP addresses.
By default, the ListenOnAllIPs
property is enabled and SQL Server listens on all of the
IP addresses. The settings of the IPAll
address apply to all of the IP addresses.
To prevent hackers from using the
default port 1433 to “Slam” the default instance, you might want to change its
port number from 1433 to another port, for example, 3660. The script
ChangeDefaultPortNumber.ps1 is shown here.
$wmi=Get-WmiObject `
-namespace root\Microsoft\SqlServer\ComputerManagement `
-class ServerNetworkProtocolProperty `
-filter "PropertyName='TcpPort' and IPAddressName='IPAll' and InstanceName='MSSQLSERVER'"
$wmi.SetStringValue(3660)
$sqlservice = Get-WmiObject `
-namespace root\Microsoft\SqlServer\ComputerManagement `
-class SqlService -filter "ServiceName='MSSQLSERVER'"
$sqlservice.StopService()
$sqlservice.StartService()
# Confirm the default port number has been changed
Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement `
-class ServerNetworkProtocolProperty `
-filter "PropertyName='TcpPort' and IPAddressName='IPAll' and InstanceName='MSSQLSERVER'" | Select PropertyStrVal
The script first uses Get-Wmiobject to create an object
associated with the TcpPort property of the IPAll address of the default
instance. Then it invokes the SetStringValue method to change the property
value from 1433 to 3660. However, the change is not effective until the default
instance is restarted, so the script restarts the default instance. Finally,
the script prints out the value of the TcpPort property to confirm the change.
If you want to assign
different ports to different IP address, then you can disable the ListenOnAllIPs
property and configure the settings of each IP address. The
DisableListenOnAllIPs.ps1 script is shown here.
# remoteComputer is the name of the computer hosts the remote instance
$strComputer = "remoteComputer"
$strInstanceName = "Instance1" # The remote SQL Server instance name
$wmi=Get-WmiObject -computerName $strComputer `
-namespace root\Microsoft\SqlServer\ComputerManagement `
-class ServerNetworkProtocolProperty `
-filter "PropertyName='ListenOnAllIPs' and InstanceName='$strInstanceName'"
$wmi.SetFlag(0)
$sqlservice = Get-WmiObject `
-computerName $strComputer `
-namespace root\Microsoft\SqlServer\ComputerManagement `
-class SqlService `
-filter "ServiceName='MSSQL`$$strInstanceName'"
$sqlservice.StopService()
$sqlservice.StartService()
$wmi=Get-WmiObject -computerName $strComputer `
-namespace root\Microsoft\SqlServer\ComputerManagement `
-class ServerNetworkProtocolProperty `
-filter "PropertyName='ListenOnAllIPs' and InstanceName='$strInstanceName'"
# Confirm the ListenOnAllIPs property has been disabled.
Write-Host "The value of the ListenOnAllIPs property is set to " $wmi.PropertyNumVal
The script first uses Get-Wmiobject to create an
object associated with the ListenOnAllIPs property. Then it disables the ListenOnAllIPs property using the SetFlag
method to set the Boolean property value. Since the change does not take effect
until the corresponding SQL Server service is restarted, the script restarts
the service. Finally, the script confirms the change by printing out the value
of the ListenOnAllIPs property.
Conclusion
We have illustrated above the power of Windows PowerShell in
conjunction with the WMI Provider for Configuration Management. In the next
article, we will show you how to monitor events in an instance of SQL Server
using Windows PowerShell and the WMI Provider for Server Events.
About the author:
Yan Pan is certified in Microsoft database certifications,
including MCTS in SQL Server 2005 and MCDBA. She has more than 5 years of
experience in SQL Server administration and Analysis Server. Currently, Yan is
the primary SQL Server DBA in one of the top finance companies on Wall Street. She can be
reached at yanpandba@yahoo.com
» 
IE 7
Firefox 2.0
Comment and Contribute
