February 17, 2016 at 11:02 am
Hello, I have a two node cluster with a availability group that contains one database for initial set up. I have the replica set for "all connections" and both databases are showing synchronized on the ssms display. When I fail over to the secondary replica and launch the app that sits on top I get errors that the database is read only and the app cant continue because of this. When I look at the database on the secondary it is in read/write mode and I can make changes through sql code. I do have a local sql user account that the app connects with and I wonder if this is what is causeing the issues as the sid's may be different from the old primary to the secondary? New to AG groups so any help would be appreciated. Thanks.
February 17, 2016 at 1:42 pm
Are you connecting to the listener or to the instance?
You should have a listener to connect to that redirects to the appropriate node depending which node is primary.
So there should be three possible connection strings:
Availability Group Listener (use this one)
\\servername_Node1\instanceName
\\servername_Node2\instanceName
February 17, 2016 at 4:09 pm
Thanks Maxer! I think I am understanding this now. I didn't have a listener so I am setting that up now to see if it works. So when I failed over the web-config of the app was pointed directly to the primary by instance name, well when it became the secondary and read only thats why I saw it as a read only database right? I never actually pointed the app to the secondary, it was always pointed to the original primary that became readonly on fail over??
February 17, 2016 at 6:38 pm
Correct, it is like the load balancer to the web farm in that it sends connections to the appropriate server.
June 5, 2019 at 2:24 pm
Dear All,
In spite of having a listener am getting read-only databases in the secondary during the failover. Not sure where it goes wrong. Any help is highly appreciated. Thank you.
______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply