Migrate a Database to Microsoft SQL Server Database

(1)

6118 Franconia Rd, Suite: 217-A, Alexandria, VA 22310

Migrate a Database to Microsoft SQL Server Database

-: NOTE :-

Altering, printing, sharing with anybody, any training institute or commercial use without written permission is 100% prohibited. Please see the copy right information.

Written by

Zakir Hossain, Manager – Enterprise Architecture Configuration & Database, US Dept. of Defense

CEO, Data Group

CS Graduate (OSU), ITIL V3, OCP, OCA, MCDBA, MCIPT (Database Admin), Security+

Other Certifications:

Oracle RAC Admin, Oracle Backup & Recovery Admin, Oracle Performance & Monitoring Admin, Oracle Application Server Admin, System Admin (Windows/RedHat), Certified Java Programmer

Zakir

Hossain,

CEO

Digitally signed by Zakir Hossain, CEO

DN: cn=Zakir Hossain, CEO, o=Data Group, ou,

email=info@DataGroupUSA.com , c=US

(2)

Email:info@DataGroupUSA.com

Web:www.DataGroupUSA.com/helpdesk Phone:703-986-9944 2/10

Copy Right: Printing, altering, sharing with anybody, any training institute or commercial use without written permission is a Federal Copy Right Violation of Intelligence Product. Warning: Violators will be prosecuted with the fullest Extent of Federal Law.

Introduction:

Your company may have different types of databases running to support different functions. It could be because they do not have a standard platform to manage databases.

Before we can migrate a database, we need to have appropriate driver for it. For an example, if you need to migrate Oracle, SQL Server, DB2, UDB, MySQL, and Sybase databases to a different type of database you need to have a driver for that specific database software.

For an example, if you need to migrate MySQL database to SQL Server database, you need to have a driver of MySQL database and if you need to migrate Oracle database to SQL Server database, you need to have a driver of Oracle database. The good news for SQL Server DBA is if you need to migrate Oracle database to SQL Server database, Oracle driver comes with the SQL Server database software. So, you do not need to install the Oracle driver separately, in most cases. However, you need to have a right version of driver to migrate a database. For an example, migrating Oracle 10g vs Oracle 11g database to SQL Server 2008, you need to have the right version of Oracle driver for Oracle 10g and 11g.

Different methods of database migration to SQL Server:

There are so many RDBMS (Relational Database Management System). Examples of some of the popular RDBMS are Oracle, SQL Server, DB2, UDB, MySQL, and Sybase. Now a day, MS Access is also

considered as RDBMS. However, MS Access is has so many limitations like number of concurrent connections, database size, performance, types of data it can support etc.

Any databases that we need to migrate, we can use following methods. 1. Using ODBC connection

2. Using DTS/SSIS package 3. Using Data Modeling Tool 4. Using third party tool

Migrate Database Using ODBC Connection:

Follow the steps below to migrate a database to SQL Server using ODBC connection: 1. Create ODBC connection to database that needs to migrate

Here we will migrate MySQL database using ODBC connection method. 2. Create a database link from SQL Server to MySQL database

3. Create database objects (like tables, views etc) using T-SQL command Details of the steps:

(3)

Migrate a Database to Microsoft SQL Server Database Step 1: Create ODBC Connection to MySQL Database:

Before you proceed, you need to ensure that you have installed MySQL ODBC Driver/Connector. Here we will be using MySQL ODBC Connector 5.1.

Steps to create ODBC connection:

Open ODBC Data Source using following steps:

Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC) > Click on tab "System DSN > Click on Add button as shown below:

(4)

Now click on System DSN tab and click on Add button. This will open the next screen as shown below:

(5)

Now select the MySQL ODBC 5.1 Driver and click on Finish. This will bring the next screen as shown below:

(6)

Fill this configuration options, based on your server. Here MySQL database is running on server DataGroup210. You can use sever name or IP address of the server.

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

Configuration description:

Data Source Name: DataGroup210 (It could be any name you like.) Description: Optional

TCP/IP Server: MySQL database Server name or IP address

Port: 3306 (Default port for MySQL database. Use right port based your server running) Named Pipe: Most cases, it does not work. So, do not use this option

