Are Availability Group Databases on a Secondary replica automatically set to read-only by the AG?

Question

I have read through the MS docs on read-only routing, but I am not talking about that.

Currently, if I execute an UPDATE against a database that is on a Secondary replica and is part of the Availability Group, I receive a message:

Msg 3906, Level 16, State 2, Line 6
Failed to update database "dbName" because the database is read-only.

My question is whether this configuration is established by default (and unavoidable) by virtue of the database being part of the AG and on a Secondary at the moment?

Some interesting extras:

The following SQL, run on that Secondary, shows I have UPDATE, ALTER and a whole lot more permissions against this database:

SELECT * 
FROM fn_my_permissions(null, 'DATABASE')   
ORDER BY subentity_name, permission_name;

The following SQL explicitly shows I have permission to UPDATE a table in that database:

SELECT * 
FROM fn_my_permissions('dbName.dbo.tblName', 'OBJECT')   
ORDER BY subentity_name, permission_name ;

In spite of this I am actually prevented from updating that table. This implies:

• The output of fn_my_permissions is not strictly correct
• The database "read-only" setting overrides individual permissions

I understand the setting in play is configured via:

ALTER DATABASE dbName SET READ_ONLY

and

ALTER DATABASE dbName SET READ_WRITE WITH NO_WAIT

The main reason for the question is to understand whether the "read only" configuration is a DBA/designer's responsibility during design and deployment of the AG, or an expected (and guaranteed?) feature by simple virtue of using an AG.

Answer 1

I would give the docs a good read. Particularly the section on availability replicas.

Every availability replica is assigned an initial role-either the primary role or the secondary role, which is inherited by the availability databases of that replica. The role of a given replica determines whether it hosts read-write databases or read-only databases. One replica, known as the primary replica, is assigned the primary role and hosts read-write databases, which are known as primary databases. At least one other replica, known as a secondary replica, is assigned the secondary role. A secondary replica hosts read-only databases, known as secondary databases.

As can be seen above, each replica can either be read-only or read-write (secondary or primary respectively). In addition there can only be one primary (read-write) replica. When we refer to replica in this respect, we are referring to the database level. Each server can host multiple availability groups, with potentially mixed primary-secondary configuration.

The situation you are referring to with multiple read-write replicas is not supported by AlwaysOn Availability Groups, which are designed to meet High Availability and Read-Scale needs, not multi-master setups. You should look at two-way replication technologies to support this need.

As for the read-only setting itself, this is automatically set by the availability group, although it's worth saying that this is not the same setting as the "Database Read-Only" setting found in the database properties (or is_read_only in sys.databases), nor is it the same setting as the file group level read-only option.

Answer 2

I have accepted George's answer because he pointed me directly to the docs that state AG databases on a Secondary will be read-only (ie. by design of the AG system by MS).

However in the last details of the question I spoke about ALTER DATABASE dbName SET READ_ONLY and George clarified that is a different setting than what is meant by the AG setting a Secondary's role to read-only, and by extension, all the DBs in that AG on that Secondary.

I investigated this further and found you can ascertain the difference between an update failing due to the DB being part of an AG on a Secondary, and due to the read-only property against the DB (which is set with the above SQL).

Below is a script that demonstrates how set read_only and set read_write affect sys.databases.is_read_only - but more specifically, it shows that you get a very slightly different error message if you attempt to update a database that is protected by this property, as opposed to it being on a Secondary in an AG.

The following SQL creates a new database which will automatically be read-write and will not be part of any AG, even if created on a replica that is acting as Secondary in some AG.

Note the comment giving the error message when you attempt to update a table while the DB is set to read_only - in particular, the portion:

• State 1

This is different than the error in the original post question, which showed

• State 2

An article elsewhere quotes MS Books Online to clarify the purpose of this value:

A one- to three-digit number with a maximum value of 127 that indicates to Microsoft support engineers and developers the location in the SQL Server code that generated the message

In other words, the error "Failed to update database "X" because the database is read-only" can originate from at least two different conditions: State 1 = database property set to read_only (visible in sys.databases.is_read_only), and State 2 = part of an AG and on a replica currently acting as a Secondary.

create database CLRTEST;
go

use CLRTEST;
go

create table CLRTEST.dbo.testTable (id int);
go

SELECT name, is_read_only FROM sys.databases where name = 'CLRTEST';
go -- Value 0 means we can read and write

SELECT * FROM fn_my_permissions(null, 'DATABASE') where permission_name = 'insert' ORDER BY subentity_name, permission_name ;  
go -- We have insert permission in the database

SELECT * FROM fn_my_permissions('testTable', 'OBJECT') where permission_name = 'insert' ORDER BY subentity_name, permission_name;
go -- We have insert permission on the table

insert into CLRTEST.dbo.testTable values (1);
go -- insert succeeds

alter database CLRTEST set read_only; -- Does change sys.databases.is_read_only to 1
go -- we set database to read-only

SELECT name, is_read_only FROM sys.databases where name = 'CLRTEST';
go -- value 1 shows database is now read-only

SELECT * FROM fn_my_permissions(null, 'DATABASE') where permission_name = 'insert' ORDER BY subentity_name, permission_name;
go -- ! but we still have insert permission in the database

SELECT * FROM fn_my_permissions('testTable', 'OBJECT') where permission_name = 'insert' ORDER BY subentity_name, permission_name;
go -- ! and we still have insert permission on the table

insert into CLRTEST.dbo.testTable values (2);
go -- ! but insert fails, with message below

-- Produces message:
-- Msg 3906, Level 16, State 1, Line 22
-- Failed to update database "CLRTEST" because the database is read-only.

alter database CLRTEST set read_write with no_wait;
go -- we set database to read-write

SELECT name, is_read_only FROM sys.databases where name = 'CLRTEST';
go -- value 0 shows database is read-write

SELECT * FROM fn_my_permissions(null, 'DATABASE') where permission_name = 'insert' ORDER BY subentity_name, permission_name ;  
go -- permissions same as all the way through this test

SELECT * FROM fn_my_permissions('testTable', 'OBJECT') where permission_name = 'insert' ORDER BY subentity_name, permission_name;
go -- permissions same as all the way through this test

insert into CLRTEST.dbo.testTable values (3);
go -- and we can insert again, as expected

use master;
go

drop database CLRTEST;
go