InsideDarkWeb.com

SQL Server 2019 standard edition CDC with SSIS standard edition

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.

Database Administrators Asked by phinman on November 21, 2021

2 Answers

2 Answers

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.

Answered by David Browne - Microsoft on November 21, 2021

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/

Answered by MSSQLServerDBA on November 21, 2021

Add your own answers!

Related Questions

Ask a Question

Get help from others!

© 2021 InsideDarkWeb.com. All rights reserved.