topic Re: Migrating Data Sources in Alteryx Connect Discussions

Migrating Data Sources

Treyson — Wed, 24 Jul 2019 20:04:38 GMT

Recently, we migrated a DB from a 2008 SQL Server instance to a 2016 SQL Server Instance.

I need to scrape this new instance and I am curious as to the best path forward. All the objects have the same names, its just that the box that everything sits on is different. Do I need to just delete that whole data source in Connect? I am worried that I am going to lose my metadata around those tables.

I understand how to pull the new instance, just don't want to break the actual Connect instance.

I need some guidance.

Re: Migrating Data Sources

MichalM — Wed, 24 Jul 2019 20:53:08 GMT

@Treyson

Does the DB sit on the same server?

Re: Migrating Data Sources

Treyson — Wed, 24 Jul 2019 22:17:24 GMT

Nope. Same DB, schemas, table names, etc. Just a new server.

Re: Migrating Data Sources

MichalM — Thu, 25 Jul 2019 07:21:44 GMT

In which case the new db will be loaded into the Connect catalogue as a new asset.

The Connect asset ID is created as a hash of concatenaded values - eg hostname/IP, schema name, table name, column name so if your new database is hosted on a different server, the asset will be given a new ID.

As for the metadata you may have entered manually for the old database, this can be exported from Connect as an XLS file, remapped to the new db and re-imported so nothing is lost.

Re: Migrating Data Sources

Treyson — Thu, 25 Jul 2019 16:48:01 GMT

@MichalM I have some follow ups on this.

I did the export of the entire object and this was all that was in the XML file:

<?xml version="1.0" encoding="UTF-8"?><xf3-dump xmlns="http://semanta.cz/schema/xf3/export"

What do I need to delete in order to remove that asset? If I recall I need to delete the stage? Is that correct?

Re: Migrating Data Sources

MichalM — Wed, 31 Jul 2019 10:14:52 GMT

Do you want to delete the old DB entirely @Treyson ?

Re: Migrating Data Sources

SeanAdams — Fri, 05 Mar 2021 16:11:54 GMT

This does seem like very useful and necessary functionality @Treyson - especially since it's quite common to have to move a DB to a new server; and you absolutely don't want to lose the investment of all the metadata that the users have captured.

Re: Migrating Data Sources

James_First_Mile — Fri, 05 Mar 2021 18:46:10 GMT

I realize the question from @Treyson was a long time ago but, for anyone else who needs to know how to completely and safely purge an asset, here are the steps:

Make a backup - copy our 'ac_work' folder in your install directory
From the GUI, find the folder asset page for the server in question
As Admin, click “Remove”. This does a “soft delete”, where the records have been flagged as inactive in H2
From Admin panel, remove “inactive entries”
From Admin panel, remove the “Stage” corresponding to the source you removed (note: only do this when you want to permanently remove a source system definition from Connect)
- The order of this step doesn’t matter relative to steps 3&4 above
From Admin panel, synchronize the “Job” that corresponds to the asset type. For DB source, this is “load-alteryx-db”
- This will get triggered automatically with every metadata loader execution, but doing it manually gives you a chance to test immediately

Re: Migrating Data Sources

Treyson — Fri, 05 Mar 2021 18:54:00 GMT

For anyone reading this, @James_First_Mile is the truth when it comes to Connect.

Re: Migrating Data Sources

James_First_Mile — Fri, 05 Mar 2021 19:09:06 GMT

@Treyson, you're too kind. Hope all is well with you.