SQL Server Migration Assistant

SQL data migration is defined as the process of transferring data from sql server to another. The migration process may appear straight forward at first but involves a lot of complexity, especially when migrating a large volume of enterprise data. When we look in ETL(Extract, Transform, Load) process, data migration involves pulling data from one system, transforming and aggregating. It is necessary and loading it onto the destination server. The data migration involves sequence of phases, each of them are more challenging than other.

The process SQL Server Migration:

To migrate databases, it typically will involve four stages as

• Extract- While retrieving data from the source database or a source server and sending it to a staging or intermediary server.
• Standardise- Matching the source data to the destination formats could cater to compatibility issues with the data type or makes sure that metadata correctly reflects the data.
• Aggregate and cleanse- which perform any aggregation tasks to de-duplicate records or calculate derived fields.
• Load- Transferring the cleansed, aggregated and transformed data into target database.

SQL Server Migration Assistant:

SQL Server Migration assistant has the same purpose as Database Migration assistant except it is designed for migrating data from a particular RDBMS which is not SQL. SQL Server Assistant for MySQL DBMS to Microsoft SQL server 2005, SQL Server 2008, SQL Server 2008 R2 platforms. The application supports conversion or migration of MySQL control operators, cursors, DML operators, stored functions, stored procedures, tables, transaction, triggers and views.

To migrate MySql databases to SQL Server or SQL Azure, we must first create an SSMA project.

How to install SSMA?

To use SSMA , we must install the SSMA client program on a computer that will access both the databases we want to migrate and target instance of SQL Server Azure SQL database.

To begin SSMA, we should Click start, point to All programs point to SQL Server Migration Assistant for Access.

Using SSMA

When we install SSMA, it helps to become a familiar with the SSMA user interface before using the tool to migrate Access databases to SQL Server or Azure SQL database. The SSMA user interface, with the metadata explorers, metadata, toolbars, output pane and  also error list pane.

This migration, while a new project and then add access databases to Access Metadata Explorer to perform tasks like:

• Exporting an inventory of access database objects to sql server or Azure sql database.
• Creating reports which analyses conversions to sql server or Azure SQL database.
• By converting Access schemas to sql server or may be to Azure sql database schemas.

First, a connection is established to an instance of Sql server. After a successful connection, a hierarchy of sql appears in sql server metadata explorer. After we convert Access schemas to SQL Server Metadata Explorer and the schemas are loaded into sql server.

Whereafter loading and converting schemas into SQL Server or Azure SQL database,we can return to Access Metadata Explorer and migrate data from Access databases into SQL Server or Azure SQL databases.

The target versions of SSMA

• SQL Server 2012
• SQL Server 2014
• SQL Server 2016
• SQL Server 2017 on windows
• Azure SQL managed Instance
• Azure synapse Analytics

Installing SSMA for MySQL client

The SSMA Mysql client will consist of the program files which will perform the tasks like

1. We have to connect to a Mysql database.
2. Connect to an instance of[!INCLUDEssNoVersion]
3. We have to convert the Mysql objects to the [!INCLUDEessNoversion].
4. Load the objects into[!INCLUDEssNoversion].
5. We have to migrate the data to [!INCLUDEssNoVersion].

Before installing SSMA we have to make sure that system should meet the requirements. 

• First thing is it needs windows 7 or higher version
• [!INCLUDEmsCoName]windows Installer 3.1 or may be later version.
• MySQL ODBC 5.1 Driver and connectivity to the Mysql databases which will migrate.We can install the Mysql from the Mysql website.
• We have to access the permission from hosts the target instances of[!INCLUDEssNoversion] where we can migrate database objects and data.
• 4GB RAM.

Questions

1. What is SSMA?
2. What is the difference between SSIS and SSMA?