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.
Best Answer
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.