2

We have multiple .NET applications that used connecting to SQL Server 2016 through the IP address.

Now, we have set up a domain name connected to 2 IP addresses. The initial connection is very slow, anything between 25 and 40 seconds.

Here is the connection string:

Provider=SQLOLEDB;Server=lpc:chiangmaiserver.ch,51231;Database=ContactDatabase;User ID=****;Password=p@****;Connect Timeout=60;

The strange thing is that sometimes, the connection gets fast for a few minutes, then goes back to slow again.

The same domain used in SSMS does not have the issue.

If we revert to IP address the problem disappears.

I tried all the solutions proposed in previous posts, none of them is working.

Fred
  • 3,365
  • 4
  • 36
  • 57
Michael
  • 96
  • 2
  • 1
    Why are you using oledb ..you should use use sqlconnection class of dotnet..? – Always_a_learner Jun 21 '20 at 04:32
  • I tried both, it doesn't make any difference. We use oledb for legacy reasons. – Michael Jun 21 '20 at 06:12
  • I hope ..delay is not at sql end ..this is because of ip address resolution time...if domain are provided by some ISP..you should connect him – Always_a_learner Jun 21 '20 at 06:41
  • check it out https://stackoverflow.com/questions/4269268/initial-connection-to-sql-server-connection-is-slow-why – Always_a_learner Jun 21 '20 at 06:46
  • Yes, I did already tried all this but it doesn't help. And the fact that SSMS doesn't have the problem points more to a connection string issue in .NET than a domain conversion issue. – Michael Jun 21 '20 at 07:38
  • 1
    Why are you using lpc (Shared Memory Protocol)? it only works if server is local. Also it's better to use native client `Provider=SQLNCLI11.1` than the legacy `SQLOLEDB`. – Jesús López Jun 21 '20 at 08:33
  • I tried with SQLNCLI11.1, it doesn't improve. I added lpc just for testing, but it doesn't make any difference either. The weird thing is that sometimes, it starts working normally for a few log ins, then goes back to long initial delay again. – Michael Jun 21 '20 at 10:01
  • 1
    I added a named pipe and the problem is gone. Thank you to both of you for your time. – Michael Jun 21 '20 at 10:59
  • 1
    I would recommend using a named pipe instead of lpc, for an explaination of why, see https://stackoverflow.com/a/175669/13631126 – TestBench Jun 21 '20 at 22:45

0 Answers0