主页 / dba / 问题 / 249123
Accepted
youcantryreachingme
Asked: 2019-09-19 21:32:12 +0800 CST

辅助副本上的可用性组数据库是否由 AG 自动设置为只读？

  • 772

我已经阅读了有关只读路由的 MS 文档，但我不是在谈论这个。

目前，如果我UPDATE对位于辅助副本上且属于可用性组的数据库执行操作，我会收到一条消息：

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

我的问题是，由于数据库是 AG 的一部分并且目前在辅助节点上，这种配置是否是默认建立的（并且是不可避免的）？

一些有趣的额外内容：

以下 SQL 在该辅助节点上运行，显示 I haveUPDATE以及ALTER对该数据库的更多权限：

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

以下 SQL 明确显示我有权访问UPDATE该数据库中的表：

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

尽管如此，我实际上被阻止更新该表。这意味着：

  • 的输出fn_my_permissions不严格正确
  • 数据库“只读”设置覆盖单个权限

我了解游戏中的设置是通过以下方式配置的：

ALTER DATABASE dbName SET READ_ONLY

和

ALTER DATABASE dbName SET READ_WRITE WITH NO_WAIT

该问题的主要原因是要了解“只读”配置是 DBA/设计人员在 AG 的设计和部署期间的责任，还是使用 AG 的预期（和保证？）功能。

availability-groups sql-server-2016

2 个回答

  1. Best Answer

    我会给文档一个很好的阅读。特别是关于可用性副本的部分。

    每个可用性副本都分配有一个初始角色 - 主要角色或辅助角色，该角色由该副本的可用性数据库继承。给定副本的角色决定了它是托管读写数据库还是只读数据库。一个副本（称为主副本）被分配为主角色并托管读写数据库，这些数据库称为主数据库。至少一个其他副本（称为次要副本）被分配次要角色。辅助副本托管只读数据库，称为辅助数据库。

    如上所示，每个副本可以是只读的或读写的（分别是辅助或主）。此外，只能有一个主（读写）副本。当我们在这方面提到副本时，我们指的是数据库级别。每个服务器可以托管多个可用性组，可能具有混合的主从配置。

    AlwaysOn 可用性组不支持您提到的具有多个读写副本的情况，AlwaysOn 可用性组旨在满足高可用性和读取扩展需求，而不是多主设置。您应该考虑支持这种需求的双向复制技术。

    is_read_only至于只读设置本身，这是由可用性组自动设置的，尽管值得一提的是，这与数据库属性（或 ）中的“数据库只读”设置不同sys.databases，也不是它与文件组级别只读选项的设置相同。

    • 2

  2. 我接受了 George 的回答，因为他直接向我指出辅助节点上的状态 AG 数据库将是只读的文档（即通过 MS 的 AG 系统设计）。

    然而，在我谈到的问题的最后一个细节中ALTER DATABASE dbName SET READ_ONLY，George 澄清说，这与 AG 将辅助节点的角色设置为只读以及该辅助节点上该 AG 中的所有 DB 的含义不同。

    我对此进行了进一步调查，发现您可以确定由于数据库是辅助节点上的 AG 的一部分而导致更新失败与数据库的只读属性（使用上述 SQL 设置）之间的区别。

    下面是一个演示如何set read_only和set read_write影响的脚本sys.databases.is_read_only- 但更具体地说，它表明如果您尝试更新受此属性保护的数据库，而不是它位于 AG 中的辅助节点上，则会收到一个非常不同的错误消息.

    以下 SQL 创建了一个新数据库，该数据库将自动进行读写，并且不会成为任何 AG 的一部分，即使是在某个 AG 中充当辅助节点的副本上创建的。

    请注意在数据库设置为时尝试更新表时给出错误消息的注释read_only- 特别是以下部分：

    • 状态 1

    这与原始帖子问题中的错误不同，后者显示

    • 状态 2

    其他地方的一篇文章引用MS Books Online 来阐明此值的目的：

    最大值为 127 的一到三位数字，向 Microsoft 支持工程师和开发人员指示生成消息的 SQL Server 代码中的位置

    换句话说，错误“无法更新数据库“X”，因为数据库是只读的”可能源于至少两个不同的条件：状态 1 = 数据库属性设置为read_only（在 中可见sys.databases.is_read_only），状态 2 = 的一部分AG 和当前充当辅助节点的副本。

    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
    • 2

相关问题