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!
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.'.