Sql-server – IP address to use with FCI, Always On AG and Always On (AG) FCI

availability-groupsclusteringfailoversql server

My understanding is that HA can be achieved using either of the below and my concern was which IP address should client applications use when connecting to SQL Server:

  1. Failover Clustering: For this setup the windows cluster and assign DNS/IP to this cluster. Then using SQL Server installation media we install SQL Server on a node and add more nodes to the cluster. If primary fails then another node will be brought online and will continue to service request. Am I right to assume that the clients should use the above cluster IP address to access SQL server? And that the Cluster IP address will decide where to direct the request to?

  2. Always on Availability groups: For this setup the windows cluster and assign DNS/IP to this cluster. Then we configure the AG on primary SQL Server and add more instances to the AG. We configure the Listener and give it a DNS/IP address. I know that client apps should use the Listener IP address and that if primary fails then the requests will to auto directed to the secondary by the listener. So what is the purpose of having the cluster DNS/IP address also?

  3. Combine the above – to get Always on Clustering (Clustering+AG): For this setup the windows cluster and assign DNS/IP to this cluster. Then using SQL installation media we install SQL server on a node and add more nodes to the cluster. Then we install a separate new SQL Server instance, add this to the same or separate Windows cluster. Then create an AG on one of the clustered nodes and add the other clustered node and also the newly created separate SQL Server instance into the AG. Here also I assume that client apps should be connecting to the Listener IP address and not the cluster IP address?

Best Answer

For Failover Clustering: ... Am I right to assume that the clients should use the above cluster IP address to access SQL server?And that the Cluster IP address will decide where to direct the request to?

When dealing with a SQL Server FCI, you'll want to use the virtual name provided in the SQL Server setup as the point of connectivity. Do not use the "Cluster Name" as that points to the node owning the cluster resources and not necessarily the SQL Server resource group.

Always on Availability groups: ... We configure the Listener and give it a DNS/IP address.

Correct, you use the listener.

So what is the purpose of having the cluster DNS/IP address also?

This is the administrative endpoint for the cluster. You'd use this to deal with cluster settings, since you're just looking at SQL Server there are high chances you'll never actually use it. It doesn't mean it isn't used, just you aren't actively using it with SQL Server as a client.

Combine the above (FCI + AG): ... Here also I assume that client apps should be connecting to the listener IP address and not the cluster IP address?

Correct.

Related Question