September 10, 2003 at 8:46 am
We are running SQL 2K on a cluster.
We have various application that connect to a named instance of SQL via a JDBC connector and these are using the convention server:port number
This is all working fine UNTIL :-
We failed over the cluster and when the SQL restarted, it used a different port number on SQL server. This then caused all our connections to stop working.
Is there a way to connect SQL to a FIXED port number for a given instance.
September 10, 2003 at 8:58 am
Have you tried use server network utility to fix the port number?
Did you say SQL Server uses different port after fail-over?
Edited by - allen_cui on 09/10/2003 09:07:21 AM
September 10, 2003 at 9:32 am
The port number has been specified in Network Utility.
SQL seems to have changed to a different port after the failover
September 10, 2003 at 9:40 am
September 10, 2003 at 10:04 am
I've reviewed these and do not think they apply in this case.
The server is currently only running SQL and no other application. I have looked at the sql error logs for when SQL was shut down and when it restarted and the first log shows SQL listening on port 1229 and the second shows port 2699. The network utility also shows that the port being used has changed.
My understanding was that this should not be the case.
September 10, 2003 at 11:47 am
I have never see these in my clustered SQL Serers, either default or named instances.
Try to fail-over back to see which port the SQL Server is going to use.
September 11, 2003 at 12:30 am
before you fail back check its portno.
with this script. Don't know where i've downloaded it from(author is mentioned), but it does the job.
then fail back and run it again. It should give the same results !
-- This script will get the listening port of the
-- SQL Server, useful for multiple instance servers
-- Vijay Anisetti
set nocount on
CREATE TABLE #GetPort
(
token varchar(100),
value varchar(20))
go
DECLARE @inst varchar(200)
DECLARE @inst1 varchar(100)
--Determine registry path and key
IF(charindex('\',@@servername) > 0)
BEGIN
SELECT @inst = substring(@@servername,charindex('\',@@servername) ,50)
SELECT @inst = 'SOFTWARE\Microsoft\Microsoft SQL Server'+@inst+'\MSSQLServer\SuperSocketNetLib\Tcp'
--SELECT @inst1 = 'TcpDynamicPorts'
SELECT @inst1 = 'TcpPort'
END
ELSE
BEGIN
if SUBSTRING(@@VERSION,23,1) = '7'
begin
SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\VIA'
SELECT @inst1 = 'DefaultServerPort'
end
else
begin
SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp'
SELECT @inst1 = 'DefaultPort'
end
END
print @inst + '\\\\////'+ @inst1
INSERT #GetPort
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1
SELECT substring(@@servername,1,25) as ServerName, value as port FROM #GetPort
DROP TABLE #GetPort
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 11, 2003 at 11:35 am
I've found TCPView from sysinternals (freeware) to be a very useful tool in troubleshooting TCP port conflicts. It lists the process and port number being used by each running process on the machine.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply