customer is using Appsync (repurpousing) to prepare DB copies for developers. He is using SQL Server 2016 and the database has CDC enabled. If we mount the database copy, created with appsync, to another server the CDC data is missing.
Is the MSSQL CDC supported with Appsync? What can we do to mount the database with CDC?
According to MS docs on this particular feature, copy management, which is what AppSync performs, and the SQL CDC feature, have different use cases. CDC changes are not committed to the source, therefor AppSync has no way to replicate them. In other words, AppSync replicates the blocks on the array, of the primary database, not what’s being managed at the SQL layer.
AppSync does not support "creating copies of a mirror" either, notated in the AppSync User and Administration Guide, which is what I believe to be a similar situation. AppSync should tolerate the CDC feature, as it does mirroring, however, has no ability to support it directly due to the way copy management is performed by AppSync. AppSync is source application focused.
“Although the representation of the source tables within the data warehouse must reflect changes in the source tables, an end-to-end technology that refreshes a replica of the source is not appropriate. Instead, you need a reliable stream of change data that is structured so that consumers can apply it to dissimilar target representations of the data. SQL Server change data capture provides this technology”