Server configuration for simple data movement at large scale?

[scotthanrahan]

Hi, I may have a requirement to move 1000 tables per day from an on-prem database to the cloud. Updates only for fact tables but perhaps the full tables for dimension tables. This is a unique case where no transformations at all will be required. 

Given that 1000 is a lot of tables to update every day, I'd like to understand the most optimal server configuration to get this job done as quickly as possible. Does anyone have a few ideas to test? 

I've been through the "scaling a private server" links but they focus more on "processing" and I don't think any processing needs to be done here. Just I/O. Thanks for your thoughts.

[SteveA]

Hi @scotthanrahan,

I expect that a regularly scheduled job or jobs running on your Server would do the trick.  The frequency of the job execution would depend on how frequently the source data changes and how long it takes up update the target tables.  If the update process is "fast enough" you could just run one scheduled job on the Server daily, updating all the target tables at once.  But you might also consider splitting the update process into multiple, smaller jobs that could run in parallel on multiple Workers as that might result in better total throughput.  Parallel jobs could reduce total throughput however, especially if they are I/O bound and/or relying on the same source database connection(s) and target connections.

Would you please provide some additional details:

• Source and target DBs
• Data size estimate
• Approximate runtime for a desktop run of the complete job
• Server topology (particularly number of physical workers)

Best,

Steve A.