Microsoft Data Migration Blog > 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:
| Roles | Description |
|---|---|
| ##MS_DatabaseManager## | Members of the ##MS_DatabaseManager## fixed server role can create and delete databases. A member of the ##MS_DatabaseManager## role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the ##MS_DatabaseManager## role don’t necessarily have permission to access databases that they don’t own. It’s recommended to use this server role over the dbmanager database level role that exists in master. |
| ##MS_DatabaseConnector## | Members of the ##MS_DatabaseConnector## fixed server role can connect to any database without requiring a User-account in the database to connect to |
| ##MS_DefinitionReader## | Members of the ##MS_DefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY DEFINITION, respectively VIEW DEFINITION on any database on which the member of this role has a user account. |
| ##MS_LoginManager## | Members of the ##MS_LoginManager## fixed server role can create and delete logins. It’s recommended to use this server role over the loginmanager database level role that exists in master. |
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
- Sequence
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.
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/.
Azure Aggregator 