MySQL to SQL Server Import and automation

Posted on 2013-12-16
Last Modified: 2013-12-28
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,
Question by:hennanra3
LVL 32

Accepted Solution

Daniel Wilson earned 250 total points
ID: 39725582

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.
LVL 16

Assisted Solution

AlexPace earned 250 total points
ID: 39727026
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.

Author Closing Comment

ID: 39743248

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now