Edit

Share via

Replication subscribers and Always On availability groups (SQL Server)

Applies to: SQL Server

When an Always On availability group (AG) fails over, containing a database that is a replication subscriber, the replication subscription might fail. For transactional replication push subscribers, the distribution agent will continue to replicate automatically after a failover if the subscription was created using the AG listener name. For transactional replication pull subscribers, the distribution agent will continue to replicate automatically after a failover, if the subscription was created using the AG listener name and the original subscriber server is up and running. This is because the distribution agent jobs only get created on the original subscriber (primary replica of the AG). For merge subscribers, a replication administrator must manually reconfigure the subscriber, by recreating the subscription.

What is supported

SQL Server replication supports the automatic failover of the publisher and the automatic failover of transactional subscribers. Merge subscribers can be part of an AG. However, manual actions are required to configure the new subscriber after a failover. AGs can't be combined with WebSync and SQL Server Compact scenarios.

Create a transactional subscription in an availability group

For transactional replication, use the following steps to configure and fail over a subscriber AG:

  1. Before creating the subscription, add the subscriber database to the appropriate AG.

  2. Add the subscriber's AG listener as a linked server to all nodes of the AG. This step ensures that all potential failover partners are aware of and can connect to the listener.

  3. Using the script in the Create a transactional replication push subscription section, create the subscription using the name of the AG listener of the subscriber. After a failover, the listener name always remains valid, whereas the actual server name of the subscriber depends on the actual node that became the new primary.

    Note

    You must use a Transact-SQL script to create the subscription. You can't use Management Studio.

  4. To create a pull subscription:

    1. Using the sample script in the Create a transactional replication pull subscription section, create the subscription using the name of the AG listener of the subscriber.

    2. After a failover, create the distribution agent job on the new primary replica using the sp_addpullsubscription_agent stored procedure.

When you create a pull subscription with the subscription database in an AG, after every failover, you should disable the distribution agent job on the old primary replica and enable the job on the new primary replica.

Create a transactional replication push subscription

-- commands to execute at the publisher, in the publisher database:
USE [<publisher database name>];
GO

EXEC sp_addsubscription @publication = N'<publication name>',
    @subscriber = N'<AG listener name>',
    @destination_db = N'<subscriber database name>',
    @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'<publication name>',
    @subscriber = N'<AG listener name>',
    @subscriber_db = N'<subscriber database name>',
    @job_login = NULL,
    @job_password = NULL,
    @subscriber_security_mode = 1;
GO

Create a transactional replication pull subscription

-- commands to execute at the subscriber, in the subscriber database:
USE [<subscriber database name>];
GO

EXEC sp_addpullsubscription @publisher = N'<publisher name>',
    @publisher_db = N'<publisher database name>',
    @publication = N'<publication name>',
    @subscription_type = N'pull';
GO

EXEC sp_addpullsubscription_agent @publisher = N'<publisher name>',
    @subscriber = N'<AG listener name or alias>',
    @distributor = N'<distributor AG listener name>', -- this parameter should only be used if the distribution database is part of an AG.
    @publisher_db = N'<publisher database name>',
    @publication = N'<publication name>',
    @job_login = NULL,
    @job_password = NULL,
    @subscriber_security_mode = 1;
GO

Availability group listener requirement

When you run sp_addpullsubscription_agent for a subscriber that's part of an AG, you must pass the @subscriber parameter value to the stored procedure as the AG listener name. If you run SQL Server 2016 (13.x) and earlier versions, or SQL Server 2017 (14.x) before CU 16, the stored procedure doesn't reference the AG listener name. It creates the subscription with the subscriber server name on which the command is executed. To resolve this issue, manually update the @subscriber parameter on the Replication Distribution Agent with the AG listener name value.

If the subscriber AG listener uses a non-default port, providing port number as part of the AG listener name in the @subscriber parameter isn't supported on Windows. As a workaround, you can create an alias for the listener and port on publisher, distributor, and subscriber server using Aliases (SQL Server Configuration Manager) or the SQL Server Client Network Utility tool (cliconfg) for SQL Server 2022 (16.x) and later versions, and pass the alias as the @subscriber parameter value.

Resume the merge agents after the availability group of the subscriber fails over

For merge replication, a replication administrator must manually reconfigure the subscriber with the following steps:

  1. Execute sp_subscription_cleanup to remove the old subscription for the subscriber. Perform this action on the new primary replica (which was formerly the secondary replica).

  2. Recreate the subscription by creating a new subscription, beginning with a new snapshot.

Note

This process is inconvenient for merge replication subscribers. However, the main scenario for merge replication is disconnected users (desktops, laptops, handset devices), which don't use AGs on the subscriber.

Related content

  • Last updated on