SQL Server Availability Group Listener problem
-
I'm hoping someone experienced with Availability Groups can help me with this issue. Our environment consists of two clustered SQL 2014 Enterprise servers running one AlwaysOn Availability Group. One server is the primary and the other is the secondary. They are in separate subnets. When our web application calls the sql-listener the following error is thrown:
The target database is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.
The problem is our web application in the same subnet as the secondary replica (10.0.1.0/24) has the connection string directed at the sql-listener and the listener is not directing communication to the Primary like it should, instead it is directing it to the secondary. Our secondary is not accessible and not read-only for compliance reasons. As a workaround we've had to replace the connection string "sql-listener" with the primary replica hostname. This is bad since if it fails over, we'd have to manually replace the connection string to point to the secondary which should now be the active replica (correct me if I'm wrong).
Primary:
Secondary:
Both listeners are in DNS:
C:\Users\User1>nslookup sql-listener
Server: mydnsbox
Address: 10.0.0.64
Name: sql-listener.mydomain.int
Addresses: 10.0.1.92
10.0.0.82
The connecting string "sql-listener" works in the 10.0.0.0/24 (primary resides here) subnet but not in the 10.0.1.0/24 (secondary resides here) subnet. All servers can communicate with each other in both subnets and ports aren't be restricted.
-
check this first see if it helps
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Perry Whittle (2/27/2016)
check this first see if it helps[ulr=http://blogs.msdn.com/b/sambetts/archive/2014/02/04/multi-subnet-clustered-sql-registerallprovidersip-sharepoint-2013.aspx]http://blogs.msdn.com/b/sambetts/archive/2014/02/04/multi-subnet-clustered-sql-registerallprovidersip-sharepoint-2013.aspx[/url]
I've already read that and just about everything else on google about this topic. This doesn't help as this basically tells the clustered servers not to create multiple IP's for the sql listener. By doing that you don't have any high availability. I need to know why my application is being sent to the secondary replica instead of the primary when it hits the listener on the secondary's subnet.
-
This doesn't help as this basically tells the clustered servers not to create multiple IP's for the sql listener.
I don't think it does. What is your Register All Providers IP setting?
-
Hi,
We had an issue with our cross subnet cluster a few years ago and it was the client that was the issue as was not > SQL Native Client 11 and utilising the parallel connection to both 'potential' ips for the listener (giving you HA).
In our case as the AGs were async and we do a manual failover we reduced HOstRecordTTL and set the RegisterAllProvidersIP to 0 so only active IP in DNS and comes online on seconadary site quick after manual failover
Are your drivers or connection methods any which may be impacted in Appendix A below?
-
travisrunyard (2/29/2016)
Perry Whittle (2/27/2016)
check this first see if it helps[ulr=http://blogs.msdn.com/b/sambetts/archive/2014/02/04/multi-subnet-clustered-sql-registerallprovidersip-sharepoint-2013.aspx]http://blogs.msdn.com/b/sambetts/archive/2014/02/04/multi-subnet-clustered-sql-registerallprovidersip-sharepoint-2013.aspx[/url]
I've already ready that and just about everything else on google about this topic. This doesn't help as this basically tells the clustered servers not to create multiple IP's for the sql listener. By doing that you don't have any high availability. I need to know why my application is being sent to the secondary replica instead of the primary when it hits the listener on the secondary's subnet.
What is the status of your Register All Providers IP setting?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
I don't think it does. What is your Register All Providers IP setting?
RegisterAllProvidersIP property for network name 'Name: sql-listener' is set to 1
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply