November 2, 2005 at 3:05 pm
I am running 2 named instances of SQL Server 2000 Enterprise (sp4) on Windows 2003 Server and have been having flaky connectivity issues for the past 2 weeks.
It seems that I can always connect (from another machie) to one or the other instances, and sometimes I can connect to both instances, but only from certain machines and that only seems to last for about 3 days after rebooting the server.
It appears that there's a resource shared by both instances that is being hogged by one or the other and I appear to be better at banging my head against the wall than figuring out how to resolve. Anybody ever run into anything like this?
Both instances are running fine and are 100% normal when viewed from the server they are running on, the issues are all in trying to connect from outside machines. I set it up to only run TCP/IP for connections (I removed named pipes). That worked great for about 4 days.
Any insight into this would be greatly appreciated.
Thanks,
Chris
November 3, 2005 at 8:36 am
Just a thought, have you tried to conenct to both instances from one or more different computers ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 3, 2005 at 10:43 am
A little more info on the problem, when I specify the port along with the server/instancename it works properly.
So munson\sql3 does not work, but munson\sql3, 3651 does work.
So it would appear that the sql3 instance is listening on the correct port but that it can't be found by remote machines for some reason. Anyone know how the port is determined by the client machone's or how to correct this?
Thanks,
Chris
November 3, 2005 at 11:07 am
hmmm ... providing the instance/port works ... hmmm ... you could add aliases with the client configuration utility as a work around but that does not fix the problem (I have multiple instance servers and just provide server_name\instance_name for a connection and have absolutely no issues). Are there any firewalls or firewall software running on the database server ? If so turn them off and try it again. As for the client figuring out the port number for an instance SQL first tries TCP port 1433 (if no aliases exists). If is does not get an answer then it uses UDP port 1434 to try and get the instance name and port number for conenction. Maybe you're a victim of the netwoprk admins locking things down ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 4, 2005 at 9:24 am
I think it is because your sql server is listening on different port than it is expecting thus causing you to have this problem. How about to alias on your computer and try to connect to it as regular. I am it works just as you would expect.
tcp expects that your sql server listen on 1433 otherwise you have to set up aliase for it to know how to make connection to your server.
mom
November 4, 2005 at 9:34 am
Yeah, I have found that creating an alias is a solution, but it's something that I did not have to do for the majority of SQL Servers and I'm wondering why it's required now. There are about 13 sql servers and only 3 of them are problematic. And at this time only 1 of them is flaky.
Is there any way to create aliases on client machines without having to install the Client Network Utility?
Overall though I hate implementing workarounds that require special attention down the road. I've got log shipping working am using a spare server as the standby and "failing over" will be much more of a headache if I have to modify aliases on each of the client machines. Right now the server is aliased with instance sql3 running on each server. To fail over at this point it's only a matter of modifying the server alias, if I make specific aliases on each client machine then the simple process becomes a huge headache so I'm hoping there's a registry setting or network setting that can be set to make it visible by just using servername\instance name like all the other sql servers I'm using.
November 4, 2005 at 9:52 am
You mention out of 13 SQL Servers only 2 are 'flaky'. It may be time to play a Sesame Street game - "one of these things is not like the other, one of these things is not the same" with your SQL Server configurations and software versions. MDAC, OS patches etc ... It sounds a bit 'childish' or 'simplistic', but that's just what may solve the problem since 11 out of 13 work just fine. I've found it's usually something simple - albeit once you find it !
Now 'failover' should not be an issue. a DNS alias will solve that as a potential issue quite simply.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 4, 2005 at 10:03 am
Yeah, running through all the differences is not a bad idea, there are some differences between servers, Win2k Server vs. Win2003 Server. The problematic setup appears to be Win2003 Server sp1 with SQL 2000 Enterprise Edition sp3.
I will keep you posted if I find anything.....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply