Managing Client Network Protocols with WMI Provider

最新推荐文章于 2018-12-16 18:31:41 发布

Burgess_Liu

最新推荐文章于 2018-12-16 18:31:41 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： PowerShell 文章标签： protocols network sqlserver microsoft class methods

本文链接：https://blog.youkuaiyun.com/Burgess_Liu/article/details/7377795

PowerShell 专栏收录该内容

30 篇文章

订阅专栏

本文介绍如何使用PowerShell脚本管理SQL Server客户端网络协议，包括查看协议列表、启用和禁用协议、更改协议顺序及修改默认端口等操作。

The ClientNetworkProtocol class represents the objects for client network protocols. To see the list of protocols of ClientNetworkProtocol class:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class ClientNetworkProtocol | Select-Object ProtocolName, ProtocolDisplayName,ProtocolOrder

To see all the methods you can apply to the client network protocol instances:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class ClientNetworkProtocol | Get-Member -memberType Method | Select-Object Name

To disable the Named Pipes protocol:

$clientprotocol=Get-WmiObject –namespace root\Microsoft\SqlServer\ComputerManagement10 -class ClientNetworkProtocol -filter "ProtocolName='np'"
$clientprotocol.SetDisable()

To verify the result:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class ClientNetworkProtocol | Select-Object ProtocolName, ProtocolDisplayName,ProtocolOrder

If you’d like to re-enable the Named Pipes protocol and place it above the TCP/IP protocol, which has an order of 2 in the list, you can simply use the SetOrderValue method toset the order of the Named Pipes protocol to 2, to take the place of the TCP/IP protocol:

$clientprotocol=Get-WmiObject –namespace root\Microsoft\SqlServer\ComputerManagement10 -class ClientNetworkProtocol -filter "ProtocolName=’np’"
$clientprotocol.SetOrderValue(2)

To verify the result, sort all the client network protocols by their order:
Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class ClientNetworkProtocol | Select-Object ProtocolName, ProtocolDisplayName,ProtocolOrder | Sort-Object ProtocolOrder

To get the property names and their associated protocol names of ClientNetworkProtocolProperty:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class ClientNetworkProtocolProperty | Select-Object PropertyName, ProtocolName

To change the default port of the TCP/IP protocol from 1433 to 7001:

$strComputer = "."
$protocolproperty=Get-WmiObject –computerName $strComputer –namespace root\Microsoft\SqlServer\ComputerManagement10 –class ClientNetworkProtocolProperty -filter "PropertyName=’Default Port’"
$protocolproperty.SetNumericalValue(7001)

To verify the result:

Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class ClientNetworkProtocolProperty -filter "PropertyName=’Default Port’"

The complete script（ChangeTCPIPDefaultPort.ps1）：

$wmi=Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class ServerNetworkProtocolProperty `
-filter "PropertyName='TcpPort' and IPAddressName='IPAll' and InstanceName='MSSQLSERVER'?

$wmi.SetStringValue(7001) | Out-Null

$sqlservice = Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService `
-filter "ServiceName='MSSQLSERVER'"

$sqlservice.StopService() | Out-Null

$sqlservice.StartService() | Out-Null

# Confirm the default port number has been changed
Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement10 -class ServerNetworkProtocolProperty `
-filter "PropertyName='TcpPort' and IPAddressName='IPAll' and InstanceName='MSSQLSERVER'" | Select-Object PropertyStrVal