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
Let's start with the SQL Server Management Studio, which is certainly mainly used for administration purposes. It is a standalone program that can be installed on a dedicated computer from which the administration is done. Of course, this should not be the database server itself, but a central administration server or the administrator's workstation.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.
As in the Configuration Manager, the returned data has a nested structure, which might be a confusing.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.
The ServerProtocols option specifies only for each of the three supported protocols whether it is enabled (True) or disabled (False).
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.
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.
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.
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
The SQL Server Configuration Manager is of course not only used to manage the network configuration, but often also to manage the various services of the SQL Server. However, these commands, among others, have been available for this purpose for a long time now: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