Azure SQL DB – Failed to update database because the database is read-only

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'

isread.

Ok, so is it read only or not?

Just to compare this is what a read only database should be returning:

yesred

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;

cad

 

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!

partner

Confusion cleared up..

 

 

 

One thought on “Azure SQL DB – Failed to update database because the database is read-only

  1. Pingback: Dew Drop - October 23, 2018 (#2829) - Morning Dew

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s