We're in the midst of migrating our analytics warehouse from an on-premises SQL Server to Redshift, but the real headache is all the data feeds. Currently, our SQL Server warehouse pulls data from various sources using a mix of SSIS packages, linked servers, flat file imports, and some outdated DTS packages. We have about 30 data sources, including Salesforce, NetSuite, SAP, Workday, ServiceNow, and several internal databases. With the move to the cloud, none of our existing ingestion methods work anymore. Linked servers aren't supported in Redshift, SSIS packages will need significant rewriting, and flat file imports require a new approach since there's no local file system. Essentially, we are rebuilding our entire ingestion layer from the ground up while also migrating the warehouse, which is a huge challenge. Although the internal database replication via DMS is fairly straightforward, I'm uncertain about how to handle the SaaS sources. Should we convert SSIS packages into Python scripts on ECS, use Glue for everything, or consider a third-party tool? The sheer volume of decisions to make is pretty overwhelming.
3 Answers
If I were you, I’d phase the migration instead of trying to do everything at once. Set up the new ingestion layer to push data into a staging area in S3 while keeping the old warehouse running. Once you’re sure the new ingestion is solid, then flip the switch to Redshift. Running both in parallel for a while gives you a safety net in case something goes wrong.
Have you thought about using AWS Database Migration Service more extensively? Since you're already using it for internal databases, it can manage a lot of data movement without needing a complete rebuild. You could move data into S3 and then load it into Amazon Redshift, using AWS Glue or custom scripts only when you need transformations. This might save a ton of effort compared to rewriting many SSIS packages.
I went through a similar process and segregated it into three parts. For internal databases, we used DMS directly to replicate to Redshift. For SaaS sources like Salesforce and Workday, we chose a managed service like Precog that writes to Redshift. Then for the few legacy feeds using flat files, we built lightweight Glue jobs since those tend to be stable and change infrequently. This three-tier strategy helped us avoid the pitfalls of trying to solve everything with a single tool.
Related Questions
Biggest Problem With Suno AI Audio
Ethernet Signal Loss Calculator
Sports Team Randomizer
10 Uses For An Old Smartphone
Midjourney Launches An Exciting New Feature for Their Image AI
ShortlyAI Review