We're pleased to announce the preview of the MySQL Schema Migration feature for Azure Database Migration Service (DMS)! This functionality enables users to migrate the schema for objects such as tables, views, triggers, events, stored procedures, and functions. This feature is useful for automating some of work required to prepare the target database prior to starting a migration.
To complete a schema migration successfully, ensure that the following prerequisites are in place.
How to get started
To begin schema migration, start DMS, and then initiate data migration using the wizard. The options to enable schema migration are described below.
Schema Migration is supported for a wide range of different MySQL objects. A schema migration can be started from the Azure Portal. Please follow the Tutorial: Migrate MySQL to Azure Database for MySQL offline using DMS tutorial for prerequisites for setting up DMS for the MySQL migration. The schema for migration can be either selected for individual tables and all other non-table types will be migrated in full by following the steps for either object types or tables below:
If you select a table in the source database that does not exist on the target database, the box under Migrate schema is selected by default. For tables that do exist in the target database, a note indicates that the selected table already contains and will be truncated. In addition, Also, if the schema of a table on the target server does not match the schema on the source, the table will be dropped before the migration continues.
When you continue to the next tab, DMS will validate your inputs and confirm that the tables selected match if they were selected without the schema migration input. If the validation passes, you will be able to begin the migration scenario.
How schema migration works
Schema migration is supported by MySQL’s “SHOW CREATE” syntax to gather schema information for objects from the source. When migrating the schema for the objects from the source to the target, DMS processes the input and individually migrates the objects. DMS also wraps the collation, character set, and other relevant information that is provided by the “SHOW CREATE” query to the create query that is then processed on to the target.
Routines and Events are migrated before any data is migrated. The schema for each individual table is migrated immediately prior to data movement starting for the table. Triggers are migrated after the data migration portion. For views, since MySQL validates the contents of views and they can depend on other tables, DMS first creates tables for views before the start of database data movement and then drops the temporary table and creates the view.
When querying the source and target, if a transient error occurs, DMS will retry the queries. However, if an error occurs that DMS cannot recover from – as an example, an invalid syntax when performing a version upgrade migration – DMS will fail and report that error message on completion. If the failure occurs when creating a table, the data for that table will not be migrated, but the data and schema migration for the other selected tables will still be attempted. If an unrecoverable error occurs for events, routines, or when creating the temporary table for views, the migration will fail prior to running the migration for the selected tables and the objects that are migrated following the data migration portion.
Since a temporary table is created for views, if there is a failure migrating a view, the temporary table will be left on the target. After the underlying issue is fixed and the migration is retried, DMS will delete that table prior to creating the view. Alternatively, if electing not to use schema migration for views in a future migration, the temporary table will need to be manually deleted prior to manually migrating the view.
You can also find detailed information on MySQL migration to Azure Database for MySQL using DMS here: Migrate MySQL to Azure Database for MySQL offline using DMS.
In future releases, we'll include support for additional migration features, so stay tuned!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.