Sql-server – Replication with Always On availability groups

availability-groupsbasic-availability-groupssql-server-2016transactional-replication

I have a three server set up:

SQL01 – Primary server
SQL02 – Secondary server
SQL04 – Remote distribution server
SQL04 – Subscribe

SQL01 / SQL02 DB's are part of basic availability groups and i am trying to configure transaction replication of some of these databases to SQL04.

SQL04 is the remote distributor.

I have been following this Microsoft guide.

My issues comes at the validation of the replica hosts as publishers sproc ( the final step!)

USE Distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
    @original_publisher = 'SQL01',
    @publisher_db = 'DB',
    @redirected_publisher = 'AG-listener';

I receive the error stating read access is not enabled, the same error which is stated in the MS guide, but i cannot work out how to step over it!

error:

The query at the redirected publisher 'SQL02' to determine whether there were sysserver entries for the subscribers of the original publisher 'SQL01' failed with
error '976', error message 'Error 976, Level 14, State 1, Message:
The target database, 'db',
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.'.

Best Answer

The problem is that procedure only works if read access is enabled for the secondaries and, as per the documentation, Basic Availability Groups do not allow readable secondaries. You need to manually perform the checks that this procedure normally handles as per the doco:

This is expected behavior. You must verify the presence of the subscriber server entries at these secondary replica hosts by querying for the sysserver entries directly at the host.

Basically, connect to each of your replicas and run this query:

SELECT * FROM sysservers

Make sure that your replication subscribers (SQL04) are listed in sysservers on each AG replica. If they are then you're fine.

Related Question