TCP PORT issue
-
Hi,
By default SQL server takes 1433 Port. its clear
But if i install 4 Named instance of sqlserver 2005 on windows server 2008
whether Port 1433 will be same for all instance ot it will change
how it will allocates for each named instance & IP
-
If I'm not mistaken when you have multiple instances of SQL Server on the same box it will use 1434. Then this is where SQL Server Browser service comes into play. It handles incoming request and directs them to the instance needed.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton -
FYI: Port 1434 is the default UDP port.
On my local box, I have two Dev servers installed (2k5 & 2k8). Looking at my SQL Server Configuration Manager, both servers have two completely different IP addresses (IP1 section). Also, I'm using dynamic port assignment.
If you have four instances on the same machine, any traffic coming in and out of the machine has to include the instance name along with the server name. No instance name indicates a default instance. I'm fairly certain this information is used to route the traffic to the proper instance once the traffic comes in through whichever port it happens to be using. If you are using dynamic ports, then you don't have to worry about anything.
I advise using dynamic ports or changing the ports all together, so you don't have to worry about someone hacking your server. Too many people know the default ports. It's bad practice to use them.
-
are for the gory details, you can run this query to detect the ports for all your instances, or go to this property panel to see and change the ports that will be used:
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
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! -
Lowell, that's brilliant code. Even though this wasn't my question, I may have to keep your solution in my SQL files for future reference. @=)
-
Brandie iIm pretty sure it works for 2000/2005 instances, but i don't have a non-default 2008 or R2 to confirm that snippet still works yet.
thanks for the kudos!
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!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply