August 12, 2009 at 10:40 pm
Thanks
August 12, 2009 at 10:52 pm
No, it is basic TCP/IP, only one listener on a port.
I do strongly recommend setting a port range policy. And set each instance to a port, and not use dynamic port assignments.
CEWII
August 14, 2009 at 3:03 am
If you want to be pedantic, you can say Yes, multiple instances can all listen on the same port.
But you will only be able to connect to the first instance that gets started.
Follow Elliott's advice, and configure erach instance so it listens on a different port.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 14, 2009 at 4:28 am
Depends what you mean by "default port".
If you mean port 1433 as used by a default instance of SQL Server, then no.
If you mean that each named instance should listen on its own default port, then the answer is yes, so long as you use dynamic port allocation, and the SQL Browser service is running. Each instance listens on a free port (which may change each time the service restarts if the port is in use) and the SQL Browser service arbitrates initial connections by responding to UDP requests on 1434.
Read the following Knowledge Base article for full details:
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 14, 2009 at 7:46 am
Ed:
If you have more than one instance configured to point at 1433 then I agree the first one to start would get it, however, any such setup would be sheer craziness.
Paul:
I think that the definition of default port precludes much of your post, SQL's default port is 1433, if you are using dynamic port allocation there really isn't such a thing as a "default" port. Maybe default for that instance on your particular server but I don't think that is how "default" is traditionally thought of..
CEWII
August 15, 2009 at 12:15 am
Elliott W (8/14/2009)
Paul:I think that the definition of default port precludes much of your post, SQL's default port is 1433, if you are using dynamic port allocation there really isn't such a thing as a "default" port. Maybe default for that instance on your particular server but I don't think that is how "default" is traditionally thought of..
Not sure what you mean by 'precludes'. I mentioned the default port for a non-named instance of 1433. 'Default' could be taken to mean the setting if you don't specify anything to the contrary - in that respect I would disagree with you. Not that it matters.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 17, 2009 at 12:01 pm
Paul,
Perhaps I'm being too semantic, I see default as if you install the product and do nothing else you can always count on the port being 1433 for a default instance. There may be a port that once installed it will tend to stick to using dynamic ports, but I *personally* don't view that as default. But that is how I see it, and mileage may vary..
CEWII
August 17, 2009 at 12:10 pm
Elliott W (8/12/2009)
No, it is basic TCP/IP, only one listener on a port.
..per machine/IP address. It's probably a given, but it is sometimes useful to add the obvious in case it wasn't so obivious....
So - 2 instances on a single server ==> only one can be on default port
2 instances on their own servers==> both can be on default port.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 17, 2009 at 1:21 pm
Matt Miller (8/17/2009)
Elliott W (8/12/2009)
No, it is basic TCP/IP, only one listener on a port...per machine/IP address. It's probably a given, but it is sometimes useful to add the obvious in case it wasn't so obivious....
So - 2 instances on a single server ==> only one can be on default port
2 instances on their own servers==> both can be on default port.
You make an interesting point, if you have more than one IP address you could have more than one SQL Server on the machine on a default port, just NOT on the same IP address.. You'd have to set it up manually but I think it would work.
2 instances on a single server ==> only one can be on default port per IP address on that server (don't count 127.0.0.1 as an address).
CEWII
August 17, 2009 at 2:01 pm
Elliott W (8/17/2009)
Paul,Perhaps I'm being too semantic, I see default as if you install the product and do nothing else you can always count on the port being 1433 for a default instance. There may be a port that once installed it will tend to stick to using dynamic ports, but I *personally* don't view that as default. But that is how I see it, and mileage may vary..
CEWII
Not necessarily true - although I would not recommend even trying to get this to work. If you have two NIC's, you can assign a separate IP to each NIC and have SQL Server running on the same port with two separate IP addresses on the same server.
Like I said - I wouldn't even want to try this myself, but it is possible.
Another possibility would be to create a single node cluster. For each instance, you would have a separate cluster group with it's own IP address/name where each instance could then be running on the same port.
Again, I would not do this and I am not recommending it. 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2009 at 2:13 pm
Depends what you mean by "default port".
If you mean port 1433 as used by a default instance of SQL Server, then no.
If you mean that each named instance should listen on its own default port, then the answer is yes, so long as you use dynamic port allocation, and the SQL Browser service is running. Each instance listens on a free port (which may change each time the service restarts if the port is in use) and the SQL Browser service arbitrates initial connections by responding to UDP requests on 1434.
August 17, 2009 at 2:17 pm
Elliott W (8/17/2009)
..... I see default as if you install the product and do nothing else you can always count on the port being 1433 for a default instance. ..CEWII
I am not sure that we can count on Default instance running on port 1433. I know about a couple of named instances on different servers that run on port 1433 and are the only instances on the servers. In this case I can add a default instance, and it will not be running on 1433.
Regards,Yelena Varsha
August 17, 2009 at 2:19 pm
I think that the definition of default port precludes much of your post, SQL's default port is 1433, if you are using dynamic port allocation there really isn't such a thing as a "default" port. Maybe default for that instance on your particular server but I don't think that is how "default" is traditionally thought of..
August 17, 2009 at 2:43 pm
I think we should really consider what the OP really wants to be able to accomplish. Most likely, they are looking for a way to have multiple default instances on a single server. Probably because the application is hard-coded to use just the server name to access and cannot access an 'instance'.
Regardless of the port assignment SQL Server uses - there is only one default instance per node or cluster that can be accessed using just the server name. If SQL Server is installed as an instance, you are going to have to reference that instance using either the IP:port, or the full instance name with the SQL Server Browser up and running.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2009 at 5:22 pm
playm77 (8/17/2009)
Depends what you mean by "default port".If you mean port 1433 as used by a default instance of SQL Server, then no.
If you mean that each named instance should listen on its own default port, then the answer is yes, so long as you use dynamic port allocation, and the SQL Browser service is running. Each instance listens on a free port (which may change each time the service restarts if the port is in use) and the SQL Browser service arbitrates initial connections by responding to UDP requests on 1434.
Copy and paste from my post seems to be working a-ok for you there playm77!
Dick. :laugh:
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply