Sql-server – High Availability with Transactional Replication

availability-groupsenterprise-editionsql serversql-server-2017transactional-replication

We are using SQL 2017 Enterprise and setting up a new DR site. One of our servers (Server B) is used for reporting and has many databases, one of which has tables populated through Transactional Replication from a different database on a different server (Server A). The Publisher is on Server A. The Distributer and Subscriber databases are both on Server B.

Server A is part of a cluster with an Availability Group which includes a DR server. Server B isn't part of a cluster, but I need a DR solution that allows for a failover if we lose a server or our entire primary site.

I'm planning to make a new cluster with Server B and a duplicate for DR (Server B2), but would like to know the best way to deal with the Distributer and Subscriber databases. Can they be part of an Always On Availability Group and will the Transactional Replication continue if there's a failover?

Are there any recommendations from anyone who has experience in this area?

Best Answer

Yes. See the documentation for a detailed list of steps.

SQL Server Replication, change data capture (CDC), and change tracking (CT) are supported on Always On availability groups. Always On availability groups helps provide high availability and additional database recovery capabilities.