2

We have a large Amazon Web Services RDS for SQL Server instance and we would like to do incremental data transfers from RDS to On-Premesis SQL Server on a regular basis.

The On-prem server will be used to feed information into other systems on an acceptable 1-day delay.

However reading through the docs and searching on google, forums etc, we have not found a seamless way to do off-AWS data transfers using RDS for SQL Server.

Built-in SQL Server functions such as Change Data Capture (CDC) are turned off as well as Replication and off-site Backup/Restore services.

Is there a way to do this or is it a limitation of using RDS?

2

Best is to load the data into a staging database on premise and then incrementally load the data to the main database.

You can use : SQL Database Migration Wizard found on codeplex.

Even though it is for Azure to onpremise sql server and vice versa, it will work for Amazon RDS as well.

Other alternative is to use SSIS as mentioned by @sqlreader.

1

Using SSIS for automation, you may be able to leverage MERGE in lieu of the CDC feature.

1

You can use the Amazon Database Migration Service. https://aws.amazon.com/dms/

As per the FAQ's:

Q. In addition to one-time data migration, can I use AWS Database Migration Service for continuous data replication?

Yes, you can use AWS Database Migration Service for both one-time data migration into RDS and EC2-based databases as well as for continuous data replication. AWS Database Migration Service will capture changes on the source database and apply them in a transactionally-consistent way to the target. Continuous replication can be done from your data center to the databases in AWS or in the reverse, replicating to a database in your datacenter from a database in AWS. Ongoing continuous replication can also be done between homogenous or heterogeneous databases. For ongoing replication it would be preferable to use Multi-AZ for high-availability.

  • Update: Continuous data replication is not currently available for SQL Server in RDS. From the DMS website docs: Supported versions are as follows: Microsoft SQL Server versions 2008R2, 2012, and 2014, for the Enterprise, Standard, Workgroup, and Developer editions. Note that change data capture (CDC) operations are not supported. The Web and Express editions are not supported. – Alistair Sep 13 '17 at 3:17
0

Import / Export Wizard can do the initial export and import and then you can customize anything you need to in SSIS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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