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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

776 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