Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How To: Migrate Entire Databases Using Alteryx

DanM
Alteryx Community Team
Alteryx Community Team
Created

 

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!

Comments
asifk
8 - Asteroid

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

DanM
Alteryx Community Team
Alteryx Community Team

@asifk,

 

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

r4upadhye
11 - Bolide
Franz
9 - Comet

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.