Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Migration Assistant 2008 SSMA


By:   |   Read Comments (6)   |   Related Tips: More > Upgrades and Migrations


Latest on-demand video "Optimize SQL Server Performance" (watch now for free)


Problem

One of my client's requirements is to migrate and consolidate his company departments' databases to SQL Server 2008. As I know the environment, they are using MySQL , MS-Access and SQL Server with different applications. Now the company has decided to have a single dedicated SQL Server 2008 database server to host all the applications. So there are a few things to do to upgrade and migrate from MySQL and MS-Access to SQL Server 2008. For the migration task, I found the SQL Server Migration Assistant 2008 (SSMA 2008) is very useful which reduces the effort and risk of migration. So in this tip, I will do an overview of SSMA 2008.

Solution

SQL Server Migration Assistant 2008 (SSMA) is a free tool released by Microsoft to speed up the database migration process. SSMA 2008 migrates the tables, views, indexes, stored procedures, functions, and triggers to a SQL Server 2008 database from a source database (Oracle / Sybase / MySql / MS-Access). Microsoft has released 4 different SSMA tools to help their customers Refer to this article for more information.

How SSMA Works

SSMA 2008 consists of three tasks. They are namely Create Report, Convert Schema, and Migrate Data. Each task has many features which help for a smooth migration.

ising sql server migration assistant 2008

Create Report

This is the first step of the migration process. SSMA analyzes the metadata of a source database and creates a report with a conversion assessment. The Assessment statistics provide the database object list based on the object category and conversion complexity with an estimated amount of manual work. Also, the Assessment Report shows how much effort is required for the migration.

For this tip purpose, I have used my own database called 'farmhousedb'. The Assessment Report for the given database is as below.

SSMA analyzes the metadata of a source database and creates a report with a conversion assessment

Convert Schema

This is the main step of the migration. In this step, SSMA converts the source schema to the target schema. It also provides a facility to define schema mapping and predefined mapping of source database data types and charset mapping to SQL Server data types and charset mapping. During the schema conversion, all the source objects are converted to equal objects in SQL Server 2008. Once the conversion is done it can synchronize with the SQL Server 2008 database. The below sample screen shows the list of objects to be created in the source database.

during the schema conversion all source objects are converted to equal objects in sql server 2008

Migrate Data

The final step is migrating data. SSMA copies the source data to the target database. Once the data are migrated the SSMA shows the Data Migration Report with Number of Rows, Number of Successfully Migrated Rows and Ratio.

ssma shows the data migration report

SSMA 2008 is an easy and quick way for data migrations. SSMA 2008 has many useful user interfaces to understand the database objects. With this free tool, we can perform assessments and automated database migrations to SQL Server 2008.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Murali Krishnan Murali Krishnan is a Lead Consultant with vast experience in Database/BI Design, Development and Administration.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, July 14, 2016 - 11:34:21 AM - chanchal dixit Back To Top

Hi,

 

Hope you are doing well.

 

My plan is to migrate from 2005 sp2 standard edition to 2012 standard edition, Can i use SQL Server Migration Assistant 2012 SSMA for this? how to get it , can you provide me more details.


Thursday, May 10, 2012 - 7:31:07 AM - Dinesh Vishe Back To Top

Please let me khow about Mssql dataware housing to oracle ..Give me some tips or software


Friday, March 30, 2012 - 4:04:31 AM - Corné Albers Back To Top

Is there a way to convert for example field descriptions from Access to SQL server with the use of this tool?

We have documented each field in Access with a description and we would like to take this information to SQL Server when we perform the conversion.

Thanks a lot for your answers!


Friday, February 24, 2012 - 1:59:08 AM - Rakesh Rao Back To Top

HI,

I want to know the disadavantage (drawbacks) of ssms. please inform me as early as possible.

Thankyou.


Wednesday, January 26, 2011 - 2:12:03 PM - Raghavendra Back To Top

Very useful tip, thanks for sharing.


Tuesday, January 25, 2011 - 12:03:49 PM - Sivakumar Back To Top

Very useful and cool tip! - Keep going :)


Learn more about SQL Server tools