We are looking to offload the data from a Production enviroment to a reporting environment. I currently have an SSIS package setup to extract and load data between the two servers.

However, now there are some development activities and the requirement is to capture and apply any schema changes to the destination server for read only purpose all while ensuring real (or near real) time sync.

I looked into implementing Change data capture during my initial ETL implementation -but data transfer and replication are not what is it originally intended for and getting to know more about undocumented behavior of CDC when used for data sync. Also it would basically be replication under the hoods.

I thought transactional replication would be best.

But I now got to know that merge replication between other production environment is already configured in the source DB. What is mt best course of action here. I dont want to keep reengineering this solution.

  • Have you considered a 3rd party tool? – McNets Dec 21 '18 at 18:38

What about Always-on with readable replicas? You could set up availability group to scale this replication to other databases.

Offload read-only workload to secondary replica of an Always On availability group

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.