How to install transactional replication when publisher is 2022, distributor is 2022, and subscriber is 2019.

SQL Server 2022 Publisher and Distributor with a SQL Server 2019 transactional Subscriber is supported; configure the Distributor AG first, redirect the Publisher through its AG listener, and create the Subscriber AG subscription by script using its listener. ^1^

Before production changes, back up the participating databases and replication configuration and test the procedure outside production. The operator needs appropriate SQL Server replication/sysadmin permissions, AG/WSFC administration rights, and permission to create and secure the snapshot share.

1. Correct the service-account prerequisite

Different service and Agent accounts between the Publisher, Distributor, and Subscriber systems are acceptable. However, every replica within the Distributor AG must run SQL Server Agent under the same domain account, with identical privileges on each node. Any replication proxy account must also exist with identical privileges on every Distributor replica. ^2^

Therefore, if Distributor replica nodes currently use different Agent accounts, change them to one common domain account before configuring replication.

2. Configure the new Distributor AG

A distribution database cannot be moved into an AG after replication is configured; this must be a new replication configuration. The Publisher and Distributor must be separate SQL Server instances. Configure through scripts, not the Configure Distribution Wizard. ^2^

On every Distributor replica, use the physical instance name and the same password:

USE master;
EXEC sys.sp_adddistributor
    @distributor = @@SERVERNAME,
    @password = N'<same-strong-password-on-every-replica>';

On the intended initial primary:

USE master;
EXEC sys.sp_adddistributiondb @database = N'distribution';
ALTER DATABASE distribution SET RECOVERY FULL;

Take full and log backups, add distribution to an AG, preferably using synchronous replicas, and create a listener such as DISTLISTENER. Configure ongoing full and transaction-log backups. Secondaries should be readable when possible.

On every secondary Distributor replica after synchronization:

EXEC sys.sp_adddistributiondb @database = N'distribution';

Create a dedicated UNC snapshot share independent of all Distributor nodes. The Snapshot Agent needs write permission; the Distribution Agent needs read permission. Grant both share and NTFS permissions explicitly, even if an account is an administrator. Remote-share access requires a domain account. ^3^

3. Register every Publisher AG replica

On every Distributor AG replica, register each physical Publisher replica using the same distribution database and UNC working directory:

EXEC sys.sp_adddistpublisher
    @publisher = N'PUBNODE1',
    @distribution_db = N'distribution',
    @working_directory = N'\\FileServer\ReplSnapshot';

EXEC sys.sp_adddistpublisher
    @publisher = N'PUBNODE2',
    @distribution_db = N'distribution',
    @working_directory = N'\\FileServer\ReplSnapshot';

If a Distributor secondary is not readable, fail over so that it becomes primary, run the commands, and then continue. On each Publisher replica, register the Distributor listener using the same password:

EXEC sys.sp_adddistributor
    @distributor = N'DISTLISTENER',
    @password = N'<same-strong-password>';

Verify replication is installed on every Publisher replica:

DECLARE @installed int;
EXEC @installed = sys.sp_MS_replication_installed;
SELECT @installed AS ReplicationInstalled;

A result of 0 means the Replication feature must be added to that SQL Server installation. ^4^

4. Create and redirect the publication

On the original Publisher primary, enable the database, then create the transactional publication, articles, Snapshot Agent, and initial snapshot using your standard transactional-replication script. Replace all placeholders:

USE master;
EXEC sys.sp_replicationdboption
    @dbname = N'<PublishedDB>',
    @optname = N'publish',
    @value = N'true';

At the Distributor, redirect the physical instance that originally published the database to the Publisher AG listener:

USE distribution;
EXEC sys.sp_redirect_publisher
    @original_publisher = N'PUBNODE1',
    @publisher_db = N'<PublishedDB>',
    @redirected_publisher = N'PUBLISTENER';

After Publisher failover, replication agents reconnect through that listener when they restart. ^5^

Add the Subscriber listener as a linked server where required:

• Every Publisher AG replica, for push-subscriber metadata.
• Every Distributor AG replica, so subscriber connectivity survives Distributor failover.
• Every Subscriber AG node, as required by the Subscriber AG procedure.

5. Create the Subscriber AG subscription by script

First add the SQL Server 2019 destination database to its AG. For a push subscription, run at the Publisher database:

EXEC sp_addsubscription
 @publication=N'<Publication>', @subscriber=N'SUBLISTENER',
 @destination_db=N'<SubscriberDB>', @subscription_type=N'Push',
 @sync_type=N'automatic', @article=N'all',
 @update_mode=N'read only', @subscriber_type=0;

EXEC sp_addpushsubscription_agent
 @publication=N'<Publication>', @subscriber=N'SUBLISTENER',
 @subscriber_db=N'<SubscriberDB>',
 @subscriber_security_mode=1;

SSMS cannot create an AG-aware Subscriber subscription; the listener-based subscription must be scripted. Push subscriptions continue automatically after Subscriber failover when created using the listener. Pull subscriptions require an agent job on the new primary after failover; install replication components on Subscriber replicas when they host pull agents. ^6^

For Windows authentication, create the Distribution Agent process account as a login at the Subscriber and grant it the required access to the destination database; the documented setup uses db_owner. The account also needs read access to the snapshot share. If agents run away from the Distributor and use Windows authentication to reach the Publisher primary, configure Kerberos; alternatively, configure valid SQL logins on every Publisher replica. ^4^

For a non-default listener port, create the appropriate SQL client alias for the listener and port on the Publisher, Distributor, and Subscriber servers, then use the alias as the Distributor or Subscriber name.

6. Validate failover readiness

Run at the Distributor:

USE distribution;
DECLARE @redirected_publisher sysname;

EXEC sys.sp_validate_replica_hosts_as_publishers
 @original_publisher=N'PUBNODE1',
 @publisher_db=N'<PublishedDB>',
 @redirected_publisher=@redirected_publisher OUTPUT;

Error 976 is expected when a Publisher secondary disallows reads or requires read intent. In that case, verify the Subscriber linked-server entries directly on each affected Publisher replica. Then generate the snapshot, initialize the subscription, and test Publisher, Distributor, and Subscriber failovers separately while confirming the Log Reader and Distribution Agent jobs resume and rows continue to arrive.

References

1. Replication backward compatibility
2. Set up replication distribution database in Always On availability group
3. Secure the Snapshot Folder
4. Configure replication with Always On availability groups
5. sys.sp_redirect_publisher (Transact-SQL)
6. Replication subscribers and Always On availability groups (SQL Server)