Solved

MySQL Dump into SQL Server

Posted on 2013-12-07
3
1,756 Views
Last Modified: 2013-12-07
I have a MySQL database and I would like to understand the best mechanism to do the following:

1) Export data from MySQL (already a database dump is being generated)
2) Schedule an import of this MySQL Database (import)
3) Do a delta load (so that I am not every night loading the whole database)
4) Run standardized scripts to modify and then output the calculated results (for consumption of a business intelligence tool that I am setting up).

I guess one consideration here is whether I import the database into MySQL or just the data dumps.

I will most likely give this job to a contractor as I know it's very easy - I just don't want to spend a long time troubleshooting.  I want to know what is required to be done with some idea technically how this will be done.  Would appreciate some details - e.g. if you advise where in the SQL Server Studio tool this is done.

Thanks,
Raheel
0
Comment
Question by:hennanra3
3 Comments
 
LVL 9

Accepted Solution

by:
QuinnDex earned 250 total points
ID: 39703245
this will cover steps 1 2 and 3 more information is needed for 4

first download the correct connector from the link below from (suggest MySQL ODBC Connector 5.1)

MySQL ODBC Connector


Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. Under the tab labelled as "System DSN", press the "Add" button.

On the "Create New Data Source" dialog that appeared, choose MySQL ODBC 5.1 Driver and then press the "Finish" button.

After that, a MySQL connection configuration dialog will appear. Add your MySQL database account information in it, preferably the "root" account which has full access to your databases in MySQL. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else.

Press the "Test" button to ensure your connection settings are set properly and then the "OK" button when you're done.

In this state, you are ready to establish a link towards MySQL database from your Microsoft SQL Server Management Studio. Open a query window and run the following SQL statement:


EXEC master.dbo.sp_addlinkedserver 
@server = N'MYSQL', 
@srvproduct=N'MySQL', 
@provider=N'MSDASQL', 
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; _
	DATABASE=yourdb; USER=root; PASSWORD=yourpass; OPTION=3'

Open in new window


This script will produce a link to your MySQL database through the ODBC connection you just created in the previous stage of this article. The link will appear in the Microsoft SQL Server Management Studio under linked servers


Create a new database in Microsoft SQL Server  called  "testMySQL". In the query window, run the following SQL statement to import table shoutbox from the MySQL database yourdb, into the newly created database in Microsoft SQL called testMySQL.

SELECT * INTO testMySQL.dbo.shoutbox
FROM openquery(MYSQL, 'SELECT * FROM yourdb.shoutbox')

Open in new window


full detail and step by step images can be seen here
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39703253
Couple of ways to pull this off, but the way I'd do this (disclaimer:  I have a hammer, and everything looks like a nail) is...

1)  SSIS package with a data flow task with defined source (MySQL) and target (SQL).
2)  SQL Agent job that fires the SSIS package on a predetermined basis (nightly, whatever), unless you guys run a third-party scheduling tool such as ActiveBatch.
3)  Either the SSIS package will handle the MERGE using JOINs, or create a SQL Stored Procedure with a MERGE statement that pulls this off.   Better would be SSIS using the Task Factory Upsert control, but that's a $1k license for the whole suite of tools.
4)  Various SQL Tasks within the SSIS package. If 'output the calculated results' means more than one target, then the data flow would have a multicast and multiple destinations.

Hope this helps.
Jim
0
 

Author Closing Comment

by:hennanra3
ID: 39703897
Thanks both!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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

12 Experts available now in Live!

Get 1:1 Help Now