Sql-server – Enable Replication/CDC from High Availability Group

high-availabilitysql-server-2016transactional-replication

I am a newbie to this Replication concept and I am trying to build some scripts to automate our Build Process where we will be disabling/re-enabling Replication on a regular basis.

Background:
We have an Application Database Server which is on High Availability (Primary & Secondary Nodes). Our Staging Database Server will be communicating with the HA Group either through a Load Balancer or Listener.

Requirement:
We would like to enable Replication at the Table level and not the Database level from HA Group to the Staging Server as we are only interested in few tables. Is this even possible? If so, I am looking for some scripts (from your Toolbox, if you have any) to create Publisher, Distributor, and Subscriber and then disable/re-enable them on a regular basis during our Build process.

Note: This would be more of a "Pull" and I am looking for Scripts that can be executed from the HA Group as part of the Build Process.

Your help with this is very much appreciated.

Thanks!

Best Answer

Transactional replication should be able to do your requirement of replicate some specific tables, as long as they have primary key.

For CDC I have not used and can't say if will work or not.

Regarding the scripts you should be able to generate them when creating the replication setup.