September 8, 2022 at 7:05 pm
I'm using SQL Server 2019 CU 17 Developer Edition and SSMS v18.10. I have 2 Azure VMs in a WSFC with a cloud witness and 1 AlwaysOn Availability Group. (I have tried with/without readable secondaries. When secondaries are readable, I specify read-intent only.) So, I have nodeA, nodeB and Listener1. NodeA and B are synchronous-commit with automatic failover. Failover dashboard says they're healthy. Everything is green.
Very often, I connect to Listener1 (via SSMS and/or Azure Data Studio) and am unable to run queries against my database. Error messages when using not-readable secondary: "The target database, 'xyz', is participating in an availability group and is currently not accessible for queries..." SELECT @@SERVERNAME returns the name of the secondary node. When using a readable secondary: "The target database ('xyz') is in an availability group and is currently accessible for connections when the application intent is set to read only..."
But I'm not connecting to nodeA or B. I'm connecting to the Listener1. Shouldn't it route my requests to the primary, all the time, unless I specify ApplicationIntent=ReadOnly? Any idea what I've done wrong?
Thanks.
...and if this is the wrong place for this kind of post, kindly point me in a better direction.
September 9, 2022 at 10:02 am
Seems as though this poster (SQL listener connection fluctuates between primary and Secondary. (microsoft.com)) had a similar problem but there wasn't a solution.
September 12, 2022 at 10:32 am
IS availability group fluctuating?
if not is the permission level is same on both instances?
Kindly attach screenshot with AG configuration and error.
Regards
Durai Nagarajan
September 12, 2022 at 11:59 am
Is it fluctuating? I can't really say how often this happens. It seems as if the listener decides incorrectly which system to forward connections to and continues forwarding incorrectly for a day before switching. Restarting the servers will correct the situation. When the systems were initially provisioned, this happened all the time. I made a few changes (seemingly unrelated: deleted all databases and restored from bak and re-added them, put all databases on a backup schedule) and the problem went away. A month later it started again.
The system is under very little load. Its main activity is running the log file backups.
Permission level? Can you clarify? I don't know what permission level you're talking about.
I am attaching screen shots of the AG configuration properties.
September 12, 2022 at 2:14 pm
Readable secondary is set to read intent only which means allows on each node only when read intent is mentioned.
set each node parameter of readable secondary to yes and check.
Regards
Durai Nagarajan
September 13, 2022 at 12:04 pm
Interesting suggestion. I'm more inclined to have the secondary reject connections for now. If I do as you suggest, I think queries will continue to succeed when they're connected to the secondary until they issue a query that modifies data. And to your previous question about it fluctuating, at the moment it seems to switch nodes after every 2nd or 3rd connection attempt. Thank you anyway.
September 14, 2022 at 4:43 pm
multisubnetfailover=true
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply