Public preview: Schema migration for target Azure SQL DB

Now, you can use Azure Database Migration Services (DMS) to perform schema migration while migrating to Azure SQL DB from the Azure Portal, Azure SQL Migration extension in ADS and PowerShell/CLI.

The new Schema migration capability in DMS provides an end-to-end experience to modernize SQL Server to Azure SQL Database. Prior to this feature, user needs to deploy the schema from source to target using tools like the SQL Server dacpac extension or the SQL Database Projects extension for Azure Data Studio, as prerequisite.

Prerequisites:  

1) - For schema migration to work, you must have SHIR version 5.37.8767.4 and above installed and registered with DMS services.

2) - Minimum permissions on source SQL Server is db_owner to access the database.

3) - Minimum permissions on target Azure SQL DB is the user should be member of the server level roles mentioned below:

Steps that create login and user:

-- Please run the script on Master database
CREATE LOGIN testuser with Password = '*********'   

ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER [testuser]; 
GO
 
ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER [testuser]; 
GO

ALTER SERVER ROLE ##MS_DatabaseManager## ADD MEMBER [testuser]; 
GO
 
ALTER SERVER ROLE ##MS_LoginManager## ADD MEMBER [testuser]; 
GO

CREATE USER testuser from login testuser;
EXEC sp_addRoleMember 'dbmanager', 'testuser';
EXEC sp_addRoleMember 'loginmanager', 'testuser';

Migrating Schema:

Schema migration will allow you to deploy missing schema objects (selected /complete missing Schema objects) from source to Azure SQL DB target along with data migration with just a single checkbox. While configuring the SQL migration, if schema migration is enabled, it will allow you to migrate schema objects mentioned below to target Azure SQL DB with the data migration:

• Schemas
• Tables
• Indexes
• Views
• StoredProcedures
• Synonyms
• DdlTriggers
• Defaults
• FullTextCatalogs
• PlanGuides
• Roles
• Rules
• ApplicationRoles
• UserDefinedAggregates
• UserDefinedDataTypes
• UserDefinedFunctions
• UserDefinedTableTypes
• UserDefinedTypes
• Users* (not every user type)
• XmlSchemaCollections

How to select Schema Migration option?

On Azure Portal:

To use schema migration feature, while migration to Azure SQL DB using Azure Database Migration Service from Azure Portal, user can select the "Migrate missing Schema" checkbox on the "5. Select database tables to migrate" tab, as shown below. 

Once you select this "Migrate missing Schema" checkbox, data migration for missing tables on Target will be allowed and you can choose the tables you want to migrate. If you do not select it, the missing table on Target will not be allowed to be selected for data migration.

Monitoring the Schema Migration:

After selecting the tables to migrate and starting the migration, first step would be to migrate the schema from source to target and user can monitor the status of the schema migration as highlighted and mentioned below.

• Migration Type: If user has selected Schema with data migration, then "Schema and Data migration" will be shown. If it is only data migration, then "Data migration" is shown.
• Schema migration status: Overall Schema migration status will be displayed here.

Once the Schema migration is completed, then only data migration will start as next step.  

Conclusion 

If your target is Azure SQL Database, you can migrate database Schema and data both using Database Migration Service. 

Helpful links

To troubleshoot DMS migration failing, visit https://aka.ms/dms-migrations-troubleshooting.

Known issues, visit Known issues, limitations, and troubleshooting - Azure Database Migration Service | Microsoft Learn

To learn more about DMS, visit https://azure.microsoft.com/en-us/products/database-migration/.