1

The primary question is:

  1. 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:

  1. Is there a manual process to do what the SSIS CDC Control Tasks automagically do?
  2. 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.

Improve this question
1
  • Not an answer to the CDC problem, but an alternative is to use history tables ("system versioned temporal tables"), also in all editions since 2016sp1, to maintain a history for synchronisation (take timestamp, send copy where SysStartTime > previous timestamp and delete rows with no new version where SysEndTime > previous timestamp, store this run's starting timestamp as reference for next time). Though in either case if both systems are Read/Write (i.e. the target isn't read only apart from this sync) you have potentially much more complicated conflict resolution to work out.
    – David Spillett
    Jul 22, 2020 at 8:33

2 Answers 2

Reset to default
0

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?

Yes. You can query the CDC system functions and views directly.

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?

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.

Improve this answer
0

You should research DMS solutions. AWS DMS, Azure DMS, and HVR DMS are the three I'm familiar with. They use CDC, are not just for migrations, and are very good for on-going replication of data where you don't have primary keys on every table or you are going from SQL to something else. HVR is probably your best bet of these three since you're going from SQL to Postgres but I know there are additional DMS products out there. Here's a link to get you started:

https://www.hvr-software.com/platforms/postgresql/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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