SQL Server Migration Assistant 2008 SSMA

By: Murali Krishnan   |   Updated: 2011-01-25   |   Comments (6)   |   Related: > Upgrades and Migrations

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.

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.

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.

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 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

• Learn about migrating MS-Access to SQL Server using SSMA
• Generate scripts for SQL Server 2008 object migration
• Overview of Microsoft SQL Server 2008 Upgrade Advisor
• Learn more about migrating to SQL Server 2008

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips