SQL Server 2014 Always On .. can I have replication without a listener?

Question

I am still a novice with SQL Server Always On, having recently converted from a standalone server to an always-on configuration. I currently have two nodes on the same subnet configured in my availability group which is working out great.

The problem comes when I try to configure a third node into the availability group. This third node is across the WAN in a completely different subnet. When I try to add the third node to the group I get an error in the step for "Joining secondary replicas to availability group "agname". clicking on the details of the error

None of the IP addresses configured for the availability group listener can be hosted by the server <new server name>. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address which can be hosted on a public cluster network for this server

So, I tried to add another listener address, but viewing properties of the already configured listener does not give the option to add another address. Trying to add a second listener throws an error that a listener is already configured.

Backing up a moment, I do not need this third node to be a failover candidate. I want it to be a read-only copy. Is it possible to add it into the replication without having a listener in its subnet?

Is there another way to add a second listener with a DHCP address in that subnet so I can complete the wizard? Searches online so far have only shown workarounds by creating additional client access points in the failover cluster configuration itself which is a route I am not sure I wanted to take.

Answer 1

You can configure multiple listeners but what I think you want to do is just configure the other IP (for the 3rd replica) at the cluster level so that your AG resource can access it. If your cluster is configured for the multi-subnet then you should have the ability to add the IP for that 3rd replica to your current listener.

If I recall you might have to create the role in the WSFC for your listener as a client access point. I know this is the required configuration when I have built AGs in Azure environments, but those may be special circumstances compared to dealing with all on-premise setups.

Answer 2

You've got a few different questions in here.

Q: Can I have Always On Availability Groups without a listener?

Yes, and I know some folks who prefer to do it that way just to avoid the problems with listeners. They code their apps to directly point to the normal primary SQL Server, and if their app fails to connect, their app code tries another server name.

I'm not a big fan of that approach in most scenarios because it takes away your flexibility to add/rename/move replicas at the DBA level. But when you've got a lot of really savvy developers, and a willingness to recompile the code if necessary (or play with DNS CNAMEs) then it can work.

Q: If I add a new replica, do I need to add a new listener?

No. However, if the new replica is in a new subnet, you'll need to add an IP address in that subnet to your existing listener.

Q: Can I get away without adding that IP?

Yes, but you're probably going to run into support problems down the road. If you're going to use a listener, I would configure it so that it can fail over to any replica. If you're not going to use a listener, then this part of the question doesn't matter.