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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

821 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