User: user name in MySQL database. Must have right privilege to this user, otherwise it will fail. Password: Type password for the user you are using

(7)

Database: Select database your planning to create connection for. It may not be available in the

drop down box. In that case, you can just type the name of the database.

Test/OK: Now click on Test button before you click on OK button. If you click on OK button OK, it will create the ODBC connection even it does not work.

Step 2: Create a DB Link from SQL Server to MySQL Database:

Before you can start migrating MySQL database to SQL Server, you must have to create a DB link to MySQL database from SQL Server.

To create to a DB link from SQL Server, you can use following tools: 1. Management Studio/Enterprise Manager

2. Query Analyzer

Here we will use Query Analyzer tool of SQL Server database. Open the Query Analyzer tool and run the following code:

EXEC master.dbo.sp_addlinkedserver @server = N'DataGroup210_SMS, @srvproduct=N'MySQL',

@provider=N'MSDASQL',

@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=DataGroup210; DATABASE=sms; USER=root; PASSWORD=root; OPTION=3'

Explanation of above code:

-- @server: DataGroup210_SMS: Link Name to SMS database on DataGroup210 Server. This name will show in SQL Server Database as Database Link. It could be any name you like

-- @srvproduct: Product Type. Example, MySQL, Oracle

-- @provider: Provider, MSDASQL: Microsoft Data Access for SQL Server -- @ provstr: SERVER= DataGroup210: MySQL Server Name, Could be IP also

This script will create a link to your MySQL database through the ODBC connection in SQL Server database. The link will appear in the Microsoft SQL Server Management Studio like this:

(8)

If it doesn't show up in Linked Server view, you may need to refresh your management studio. Step 3: Migrate Objects from MySQL to SQL Server Databases:

Now to migrate the objects from MySQL database to SQL Server, you must need to follow the steps: 1. Create a database, where you would like to migrate the database from MySQL.

Here we are migrating SMS database from MySQL Database to SQL Server database. So, we have created SMS database in SQL Server

2. Run the following T-SQL statement to migrate database objects: Here we are migrating following tables:

note attendance candidate invoice payment SELECT * INTO SMS.dbo.note FROM

openquery(DataGroup210_SMS, 'SELECT * FROM sms.note') SELECT * INTO

(9)

Migrate a Database to Microsoft SQL Server Database SMS.dbo.note

FROM

openquery(DataGroup210_SMS, 'SELECT * FROM sms.attendance) SELECT * INTO

SMS.dbo.note FROM

openquery(DataGroup210_SMS, 'SELECT * FROM sms.attendance') SELECT * INTO

SMS.dbo.note FROM

openquery(DataGroup210_SMS, 'SELECT * FROM sms.payment') SELECT * INTO

SMS.dbo.note FROM

openquery(DataGroup210_SMS, 'SELECT * FROM sms.invoice') SELECT * INTO

SMS.dbo.note FROM

openquery(DataGroup210_SMS, 'SELECT * FROM sms.invoice') Explanation of above code:

SELECT*INTO

SMS.dbo.note: DB and Table Name in SQL Server FROM

openquery(DataGroup210_SMS, 'SELECT * FROM sms.note') DataGroup210_SMS: DB Link name you have created in previous step sms.note: DB and table name in MySQL database

Migrate Database Using Data Modeling Tool:

There are so many tools in the market can be used to migrate MySQL database to any database systems. Some of the popular tools can be used for this purpose:

(10)

2. Visio

3. Oracle SQL Developer

Follow the steps below to migrate a database using Data Modeling Tool: 1. Create Logical Data Model (E-R Diagram):

Here you have the physical database of the database that you need to migrate. So, you need to use the backward engineering/reverse engineering technique to create the Logical Data Model of the respective database.

2. Data Transformation:

After creating the Logical Data Model, you should verify data types, length, primary key, foreign key and other constraints as needed.

Based on your need and supported data types of the destination/target database, change to appropriate data type and then check and change other information like length, primary key, foreign key and other constraints as needed.

3. Create Physical Database:

Now you have the Logical data model of the database that you are ready to migrate. Now you need to use the forward engineering technique to create the Physical Database in the destination database on destination server.