Quite an interesting situation I found myself in where I was perplexed for about 5 minutes. I was connected to an Azure SQL Database where I was configuring some users where then I executed a query and was presented with the following message:
Msg 3906, Level 16, State 2, Line 2
Failed to update database “testdb” because the database is read-only.
Did I used the ALTER DATABASE command and put this database into read-only? That was my initial thought. I connected to the database and issued the below.
SELECT name,user_access_desc,is_read_only FROM sys.databases WHERE name = 'testdb'
Ok, so is it read only or not?
Just to compare this is what a read only database should be returning:
So, what is going on? Any ideas?
Answer is….. ACTIVE GEO REPLICATION!
I issued the TSQL below against the database:
SELECT link_guid , partner_server , last_replication , replication_lag_sec FROM sys.dm_geo_replication_link_status;
This is the relationship, Cadbury is the primary and milkchoc is the secondary and the database I was trying to update was located on the secondary server which is read only by nature when in a replication partnership. Quite simply I had setup a failover group as shown below and was connected to the wrong server. This is why you should be using the Read/write endpoint!
Confusion cleared up..