The primary question is:
- Are there any methods for using SQL Server standard edition CDC for data synchronization to cloud based non SQL server databases that don't require use of SSIS Enterprise Edition?
Related questions are:
- Is there a manual process to do what the SSIS CDC Control Tasks automagically do?
- OR – is there some other performant way (i.e. not using triggers) to sync data changes from SQL Server Standard Edition out to a non SQL Server cloud database? Log shipping does not appear to be an option.
The background is:
After seeing that Microsoft enabled CDC in the standard editions of SQL Server 2016 sp1 and above, we built a SSIS package based on that to sync data from an on premise database out to a postgres database in the "cloud".
In testing from Visual Studio, everything works fine, but when we deploy the package to our SQL Server 2019 standard edition server, we run into an error that states:
The task "Mark Initial Load" cannot run on the installed edition of Integration Services. It requires Enterprise Edition (64-bit) or higher.
So it appears that while you can enable CDC on SQL Server standard editions, you can't actually make use of Microsoft's other tools to get any benefit from it without paying for enterprise edition anyway.
Best Answer
Yes. You can query the CDC system functions and views directly.
No. For a SQL Server or Azure SQL subscriber you can simply use Transactional Replication. But for other destinations, you'll need to write code or use a 3rd party solution.