SQL Availability Groups: Can clients use cluster name instead of listener name(s)?

Question

When using Availability Groups, is it possible to simply just configure all clients/applications to connect to the database(s) by using the cluster name/IP instead of the AG listener for the AG that the database resides in?

If it is possible, would it be advised against?

Example:

• 3 node cluster with cluster name of "SQLCLUSTER2014" and cluster IP of 10.0.1.10
• 2 nodes located at main site, one node off site at a DR site
• 2 AG's, with listeners called "AG1_LISTENER" and "AG2_LISTENER"
• Create a DNS A record for "SQLCLUSTER" that points to 10.0.1.10 (the cluster IP)
• Configure all clients/application to point to "SQLCLUSTER" in their connection string

So when you upgrade to SQL 2016 and move your databases to the new server, you can just point your A record to the new cluster IP and not have to change connection strings in applications.

It seems like this would be a better setup because it allows you to utilize DNS CNAME's or A records to mask server moves, which I've done to great success on systems that don't use AG's.

Is there any reason not to do this? Any potential issues reaching the DR site if the 2 main nodes go offline?

Thanks.

Answer 1

First, when using AGs, you generally are not using a clustered instance of SQL Server, you are using multiple standalone SQL Servers. You are able to technically connect to each individual SQL Server in the Windows Cluster Separately. Each having their own configuration and resources.

Secondly, each group has its own name in AD as a computer object and its own IP address. These are registered in DNS. These are different than the cluster name. The listener name is a special object to route traffic based on incoming query type. For instance, if you configured your AG to use a secondary node for read only queries, the listener name is what routes that query to the secondary node, not the cluster group. Backups are also attached to that listener name for routing traffic from the primary to the secondary node.

If you are monitoring AG health, it will be based off of the listener name/object, not the cluster name. It is possible to have a 3 node windows cluster, but only have an AG involving 2 nodes. The cluster name would not properly handle that.

Adding further complexity to this question. With SQL 2016, you have distributed availability groups which uses separate clusters on different networks or domains. Those separate clusters are clusters all to them selves and are only joined via the listener name for failover purposes.

Lastly, if you chose to extend your AG to Azure, you will need to use the listener when connecting to the "active" database so your query can route to the appropriate node. The cluster resource name does not know about the Azure secondary node.

Answer 2

I just tested it and yes, you can connect to the cluster name/IP, at least using SSMS. Now, just because you can, does that mean you should? That answer is, "no."

As has been mentioned, an Availability Group listener is created so that you can connect to that network name and it will determine which replica to which you should be connected, based on your routing rules and lists and your client connection parameters. However, the Availability Group listener is the only main cluster resource that is specifically part of the Availability Group. Additionally, it does not have to follow the "owner" of the cluster service. Essentially, node B can own the cluster IP address while node A is the "primary replica" to which the Listener will route.

Where connecting to the cluster IP will hurt you here is that if that's how you connect, in the scenario I described above you will get connection errors unless you've configured your AG for readable secondaries because you're technically connecting to the secondary replica, even though the cluster node that hosts that replica is hosting the cluster IP at that moment. Even if you have configured this feature, you're not going to be able to write to the secondary replica.

Answer 3

Point your DNS record to the AG listener. I don't think you can connect to the cluster resource name or IP, though admittedly I haven't tried it.

Answer 4

Late to the party, but we should distinguish between Enterprise vs Standard editions of SQL Server.

If you are using SQL Standard which only supports Basic Availability Groups (2 nodes only, Active/Passive, secondary node is NOT readable) versus Enterprise's full function Availability groups. -- Share nothing hardware environment

So with SQL Server Standard is there any value to creating the listeners if we are primarily interested in a server-level, hardware / OS protection scenario. -- Understanding that they maybe other reasons to you want to fail an individual database...

Testing Environment: -- SQL Server Standard 2016 SP1 -- Basic Availability Groups without Listeners -- SQL Connection strings using the Windows Cluster Name as defined via WSFC.

Testing Scenario: -- Excel file query against the Cluster name. -- Primary/Active node returns data -- Disable NIC on primary SQL node -- WSFC shows 2nd node servicing the BAGs (Basic Availability Groups) under Cluster's Roles. -- Rerun Excel query against the Cluster name to validate successful fail-over.

Testing Notes: -- Need to ensure that user's have permissions on both SQL nodes (didn't add user to the second node initially which caused the Excel query to fail due to an authentication issue). Added permissions and the query ran successfully.

-- Tested with Domain user accounts, local SQL user accounts use server specific SIDs which will cause other issues with replication (as they do with Backup/Restore across different servers).

Mike Peterson