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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Multiple Instances with Different IP...

17 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

Vikram-258966

Posted Thursday, February 19, 2009 7:45 PM

Valued Member

Group: General Forum Members
Last Login: Monday, May 06, 2013 12:09 PM
Points: 62, Visits: 107

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

Leo Leong

Posted Friday, February 20, 2009 3:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:50 AM
Points: 106, Visits: 315

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

Vikram-258966

Posted Saturday, February 21, 2009 12:29 PM

Valued Member

Group: General Forum Members
Last Login: Monday, May 06, 2013 12:09 PM
Points: 62, Visits: 107

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

Leo Leong

Posted Sunday, February 22, 2009 6:31 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:50 AM
Points: 106, Visits: 315

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

2 Tim 3:16

Posted Monday, February 23, 2009 7:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 1,081, Visits: 1,454

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

EdVassie

Posted Tuesday, February 24, 2009 5:32 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 AM
Points: 2,621, Visits: 2,759

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.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,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

Daniel-263255

Posted Tuesday, February 24, 2009 8:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:30 PM
Points: 150, Visits: 428

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

intekhab.alam

Posted Thursday, May 14, 2009 11:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:49 AM
Points: 2, Visits: 43

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

Andrew Hatfield

Posted Thursday, May 14, 2009 11:58 PM

SSC-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

intekhab.alam

Posted Friday, May 15, 2009 12:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:49 AM
Points: 2, Visits: 43

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 »

17 posts, Page 1 of 2

1 2 »»

Permissions