question

stevencf avatar image
stevencf asked

SQL 2016 Availability Group Replication issues: Distributor (named instance) and Publisher (default instance)

Following the documentation provided (Configure replication with availability groups - SQL Server Always On | Microsoft Docs) I set up an AG-based Distributor (REPLAG) and an AG-based Publisher (PUBAG). I only have two servers to work with so REPLAG involves named instances SRV1\REPLDIST and SRV2\REPLDIST and the PUBAG involves the default instances SRV1 and SRV2.

For REPLAG the named instance and AG Listener are all set up to use port 54321. Everything seems to be OK - it even accepts the "sp_redirect_publisher" call to go from Original Publisher SRV2 to PUBAG without any error. When I run "sp_validate_replica_hosts_as_publishers" the value of @Redirected_publisher comes back as PUBAG (good thing) but I get the following errors:

Msg 21890, Level 16, State 1, Procedure sp_hadr_verify_replication_publisher, Line 95 [Batch Start Line 8] The SQL Server instance 'SRV1' with distributor 'REPLAG, 54321' and distribution database 'distribution' cannot be used with publisher database 'Test'. Reconfigure the publisher to make use of distributor 'SRV2\REPLDIST' and distribution database 'SRV2\REPLDIST. One or more publisher validation errors were encountered for replica host 'SRV1'.

Msg 21890, Level 16, State 1, Procedure sp_hadr_verify_replication_publisher, Line 95 [Batch Start Line 8] The SQL Server instance 'SRV2' with distributor 'REPLAG, 54321' and distribution database 'distribution' cannot be used with publisher database 'Test'. Reconfigure the publisher to make use of distributor 'SRV2\REPLDIST' and distribution database 'SRV2\REPLDIST. One or more publisher validation errors were encountered for replica host 'SRV2'.

It doesn't matter whether I execute the verify procedure in a query on the Listener REPLAG or its Primary Replica SRV2\REPLDIST...the error comes up. I've been battling this for a while and I'm pretty close to giving up on the AG-based Distributor and going with a standalone remote Dist. I tried to search on Error/Msg 21890 but there's nothing out there. Any and all feedback is greatly appreciated.

replicationavailability groups
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cmackin avatar image
cmackin answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

drc avatar image
drc answered

Got same error, shouldn't distribution on AG lay in named instance? any advice?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cmackin avatar image
cmackin answered

This is what I used to set it up, good luck!

/*--------------------------------------------------------------------------------------------------------- This is sourced from: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-2017 */--------------------------------------------------------------------------------------------------------- -- Configure distribution at the distributor USE master; GO EXEC sys.sp_adddistributor @distributor = '<YourServerHA1\I1>', -- SQL Instance that will act as Distributor @password = '<YourPassword>'; -- Create the distribution database at the distributor USE master; GO EXEC sys.sp_adddistributiondb @database = 'distribution', @data_folder = 'F:\SQL Data\', @data_file = 'distribution.mdf', @log_folder = 'L:\SQL Logs\', @log_file = 'distribution.ldf', @security_mode = 1; -- Configure the remote publisher /* Note If any modified replication agents run on a computer other than the distributor, use of Windows authentication for the connection to the primary will require Kerberos authentication to be configured for the communication between the replica host computers. Use of a SQL Server login for the connection to the current primary will not require Kerberos authentication. */ USE [master] GO CREATE LOGIN [PublisherUser] WITH PASSWORD=N'<YourPassword>', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON GO EXEC sp_help_revlogin PublisherUser GO USE master; GO EXEC sys.sp_adddistpublisher @publisher = '<YourServerHA1\I1>', @distribution_db = 'distribution', @working_directory = '\\YourServerHA1\SQL Replication Data\', @login = 'PublisherUser', @password = 'pA$pubW0rdsql17sKIERJ80jhsd'; ---------------------------------------------------------------------------------------------------------------------------- ------------------------------------------- APPEARS Redundant and not working ---------------------------------------------- ------ Configure remote distribution ----exec sys.sp_adddistributor ---- @distributor = '<YourServerHA1\I1>', ---- @password = '<YourPassword>'; ---------------------------------------------------------------------------------------------------------------------------- -- Enable the database for replication USE master; GO EXEC sys.sp_replicationdboption @dbname = '<DatabaseForReplication>', @optname = 'publish', @value = 'true'; GO EXEC sys.sp_replicationdboption @dbname = '<OtherDatabaseForReplication>', @optname = 'publish', @value = 'true'; GO ---------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- -- RUN ON THE PRIMARY REPLICA -- -- At the distributor, configure distribution for each secondary replica host EXEC sys.sp_adddistpublisher @publisher = '<YourServerHA2\I2>', @distribution_db = 'distribution', @working_directory = '\\YourServerHA1\SQL Replication Data\', @login = 'PublisherUser', @password = '<YourPassword>'; ---------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- -- RUN ON THE SECONDARY REPLICA -- -- At each secondary replica host, configure distribution USE master; GO EXEC sys.sp_adddistributor @distributor = '<YourServerHA1\I1>', @password = '<YourPassword>'; GO -- At each secondary replica host, make sure that the push subscribers of the database publications appear as linked servers EXEC sys.sp_addlinkedserver @server = '<YourServerHA1\I1>'; GO -- RUN ON THE PRIMARY REPLICA -- -- Redirect the Original Publisher to the AG Listener Name USE [distribution]; GO EXEC sys.sp_redirect_publisher @original_publisher = '<YourServerHA1\I1>', @publisher_db = '<DatabaseForReplication>', @redirected_publisher = '<YourServer-AG Listener>'; GO EXEC sys.sp_redirect_publisher @original_publisher = '<YourServerHA1\I1>', @publisher_db = '<DatabaseForReplication>', @redirected_publisher = 'YourServer'; GO -- verify that all replica hosts are now configured USE [distribution]; GO DECLARE @redirected_publisher sysname; EXEC sys.sp_validate_replica_hosts_as_publishers @original_publisher = '<YourServerHA1\I1>', @publisher_db = '<OtherDatabaseForReplication>', @redirected_publisher = @redirected_publisher output; EXEC sys.sp_validate_replica_hosts_as_publishers @original_publisher = '<YourServerHA1\I1>', @publisher_db = '<OtherDatabaseForReplication>', @redirected_publisher = @redirected_publisher output; /* Use the PublisherUser when setting up the initial Publication But you can use AM_SVC_mssql8 for the subscription info */

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.