SQL Server – Default Instance Name in a Cluster

clusteringinstancesql server

I have a cluster set up with two virtual instance names

WIN2K8SQL1\INST1

WIN2K8SQL2\INST2

that was set up by the IaaS company.
Now developers are complaining about the naming convention so we decided to create an alias in the DNS. But alias only works for WIN2K8SQL1 and not for INST1, so that they need to use that in the connection strings:

MEANINGFULNAME\INST1

Is there a way to name the instances on the cluster MSSQLSERVER?

Best Answer

DNS CNAMEs take care of the host name only, but do absolutely nothing for the port number, which is what the instance name represents.

If you want to use CNAMEs for this, you will have to make the two instances behave as if they both were default instances: in other words they will both have to listen on port 1433.

Each instance in a cluster has its own IP address, so this is totally possible. More information can be found on this blog post.

In your case, you could set up something like this:

MEANINGFULNAME1 -- > WIN2K8SQL1\INST1
MEANINGFULNAME2 -- > WIN2K8SQL2\INST2

Where MEANINGFULNAME1 is a CNAME for WIN2K8SQL1 and MEANINGFULNAME2 is a CNAME for WIN2K8SQL2.

Related Solutions

Sql-server – What are the naming rules for named instances on a cluster

You are correct in noting that the virtual name needs to be distinct. I'm having a hard time understanding what you're describing so I'm going to try to lay it all out.

SINGLE INSTANCE FAILOVER CLUSTER

If you're truly looking for "Active/Passive" then you're going to need at least four servers - two nodes for each instance. Let's call the instance SQLPROD-EAST.

You could then have:

Node A: SQLPROD-EASTA
Node B: SQLPROD-EASTB

Virtual Name: SQLPROD-EAST

as your nodes.

The virtual name would be SQLPROD-EAST and you'd install the default instance of SQL Server would would take the name SQLPROD-EAST. You'd then replicate this topology with SQLPROD-WEST:

Node A: SQLPROD-WESTA
Node B: SQLPROD-WESTB

Virtual Name: SQLPROD-WEST

MULTI-INSTANCE FAILOVER CLUSTER

This is where your description got confusing because it seems like you began describing Active/Active. This might look like:

Node A: SQLPRODA
Node B: SQLPRODB

Virtual Name: SQLPROD

as your nodes.

The virtual name would be SQLPROD and you'd install two named instances of SQL Server SQLPROD\EAST and SQLPROD\WEST.

Sql-server – How to setup SQL active/active cluster to achieve Blue / Green instance switching

What you are looking to do is basically have two instances running that you can redirect clients between. There's no need to do this within Failover clustering as this won't help you any. Doing this simply requires that you setup two standalone instances of SQL Server, each on a separate Windows server. Then setup a CNAME in DNS and use that to point people to the correct SQL Instance.

Now for some of the problems.

The data won't be in sync between the two instances.
DNS takes time to replicate and update on the client machine's local cache, so you'll end up with users connected to both systems.

A better solution would be to take a snapshot of the database before doing the schema change release. If the release is successful delete the snapshot. If the release fails and you have to rollback you'll need to restore the database from the snapshot. This will be pretty quick, but there will be an outage while the rollback happens.

What you are looking for basically won't work.

Best Answer

Related Solutions

Sql-server – What are the naming rules for named instances on a cluster

Sql-server – How to setup SQL active/active cluster to achieve Blue / Green instance switching

Related Question