In SQL Server, Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. It is highly recommended to plan the high availability of the replication along with the servers to avoid data inconsistency.

We have both Publisher and Subscriber server on ALWAYS ON AVAILABILITY GROUP(AAG) for high availability and disaster recovery. In case if one of the active primary nodes goes down automatically the AAG initiate failover and make the secondary node as a primary active node.

Replication from publisher to the subscriber is very transactional and any inconsistency or delay will impact the tableau report generation Problems with our high-availability solution has been resulting in:

  • Replication failure in case of publisher failover
  • Replication failure  in case the subscriber failover
  • The permanent issue with the disk space for the drive of the transaction log of the publisher’s database. As it
    holds undistributed commands.
  • A long conference calls with the stakeholders

From the requirement perspective, in our environment, we need to be able to provide the following

  • HA & DR for our application databases.
  • Reporting-specific capabilities on a subset of our live
    transactional data.
  • Specific indexes on the reporting subset of data in order to
    provide performance for reporting that does not impact the transactional
    data.

To meet these requirements we designed a new Replication topology with the remote distributor and integrated the replication with ALWAYS ON AVAILABILITY GROUP
Microsoft directly does not support replication to be node sensitivity and it always binds itself with one single server. We designed a new solution.

First, we moved the distribution database to another standalone machine from local machine. This will remove the replication dependency from the local physical server distribution database and act independently. Second, we bind the subscriber’s AG listener name to the publisher instead of having the physical server name. This will make the subscriber independent.

We did the following to the environment to achieve the goal

  • 2 Node Windows Server Failover Cluster (WSFC) using ALWAYSON-AVAILABILITY GROUP
    • PrimaryNode1
    • SecondaryNode2
  • 2 ALWAYS ON Listeners for Galileo and Tableau Instances
  • 1 Standalone default SQL Server instance to act as Remote
    distributor
  • 2 named instances of SQL Server
    • GSQLPROD instance AG
    • TSQLPROD instance AG
  • Link Servers for replication communication

In order to set up our environment to use transactional replication in conjunction with AGs, we did the following steps

  • Configure all AG Replicas as Publishers
  • Create an AG
  • Created appropriated link servers for the AG replicas
  • Configure the publication for redirection
  • Add AG subscriber listener

The replication continues even after an AG failover of the Publisher or Subscriber and it 24/7 ON now. We achieved our goal by designing this out of box solutions.