April 29, 2010 at 11:20 am
Hi,
We have SQL Server 2005 64 bit with SP3 and we have 10 servers and each server has atleast 2 instances. And all servers are in same domain.
Right now, I did NOT configure any dedicated port for the SQL instance and they are dynamic. Now, we want to assign a dedicated port to each sql instnace
Questions:
1. Can we use same port for all the instances in 10 sevrers? for example, 2122 port for all sql instances on all servers? Is that the right way to do? or
2. Do we need to MUST assign a different port for each sql instance? i.e no instance can use the same port? Is that correct?
3.If we assign a dedicated port to each sql instance, then do we require to have SQL Sevrer Browser Service in start state? Or we can disable this service?
Please advice me
Thanks
April 29, 2010 at 6:09 pm
Mani-584606 (4/29/2010)
1. Can we use same port for all the instances in 10 sevrers? for example, 2122 port for all sql instances on all servers? Is that the right way to do? or
You can use the same port on different servers, but not on the same phisical server.
Mani-584606 (4/29/2010)
2. Do we need to MUST assign a different port for each sql instance? i.e no instance can use the same port? Is that correct?
This is correct for the instances on the same server. Instances on different servers can use the same port.
Mani-584606 (4/29/2010)
3.If we assign a dedicated port to each sql instance, then do we require to have SQL Sevrer Browser Service in start state? Or we can disable this service?
You can disable Browser Service, but then client needs to know on which port to connect to the instances. I use cliconfg.exe utility for client's configuration, the same could be set in client's registry (Browser Service disabled).
April 29, 2010 at 8:02 pm
I am curious about why you want to do this? Is there some reason you want to assign a static port instead of just using the dynamic ports?
Not sure I am clear on what benefits this will provide.
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
April 29, 2010 at 8:40 pm
magasvs (4/29/2010)
Mani-584606 (4/29/2010)
1. Can we use same port for all the instances in 10 sevrers? for example, 2122 port for all sql instances on all servers? Is that the right way to do? or
You can use the same port on different servers, but not on the same phisical server.
Mani-584606 (4/29/2010)
2. Do we need to MUST assign a different port for each sql instance? i.e no instance can use the same port? Is that correct?This is correct for the instances on the same server. Instances on different servers can use the same port.
Mani-584606 (4/29/2010)
3.If we assign a dedicated port to each sql instance, then do we require to have SQL Sevrer Browser Service in start state? Or we can disable this service?You can disable Browser Service, but then client needs to know on which port to connect to the instances. I use cliconfg.exe utility for client's configuration, the same could be set in client's registry (Browser Service disabled).
Second the information. I prefer to disable the browser service. The problem is that the clients must then know the port number. There are alternatives for that as indicated already.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 29, 2010 at 8:42 pm
Jeffrey Williams-493691 (4/29/2010)
I am curious about why you want to do this? Is there some reason you want to assign a static port instead of just using the dynamic ports?Not sure I am clear on what benefits this will provide.
Server consistency.
Firewall rules in case the servers are zoned differently (makes administration a little easier).
For the really tight and secure shops, permissions can also cause issues due to policies in place if the port changes after a reboot.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 1:39 am
Jason - thanks, just never needed to do that myself.
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
April 30, 2010 at 8:39 am
Jeff - NP.
Many places don't go to that extreme. I just happened to work in a place that did. I do like the extra security - looking back on it - despite the extra frustration it may have occasionally caused.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 11:25 am
Currently, we have Dynamic ports for all the named instances on the server and the application connects to SQL Server using the the instance name like ServerName\INS1.
If I make it Static port, for example say 52342, then how the client application needs to connect the above sql server instance?
I'm going through the document SQLServer2008FailoverCluster and it says we need to enable the browser service if if you configure static port. Please see the below from the documnet
If you assign static IP ports to all of your SQL instances, you will need to do one of two things:
1. Enable the SQL Server Browser service on each node of the cluster.
2. Create an alias on all clients, including all nodes of the cluster, which specifies the port number of the SQL Server instance. Client applications can alternatively add the port number in the connection string.
Thanks
April 30, 2010 at 11:31 am
I've done this, and we've set static ports in the firewall. If you don't do this, then you open a big range on the firewall, which upsets security people. Also tends to violate the least privilege principle.
You can connect as
server/instane name, port
so
myserver, 5346
April 30, 2010 at 11:35 am
I've done this, and we've set static ports in the firewall. If you don't do this, then you open a big range on the firewall, which upsets security people. Also tends to violate the least privilege principle.
You can connect as
server/instane name, port
so
myserver, 5346
Thanks Steve. But does it require to enable the Browser service after assigning static port?
April 30, 2010 at 11:39 am
Mani-584606 (4/30/2010)
I've done this, and we've set static ports in the firewall. If you don't do this, then you open a big range on the firewall, which upsets security people. Also tends to violate the least privilege principle.
You can connect as
server/instane name, port
so
myserver, 5346
Thanks Steve. But does it require to enable the Browser service after assigning static port?
No, the browser service listens on port 1434 (non configurable) and tells a client what port to talk to SQL on whether it's a named instance or default.
If connecting via Server, 7501 then there is no need to use the browser service.
At my current company, we disable the browser service on all SQL Server's
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 30, 2010 at 11:53 am
Thanks,
Does the Browser services MUST need to be enabled even if we configure static port in CLUSTERED Environment.
From SQLServer2008FailoverCluster document:
If you assign static IP ports to all of your SQL instances, you will need to do one of two things:
1. Enable the SQL Server Browser service on each node of the cluster.
2. Create an alias on all clients, including all nodes of the cluster, which specifies the port number of the SQL Server instance. Client applications can alternatively add the port number in the connection string.
Thanks
April 30, 2010 at 12:00 pm
Intuitevly that's all wrong, if your using dymanic ports you need it, not for static.
Here's is MSFT's documentation for the browser service for 2008, it makes no mention of it being required in a cluster
http://msdn.microsoft.com/en-us/library/ms181087.aspx
SQL Server Browser is not a clustered resource and does not support failover from one cluster node to the other. Therefore, in the case of a cluster, SQL Server Browser should be installed and turned on for each node of the cluster. On clusters, SQL Server Browser listens on IP_ANY.
The way I read this, is that's it's not a clusterd resource, and needs to be on every node, but not that it's required to be running
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 30, 2010 at 12:01 pm
Mani-584606 (4/30/2010)
Thanks,Does the Browser services MUST need to be enabled even if we configure static port in CLUSTERED Environment.
From SQLServer2008FailoverCluster document:
If you assign static IP ports to all of your SQL instances, you will need to do one of two things:
1. Enable the SQL Server Browser service on each node of the cluster.
2. Create an alias on all clients, including all nodes of the cluster, which specifies the port number of the SQL Server instance. Client applications can alternatively add the port number in the connection string.
Thanks
No. Not even in clustered environments is it an absolute must. We have disabled the browser service in our clusters and just connect via the servername,port method.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply