November 30, 2007 at 6:00 am
I want to set up additional instances for SQL Server cluster, eg.
SQLVIRTUAL1\INST1 and SQLVIRTUAL1\INST2.
Each instance has its own IP address, cluster group and disks.
SQLVIRTUAL1\INST1 IP1
SQLVIRTUAL1\INST2 IP2
Can I use the same port number: 1433 for all three instances: Default and both above?
I know that for three NIC's it's possible but in this case we have only one public interface and all three IP Addresses are virtual once attached to one physical.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
December 2, 2007 at 12:48 pm
Each instance requires its own IP. You can set those in the Configuration Manager.
However, you should be very careful about exposing 1433 to your public side. A report last week about lots of db servers open on the Internet just invites people to attack you. Only specific IPs should be able to get to the SQL Server.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 3, 2007 at 1:30 am
Each instance ahs its own IP. My question is another:
Can I assigned the same port 1433 to those three instances?
I don't know if cluster is so clever that it will recognize that those ports are belong to different IP Addresses assuming that there is only one physical.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
December 3, 2007 at 6:54 am
Are you able to try this out? If you ever try it out, let us know if it works.
You could put a very simple table on each instance with different data in each instance. Then connect with a basic app to a specific instance and port (xxx.xxx.xxx.xxx, port) and see what data you get back in a query. You might want to try it first with only one instance up at a time. Then if that all works you could bring them all up and then try each ip, port combo to verify there is no issues with them all up.
Jimmy
"I'm still learning the things i thought i knew!"December 3, 2007 at 6:59 am
I can test this but still without official statement from Ms we can't implement this on prod. But if you don;'t have idea maybe it's good to open case and ask them directly.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
December 3, 2007 at 7:15 am
Yes that sound good, Please go ahead and open a case with MS. Update us also .
"More Green More Oxygen !! Plant a tree today"
December 3, 2007 at 7:46 am
If you have 3 separate NICs, you might be able to do this, but my suspicion is that the default instance will bind to 1433 on all NICs.
If this is clustered, these aren't the NICs being used for the heartbeat or some other cluster communication are they?
I'd recommend you not do this. If there are any places where this causes issues, you will end up with other administrators very confused. I have also had many network engineers be concerned about running different production services out of different NICs. We get strange behaviors sometimes.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 3, 2007 at 8:10 am
The problem is that in standard clusterconfiguration you have two physical NICs: Public nad heartbeat and several virtual NICs for Virtual OS and each virtual SQL instance.
Now the question is how Physical NIC will behave when there will be three virtual IP Addresses listen on the same port 1433
SQL1 IP (10.1.1.2): 1433
SQL2 IP (10.1.1.3): 1433
SQL3 IP (10.1.1.4): 1433
bounded to
Physical NIC (10.1.1.1)
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
December 3, 2007 at 8:15 am
That's a good question. I'd open the MS case, since they will need to give you proof anyway.
Love to hear the answer. My suspicion is they'll say it won't work. be sure you push for a senior engineer to confirm one way or the other.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 3, 2007 at 11:49 pm
The technical paper on Clustering indicates that this is possible due to the obvious reasoning of TCP communications occurs on an IP and Port level. As Virtual SQL instances are bound to a Virtual IP then port 1433 is only in use once for each Virtual SQL instance, as opposed to a Non clustered server where only a single IP is supporting multiple SQL instances and thus each has to operate on a unique port.
the excerpt from the MS doc is below
Multiple-Instance Cluster
A multiple-instance cluster has up to four server nodes and supports up to 16 instances (1 default, 15 named or 16 named). Each SQL Server 2000 virtual server requires its own disk resources that cannot be used by other instances. These disk resources are the logical drive names (for example, drive F:\) used by SQL Server on which to store data and log files. Separate physical disk sets are needed to make up the logical drive, unless your disk subsystem supports multiple logical drives on one physical drive set. SQL Server in a clustered environment also behaves differently from a stand-alone named instance in relation to IP ports. During the installation process, a dynamic port that may be something other than 1433 is configured, and that port number is reserved for the instance. In a failover cluster, multiple instances can be configured to share the same port, such as 1433, because the failover cluster listens only to the IP address assigned to the SQL Server virtual server, and is not limited to a 1:1 ratio. However, for security and potentially increased availability, you may want to assign each virtual server to its own unique port of your choice or leave it as it was configured during installation.
And further reading on clustering (yes its SQL2000 but the concept still applies - I couldn't locate the 2005 doc) is here
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx#EDAAC
December 4, 2007 at 3:05 am
Thanks a lot for this findings.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
December 5, 2007 at 4:24 pm
You definitely can have multiple virtual instances with the same port. We have a couple of clustered instances in our lab running in an active-active configuration. Right now they are both on the same node and port, but different virtual ips.
Andy
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply