Solved

MySQL Dump into SQL Server

Posted on 2013-12-07
3
1,939 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 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

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…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.

732 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