Skip to content

Latest commit

 

History

History
73 lines (50 loc) · 5.54 KB

mapping-mysql-databases-to-sql-server-schemas-mysqltosql.md

File metadata and controls

73 lines (50 loc) · 5.54 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom f1_keywords helpviewer_keywords
Mapping MySQL Databases to SQL Server Schemas (MySQLToSQL)
Learn to customize SSMA for MySQL mappings between MySQL schemas and SQL Server or Azure SQL Database or accept the default.
cpichuka
cpichuka
01/19/2017
sql
ssma
conceptual
sql-migration-content
ssma.mysql.schemamappingpanel.f1
ssma.mysql.typemappingdatagridview.f1
Mapping, Modifying target database and schema
Mapping, reverting to default database and schema

Mapping MySQL Databases to SQL Server Schemas (MySQLToSQL)

By default, SSMA for MySQL migrates all objects in a MySQL schema to a [!INCLUDEssNoVersion] or Azure SQL Database named for the schema. However, you can customize the mapping between MySQL schemas and [!INCLUDEssNoVersion] or Azure SQL Database.

MySQL and SQL Server or SQL Azure Schemas

The MySQL concept of a schema maps to the SQL Server concept of a database and one of its schemas. SSMA refers to the SQL Server combination of database and schema as a schema.

The MySQL concept of a schema maps to the SQL Server concept of a database and one of its schemas. For example, MySQL might have a schema named HR. An instance of SQL Server might have a database named HR, and within that database are schemas. One schema is the dbo (or database owner) schema. By default, the MySQL schema HR will be mapped to the [!INCLUDEssNoVersion] database and schema HR.dbo. SSMA refers to the [!INCLUDEssNoVersion] combination of database and schema as a schema.

You can modify the mapping between MySQL and [!INCLUDEssNoVersion] or Azure schemas.

Modifying the Target Database and Schema

In SSMA, you can map a MySQL schema to any available [!INCLUDEssNoVersion] or SQL Azure schema.

To modify the database and schema

  1. In MySQL Metadata Explorer, select Schemas.

    The Schema Mapping tab is also available when you select individual schemas. The list in the Schema Mapping tab is customized for the selected object.

  2. In the right pane, click the Schema Mapping tab.

    You will see a list of all MySQL schemas, followed by a target value. This target is denoted in a two part notation (database.schema) in [!INCLUDEssNoVersion] or SQL Azure where your objects and data will be migrated.

  3. Select the row that contains the mapping that you want to change, and then click Modify.

    In the Choose Target Schema dialog box, you may browse for available target database and schema or type the database and schema name in the textbox in a two part notation (database.schema) and then click OK.

  4. The target changes on the Schema Mapping tab.

Modes of Mapping

  • Mapping to SQL Server

You can map source database to any target database. By default source database is mapped to target [!INCLUDEssNoVersion] database with which you have connected using SSMA. If the target database being mapped is non-existing on [!INCLUDEssNoVersion], then you will be prompted with a message "The Database and/or schema does not exist in target [!INCLUDEssNoVersion] metadata. It would be created during synchronization. Do you wish to continue?" Click Yes. Similarly, you can map schema to non-existing schema under target [!INCLUDEssNoVersion] database which will be created during synchronization.

  • Mapping to SQL Azure

You can map the source database to the connected target [!INCLUDEssNoVersion] database or to the any schema in the connected target [!INCLUDEssNoVersion] database. If you map source Schema to any non-existing schema under connected target database, then you will be prompted with a message "Schema does not exist in target metadata. It would be created during synchronization. Do you wish to continue? " Click Yes.

Reverting to the Default Database and Schema

If you customize the mapping between a MySQL schema and a SQL Server schema, you can revert the mapping back to the default values.

To revert to the default database and schema

  1. Under the schema mapping tab, select any row and click Reset to Default to revert to the default database and schema.

Next Steps

If you want to analyze the conversion of MySQL objects into SQL Server or SQL Azure objects, you can create a conversion report otherwise you can Convert the MySQL database object definitions into SQL Server or SQL Azure schemas

See Also

Project Settings (Conversion) (MySQLToSQL)
Connecting to Azure SQL Database (MySQLToSQL)
Migrating MySQL Databases to SQL Server - Azure SQL Database (MySQLToSQL)
Connecting to SQL Server (MySQLToSQL)