SQLTeam.com | Weblogs | Forums

Isolate the database from the cloud live version their local network

mysql
#1

We currently have a mysql database in the cloud and accessed through a portal. We would like to isolate the database from the cloud live version to a local network for clients to run queries locally. Can the SQL database be exported to Excel or MS Access to run these queries?

#2

this is a microsoft sql server forum but...
You probably can export it but will your cloud provider allow this?

#3

If you can connect to the cloud version using ODBC/OLEDB then you could build an SSIS package to extract data from the cloud and load to a local instance.

The problem will most likely be connecting to the cloud version. In a lot of cases - the cloud version is sitting behind a firewall that requires SSH connections. Getting an SSH connection to be available from a non-user (background) process is not trivial, but it can be done.

#4

We are the provider. Just trying to provide a solution to the clients accessing their database.

#5

do they have an FTP you can dump to on schedule. how fresh of data do they want? daily, hourly, weekly?

#6

I will look into the ODBC option. Concerned with how much resources this will take up and bandwidth. Thank you

#7

They would want the entire database as I understand it. They could require to do multiple queries in a few days and then a waiting period of several weeks before running them again.

#8

Hi Yosiaz, are you suggesting to export the database from our end to an FTP folder for the client to then access and download. That might work.

#9

Once they download from FTP site they should be able to import into MS Access or should they setup an mysql server at their end as well?

#10

Do they have the bandwidth/technical people to know how to do that? What file type would it be, what extension?

#11

I downloaded and installed the mysql ODBC driver and appears in Administrator Tools but if I try to import as per article 6.4.2 Importing MySQL Data to Access (located on mysql site) the driver is not available.

Would I need MySQL installed on the local system to make this work?
Thank you

#12

You would need the ODBC drivers installed on the local system - then you can use them in SSIS to connect to the cloud version.