Failed to update database because the database is read-only

Question

Failed to update database because the database is read-only

webrunner

SSC-Dedicated

Points: 31866
More actions
June 22, 2020 at 5:46 pm
Go to Answer
#3764703
Hello experts,
A client reports they are getting this error when trying to connect to a database:
```
Could not log login info.  Failed to update database because the database is read-only.
```
However, when I check the database, it is not in a read-only state:
```
SELECT DATABASEPROPERTYEX('MyDBNAme', 'Updateability') as MyDBState

MyDBState
READ_WRITE
```
I also ran this query, and it returns 0 rows:
```
select name, user_access, user_access_desc, state_desc, is_read_only 
from sys.databases 
where is_read_only = 1
```
The issue was reported after I modified a process to automatically restore the database in dev from a production backup, as mentioned in this article.
https://www.mssqltips.com/sqlservertip/5194/automate-refresh-of-a-sql-server-database-that-is-part-of-an-availability-group/
The database is in an availability group, so I suspect somehow its participation in that group may be causing the issue. But does anyone know what problem would cause the client to see a "read-only" error when the database is not actually in a read-only state?
Thanks for any help.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Jeffrey Williams

SSC Guru

Points: 90303
More actions
June 22, 2020 at 9:29 pm
Answer
#3764734

What is their connection string?
And why are they using the wrong listener to access a database? If using the correct listener works as expected - then they should use that one instead of 'hoping' the AG's are both currently running from the same system.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Are they connecting to the listener - or directly to the server? If connecting to the listener - do you have a secondary in that AG set to read-intent? And - if that is the case, are the users passing in the parameter on the connection indicating a read only connection?

If you have read-intent setup and configured and a connection is defined as read-only, they would be routed to the secondary database which is set to read-only and that would generate this type of error.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

webrunner SSC-Dedicated Points: 31866 More actions · Answer 2

Thank you, Jeffrey!

Yes, the client reported this error using the listener name. I will investigate the read-intent setting as you advised.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31866 More actions · Answer 3

Follow-up:
"If connecting to the listener - do you have a secondary in that AG set to read-intent?"

All of the secondaries I checked have these settings:

Connections in primary role: Allow all connections

Readable secondary: Yes

What is odd is that all of the secondaries in production have the same settings

Connections in primary role: Allow all connections

Readable secondary: Yes

But this error is not happening there.

Also, sorry I didn't mention this possibly crucial detail before: The client was using a listener that we changed when we assigned the DB in question to a different group. Say, AvailGroupDev01 and AvailGroupDev02.

The "read-only" error happens when the client tries to connect to the DB via the old AvailGroupDev01, but no such error when they try to connect to the DB via AvailGroupDev02.

But both AvailGroupProd01 and AvailGroupProd02 connections throw no errors when the client tries either one.

Can you shed any light on the issue given the additional above info?

Thanks again,

webrunner

This reply was modified 4 years, 5 months ago by webrunner.

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31866 More actions · Answer 4

Thanks again, Jeffrey,

You make excellent points. I tried asking the client about the connection string, but they use an application, so all I could get was the general info about the server name they enter when they are presented with the application login form. I'll ask the client if they can get the underlying connection info from the application vendor, or put me in touch with the vendor to get more info about the setup.

And yes, if needed I will let the client know that they should use the properly working AG, not hope to keep using the old config. This is probably more of a communication and training issue on my part. I am relatively new to AGs and didn't realize at first that their references to AvailGroupDev01 were something they had started using in the application. To them I suppose it seems arbitrary and they don't want to have to ask users to reconfigure the connection, but as you point out, that is not a real reason not to use the proper config.

Thanks again!

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

By using the 'wrong' listener - it is entirely possible that AG group is active on node 1 and the other AG group is active on node 2. In that scenario, using the wrong listener will attempt to access the database from the wrong node - and that will cause the error they are seeing.

Just because it is currently working in production does not mean it will always work. If the AG AvailGroupProd01 fails over - and that group does not contain the database they are accessing - but is the connection they are using for that database, it will fail with the same error.

One last note: is there a specific reason you have setup your secondaries as read-intent? Unless you are experiencing some type of blocking issues related to processes reading data it really isn't warranted. Further - it is not recommended to be offloading your reads to the HA node, especially since a fail-over would result in those reads now occurring on the single node (assuming the other node is no longer available).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

webrunner SSC-Dedicated Points: 31866 More actions · Answer 6

Thanks again, Jeffrey, much appreciate your additional advice.

Sorry for any confusion regaridng the secondaries. I don't think any of our secondaries are set to read-intent. They all look like they're set to 'Yes'.

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html