question

Raphi-7891 avatar image
0 Votes"
Raphi-7891 asked RaphaelBoosAdmin-9560 commented

Distributed Network Name for failover cluster instances in SQL 2019

Hi

The feature with DNN and FCI, that is available since SQL Server 2019 CU2, is pretty interesting for us but we are not exactly sure if we are on the right path.

We are currently setting up an SQL Failover Cluster with two servers (Server1, Server2) and two instances that are running on the same servers, called "INSTANCE_A" and "INSTANCE_B". Server1 is currently holding the roles but can be failed over without any issues.

I have two roles in my failover cluster manager, one is called "SQL Server (INSTANCE_A)" and one is called "SQL Server (INSTANCE_B)".
In both roles are the SQL Server and SQL Server Agent resource and SQL Server CEIP role, according to the configured instance.
In addition, both roles use an own (virtual) server name and ip address in the same subnet, sqlc01 (10.0.0.3) for INSTANCE_A and sqlc02 (10.0.0.4) for INSTANCE_B.
In my understanding, we can define two different DNN, one for "Server1\INSTANCE_A" and one for "Server1\INSTANCE_B".
For both roles we set a Distributed Server Name / DNN to FCIA (INSTANCE_A) respectively FCIB (INSTANCE_B).

Now when I try to connect to FCIA with the SQL Management Studio (18.7.1), the correct databases will be displayed, the one from "Server1\INSTANCE_A".
When I try to connect to FCIB, the same databases are displayed as in FCIA. When I add the instance name to the DNN, like FCIA\INSTANCE_A and FCIB\INSTANCE_B, I connect to the correct instances.

What is the advantage in using the DNN instead the virtual server name, when I still have to define the instance name in the connection string?
Our idea was that we only can use FCIA and FCIB for the connection strings, but it seems that this is not the correct way.
I know we can use network aliases, but this did not work correctly either, I think there is a missing setting too.

Thank you in advance.

sql-server-generalazure-sql-virtual-machines
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SeanGallardy-MSFT avatar image
1 Vote"
SeanGallardy-MSFT answered RaphaelBoosAdmin-9560 commented

If you're not using SQL Server in Azure or another cloud provider where an internal load balancer is needed, then there is no point to use a DNN. That's the basics.

So if you're doing this on-prem, just do it the standard way as using a DNN actually makes more work for you.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for the response.

We are using Azure SQL Virtual Machines with a failover cluster (as we cannot use Azure SQL Managed Instances).

According to this Microsoft article: https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-distributed-network-name-dnn-configure, a load balancer is not needed anymore when using DNN ("It provides an easier way to connect to the SQL Server failover cluster instance (FCI) than the virtual network name (VNN), without the need for an Azure Load Balancer.")
That was our intention. But as you already mentioned when it does not give us any advantages, it makes just more work for us.

What do you suggest instead for the client connections? Just a DNS Alias on sqlc0001 and sqlc0002? Or should we only use the server name of the cluster role (like sqlc0001\INSTANCE_A)?

Thank you.

0 Votes 0 ·

Great, you're using it with the correct intent.

Now with the new information you provided and the original post, DNN is not your problem. DNN's just like a VNN do not have port numbers, so using the DNN alone will connect to whatever instance is listening on port 1433 - if any. The reason it works when you using the instance name is because the browser service is then queried for the correct port number to that instance.

Since you're using multiple instances on the same cluster, you'll need unique ports for each instance as the DNN is going to be tied to the individual node address. Your connection string, if you don't want to use the browser service, will need to include the port number for that instance. Once that is done you shouldn't have any other issues, but do make sure that multisubnetfailover is also set as part of the connection string.

1 Vote 1 ·

I figured something similar in the meantime and I will go with the port approach.
Thank you for your help!

0 Votes 0 ·