how to receive transaction records in AOAG setup

Question

how to receive transaction records in AOAG setup

SQL Galaxy

One Orange Chip

Points: 29472
More actions
September 23, 2022 at 4:00 am

#4091025

Hi.
AlwaysOn setup can able to make it replication subscriber and distributor by using AG Listener?
Does AG Listener receive transactional data from publisher server? so whenever failover happen can AOAG setup subscriber should be receive data from publisher? if possible please share the reference link for configure publisher and AOAG subscriber, distributor .

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-ver16

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Hi Ant-Green, Thanks for mentioned URL for how replication configured in AOAG setup and send transactional records to another server. but my requirements is receiving records in AOAG from another standalone server.

standalone server act as publisher role and AOAG in subscriber role. will it possible do this setup?

thanks

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

Yes is possible.

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replication-subscribers-and-always-on-availability-groups-sql-server?view=sql-server-ver16

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Hi.

Successfully configured Publisher as a standalone server and Published database too.

Server1 - publisher database has been synchronized to AO availability group setup as a subscriber Server1 and Server2.

As below commands are executed successfully in Server1 as publisher as well as distributor.

--commands to execute at the publisher Server1, in the publisher database:  
use RUMS
GO
EXEC sp_addsubscription @publication = N'Pub_TB002',   
       @subscriber = N'BSQLST02', --AOAG_Listener name   
       @destination_db = N'RMS',   
       @subscription_type = N'Push',   
       @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0;  
GO  
EXEC sp_addpushsubscription_agent @publication = N'Pub_TB002',   
       @subscriber = N'BSQLST02', --AOAG_Listername   
       @subscriber_db = N'RMS',   
       @job_login = null, @job_password = null, @subscriber_security_mode = 1;  
GO

Dynamic Link server BSQLST02 AOAG_ListenerName has been created in Publisher server also validated and test connection succeeded.

USE distribution;  
GO  
EXEC sp_redirect_publisher   
@original_publisher = 'Server1', --Publisher Server1 
@publisher_db = 'RMS',  
@redirected_publisher = 'BSQLST02';

--results
Commands completed successfully.

Finally validation re-direct throwing errors. So any steps are missing my side. please suggest for fixing this errors. also AOAG failover primary server1 role changed to secondary role. Server2 Primary role -

An error occurred while attempting to access the subscription. (View Synchronization Status)

------------------------------

The subscription does not exist. (View Synchronization Status)

USE distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
@original_publisher = 'Server1', --Publisher Server role
@publisher_db = 'RMS',
@redirected_publisher = @redirected_publisher output;
SELECT @redirected_publisher

Msg 21889, Level 16, State 1, Procedure sys.sp_hadr_verify_replication_publisher, Line 77 [Batch Start Line 2]
The SQL Server instance 'Server2' is not a replication publisher. Run sp_adddistributor on SQL Server instance 'Server2' with distributor 'Server1' in order to enable the instance to host the publishing database 'RMS'. Make certain to specify the same login and password as that used for the original publisher.
OLE DB provider "SQLNCLI11" for linked server "[5E0D3E1C-BD30-45E2-B89A-5929B03C0289]" returned message "Deferred prepare could not be completed.".
Msg 21899, Level 11, State 1, Procedure sys.sp_hadr_verify_subscribers_at_publisher, Line 109 [Batch Start Line 2]
The query at the redirected publisher 'Server2' to determine whether there were sysserver entries for the subscribers of the original publisher 'Server1' failed with error '208', error message 'Error 208, Level 16, State 1, Message: Invalid object name 'RMS.dbo.syssubscriptions'.'.
One or more publisher validation errors were encountered for replica host 'Server2'.
Msg 21889, Level 16, State 1, Procedure sys.sp_hadr_verify_replication_publisher, Line 77 [Batch Start Line 2]
The SQL Server instance 'Server3' is not a replication publisher. Run sp_adddistributor on SQL Server instance 'Server3' with distributor 'Server1' in order to enable the instance to host the publishing database 'RUMS'. Make certain to specify the same login and password as that used for the original publisher.
OLE DB provider "SQLNCLI11" for linked server "[5E0D3E1C-BD30-45E2-B89A-5929B03C0289]" returned message "Deferred prepare could not be completed.".
Msg 21899, Level 11, State 1, Procedure sys.sp_hadr_verify_subscribers_at_publisher, Line 109 [Batch Start Line 2]
The query at the redirected publisher 'Server3' to determine whether there were sysserver entries for the subscribers of the original publisher 'Server1' failed with error '208', error message 'Error 208, Level 16, State 1, Message: Invalid object name 'RMS.dbo.syssubscriptions'.'.
One or more publisher validation errors were encountered for replica host 'Server3'.

This reply was modified 2 years, 2 months ago by SQL Galaxy.