Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

MySQL to SQL Server Import and automation

I have a Dedicated Server where our website and transactional database are stored.   This server has a MySQL database.

I have a separate reporting server that I am going to use to import the data and using reporting tools on top of this.

I am seeking to do a few things:

1) Automate the creation of a table in the original MySQL database using a View.  The purpose of doing this is to have a 'source' table with all the precalculated fields and necessary dimensions (OLAP dimensional structure) for easy reporting.   I would like some advice on how to do this in MySQL.  I currently have an SQL VIEW that takes 5 minutes to run - I plan on using a CREATE TABLE statement using this SELECT query to create a table.  My issue here is that I don't want to everytime overwrite the table but rather re-import the data into the table.    How can I schedule such a job to run once an hour?  Please can you provide details on creating a table and then updating it subsequently without overwriting the table structure (indexes etc) with DDL.

2) Automate copying the table from the source (MySQL) database on the main server into the SQL Server 2012 database on the different reporting server.   The methods I can think of are with very little MySQL or SQL Server experience:

I) FTP a .csv file from main server to reporting server and set up a schedule to import this in SQL Server.

II) Set up a direct connection from the SQL Server to MySQL database.

I would be grateful for a detailed answer with explanations of how the advised steps can be completed - e.g. where in application I need to look to do this.

I will give this job to a SQL Server expert.

Kind Regards,
2 Solutions
Daniel WilsonCommented:

Regarding the creation of your OLAP source table, I would create it once manually.  I would have the automated job:
Truncate Table OlapSource;
Insert Into OlapSource (col1, col2, ...)
(Select col1, col2, ... from OlapView);

Open in new window

Once you have that running manually, look at automating it.  MySQL does not offer a direct way to set up an automated job.  So you'll want to create a script -- PHP, C program, something -- that will connect to the DB and run that code.  You then want to set up a cron job to execute that job on whatever schedule you have in mind.

Given that the source is on a different server and probably a different network from your reporting server, using FTP to transfer the CSV file is one of your better options.  You could, of course, create a webservice that would feed out the information as XML of JSON ... but FTP'ing a CSV sounds a little simpler.
With regard to the data export... are there any fields in the data like a timestamp or automatically increment ID field that you could use to make a query that only includes the data that is new or changed since the last export?  Sending only the changes would be better for performance of both the database query and the file transfer.
Here are a couple of things with regard to the CSV file transfer:

1. Set it up to export the file to a temporary location and then move the file into the FTP outbound folder after the full file is written to disk.  If you export directly to the FTP pick-up location there is a risk that an incomplete/partial file will be transferred to MS SQL Server.

2. If the output file contains any confidential data be aware that FTP is not secure so you'll need to use FTPS or SFTP to keep it safe if the transfer is over the internet... if it is internal on your LAN then it may not matter depending on your office rules.
hennanra3Author Commented:

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now