External database connection to a remote database

[OvaisAkhter]

We have a hosted adobe campaign instance and we will like to export data from this instance to our data ware house hosted remotely (Azure SQL can be an option). In an ideal situation direct access to the database will be most beneficial since then we can use SSIS to periodically download the data into our own database. I believe this does not seems to be a possibility.

I have been looking into the FDA provided by AC mentioned in detail here

Accessing an external database (FDA)

I wonder if this is possible to connect to an external (remotely hosted) database to export the data directly into the data ware house. The article also mentions "HTTP relay to a remote instance" which I think will enable to connect to another Adobe Campaign Instance not a database.

Has any of you had any experience in these cases?

[Jean-Serge_Biro · Accepted solution]

Hi Ovais,

Often, linking databases geographically away (not the same network) can lead to usual issues such as performance bottlenecks and network outage (see literature on commit 2 phases DB mechanism, etc). FDA or database links are quite the same, from infrastructure point of view.

Moreover, it can ease hacking exploits with opening SQL Server ports, and use of VPN is highly recommended.
So take care to these infrastructure considerations.

Thats' being said, I strongly suggest you to use Microsoft SSIS packages to exchange data by .csv files, between your AC instance and DW db, with help of SFTP protocol (not FTP; so third-party plugin in SSIS).

You can also use Adobe Campaign workflow and SFTP connector to send / receive data to/from your DW located on Azure.

Regards.
J-Serge