Do applications have to specify TCP: in the connection string to availability group listeners?

Question

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.

Answer 1

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.

Answer 2

The accepted answer indicates no, however when testing using the ODBC driver on Windows Server, our connection to the DB with multi-subnet failover was failing.

After much trial and error, I decided to try tcp: and prepending the host with tcp: allowed the driver to connect!

The Microsoft docs also state:

To connect to an availability group listener, a client must use a TCP connection string.

This is opposite to what the accepted answer states about it being optional.

I'm not a SQL Server expert but to save someone else the same hours I spent trying every single connection string combination, definitely do try with tcp:.

Not discrediting the accepted answer, but this is what worked for us in line with the official documentation.