This website use cookies to help you have a superior and more admissible browsing experience on the website.
Loading...
By:
Microsoft SQL Server Always On Availability Groups (AG) provide high availability and disaster recovery for mission-critical databases. At the same time, SQL Server Replication supports data distribution, reporting offloading, incremental synchronization, and cross-server data consolidation.
In most enterprise environments, these two technologies are used together to build resilient data pipelines that continue operating even after a replica failover—without disrupting downstream subscribers.
However, database administrators often encounter challenges due to native limitations between Availability Groups and replication. By default, databases hosted in AGs cannot be directly configured as replication publishers. Without proper configuration, replication can break entirely after an automatic or manual failover.
This guide walks through configuring replication with Always On Availability Groups. It includes required prerequisites, detailed steps and common runtime issues,
Before we get started, please know the Always On AG and SQL Server replication supported scenario and limitation:
Supported Replication Types:
Unsupported & Incompatible Replication Scenarios:
Microsoft explicitly restricts the following replication topologies with AG-hosted databases:
Key Architectural Rules:
If the Aways On AG can meet your requirements, we will also introduce an alternative for easier and more powerful replication solution in this article.
Before you begin setting up, please ensure you satisfy all infrastructure, edition, and configuration prerequisites.
1. You require SQL Server Enterprise Edition across all Availability Group replica nodes. Basic AG and replication integration is fully supported starting with SQL Server 2016. Distribution database high availability via Availability Groups is supported in SQL Server 2016 SP2 CU3, SQL Server 2017 CU6, and all later releases.
2. Your AG needs to be fully already configured with healthy primary and secondary replicas, synchronized user databases, and no unresolved AG health warnings.
3. The SQL Server Replication feature needs to be installed on every node in the Availability Group cluster, not only the primary replica. Replication agents and metadata require consistent binaries across all potential failover publishers.
4. A standalone remote SQL Server Distributor is highly recommended for production. Local distributors are supported but eliminate HA separation and increase complexity. The distributor manages all replication metadata, agent jobs, and transaction log delivery.
5. A DNS-named Availability Group Listener is a must for this integration. The listener provides a static network endpoint that remains unchanged during AG failover, which powers transparent replication publisher redirection.
6. Security & Network Permissions need to be configured correctly.
7. AG user databases need to be in the FULL recovery model, with transaction log backups configured to support transactional replication log reading.
Follow these steps below in your SQL Server environment.
The Distributor is the heart of replication. It stores the distribution database that holds metadata and transaction commands. This is how to set up replication distributor and distribution database
► Option A: Standalone Distributor (All Supported Versions)
1. Connect to the Distributor instance in SQL Server Management Studio (SSMS).
2. Right-click Replication and select Configure Distribution.
3. Follow the wizard to set the instance as its own Distributor, specify a snapshot folder, and create the distribution database.
4. Alternatively, use T-SQL:
USE master;
GO
EXEC sp_adddistributor @distributor = @@SERVERNAME, @password = N'StrongPassword!';
GO
EXEC sp_adddistributiondb @database = N'distribution';
GO
► Option B: Distribution Database in an Availability Group (SQL Server 2016 SP2-CU3+ / 2017 CU6+)
If you require HA for the Distributor, you can place the distribution database in its own AG. This configuration requires careful setup, but the key requirement is that the Publisher and Distributor need to be on separate SQL Server instances. The distribution database AG need to have its own listener.
With the Distributor ready, configure the database for publication on the current primary replica.
1. Connect to the primary replicainstance.
2. Enable the database for transactional replication:
USE [YourDatabaseName];
GO
EXEC sp_replicationdboption
@dbname = N'YourDatabaseName',
@optname = N'publish',
@value = N'true';
GO
3. Create the publication. Important: At this stage, you still use the physical server name of the primary replica, not the AG Listener. The redirection to the listener happens in a later step.
USE [YourDatabaseName];
GO
EXEC sp_addpublication
@publication = N'YourPublicationName',
@description = N'Transactional publication of database.',
@sync_method = N'concurrent',
@retention = 336,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@repl_freq = N'continuous',
@status = N'active',
@independent_agent = N'true',
@immediate_sync = N'true',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@allow_queued_tran = N'false',
@allow_dts = N'false',
@replicate_ddl = 1,
@allow_initialize_from_backup = N'true',
@enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false';
GO
4. Add articles (tables, views, etc.) to the publication:
USE [YourDatabaseName];
GO
EXEC sp_addarticle
@publication = N'YourPublicationName',
@article = N'YourTableName',
@source_owner = N'dbo',
@source_object = N'YourTableName',
@type = N'logbased',
@description = NULL,
@creation_script = NULL,
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'none',
@destination_table = N'YourTableName',
@destination_owner = N'dbo',
@status = 24;
GO
— Repeat for each table you wish to replicate
Every secondary replica that could become the primary after a failover must be properly configured to host the publication.
On each secondary replica host, perform the following:
1. Add the Distributor(if not already present):
USE master;
GO
EXEC sp_adddistributor
@distributor = N'YourDistributorServerName',
@password = N'StrongPassword!';
GO
2. Register each secondary replica with the Distributor. Run this on the Distributorinstance:
USE distribution;
GO
EXEC sp_adddistpublisher
@publisher = N'SecondaryReplicaHostName', -- Physical server name of the secondary replica
@distribution_db = N'distribution',
@working_directory = N'\\NetworkShare\ReplData',
@security_mode = 1, -- 1 = Windows Authentication
@trusted = N'false',
@thirdparty_flag = 0,
@publisher_type = N'MSSQLSERVER';
GO
— Repeat for each secondary replica that will be a potential publisher
3. Add the subscriber as a linked serveron each secondary replica (required for push subscriptions):
— Run on each secondary replica
EXEC sp_addlinkedserver
@server = N'SubscriberServerName',
@srvproduct = N'SQL Server';
GO
This step completes the configuration on the secondary replicas so that they are fully ready to act as publishers.
On each secondary replica host, execute:
-- This step registers the publication on the secondary
USE [YourDatabaseName];
GO
EXEC sp_replicationdboption
@dbname = N'YourDatabaseName',
@optname = N'publish',
@value = N'true';
GO
Note: Do not attempt to re-create the publication on the secondary. The publication already exists in the database and will be recognized when the database becomes primary.
This is the critical step that enables seamless failover. It tells the Distributor that when it cannot reach the original publisher, it should connect to the AG Listener instead.
Run this on the Distributor instance, in the distribution database:
USE distribution;
GO
EXEC sys.sp_redirect_publisher
@original_publisher = N'PhysicalPrimaryServerName', -- The original primary's hostname
@publisher_db = N'YourDatabaseName',
@redirected_publisher = N'YourAGListenerName'; -- Can include port if non-default, e.g., 'Listener,2233'
GO
Verify the redirection:
USE distribution;
GO
SELECT * FROM MSredirected_publishers;
GO
Before creating subscriptions, Microsoft recommends validating that all replicas are properly configured to serve as publishers.
Run this validation on the Distributor:
USE distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
@original_publisher = N'PhysicalPrimaryServerName',
@publisher_db = N'YourDatabaseName',
@redirected_publisher = @redirected_publisher OUTPUT;
GO
If the validation returns errors, review the previous steps to ensure all secondary replicas have been correctly registered with the Distributor and have the publication database marked for publishing.
Now create the subscription. And you will need to use T-SQL scripts; the SSMS wizard does not properly handle AG listener scenarios.
►Option A: Push Subscription (Recommended for AG Subscribers)
Create the push subscription at the Publisher using the AG Listener name of the subscriber (if the subscriber database is in an AG) or the physical server name (if not).
-- Run on the publisher (primary replica)
GO
-- Add the subscription
EXEC sp_addsubscription
@publication = N'YourPublicationName',
@subscriber = N'SubscriberAGListenerName', -- Use listener if subscriber is in AG
@destination_db = N'SubscriberDatabaseName',
@subscription_type = N'Push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0;
GO
-- Add the Distribution Agent job
EXEC sp_addpushsubscription_agent
@publication = N'YourPublicationName',
@subscriber = N'SubscriberAGListenerName',
@subscriber_db = N'SubscriberDatabaseName',
@job_login = N'DOMAIN\ReplAgentAccount',
@publication = N'YourPublicationName',
@job_password = N'StrongPassword!',
@subscriber_security_mode = 1, -- 1 = Windows Authentication
@frequency_type = 64, -- 64 = Continuously
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20240101,
@active_end_date = 99991231,
@dts_package_location = N'Distributor';
GO
►Option B: Pull Subscription
Pull subscriptions are created at the Subscriber. Use the AG Listener name for the publisher.
-- Run on the subscriber
USE [SubscriberDatabaseName];
GO
-- Add the pull subscription
EXEC sp_addpullsubscription
@publisher = N'YourAGListenerName', -- Publisher AG Listener
@publication = N'YourPublicationName',
@publisher_db = N'YourDatabaseName',
@independent_agent = N'True';
GO
-- Add the Distribution Agent job on the subscriber
EXEC sp_addpullsubscription_agent
@publisher = N'YourAGListenerName',
@publisher_db = N'YourDatabaseName',
@publication = N'YourPublicationName',
@distributor = N'YourDistributorServerName',
@distributor_security_mode = 1,
@distributor_login = N'',
@distributor_password = N'',
@enabled_for_syncmgr = N'False',
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20240101,
@active_end_date = 99991231,
@alt_snapshot_folder = N'',
@working_directory = N'';
GO
When you create a pull subscription and the subscriber database is in an AG, the distribution agent job is created only on the current primary replica of the subscriber AG. After a failover of the subscriber AG, replication will continue automatically if the original subscriber server remains operational (the job still runs there).
However, for long-term stability, you should eventually disable the job on the old primary and enable/create it on the new primary. See the “Post-Failover Management” section for details.
Generate the initial snapshot to synchronize the subscriber.
-- Run on the publisher (primary replica)
USE [YourDatabaseName];
GO
EXEC sp_startpublication_snapshot @publication = N'YourPublicationName';
GO
Monitor the snapshot generation and delivery in Replication Monitor or by querying MSsnapshot_history in the distribution database.
For easier monitoring, add the original publisher to Replication Monitor:
1. In SSMS, connect to the Distributor instance.
2. Expand Replication, right-click Replication Monitor, and select Add Publisher.
3. Add the original physical server name of the publisher. Replication Monitor will automatically detect the redirected publisher and display the correct information.
Note: After a failover, Replication Monitor may continue to show the original primary name, but replication functionality is unaffected.
While Always On Availability Groups offer high availability, integrating availability groups with replication technologies is a complex operation. If your actual requirements involve achieving read-write separation, visual reporting, or heterogeneous data integration through data distribution, consider a professional database replication solution.
i2Stream provides an enterprise-grade and professional alternative solution that addresses the limitations organizations face when trying to extend Always On AGs beyond pure HA scenarios. No Enterprise edition is required, and it does not rely on Windows Cluster.
Main features and advantages of i2Stream:
You can click the free trial button below. And we will contact you soon and help you to deploy it on your environemnt and guide you how to use it.
Symptoms: Log Reader Agent or Distribution Agent fails with connection errors after an AG failover.
Common causes:
Resolution:
Symptoms: Transactions fail to replicate, with errors about uncommitted transactions.
Common causes: The distribution database is not properly synchronized, or there are conflicts during AG synchronization.
Resolution:
Symptoms: Snapshot Agent reports “Could not retrieve agent status” or similar errors when the database is in an AG.
Common causes: The database was added to the AG before replication was fully configured, or Contained AG is being used (SQL Server 2022).
Resolution:
Symptoms: After failover, the Log Reader Agent continues attempting to connect to the former primary.
Common causes: sp_redirect_publisher was executed on the wrong database or with incorrect parameters.
Resolution:
1. On the Distributor, in the distribution database, verify the redirection:
SELECT * FROM MSredirected_publishers;
2. If incorrect, drop and recreate the redirection:
EXEC sys.sp_redirect_publisher
@original_publisher = N'PhysicalServerName',
@publisher_db = N'YourDatabase',
@redirected_publisher = N'AGListenerName';
3. Restart the Log Reader Agent job
Symptoms: After failover, Replication Monitor continues to display replication information under the name of the original primary instance.
Explanation: This is a known behavior. Replication Monitor is unable to adjust the name of the publishing instance after failover and continues to display the original primary name.
Resolution: This is cosmetic only; replication functionality is not affected. Tracer tokens entered on the new publisher using T-SQL will be visible in Replication Monitor.
Configuring replication with Always On Availability Groups transforms your SQL Server infrastructure from a simple HA solution into a comprehensive data distribution platform with built-in resiliency.
But if you want an easier and enterprise-grade database replication solution, i2Stream is a better choice. It provides non-intrusive CDC and agentless architecture to continuously replicate data between different database platforms.
