Hot File

Migrate MySQL to Microsoft SQL Server

View: 2430    Dowload: 0   Comment: 0   Post by: naruto  
Author: none   Category: SQL Server   Fields: none

0 point/2 review File has been tested

This article describes a few simple steps in order to migrate MySQL into Microsoft SQL Server 2005. The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to a Microsoft SQL Server environment.

Introduction

Initially, I started my search for an article on CodeProject regarding MySQL->MS SQL migration without any success. I had an old PHPbb forum running, that needed to be upgraded to a Microsoft environment entirely. I could have just kept MySQL and Apache server, but instead I decided to migrate the entire concept of PHPbb to a YAF-forum.

Setup ODBC Connection to MySQL Database

This article will not go through how to setup a MySQL or Microsoft SQL server, but make sure you have downloaded at least the MySQL ODBC Connector from here.

For this article, I downloaded the MySQL ODBC Connector 5.1.

The setup of this connector is pretty simple:

  • 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. In this case, my database is called "tigerdb". 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.

Create a Microsoft SQL Link to your MySQL Database

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=tigerdb; USER=root; PASSWORD=hejsan; OPTION=3'

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 like this:

If it doesn't show up in the treeview, press the refresh button.

Import Data between the Databases

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

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

That's it!

Points of Interest

During this migration, I had to import lately my newly migrated database into the structure of "Yet Another Forum" tables. For that, I used a series of SQL-scripts. However I am not going to post them here. If folks leave comments here about the need for these scripts, just tell me and I will gladly change this article and start adding them. You're welcome to post your comments.

Another issue you will most likely encounter are the differences between these two databases based on datatypes. I would suggest to proceed with a reverse engineering of your MySQL database (for example, Visio is one application that provides reverse engineering functionality) and start mapping all the differences and potential risks of losing parts of data for instance, within varchar columns.

Migrate MySQL to Microsoft SQL Server

Migrate MySQL to Microsoft SQL Server Posted on 06-05-2014  This article describes a few simple steps in order to migrate MySQL into Microsoft SQL Server 2005. The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to a Microsoft SQL Server environment. 5/10 2430

Comment:

To comment you must be logged in members.

Files with category

  • Insufficient access rights error when doing backup in SQL Server

    View: 366    Download: 0   Comment: 0   Author: none  

    Insufficient access rights error when doing backup in SQL Server

    Category: SQL Server
    Fields: none

    2.25/2 review
    All of sudden SQL Server Management studio gives me the following error message when I'm trying to browse for the backup file location.

  • Migrate MySQL to Microsoft SQL Server

    View: 2430    Download: 0   Comment: 0   Author: none  

    Migrate MySQL to Microsoft SQL Server

    Category: SQL Server
    Fields: none

    0/2 review
    This article describes a few simple steps in order to migrate MySQL into Microsoft SQL Server 2005. The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to a Microsoft SQL Server environment.

  • COUNT of DISTINCT Rows in SQL Server

    View: 1287    Download: 0   Comment: 0   Author: none  

    COUNT of DISTINCT Rows in SQL Server

    Category: SQL Server
    Fields: Other

    0/4 review
    SQL Server does not support COUNT(DISTINCT *). For example, the below query fails.

  • Finding SQL Servers on the Network

    View: 618    Download: 0   Comment: 0   Author: none  

    Finding SQL Servers on the Network

    Category: SQL Server
    Fields: Other

    0/2 review
    I am a TSQL fanatic. The programs I code are highly dependent upon MS SQL stored procedures. I pay for this love of TSQL when a major overhaul of the system is necessary. Sometimes my code needs to be updated heavily in two places; client and...

  • SQL Server and Database Enumerator

    View: 596    Download: 0   Comment: 0   Author: none  

    SQL Server and Database Enumerator

    Category: SQL Server
    Fields: Other

    0/1 review
    This sample helps you to enumerate the list of SQL Servers and the databases and languages that a particular SQL server has. The class CSQLInfoEnumerator encapsulates this functionality into it.

  • Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    View: 618    Download: 0   Comment: 0   Author: none  

    Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    Category: SQL Server
    Fields: Other

    0/2 review
    The DMVs; newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance....

  • Table Value Parameter in SQL Server 2008

    View: 529    Download: 0   Comment: 0   Author: none  

    Table Value Parameter in SQL Server 2008

    Category: SQL Server
    Fields: Computers - Technology

    0/1 review
    One of the fantastic new features of SQL Server 2008 is the Table value parameter. In previous versions of SQL Server, there wasn’t a native way to pass a table to a Stored Procedure or functions. The usual workaround was to pass a large varchar or...

  • SQL Server DO's and DONT's

    View: 521    Download: 0   Comment: 0   Author: none  

    SQL Server DO's and DONT's

    Category: SQL Server
    Fields: Computers - Technology

    0/0 review
    So, you are now the leader of a SQL Server based project and this is your first one, perhaps migrating from Access. Or maybe you have performance problems with your SQL Server and don't know what to do next. Or maybe you simply want to know of some...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd