Dbatools as a replacement for SQL Server Configuration Manager
Controlling or adjusting the network configuration of a Microsoft SQL Server is still a reason for many administrators to connect to the server itself via RDP and start the SQL Server Configuration Manager there. But there is now an alternative to this, because I have added two new commands to the PowerShell module dbatools.
Small overview of the graphical interfaces
In addition, there is the SQL Server Configuration Manager, which can be found easily in Windows Start menu when looking for the red toolbox. However, this is not a program at all, but merely a snap-in for the MMC, the Microsoft Management Console. This is exactly the problem: The snap-in is only available on the SQL Server itself.
WMI - Windows Management Instrumentation
SQL Server Configuration Manager uses Windows Management Instrumentation, or WMI, to communicate with the operating system and thus to display and update the information.
Microsoft provides in the file Microsoft.SqlServer.SqlWmiManagement.dll the .NET class Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer, which can be used very easily within PowerShell to take over the tasks of the SQL Server Configuration Manager. I have explained the necessary code here.
Get-DbaNetworkConfiguration to display the current state
To make it even easier for users of the PowerShell module dbatools, I recently added the command Get-DbaNetworkConfiguration there, which reads a lot of the information displayed in the Configuration Manager via the .NET class and returns it as a PowerShell object.
PS C:\> Get-DbaNetworkConfiguration -SqlInstance SRV1\SQL2017
ComputerName : SRV1
InstanceName : SQL2017
SqlInstance : SRV1\SQL2017
SharedMemoryEnabled : True
NamedPipesEnabled : False
TcpIpEnabled : True
TcpIpProperties : @{Enabled=True; KeepAlive=30000; ListenAll=True}
TcpIpAddresses : {@{Name=IP1; Active=True; Enabled=False; IpAddress=fe80::5826:15a1:9f9c:55e3%6; TcpDynamicPorts=0; TcpPort=},
@{Name=IP2; Active=True; Enabled=False; IpAddress=192.168.3.51; TcpDynamicPorts=0; TcpPort=}, @{Name=IP3;
Active=True; Enabled=False; IpAddress=192.168.3.70; TcpDynamicPorts=0; TcpPort=}, @{Name=IP4; Active=True;
Enabled=False; IpAddress=fe80::509b:39b9:c7d3:f6c%5; TcpDynamicPorts=0; TcpPort=}...}
If only partial information is required, the output of the command can be customized using the OutputType parameter. In addition to the default setting Full, the options ServerProtocols, TcpIpProperties and TcpIpAddresses are also available.
PS C:\> Get-DbaNetworkConfiguration -SqlInstance SRV1\SQL2017 -OutputType ServerProtocols ComputerName : SRV1 InstanceName : SQL2017 SqlInstance : SRV1\SQL2017 SharedMemoryEnabled : True NamedPipesEnabled : False TcpIpEnabled : True
The ServerProtocols option specifies only for each of the three supported protocols whether it is enabled (True) or disabled (False).
PS C:\> Get-DbaNetworkConfiguration -SqlInstance SRV1\SQL2017 -OutputType TcpIpProperties ComputerName : SRV1 InstanceName : SQL2017 SqlInstance : SRV1\SQL2017 Enabled : True KeepAlive : 30000 ListenAll : True
The TcpIpProperties option returns the properties of the TCP/IP protocol. The parameter ListenAll is of major importance here. It specifies whether the SQL Server accepts connections on all existing IP addresses or only on specified addresses. The default value is True, this should only be changed in special situations.
PS C:\> Get-DbaNetworkConfiguration -SqlInstance SRV1\SQL2017 -OutputType TcpIpAddresses ComputerName : SRV1 InstanceName : SQL2017 SqlInstance : SRV1\SQL2017 Name : IPAll TcpDynamicPorts : 49919 TcpPort :
The TcpIpAddresses option provides information about the IP addresses used. If the TCP/IP parameter ListenAll is set to the default value True, then only the information for the entry named IPAll is displayed here, since the other entries are not active.
Set-DbaNetworkConfiguration to adjust the configuration
The Set-DbaNetworkConfiguration command is available for convenient modification of the network configuration. This offers on the one hand the possibility to receive a structure with all configuration settings, as they are delivered by the command Get-DbaNetworkConfiguration. I would like to refer to the example in the documentation. On the other hand, the parameters EnableProtocol and DisableProtocol as well as DynamicPortForIPAll and StaticPortForIPAll are available, which I would like to introduce right now.
PS C:\> Set-DbaNetworkConfiguration -SqlInstance SRV1\SQL2017 -DisableProtocol SharedMemory
WARNING: [10:28:50][Set-DbaNetworkConfiguration] A restart of the service for instance SQL2017 on SRV1 is needed for the changes to take effect.
ComputerName : SRV1
InstanceName : SQL2017
SqlInstance : SRV1\SQL2017
Changes : {Changed SharedMemoryEnabled to False}
RestartNeeded : True
Restarted : False
This shows the command to disable the shared memory protocol. Since I did not use the RestartService parameter, I am informed by a warning message and additionally by the return that a restart is required to implement the change.
PS C:\> Set-DbaNetworkConfiguration -SqlInstance SRV1\SQL2017 -StaticPortForIPAll 51433 -RestartService
ComputerName : SRV1
InstanceName : SQL2017
SqlInstance : SRV1\SQL2017
Changes : {Changed TcpDynamicPorts for IPAll to , Changed TcpPort for IPAll to 51433}
RestartNeeded : True
Restarted : True
When changing to a static port, I used the RestartService parameter, whereupon the SQL Server service was restarted and the change was thus fully implemented.
Small outlook on the control of the services
If something is missing...
If you are missing more functionality of the SQL Server Configuration Manager in dbatools, please feel free to contact us. Either create a feature request on GitHub or just email us.
Are you interested in further training or do you have questions about MS SQL Server? Contact us or visit one of our courses from our seminar store:
SQL Workshops
Principal Consultant bei ORDIX
Bei Updates im Blog, informieren wir per E-Mail.
Kommentare