Dbatools as a replacement for SQL Server Configuration Manager

dbatools

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.

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=}...} 
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. 

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  

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.

By accepting you will be accessing a service provided by a third-party external to https://blog.ordix.de/