Issue with SQl 2014 AG and listener

High Availability
,
1

I have issue with my SQL AG; when I am doing a failover I can't ping anymore the listener

I have double check firewall on all SQL servers and it is not active

Here an overview of my environment

2 SQL 2014 server configure in 2 AG; one for each SQL Instance

Server one
2 instance

public lan : 10.10.0.x /24

Windows failover cluster network (private or Heartbeat) : 10.10.2.x /24

AG replication network instance 1: 10.10.3.x /24 on port 5022
AG replication network instance 1: 10.10.3.x /24 on port 5023

listener instance 1: 10.10.0.x /24 on port 1433
listener instance 2: 10.10.0.x /24 on port 2020

Server Two

public lan : 10.10.0.x /24

Windows failover cluster network (private or Heartbeat) : 10.10.2.x /24

AG replication network Instance One: 10.10.3.x /24 port 5022
AG replication network instance 1: 10.10.3.x /24 on port 5023

listener Instance one: 10.10.0.x /24 port 1433
listener instance 2: 10.10.0.x /24 on port 2020

Scenario 1

start a ping to the listener name

After a failover to the secondary if the pc is on the same network than the public network and the listener (10.10.0.x /24) the ping is lost for a ping or two and the ping come back. The ping is done on the listener DNS Name

connection to SSMS are working with both listener name and sql instance name

Scenario 2

start a ping to the listener name

after a failover to the secondary if the pc is not on the same network than the public and listener network (10.10.0.x /24) the ping is lost and doesn't come back. To get the ping back I have to failover back to the primary. For example if I am doing the ping from my workstation I lost the ping until the failover to the primary occured

connection to SMSS are not working with the listener name but are working with the sql instance name

never lost ping to sql servers by using the windows computer name

seems strange to me

thanks for your help

2

So, how do you expect the listener to be able to see the failover replica? You probably need some explicit routing between them to make that happen.

3

Hi

the failover replica is visible if I ping the host by IP or name; only the listener is not reachable but both listener and the server are on the same network

my pc is 10.10.8.188 /24
sql server is 10.10.0.132 /24
listener is 10.10.0.89 /24

my pc ping 10.10.0.132 and not listener

so from what I see the route between my laptop and server network is ok

thanks

4

so listener is on a third network, You need to set up routing to that from both your workstations and the replicas