SSMS 18.9.2 connection too slow to connect to SQL Server when I'm on VPN

Question

HI,

today I manage some databases using SSMS connected in a VPN with the company.

When we connect to the server the response is very slow.

What is slow? It doesn't connect right away like this when I'm on the office network.

When switching between databases, sometimes the database list crashes.

My internet is 200Mbps, I transfer files to the server by TCP/445 SMB very fast connected to the VPN with the office.

But when I make the connection with SSMS to the database, everything is very slow.

I already read some network reconfiguration change suggestions, but I didn't find the options for Wifi 5.G usage

The response time from my computer to the server IP is 6ms.

When I take the same computer to the company and connect to the database, everything works perfectly.

Any concrete suggestions of what I should do to speed up this connection process?

Thanks.

Answer 1

Hi @Sandro Alves ,

Are you the only user facing this issue over VPN or other user also facing this issue over VPN? Try to use the IP address of the server not the hostname for the connection, check that the port that SQL Server is using is not being blocked by either your firewall or the VPN, force the protocol to TCP/IP and the TCP port is 1433, if you are connecting by IP, you can try to add a entry in your hosts file. Check your server or client DNS settings

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello,

I managed to solve the initial problem that was slow to connect to the sql server with studio.

For IP the slowness was abusrda and for FQDN it was faster. The problem probably happens to everyone who tries to perform the same action, but I only got complaints from two DBAs. The rest are developers and I honestly don't know how they access the database to carry out their developments.

I found a comment on the internet that the problem was with name resolution and I couldn't believe this was true and it is.

For some reason when we connect by IP the studio uses the resolution again reverse to probably perform some action internally until then inexplicable.

I simply entered the hosts file on my computer, added a name to the IP and that was it, it connected fast. Trying to list the databases was also very fast. Contour solution.

To solve this for everyone, as I use a name resolution from one domain to another, that is, I do name resolution forwards, it was necessary to create the reverse zones in the local DNS.

The most interesting thing is that I tried to connect by the domain's FQDN it works directly, but if I try to resolve the name using the IP it does not return the full FQDN.

Having done that and solving this part of the connection, the DBAs came up with another complaint, which is the response time for queries that they perform the tuning.

When I run a query from my machine that is on a 5G connection using nearby Wifi and with an Internet of 200Mbps I have a latency of 6/7ms for the SQL server IP.

While my co-workers with a similar scenario get a latency of 30ms.

The same query running on mine takes 5ms while theirs takes 12ms. On the local network the query is 0/2s.

This is bad for them as it masks the tuning work.

So what I did was make an RDWeb with Studio available to them on a server on the same network so that their experience is as close to the reality of application queries.

At first problem solved.

It is learning to look at name resolution (DNS) before thinking about any other situation.

Thanks.

Answer 3

If this only happens if you use a VPN connection to your company, then there is a "problem" with that connection.

AND it does not automatically means that your connection is using that 200MBit internet speed... if your company has a 100MB internet connection... or the VPN access point is limited to 10MBit... I had had those issues with HomeOffice on a former employer... at home, the internet speed was 100MBit, and the VPN Gateway was limiting connections to ~6MBit.
There are many more limiting options between your SSMS and your SQL Server if you are at home using a VPN...

And maybe the firewall behind the VPN gateway is checking the SQL connection on more rules than for SMB traffic.

Maybe check with the networking team of your company to resolve those issues.