Sql-server – Troubleshooting slow connection establishment

availability-groupssql serversql-server-2012

The client and the server are running on Windows on different subnets.

I'm facing a 20s delay to establish a connection to a SQL 2012 Always On availability group using .NET Core Sql client using sql authentication.

I have no problem establishing a connection to other SQL Server instances (no availability group). The connection establishment is instantaneous.

I do not have the same problem when using the MS sql command line client (sqlcmd which uses ODBC).

I need help solving this problem. I don't know yet if it is network or "AlwaysOn" configuration issue.

I'm able to provide network traces if requested.

EDIT 1: Wireshark traces

106 42.144914   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
107 42.690863   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
108 42.816460   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
109 44.157170   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
110 44.700425   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
111 44.824821   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
112 48.161575   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
113 48.708869   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
114 48.832817   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
115 50.537018   172.23.10.49    10.150.200.213  TCP 55  [TCP Keep-Alive] 56094 → 1433 [ACK] Seq=8294 Ack=3419 Win=2101760 Len=1
116 50.537561   10.150.200.213  172.23.10.49    TCP 66  [TCP Keep-Alive ACK] 1433 → 56094 [ACK] Seq=3419 Ack=8295 Win=64512 Len=0 SLE=8294 SRE=8295
117 50.585230   10.150.200.213  172.23.10.49    TCP 60  [TCP Keep-Alive] 1433 → 56094 [ACK] Seq=3418 Ack=8295 Win=64512 Len=1
118 50.585293   172.23.10.49    10.150.200.213  TCP 66  [TCP Keep-Alive ACK] 56094 → 1433 [ACK] Seq=8295 Ack=3419 Win=2101760 Len=0 SLE=3418 SRE=3419
119 56.161835   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56098 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
120 56.724399   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56101 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1
121 56.833741   172.23.10.49    10.140.200.213  TCP 66  [TCP Retransmission] 56102 → 1433 [SYN] Seq=0 Win=64240 Len=0 MSS=1460 WS=256 SACK_PERM=1

Best Answer

Since the connection is fast from sqlcmd, but slow from the .NET Core SqlClient driver, it's most likely this problem is on the client driver side (rather than an AG configuration issue).

You should add MultiSubnetFailover=true to the connection string to speed things up, as it shortens the retry interval. See the docs for details:

Connecting With MultiSubnetFailover

When MultiSubnetFailover=True is specified for a connection, the client retries TCP connection attempts faster than the operating system’s default TCP retransmit intervals. This enables faster reconnection after failover of either an AlwaysOn Availability Group or an AlwaysOn Failover Cluster Instance, and is applicable to both single- and multi-subnet Availability Groups and Failover Cluster Instances.

This isn't needed anymore on .NET Framework 4.6.1 and higher because TransparentNetworkIPResolution is on by default (which causes the driver to attempt connections to all the replicas in parallel, and use the first one that works, rather than trying them one-by-one). But it looks like that enhancement hasn't made it to .NET Core yet.

Related Question