3

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.

3

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.

  • Will the readonly work on secondary nodes without specifying the TCP in the connection string? How can I check that the readonly queries are being redirected to secondary nodes? – Danielle Paquette-Harvey Apr 9 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.