Had a client today asking about this situation. They were working away on a machine and suddenly they got the message “Failed to update the database because the database is read-only”.
- The user hadn’t changed anything that they were aware of.
- Based on the user’s permissions (ie: what they could see), everything in SSMS looked normal.
- When they checked the sys.databases view, the database showed MULTI_USER.
- There was enough disk space.
- Folder permissions had not changed.
- The user was puzzled.
The issue was caused by the database being part of an availability group, and the AG had failed over. So suddenly, the user was connected to the replica database, not the primary. This is why the database said it was read-only.
What the user should have done was to have connected to the AG listener in the first place, not to the server name. Then when failover occurs, the listener would follow the primary server.
I think this error message is confusing. I’d really prefer that the system tell you that you are now not connected to the primary replica.