I have a strange issue, when I failover my AG from node1 to node2, the application log has this message "the database 'abc' is read-only", and is unable to perform any write operations on the db.
The application uses the listener IP in its connection string, and the listener is configured with the default port 1433.
when I failback to node1 everything works well.
the application account has sysadmin on both the nodes.
I have checked the sql error log and found nothing related to login or permission issues.
any help is much appreciated.
I was going to point you to posts here from a always on guru named @ahmeds08 but then I see it is you yourself asking that question!
is the app using ApplicationIntent=ReadOnly? is it possible they are using that (read-Only) connection to write to DB? can you trace connection to db to see what they are using to connect? Just asking dumb questions. We had same issue and found that they missed a connection string and it was connecting to primary server instead of AG
Not using ApplicationIntent=ReadOnly.
I have also tested using SSMS from the app servers and everything works fine(read and write), with failover and failback.
Even I thought the same, they might have hardcoded the instance name somewhere. But, its just the listener IP they are using.
Also used profiler, I can see connections from app server.
Out of ideas
This application does it use a service account? To connect and all
Is it a web app?
Oh you did already specify sorry
account has sysadmin on both the nodes.
Problem was with the app configuration, after reconfiguring the app configuration everything looks good now.
Initial app setup was done with standalone sql, later alwayson was implemented.
I think the app somewhere cached the standalone node name and was passing the connection to the node1.
Do share. I heard our sys admin once say applications need to be always on or cluster aware. Been a while.
that's good to know. thanks for providing the issue