on 09-27-2016 09:12 AM - edited on 07-27-2021 11:36 PM by APIUserOpsDM
How To: Migrate Entire Databases Using Alteryx
Can I? Should I?
While the answer to this question is yes, Alteryx can do that, it should only be done with a few caveats.
These caveats stem from Alteryx's need to create temp files of your data to allow for manipulation without changing your source database/file. This allows Alteryx to separate the "working" data from your "real" data (housed in the database). Basically, if you're attempting to move the data from one database to another, your entire database will be duplicated in a temp file.
1. The first caveat is time sensitivity; using Alteryx to move a massive store of data will require the processing time to write the database to temporary memory.
2. If the Alteryx workflow were to timeout, error, or otherwise be interrupted, the process would stop and the user would need to figure out where it stopped on their data. Then change the workflow to eliminate the incomplete data and start the process anew. This is the second caveat to keep in mind.
While Alteryx can do many amazing things with your data, using it to migrate entire database worth of data is a tricky operation that has to be prepared for (allocate proper temporary memory) and monitored closely. Do so with caution!
Hi @DanM
Thanks for this info, I often find that when i try to append data onto a Table, i have to use Data =Stream to create the Temp Table and also use Write in DB tool to append, however i find a better option is to use the Data Stream tool and create a new table, thereafter read in DB from the new table and append to the Target Table.
I find that with Clients data is stored on Multiple servers and we often have to copy the source data and write it to 1 server where all the calculations/ queries are built.
I do hope Alteryx will look into this further and find an easier solution to migrate data between servers as it will be extremely useful.
Thanks for sharing the article above!
Regards,
Asif
We are constantly adding more features to the Input and In-DB tools. However, with the use case you have given, the first part is considered data migration and is not Alteryx primary use case to migrate data into one database for querying. You can do it, as you have seen, but Alteryx was not built to be used as a migration tool. Please add your Idea to the Designer Ideas page if you would like to see us look into this capability.
Thank
DanM
Architecting a database migration datapipeline; Thanks Dan, I have to say a DB migration tool seems like a very useful idea. I understand the constraints of the Designer doing that operation. How about if the workflow is deployed on the Gallery server. Will that remove some of the limitations posed by the desktop/download/upload datapipeline ? Thanks.