Connecion Issue's to a SQL Instance

Question

Connecion Issue's to a SQL Instance

bernaerts_joris

SSC Veteran

Points: 261
More actions
February 22, 2019 at 8:04 am

#373906

Hi,
Recently we started experiencing connection issues for a specific Instance on a server where multiple instances are present. This is a development server.
On the machine itself when using SSMS we connect immediately to all instances. When accessing SQL from another server using SSMS we have 1 instance (from 7) which takes >30 seconds to connect. All other instances connect almost immediately.
Because of these issue the app server constantly loses its connection and wont reconnect because the connection request timed out.
TCP/IP's are configured correctly ect.
Anybody here that can point me in the right direction to start resolving this issue ?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i would guess that although you said the TCP is set up correctly, it sounds to me like one instance is using a dynamic port for connections, and the other six have been correctly set to use a specific static port.
is the SQL Browser service running on the multi instance host, or is the service disabled?
after that i would check the TCP IP settings for each instance,especially if the server has multiple nic cards, it might be that one IP is not set correctly,and that it fails to connect via one nic, but works on the other.; so make sure IP-ALL has a specific static port?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bernaerts_joris SSC Veteran Points: 261 More actions · Answer 2

@lowell

Thanks for the reply
I used
SELECT 'TCP Port' as tcpPort, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE '%IPALL' AND value_name in ('TcpPort','TcpDynamicPorts')

to see if an instance uses dynamic and all returned a Fixed port. SQL Browser is running.

Gamleur84 Default port Points: 1475 More actions · Answer 3

I had a similar issue in the past and found warnings/error in the event logs. From there, I googled and found a known issue and a registry key.

Did you check the event logs ?