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?
this is a microsoft sql server forum but...
You probably can export it but will your cloud provider allow this?
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.
We are the provider. Just trying to provide a solution to the clients accessing their database.
do they have an FTP you can dump to on schedule. how fresh of data do they want? daily, hourly, weekly?
I will look into the ODBC option. Concerned with how much resources this will take up and bandwidth. Thank you
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.
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.
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?
Do they have the bandwidth/technical people to know how to do that? What file type would it be, what extension?
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
You would need the ODBC drivers installed on the local system - then you can use them in SSIS to connect to the cloud version.