February 19, 2009 at 7:45 pm
Hi,
We have one standalone SQL 2005 enterprise edition (64 bit) server. Its on windows 2003 server.
We have multiple Ip address configured on this server and also have 3 instances of SQL server instances installed on this server.
Now my requirement is
Run all different installed SQL server instances on different IP ADDRESSes but on same port number 1433. I was able to do IP address by making changes to TCP/IP properties in Configuration manager.
however I AM NOT able to start all 3 SQL server instance on same port. I came across this article below, but this does not clearly define how to run multiple instance with different Ip address on same port number.
CAN SOMEONE PLEASE assist and provide me steps on how to do this/????
February 20, 2009 at 3:45 am
just a suggestion.
in your server, each instance should have 1 port.
after that, do a mapping on your firewall.
map external port to local port for each instance.
February 21, 2009 at 12:29 pm
By one port do you meaN SAME port. Can you brief how can we configure within sql server multiple instances with different ip address on same port number
February 22, 2009 at 6:31 pm
different ports.
for example,
port for server\instance1 = 1434
port for server\instance2 = 1435
port for server\instance3 = 1436
in your firewall,
map IP1:1433 to your server's local IP:1434
map IP2:1433 to your server's local IP:1435
i am not sure whether this works for u.
February 23, 2009 at 7:20 am
Just curious here, do you have some kind of vendor requirement to hard code the port number in the connect string?
SQL Browser listens in on port 1434 and then reconciles all named instances on the server using the dynamic port feature.
this way, you don't have to worry about ports at all but just connect usiong the named instance string only.
Tim White
February 24, 2009 at 5:32 am
The suggestion about using your firewall or network switch configuration is the best way to achieve this.
One way to think of the TCP address and port numbers is to think of your home.
Your home can have a number of different addresses. You can call it 'Willow Cottage', '143 Shady Lane', '2nd house after the pub'. These all relate to TCP addresses, and people can find your house by any of these addresses.
Inside your house you have rooms. These relate to port numbers. If you have something that needs a room to itself (e.g. a teenager or a SQL Server instance), you cannot make it share with another instance of the same thing.
The firewall or other address translation tool allows you to have a level of indirection between what the application servers know about your SQL Server machine and the reality of what actually runs on your SQL Server machine.
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
February 24, 2009 at 8:08 am
You'll need to use SQL Server configuration Manager to set SQL server's IP bindings. I've had to do something similar to meet a company policy. This is what I ended up doing.
Under SQL Serer 2005 Network Configuration will be a listing for each instance in the right will be a listing of the Protocols.
Under each instance open the properties of TCP/IP
1)Set "Listen All" = No.
2)Set the desired IP and port
3)Disable dynamic ports by removing '0' from TCP Dynamic ports for all IP's except for 127.0.0.1 and "IPAll"
4)Shutdown all instances of SQL
5)Start up all instances of SQL
Any errors can be troubleshot using the errorlogs for each instance (.MSSQL\LOG\ERRORLOG)
May 14, 2009 at 11:36 pm
We have similar kind of requirement and need to setup multiple port (few duplicate within instances) for two instance running on same cluster node
Could you please let me know if suggested changes ( Filrewall & SQL server config mgr ) has been implemented in ur env and its working fine.
if you have setup in ur env then request you to share the steps and feedback how its running so far.
fyi.. my requirement
server\instance1 - Port 1433, 1434
server\instance2 - Port 1433, 1435
Enabled protocal - Shared Memory, TCP/IP, Named Pipes
Thanks in advance for your response & help.
May 14, 2009 at 11:58 pm
You should always use static ports instead of dynamically assigning them. Where possible, disable the Browser service.
If you were to run the 3 instances on the same port and bound to 3 different IP addresses, Kerberos will not work. Each host is given a Kerberos ticket, not each name or NIC or IP. Your SPNs reflect the NetBIOS and FQDN names of the host, port and service.
I see no benefit to what you are trying to do. A better solution would be to separate your instances on dedicated ports. Specifying which IP Address your instance is bound to should be fine as long as your aware of your AD Domain and how Kerberos works.
--
Andrew Hatfield
May 15, 2009 at 12:37 am
Thanks for response.
Here are the details abt my env...
I am migrating db sql2k -->sql2k5 where sql2k5 is running on 2 node cluster. and to balance the node separating the db across two instance (active\active mode) running on same cluster nodes..
Old sql2k instance were using 1433 node & to avoid code changes at app side to SQL to listen same port for both instances running on cluster.
Cluster servername - serverN1\serverN2
1st instance name - sqlvirtualA\instance1 - Port 1433,1434
2st instance name - sqlvirtualB\instance2 - Port 1433,1435
I got the error message for SPM/kerberos authentication as you mentioned in your reply too.
sql error log say ----
"The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies."
based on this error message NTLM authentication scheme is used once kerberos failed. I queried below statement and result is "NTLM"
"select auth_scheme from sys.dm_exec_connections where session_id=111"
-- SQL instance is coming up properly & and I can make connection both the instances remotely using similar port using query analyser. also connection from app server\clinet have no issue using below connection string.
sqlvirtualA,1433
sqlvirtualA,1434
sqlvirtualB,1433
sqlvirtualB,1435
- app uses dns alias to connect to SQL instances and have no issues as well.
dns alias - sql-db1.fqdn mapped to sqlvirtualA
dns alias - sql-db2.fqdn mapped to sqlvirtualB
My biggest concern\question..
how this will behave going forward?
what idoes MS say for such cinfiguration? and des it a sound proof or suggested seting which can be implemnedted on critical Prod env?
Are you\anone aware about such setting already implement on any env and how its working?
again.. you reponse will be much appreciated. Awaiting your response.
Thanks...
May 16, 2009 at 7:14 pm
intekhab.alam (5/15/2009)
Thanks for response.Here are the details abt my env...
I am migrating db sql2k -->sql2k5 where sql2k5 is running on 2 node cluster. and to balance the node separating the db across two instance (active\active mode) running on same cluster nodes..
Old sql2k instance were using 1433 node & to avoid code changes at app side to SQL to listen same port for both instances running on cluster.
Cluster servername - serverN1\serverN2
1st instance name - sqlvirtualA\instance1 - Port 1433,1434
2st instance name - sqlvirtualB\instance2 - Port 1433,1435
intekhab.alam (5/15/2009)
I got the error message for SPM/kerberos authentication as you mentioned in your reply too.sql error log say ----
"The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies."
This isn't necessarily a problem. Because you are running your services under an AD account, it won't have the required privileges to create SPNs by default. It is better to create your SPNs manually anyway. To enable Kerberos in a cluster, you will need to enable "Register in DNS" and "Enable Kerberos" in your Network Name resource for each cluster group. This will require an cluster group outage to take effect. When it does, a new AD Computer account will be created in your CN=Computers folder. Move this to the same organisational unit as your serverN1 and serverN2 AD Computer accounts.
Then create your SPN as required, eg
setspn -A MSSQLSvc/sqlvirtualA:1433 [service account]
setspn -A MSSQLSvc/sqlvirtualA.[your ad domain]:1433 [service account]
Do that for both instances. You will also need to enable Delegation on the Kerberos tab of the that is running the SQL Database Engine. Setting it to Allow delegation for any Kerberos service is the easiest, but you can lock it down further if you wish.
intekhab.alam (5/15/2009)
based on this error message NTLM authentication scheme is used once kerberos failed. I queried below statement and result is "NTLM""select auth_scheme from sys.dm_exec_connections where session_id=111"
Expanding on that a little more nicely is
select c.auth_scheme, s.login_name
from sys.dm_exec_connections as c, sys.dm_exec_sessions as s
where c.session_id = s.session_id
intekhab.alam (5/15/2009)
-- SQL instance is coming up properly & and I can make connection both the instances remotely using similar port using query analyser. also connection from app server\clinet have no issue using below connection string.sqlvirtualA,1433
sqlvirtualA,1434
sqlvirtualB,1433
sqlvirtualB,1435
- app uses dns alias to connect to SQL instances and have no issues as well.
dns alias - sql-db1.fqdn mapped to sqlvirtualA
dns alias - sql-db2.fqdn mapped to sqlvirtualB
My biggest concern\question..
how this will behave going forward?
what idoes MS say for such cinfiguration? and des it a sound proof or suggested seting which can be implemnedted on critical Prod env?
Are you\anone aware about such setting already implement on any env and how its working?
again.. you reponse will be much appreciated. Awaiting your response.
Thanks...
If you are already using dns aliases to support your application (which is a good thing), then why do you want to try and make life a whole heap more complicated by trying to do what you're describing here? It is much simpler, and supported, to simply set each instance to a unique port. That they are in separate cluster groups gives them separate IP addresses. From memory, I don't think the SQL Network Configuration Utility provides an IP address option for your virtual (cluster group) IPs.
--
Andrew Hatfield
May 17, 2009 at 8:01 am
Andrew Hatfield has described very well what needs to be done when running multiple SQL Server instances on a cluster.
.. you are already using dns aliases to support your application (which is a good thing)
Excellent - this is the way to go.
... why do you want to try and make life a whole heap more complicated by trying to do what you're describing here? It is much simpler, and supported, to simply set each instance to a unique port. That they are in separate cluster groups gives them separate IP addresses.
Agree with Andrew.
From memory, I don't think the SQL Network Configuration Utility provides an IP address option for your virtual (cluster group) IPs.
Andrew is correct that IP cannot be specified but the port can be specified.
You need to take only a few more steps to have the SQL Server instances have fixed ports rather than dynamically assigned ports and to map the dns aliases to the actual SQL Server instance names. This will insure that the clients can connect using only the DNS Alias and do not need to specify IP or port.
First, decided what ports are going to be used for SQL Server. Registered Ports are those from 1024 through 49151 and Dynamic and/or Private Ports are those from 49152 through 65535. These ports number ranges should not be used for named SQL Server instances as a future conflict is possible. Reference http://www.iana.org/assignments/port-numbers
Second, so that no other program requesting a dynamic port will be assigned your desired SQL server port, reserve the desired port numbers. "How to reserve a range of ephemeral ports on a computer that is running Windows Server 2003 or Windows 2000 Server" http://support.microsoft.com/kb/812873/
Third, "Configuring SQL Server Named instances to use a fixed port"
http://technet.microsoft.com/en-us/library/ms345327(SQL.90).aspx. This will require a SQL Server restart.
Fourth, map the DNS aliases to the SQL Server using the SQL Server Configuration Manager.
Finally test by connecting using the DNS Alias without a port number from a different server.
Good Luck
SQL = Scarcely Qualifies as a Language
July 14, 2009 at 11:52 pm
how did you use the instance name ,port number in the DNS alias.
i am trying to move instances from one server to another,rename them and change the default port. so i wanted to use DNS aliases to redirect clients to the new server,instance and port without having to reconfigure my applications or clients.
February 3, 2010 at 8:49 am
Andrew, not sure I understand why SPNs and kerboros will not work? Can you clarify?
February 6, 2010 at 6:44 pm
This thread is a little old, so please bear with me.
SPNs and Kerberos work, but for a 2003 cluster, they are not created automatically. When using SQL in a cluster, you must enable "Register in DNS" and "Use Kerberos" and then create your SPNs.
Hope this answers your question
--
Andrew Hatfield
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply