Solved

MySQL to SQL Server Import and automation

Posted on 2013-12-16
3
622 Views
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,
Raheel
0
Comment
Question by:hennanra3
3 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 250 total points
ID: 39725582
Raheel,

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

Assisted Solution

by:AlexPace
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.
0
 

Author Closing Comment

by:hennanra3
ID: 39743248
Thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

708 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

19 Experts available now in Live!

Get 1:1 Help Now