0

I'm setting up multiple SQL instances on an active/active cluster, and on our existing SQL Cluster, the cluster name is SQLCLUSTER, but we access the instances as SQLCLUSTERINST1\Instance1, SQLCLUSTERINST2\Instance2, etc. Since each instance has its own IP and network name anyway, can I install SQL as the "Default" instance on each network name? I'd really like to access my instances without having to give the instance name (ie, instead of the above, just SQLCLUSTERINST1, SQLCLUSTERINST2, etc), but my understanding of SQL is that, even in a cluster, the instance name is required, even though the IP already uniquely identifies an instance.

Does anybody know if I can do this? I'm about to install the first instance, and I wanted to get an answer to this before I start installing them as named instances if I don't need to. It just seems reduntant, and potentially unnecessary, to have to give the instance cluster name and the instance name to connect to a server when just the instance cluster name would uniquely identify a sql instance as-is. I would expect one default instance per cluster group (as they'd share an IP), but not per cluster.

Improve this question

3 Answers 3

Reset to default
2

You can only use default instances in an active/passive cluster. The reason for this is because you cannot have multiple default instances installed on the same server, clustering requires an instance to be installed on each node of the cluster to support fail over.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I understand how clustering works, and that's why I'd done instances in the past. The question I had was why an instance name is required if each instance is already uniquely identified with a hostname/ip? Instance names and unique ports are required when all the instances share an IP, but when they're on different IP address anyway, why couldn't each instance use port 1433?
Because SQL server wont let you install 2 default instances on the same machine.
And this sucks. Makes it so you can't assign DNS entries for database names so applications don't have to change connection strings when databases move to different servers.
@sqlryan - is it true what is stated in this answer - that You can only use default instances in an active/passive cluster.; I mean to ask - an active/active cluster also supports having one default instance, isn't it?
@variable I wrote that answer 15 years ago and I haven't been an MSSQL DBA since SQL2016 so I wouldn’t trust a newer answer from me. If I understand what you’re asking though, a server can only have one default instance, but not sure how that’s changed since I last set up clustering or with Always On and all the changes there.
|
1

I ended up finding a work-around for this. While I installed named instances on the cluster, I can access them using port 1433 on each DNS name, so I don't have to provide the instance name to connect, which is what I was after.

To get this accomplished, I have to modify the listener configuration to force each instance to listen on port 1433 on its dedicated IP, rather than just relying on dynamic ports and the SQL Browser.

I've detailed the steps on my blog

Improve this answer

Comments

0

Good idea rwmnau. I haven't read your blog post, yet, but I suspect the limitation revolves around registry keys or directory structures. Remember, each node only has one registry hive for SQL Server. There's a registry key that lists the instances on the box. It's a space separated list. I'm pretty sure that list has to have distinct values in it, therefore, you can't have more than one MSSQLSERVER instance. The internal instance name for default instances is MSSQLSERVER. So I think, if nothing else, there's your limitation, there. However, I do think you have a wonderful idea with using port 1433 for all instances involved. Good job and thanks for sharing. I think I might try that myself on my next cluster!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.