Incremental Upload data from sql server to Amazon Redshift

Question

Our Raw Data is in SQL SERVER,Data keep on growing,Growth rate is high,We need to load the data incrementally to Redshift for Analytics.Can you please point out me a good practice to load the data.How feasible with SSIS to load directly to Redshift (with out S3).

Answer 1

It's going to be impractical to load from SQL Server to Redshift without landing the data on S3. You can try loading via SSH but, of course, SSH on Windows is not well supported.

http://docs.aws.amazon.com/redshift/latest/dg/loading-data-from-remote-hosts.html

I did a presentation a while back on what we discovered when migrating from SQL Server to Redshift. One of the things we discovered was that SSIS was not very useful for interacting with AWS services.

http://blog.joeharris76.com/2013/09/migrating-from-sql-server-to-redshift.html

Finally, you could look into some of the commercial "replication" tools that automate the process of incrementally updating Redshift from an on-premise database. I hear good things about Attunity.

http://www.attunity.com/products/attunity-cloudbeam/amazon-redshift