sql - 使用 PowerShell 将 SQL 的 TCP 端口设置为 1433

Question

我目前在 SQL Server 配置管理器中的 TCP 端口显示为空白。我需要弄清楚如何使用 PowerShell 将其更改为端口 1433。我已经能够运行它来实际获取值:

$smo = 'Microsoft.SqlServer.Management.Smo.'
$wmi = new-object ($smo + 'Wmi.ManagedComputer')

$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + 
"']/ServerInstance[@Name='SQLSERVER']/ServerProtocol[@Name='Tcp']"
$Np = $wmi.GetSmoObject($uri)
$NPProp = $Np.IPAddresses.where({$_.Name -eq 'IPALL'})
$NPPropTCP = $NPProp.IPAddressProperties.where({$_.Name -eq 'TcpPort'})
$NPPropTCP.Value

但是，当我运行时:

$NPPropTCP[1].Value = '1433'

我收到以下错误:

The property 'Value' cannot be found on this object. Verify that the property exists and can be set.
At line:20 char:1
+ $NPPropTCP[1].Value = '1433'
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [],
ParentContainsErrorRecordException
+ FullyQualifiedErrorId : PropertyNotFound

我想这可能很简单，但我被卡住了......

Answer 1

更新以显示每个命令的结果

您的代码中有错误/遗漏的东西。

试试这个方法...

'Loading SQLPS environment'
Import-Module SQLPS -DisableNameChecking -Force


Results

Loading SQLPS environment


'Initializing WMI object and Connect to the instance using SMO'
($Wmi = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $env:COMPUTERNAME)


Results

Initializing WMI object and Connect to the instance using SMO


ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services           : {MSSQLFDLauncher, MSSQLSERVER, SQLBrowser, SQLSERVERAGENT}
ClientProtocols    : {np, sm, tcp}
ServerInstances    : {MSSQLSERVER}
ServerAliases      : {}
Urn                : ManagedComputer[@Name='SQL01']
Name               : SQL01
Properties         : {}
UserData           : 
State              : Existing



($uri = "ManagedComputer[@Name='$env:COMPUTERNAME']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']")


Results

ManagedComputer[@Name='SQL01']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']



# Getting settings
($Tcp = $wmi.GetSmoObject($uri))


Results

Parent              : Microsoft.SqlServer.Management.Smo.Wmi.ServerInstance
DisplayName         : TCP/IP
HasMultiIPAddresses : True
IsEnabled           : True
IPAddresses         : {IP1, IP2, IP3, IP4...}
ProtocolProperties  : {Enabled, KeepAlive, ListenOnAllIPs}
Urn                 : ManagedComputer[@Name='SQL01']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']
Name                : Tcp
Properties          : {Name=DisplayName/Type=System.String/Writable=False/Value=TCP/IP, Name=HasMultiIPAddresses/Type=System.Boolean/Writable=False/Value=True, 
                      Name=IsEnabled/Type=System.Boolean/Writable=True/Value=True}
UserData            : 
State               : Creating


$Tcp.IsEnabled = $true


($Wmi.ClientProtocols)


Parent             : Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
DisplayName        : Named Pipes
IsEnabled          : True
NetworkLibrary     : SQLNCLI11
Order              : 3
NetLibInfo         : Microsoft.SqlServer.Management.Smo.Wmi.NetLibInfo
ProtocolProperties : {Default Pipe}
Urn                : ManagedComputer[@Name='SQL01']/ClientProtocol[@Name='np']
Name               : np
Properties         : {Name=DisplayName/Type=System.String/Writable=False/Value=Named Pipes, Name=IsEnabled/Type=System.Boolean/Writable=True/Value=True, 
                     Name=NetworkLibrary/Type=System.String/Writable=False/Value=SQLNCLI11, Name=Order/Type=System.Int32/Writable=True/Value=3}
UserData           : 
State              : Existing

Parent             : Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
DisplayName        : Shared Memory
IsEnabled          : True
NetworkLibrary     : SQLNCLI11
Order              : 1
NetLibInfo         : Microsoft.SqlServer.Management.Smo.Wmi.NetLibInfo
ProtocolProperties : {}
Urn                : ManagedComputer[@Name='SQL01']/ClientProtocol[@Name='sm']
Name               : sm
Properties         : {Name=DisplayName/Type=System.String/Writable=False/Value=Shared Memory, Name=IsEnabled/Type=System.Boolean/Writable=True/Value=True, 
                     Name=NetworkLibrary/Type=System.String/Writable=False/Value=SQLNCLI11, Name=Order/Type=System.Int32/Writable=True/Value=1}
UserData           : 
State              : Existing

Parent             : Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
DisplayName        : TCP/IP
IsEnabled          : True
NetworkLibrary     : SQLNCLI11
Order              : 2
NetLibInfo         : Microsoft.SqlServer.Management.Smo.Wmi.NetLibInfo
ProtocolProperties : {Default Port, KEEPALIVE (in milliseconds), KEEPALIVEINTERVAL (in milliseconds)}
Urn                : ManagedComputer[@Name='SQL01']/ClientProtocol[@Name='tcp']
Name               : tcp
Properties         : {Name=DisplayName/Type=System.String/Writable=False/Value=TCP/IP, Name=IsEnabled/Type=System.Boolean/Writable=True/Value=True, 
                     Name=NetworkLibrary/Type=System.String/Writable=False/Value=SQLNCLI11, Name=Order/Type=System.Int32/Writable=True/Value=2}
UserData           : 
State              : Existing



$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties


Name       : TcpDynamicPorts
Value      : 
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}

Name       : TcpPort
Value      : 14433
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}


'Setting IP Properties'
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value="1433"

'Review properties'
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties


Name       : TcpDynamicPorts
Value      : 
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}

Name       : TcpPort
Value      : 1433
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}


'Save properties'
$Tcp.Alter()

'Review properties'
$wmi.GetSmoObject($uri + "/IPAddress[@Name='IPAll']").IPAddressProperties


Name       : TcpDynamicPorts
Value      : 
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}

Name       : TcpPort
Value      : 1433
Type       : System.String
Writable   : True
Readable   : True
Expensive  : False
Dirty      : False
Retrieved  : True
IsNull     : False
Enabled    : False
Required   : False
Attributes : {}