我们有 3 节点 SQL Server 集群(Always On 可用性组)。
我想在配置为异步的第三个节点上实现事务复制。
当我尝试创建 Publisher 时,最初出现错误:
This database is not enabled for publication.
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
Changed database context to 'prod'. (.Net SqlClient Data Provider)
然后我尝试使用以下命令添加用于发布的数据库。
USE prod
EXEC sp_replicationdboption @dbname = 'prod',
@optname = 'publish',
@value = 'true'
GO
我收到另一个错误:
Msg 3906, Level 16, State 2, Procedure sys.sp_MSdrop_pub_tables, Line 6 [Batch Start Line 0]
Failed to update database "prod" because the database is read-only.
上述错误令人惊讶,因为我可以在数据库选项中看到“只读”选项为“假”。
然后我尝试使用以下命令修复更新问题:
USE prod GO
ALTER DATABASE [prod] SET READ_WRITE WITH NO_WAIT
GO
现在我面临错误:
Msg 1468, Level 16, State 3, Line 9
The operation cannot be performed on database "prod" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 9
ALTER DATABASE statement failed.
我尝试在节点 2 上进行“prod”数据库发布,但出现以下错误:
Msg 976, Level 14, State 1, Line 1
The target database, 'prod', is participating in an availability group and is currently not accessible for queries.
Either data movement is suspended or the availability replica is not enabled for read access.
To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.
For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.
为了在节点 3 或节点 2 上启用事务复制,我必须执行哪些步骤或配置?
我在这里做错了什么或错过了什么?
您无法在只读副本上创建发布。您只能在可读写的主设备上执行此操作。