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.
