SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Instances with Different IP Addresses and Same Port Number in SQL Server


Multiple Instances with Different IP Addresses and Same Port Number in SQL Server

Author
Message
Vikram-258966
Vikram-258966
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 109
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/????


http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.server&tid=d1b65e11-af7b-4638-896c-bf4fa0b1c997&cat=en_US_671e06d0-f20d-4bb3-9c6a-42c825ddb1dc&lang=en&cr=US&sloc=&p=1
Leo Leong
Leo Leong
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 328
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.
Vikram-258966
Vikram-258966
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 109
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
Leo Leong
Leo Leong
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 328
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.
2 Tim 3:16
2 Tim 3:16
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1817 Visits: 1547
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
EdVassie
EdVassie
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13637 Visits: 3894
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
Daniel-263255
Daniel-263255
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 606
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)
intekhab.alam
intekhab.alam
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 48
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.
Andrew Hatfield
Andrew Hatfield
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 264
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
intekhab.alam
intekhab.alam
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 48
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search