standalone SQL2008 64 bit - can we use multiple name instance with same IP but different ports?

Question

standalone SQL2008 64 bit - can we use multiple name instance with same IP but different ports?

suan

Valued Member

Points: 50
More actions
January 18, 2010 at 6:14 pm

#215803

I have installed a standalone SQL2008 64 bit Enterprise Edi in a server.
I have installed a few SQL named instances. Is it a correct way to access to various named instance using the below approach:
==>Can I using the same IP address with different port to access different instances?
Or I need to create multiple IP to assigned to different instances and use the same port?
My Experience:
the default port is 1433, i use it for default instance. For the named instance, it appear to have dynamic port, when I try to access servername\instancename, I am able to access it.
However, I do not wish to have dynamic port, I prefer to have fix port, so that when I restart the instance, i do not worry about the port changed.
Please advice.
Many Thanks

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 740723 More actions · Answer 1

You can use one IP, and set specific ports for the named instances (and even the default) in the SQL Server Configuration Manager.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

Just backing up what Steve said. 1 IP address, multiple ports.

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

suan Valued Member Points: 50 More actions · Answer 3

thanks for the prompt reply.

few more questions

1) my organisation is implementing firewall, will the default dynamic port assigned to the named instance will be block as well?

2)will there be a different interm of performance and etc for multiple IPs using a single tcp/ip port Vs single IP using multiple ports?

3)If this is a clustering with nodes, the multiple named instances can be still using single IPs with multiple ports?

4) will there be a performance difference if we have 2 instances instead of 1 instances if we have multiple DBs to be hosted?

and if 1 DB needs to implement TDE, will it be more advisable to split those dbs that do not need tde into another instance so that it will reduce the possibility of the slowness occured in the instance which has TDE. I understand that if the DB is having TDE (transparent data encryption) the tempdb size is growing and a lot of IO in the log file...Not sure if there is another instance to isolate those dbs that do not need TDE, to ensure those dbs are not affected by the TDE.

WIth the assumption all dbs/instances are in the same disk array partitions..

if the db and instances are in the different disk array with multiple raid card, will it help in the perfromance? and also improve IO...and when we implement TDE,will it get some benefit out the architecture set?

Please advice.

Many thanks..

SQLRNNR SSC Guru Points: 281344 More actions · Answer 4

suan (1/19/2010)
thanks for the prompt reply.
few more questions
1) my organisation is implementing firewall, will the default dynamic port assigned to the named instance will be block as well?

Typically yes. You would need to open up the firewall ports. A better option is to specify static ports. You will still need to open up the ports, but they don't pose the problem of dynamically changing upon service restart.

2)will there be a different interm of performance and etc for multiple IPs using a single tcp/ip port Vs single IP using multiple ports?

Multiple IPs would mean multiple servers - or VMs. If you are trying to install on a single server - one IP is the way to go. Will there be different performance v. multiple servers - potentially, and typically yes. It depends on your usage.

3)If this is a clustering with nodes, the multiple named instances can be still using single IPs with multiple ports?

Yes. Just remember that in a clustered environment, you must have dedicated resources to each SQL server.

4) will there be a performance difference if we have 2 instances instead of 1 instances if we have multiple DBs to be hosted?

It depends on Usage.

and if 1 DB needs to implement TDE, will it be more advisable to split those dbs that do not need tde into another instance so that it will reduce the possibility of the slowness occured in the instance which has TDE. I understand that if the DB is having TDE (transparent data encryption) the tempdb size is growing and a lot of IO in the log file...Not sure if there is another instance to isolate those dbs that do not need TDE, to ensure those dbs are not affected by the TDE.
WIth the assumption all dbs/instances are in the same disk array partitions..

It is worth a test.

if the db and instances are in the different disk array with multiple raid card, will it help in the perfromance? and also improve IO...and when we implement TDE,will it get some benefit out the architecture set?

Different disk arrays with different raids and cards will often times help improve performance. This is one that your server usage makes a big difference on performance and whether or not there will be any noticeable effect.

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

suan Valued Member Points: 50 More actions · Answer 5

thanks for your reply

I do not have the test environment, May I know can we allow to set an instance with both dynamic port and static tcp port?

Can we assign the same static port to different instances which use the single IP in cluster environment or standalone environment?

suan Valued Member Points: 50 More actions · Answer 6

What is the best way to allocate the memory for each sql instances?

if we do not allocate any memory usage, will all the instances share the same pool of memory?

In SQL DB usgae perspective, is I/O issue more important than Memory issue?

SQLRNNR SSC Guru Points: 281344 More actions · Answer 7

suan (1/19/2010)
thanks for your reply
I do not have the test environment, May I know can we allow to set an instance with both dynamic port and static tcp port?
Can we assign the same static port to different instances which use the single IP in cluster environment or standalone environment?

You may install multiple instances on the same server, one with static and others with dynamic.

I prefer to not use the same port for different instances, unless they are on different servers.

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

SQLRNNR SSC Guru Points: 281344 More actions · Answer 8

suan (1/19/2010)
What is the best way to allocate the memory for each sql instances?
if we do not allocate any memory usage, will all the instances share the same pool of memory?
In SQL DB usgae perspective, is I/O issue more important than Memory issue?

Set min and max memory for each instance and leave enough memory for the OS.

If you do not specify those settings, then SQL Server will govern the usage between the instances.

IO and Memory are both very important. However, at different times you may have to treat one more than the other in order to maintain your databases.

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