In this article I talk about SQL Server Protocols.
You can take a look at both client and server side protocols, and you don't necessarily have to do it with SQL Server Configuration Manager.

SQL Server network protocols

• Installing network protocols on the server and client computers.
  Network protocols are a feature of the operating system and are installed and configured using Microsoft Windows tools. For information about installing and configuring network protocols, see your Windows documentation, or contact your network administrator.

Connecting to the SQL Server Database Engine on MSDN.

There’s not much use in having a SQL Server if users cannot connect to it. For that we need protocols. We want those to be secure, fast and reliable.

the protocols

What protocols are there on a SQL Server?

Shared Memory

Can only be used when a client is on the same computer as the server. It dedicates an amount of RAM that can be accessed by both the SQL instance and the client. Evidently, this is the fastest way of communication. It has no configurable settings.

Named Pipes

Makes a specific pipe address for communication between processes without using the TCP protocol. The output of one process becomes the input for another. In a fast LAN it can provide better performance.

TCP/IP

This is the default protocol for Windows clients. Connections can be made from other networks, and from all kinds of operating systems. The SQL instance listens on a TCP port. This is considered the best choice after shared memory.

VIA

A deprecated protocol. This is better left disabled.

There is also an order between client protocols, that dictates which protocol will be tried for making a connection first, second and so on. Default, all client protocols except VIA will be enabled, and the order will be

1. Shared memory
2. TCP/IP
3. Named Pipes

Server protocols will not be enabled by default. There is a server protocol setting per installed SQL instance:

Microsoft SQL Server does not support the Banyan VINES Sequenced Packet Protocol (SPP), Multiprotocol, AppleTalk, or NWLink IPX/SPX network protocols.

On Failover Clusters only TCP/IP and Named Pipes will work.

There needs to be at least one protocol that’s enabled on both the server and the client, or communication will not be possible. Multiple protocols can be enabled at the same time, but only one will be used at a time for the communication between a server and a client.

Changing the status of a protocol only becomes effective after the next restart of SQL server service. You can force the chosen protocol in SQL Native client by prefixing the computername with “np:” or “tcp:”. When using the IP Address of the server, it will always use TCP/IP, as will using the FQDN.

The MMC

Sure, there's the MMC :-)

PS> C:\Windows\SysWOW64\SQLServerManager13.msc

But, hey, this is the Power DBA, right?

with PowerShell

SQL Server protocols can be managed using the ComputerManagementXX namespace:

PS>  $NamedPipesProtocol = Get-WmiObject -namespace 'root\Microsoft\SqlServer\ComputerManagement10' -class ClientNetworkProtocol -filter "ProtocolName='np'" 
PS>  $NamedPipesProtocol.setdisable()

What makes it a bit difficult, is the 'XX' changes with every version of SQL Server. So it's mandatory you either know this for the computer you want to manage, or wrap this statement in some sort of discovery.
Additional problems arise if you have multiple installations, and even worse, removed installations.

I explained this in managed computer error

dbatools

Luckily, dbatools has functions that solve those difficulties for you and connect you with the desired protocol objects.

With Get-DbaServerProtocol you can see the status of those protocols on the server side, and also use the .enable() and .disable() methods.

With Get-DbaClientProtocol you can see the status of those protocols on the client side, and also use the .enable() and .disable() methods.

Learn more about those protols with:

PS>  help Get-DbaClientProtocol -ShowWindow
PS>  help Get-DbaServerProtocol -ShowWindow

To be continued

There are some dependant subjects, like SQL services and the SQL Browser.
I'll talk about those in a future article.

Previous Post Next Post