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?
Best Answer
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.