Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Multiple Instances with Different IP Addresses and Same Port Number in SQL Server Expand / Collapse
Author
Message
Posted Thursday, February 19, 2009 7:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:28 PM
Points: 62, 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
Post #661028
Posted Friday, February 20, 2009 3:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 24, 2014 3:59 AM
Points: 106, 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.
Post #661171
Posted Saturday, February 21, 2009 12:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:28 PM
Points: 62, 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
Post #662080
Posted Sunday, February 22, 2009 6:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 24, 2014 3:59 AM
Points: 106, 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.
Post #662322
Posted Monday, February 23, 2009 7:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:01 PM
Points: 1,081, Visits: 1,482
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
Post #662558
Posted Tuesday, February 24, 2009 5:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:50 AM
Points: 2,854, Visits: 3,174
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 2014, 2012, 2008 R2, 2008 and 2005. 29 May 2014: now over 29,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #663366
Posted Tuesday, February 24, 2009 8:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 9:00 AM
Points: 150, Visits: 491
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)


Post #663529
Posted Thursday, May 14, 2009 11:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 2:07 AM
Points: 2, 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.

Post #717610
Posted Thursday, May 14, 2009 11:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 25, 2010 9:38 PM
Points: 107, 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
Post #717624
Posted Friday, May 15, 2009 12:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 2:07 AM
Points: 2, 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...
Post #717652
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse