SQL Server – Do Applications Need to Specify TCP in Connection String for Availability Group Listeners?

availability-groupsconnectivitysql serversql-server-2016

In articles that I have read about applications accessing databases through the availability group listener, they all seem to want to use TCP: prefix before the listener name in their connection strings to force a TCP connection.

My question is: is this really necessary? I've tested a few applications without using this prefix and they all successfully established a TCP connection to the primary server in the AG group. I also tested failover to one of the synchronous secondaries and that seems to have gone smoothly as well.

Best Answer

No, it's not necessary. The articles may be including it for completeness sake, but it's not required at all.

The TCP: prefix isn't unique to AG listeners - you can connect directly to a standalone SQL instance by adding the TCP: prefix, but it doesn't change how the connection is established, unless you're connecting locally.

If you connect to an instance locally it will default to using Shared Memory for the connection. If you instead prefix the connection with TCP: it will force the connection to use TCP as the protocol. You can test verify this using the following script. 

select c.net_transport, s.host_name, s.program_name
from sys.dm_exec_connections c
join sys.dm_exec_sessions s
on c.session_id = s.session_id
where s.host_name = host_name()

Open up a connection to a local instance without the prefix and you'll only see Shared Memory connections. Then try with the prefix and you'll see it switch to TCP.

Connections to AG listeners however (even if you're connecting from the primary replica), will always use TCP, prefix or not.

Related Question