Share via

Replication Enhancement – Distribution Database in Availability Group

  • Article

SQL Server replication uses the publisher, distributor, and subscriber paradigm to provide logical data replication capability between different SQL Server instances, and sometime with heterogeneous data source or data destination. Replication uses the distribution databases hosted on a distributor to centrally manage and control the configuration and operation of data replication. While the actual data to be replicated are within user databases, SQL Server replication uses objects and artifacts outside the user databases, such as master database, msdb database, distribution databases, and even file systems. 

SQL Server uses availability groups (AG) to provide physical data replication capability for a group of databases among a set of SQL Server instances. An availability group is self-contained such that all the databases to be replicated through the Availability Group are included as one unit. 

Many enterprise customers have asked the capability to combine the usage of SQL Server replication and Always On, such that they can place replication distribution databases within an Always On AG to achieve high availability for their distribution databases, with the expectation that after doing so and when AG failover happens, SQL Server replication will continue functioning seamlessly and correctly. While the Replication publication and subscription databases can be configured to use Availability Groups, this support was lacking for the replication Distribution Databases.

SQL Server engineering team is excited to announce the new enhancement around the configuration of Replication Distribution Database in an Availability Group. This feature enhancement would be available with SQL Server 2017 CU6 and will be ported to SQL Server 2016 in a subsequent CU for SP2.

The improvement can help solve the following customer scenarios

  1. High-Availability for Replication Distribution database - The distribution database is the heart of the replication topology. The loss of the distribution database means that the entire topology stops receiving changes. The distribution server can be protected by a SQL Server FCI instance, but this provides only local HA but not site DR. To achieve site DR, the current recommended solution is to use geographically dispersed Failover Cluster Instances with storage level replication using
    1. Either hardware-based SAN replication, which is prohibitively expensive for customers
    2. Or using Windows 2016 S2D software-based SAN replication. While this provides a low-cost solution, this feature is not present in earlier versions of Windows and hence does not work for SQL server versions like SQL Server 2012 and SQL server 2014.
  2. Minimize Upgrade/Migration Downtime – Since SQL Server Replication depends heavily on server names, upgrade/migration of SQL Server instances hosting the replication distribution database requires re-deployment/re-setup of the entire replication topology. This is both time-consuming and requires substantial downtime. The improvement would help minimize the downtime and complexity to upgrade SQL Servers in a replication topology.
  3. Standardize a single HADR solution across enterprise – Majority of our Tier-1 customers are standardizing SQL Server Availability Groups as the solution of choice for their HADR requirements, except for replication distribution database, for which SQL Server FCIs were the only option. The new feature enhancement addresses this scenario.

Documentation around the feature capabilities and configuration details (along with Sample scripts) can be found here.

Supported Scenarios

  • Configuring distribution database to be included in an AG.
  • Configuring replication such as publications and subscriptions before and after AG failover.
  • Replication jobs functional before and after failover.
  • Removing replication at distributor and publisher when distribution database is in AG.
  • Adding or removing nodes to existing distribution database AG.
  • A distributor may have multiple distribution databases. Each distribution database can be in its own AG and or multiple distribution databases can be part of the same AG.

Feature Limitations and Restrictions

The following limitations and restrictions exists in the first release of the feature.

  • Local distributor is not supported. For example, publisher and distributor must be different SQL Server instances. A publisher using itself as distributor (a.k.a. local distributor) cannot support distribution databases in an AG.
  • The following Replication scenarios are not supported
    • Merge replication is not supported.
    • Transactional replication with immediate or queued updating subscriber is not supported.
    • Peer to peer replication is not supported.
    • Bidirectional transactional replication is not supported.
  • All SQL Server instances hosting distribution database replicas must use the same version of SQL Server i.e. SQL Server 2017 CU 6 or later, and
  • The distribution database AG must have a listener configured.
  • Secondary replicas in a distribution database AG can be synchronous or asynchronous. Synchronous mode is recommended and preferred. With Async secondaries there is a possibility of Data-loss and possible non-convergence issues.
  • All secondary replicas in a distribution database AG must be readable. This is because there are some configuration steps which needs to be executed on the Secondary Replicas.
  • All the nodes in the distribution database AG need to use the same domain account to run SQL Server Agent, and this domain account needs to have the same privilege on each node.
  • If any replication agents run under a proxy account, the proxy account needs to exist in every node in the distribution database AG and have the same privilege on each node.
  • If you need to make changes to distributor or changes distribution database properties, this needs to be done in all replicas participating in distribution database AG.
  • Configuring distributor on the publisher needs to be done with scripts. The replication wizard cannot be used. Replication wizards and property sheets for other purposes are supported.
  • Replication monitor and other replication UI that connects using the AG listener name, are not supported as of SQL Server 2017 CU 6. To administer replication agents associated with the distribution database in an AG, use job property and job history. This capability would be available in subsequent releases of SSMS.
  • Configuring the AG for distribution databases can only be done through scripts.
  • Setting up distribution databases in an AG needs to be a new replication configuration. Switching an existing distribution database to an AG is not supported. Also, once a distribution database is taken out an AG, it can no longer function as a valid distribution database and should be dropped.

 

UPDATE 05/10

Starting with SQL Server Management Studio 17.7, Replication monitor supports registering a listener for scenarios where publisher database and/or distributor database is part of Availability Group. This feature requires the distribution database to be upgraded to SQL 2016 SP2 CU3 and above OR SQL 2017 CU6 and above.

Update 10/04

Starting with SQL Server 2016 SP2-CU3, this feature is now supported for SQL Server 2016.

 

Sourabh Agarwal Senior PM, SQL Server Tiger Team Twitter | LinkedIn Follow us on Twitter: @mssqltiger | Team Blog: Aka.ms/sqlserverteam

Comments

  • Anonymous
    April 30, 2018
    Is it recommended that publisher databases participating in an AG should also operate in Synchronous mode?Otherwise there could also be a possibility of Data-loss and possible non-convergence issues.
    • Anonymous
      April 30, 2018
      Yes.