Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2223
  • Last Modified:

MySQL Dump into SQL Server

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
hennanra3
Asked:
hennanra3
2 Solutions
 
QuinnDexCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
hennanra3Author Commented:
Thanks both!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now