如何在SQL Server 2016 Standard Edition下为Always On Availability组配置复制。 [英] How to Configure Replication for Always On Availability Groups under SQL Server 2016 Standard Edition.
问题描述
我想在数据库上设置事务复制，该数据库是基本AlwaysOn可用性组的一部分，没有可读的辅助数据。
服务器1：AlwaysOn可用性组主副本和事务复制发布者
服务器2：AlwaysOn可用性组主副本和事务复制发布者
服务器3 ：复制订阅者和发布者
我收到了错误消息 （如下所示）当我在分发数据库中执行sys.sp_validate_replica_hosts_as_publishers时：
消息21899，级别11，状态1，过程sp_hadr_verify_subscribers_at_publisher，行109 [批处理开始行2]
重定向发布者'Server 1'的查询，以确定原始发布者'Server 1'的订阅者是否有sysserver条目失败，错误'976'，错误消息'错误976，级别14，状态1，消息：目标数据库'xxx'，
正在参与可用性组，并且当前无法查询。数据移动暂停或可用副本未启用读取访问权限。要允许对可用性组中的此数据库和其他数据库进行只读访问，
将启用对组中一个或多个辅助可用性副本的读取权限。 有关详细信息，请参阅SQL Server联机丛书中的ALTER AVAILABILITY GROUP语句。'。$
副本主机'Server 1'遇到一个或多个发布者验证错误。
由于SQL Server版本的限制，可读的辅助设备无法设置为只读或预期读取。
是否可以在此方案中为Always On Availability Groups配置复制以及如何设置。
您好winter007，
根据错误消息，您看起来正在辅助节点上运行sys.sp_validate_replica_hosts_as_publishers。请参阅此博客以配置永远在线的复制： https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/30/setting-up-replication-on-a-数据库 - 属于everon-availability-group /
这篇文章介绍了使用标准版在Always-on上创建事务复制： https://blog.sqlauthority.com/2015/12/17/sql-server-how-to-create-a-readable-secondary-server-in-sql-server -standard-notes-from-the-field-107 /
B est问候，
Teige
I would like to setting up Transactional replication on a database which is part of basic AlwaysOn Availability Group with No readable secondary
Server 1 : AlwaysOn Availability Group Primary replica and transactional replication publisher
Server 2 : AlwaysOn Availability Group Primary replica and transactional replication publisher
Server 3 : replication subscriber and distributor
I received the error message (as below) when I EXEC sys.sp_validate_replica_hosts_as_publishers at distribution database:
Msg 21899, Level 11, State 1, Procedure sp_hadr_verify_subscribers_at_publisher, Line 109 [Batch Start Line 2]
The query at the redirected publisher 'Server 1' to determine whether there were sysserver entries for the subscribers of the original publisher 'Server 1' failed with error '976', error message 'Error 976, Level 14, State 1, Message: The target database, 'xxx', 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.'.
One or more publisher validation errors were encountered for replica host 'Server 1'.
Due to SQL Server edition limitation, readable secondary is not able to set to read-only or intended read.
Is it possible to Configure Replication for Always On Availability Groups in this scenario and how to setup.解决方案Hi winter007,
Based on the error message, it looks like that you are running sys.sp_validate_replica_hosts_as_publishers on the secondary node. Please refer to this blog to configure replication on Always-on: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/30/setting-up-replication-on-a-database-that-is-part-of-an-alwayson-availability-group/
Here is an article which described creating transactional replication on Always-on with Standard edition: https://blog.sqlauthority.com/2015/12/17/sql-server-how-to-create-a-readable-secondary-server-in-sql-server-standard-notes-from-the-field-107/
Best Regards,
Teige
这篇关于如何在SQL Server 2016 Standard Edition下为Always On Availability组配置复制。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
