?
Solved

MySQL Dump into SQL Server

Posted on 2013-12-07
3
Medium Priority
?
2,052 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 9

Accepted Solution

by:
QuinnDex earned 1000 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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

770 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