May 16, 2012 at 7:50 am
I am configuring my Availability Group Listener and running into an issue during testing. The configuration of my AG is 3 SQL Servers (replicas). I can ping between all of them by name (SQL1, SQL2, SQL3) without issue. I can connect via SSMS as well. All is well until I start testing my AG Listener.
AG Listener was created when the Primary was SQL1. I could connect to the app (separate machine altogether) via the AG Listener and connect to it via SSMS on SQL1. I cannot ping and/or connect to it via SSMS on SQL2 or SQL3.
When I failover the AG, I am then able to ping and/or connect to theAG Listener on the server that now hosts the Primary (i.e. SQL2). However, I am unable to connect to the AG Listener anymore through the app. The other 2 SQL Servers hosting the secondaries are still unable to connect to the AG Listener.
I deleted my AG Listener and recreated it to insure all was well. I am experiencing the same behavior.
Any insight would be greatly appreciated!
Thanks,
Dave
May 18, 2012 at 2:17 pm
I will be going through the same exercice in a few days, installing AlwaysOn Avail Groups on a cluster.
Have you configured the listener on default port 1433, on all replicas?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 31, 2012 at 9:00 am
Yes. I did something to this affect:
ALTER AVAILABILITY GROUP [CW1]
MODIFY REPLICA ON N'SQL1'
WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [CW1]
MODIFY REPLICA ON N'SQL1'
WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL1.koth.local:1433'));
ALTER AVAILABILITY GROUP [CW1]
MODIFY REPLICA ON N'SQL2'
WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [CW1]
MODIFY REPLICA ON N'SQL2'
WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL2.koth.local:1433'));
ALTER AVAILABILITY GROUP [CW1]
MODIFY REPLICA ON N'SQL3'
WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [CW1]
MODIFY REPLICA ON N'SQL3'
WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL3.koth.local:1433'));
ALTER AVAILABILITY GROUP [CW1]
MODIFY REPLICA ON N'SQL1'
WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL3','SQL2','SQL1')));
ALTER AVAILABILITY GROUP [CW1]
MODIFY REPLICA ON N'SQL2'
WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL3','SQL1','SQL2')));
GO
October 20, 2015 at 9:25 pm
Hi,
Sorry for reviving this thread so late 🙂
Did you manage to fix this?? I am encountering the exact same issue..
October 21, 2015 at 5:22 am
What's the error message?
October 21, 2015 at 5:39 am
Let me try to put the gist of it:
I am not able to connect to the AG listener outside the primary SQL Server instance..
If I manually failover, I am able to connect to AG listener via the secondary ( which is the new primary after failover).
I checked error logs.... and I see nothing 🙁 Is there anything very obvious I am missing?
October 21, 2015 at 5:47 am
ganeshhegde (10/21/2015)
I am not able to connect to the AG listener outside the primary SQL Server instance.
And this results in absolutely no error message?
October 21, 2015 at 9:09 am
My original issue was caused by an environmental issue. The individual that set up the virtual cluster used templates but did not sysprep the servers. That said, DNS threw a fit. Once I realized what had not been done and redid it myself, all was well with a standard configuration of AGs and AG Listeners.
October 25, 2015 at 10:00 am
Sorry for the late update.
So i destroyed the cluster, recreated a new WSFC cluster, created the AG group. All well.
Things I am able to do:
a) Failover successfully
b) Connect to secondary replica from primary and vice versa.
c) Connect to the SQL Server AlwaysOn Listener from the Primary that is active.
Unfortunately I am still unable to connect to the SQL Server AlwaysOn Listener from any node that is not SQL primary.
SQL Error logs show nothing, as well as the cluster log.
The only error message I get while connecting to the Always On Listener from non-primary node is this:
TITLE: Connect to Server
------------------------------
Cannot connect to mylistener.example.com.
------------------------------
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
------------------------------
The network path was not found
------------------------------
BUTTONS:
OK
------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply