Optimize Azure Log Costs: Split Tables and Use the Auxiliary Tier with DCR
August 4, 2025Koulutusinfoa kumppaneille 3.9. klo 17 EMEA FY26 Partner Skilling Connection
August 4, 2025
We’re thrilled to announce that Schema Migration support in Azure Database Migration Service (DMS) is now generally available (GA)! This milestone marks a significant leap forward in simplifying and accelerating your journey to the cloud.
Introduction:
With the general availability of schema migration in Azure Database Migration Service (DMS), users can now deploy missing schema objects along with either selective or all Tables from the source database to the target Azure SQL Database with a single step. By enabling the schema migration checkbox during SQL migration setup, the service automatically includes the following schema components in the migration process alongside the data:
- Schemas
- Tables
- Indexes
- Views
- Stored Procedures
- Synonyms
- DDL Triggers
- Defaults
- Full-Text Catalogs
- Plan Guides
- Roles
- Rules
- Application Roles
- User-Defined Aggregates
- User-Defined Data Types
- User-Defined Functions
- User-Defined Table Types
- User-Defined Types
- Users (limited user types)
- XML Schema Collections
This enhancement streamlines the migration workflow, reduces manual effort, and ensures consistency between source and target environments.
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. |
Selecting the Schema Migration
1) In Azure Portal
To enable schema migration when migrating to Azure SQL Database using Azure Database Migration Service (DMS) via the Azure Portal, follow these steps:
- In DMS migration wizard, navigate to the “5. Select database tables to migrate” tab during the migration configuration process.
- On this tab, locate and select the checkbox labeled “Migrate missing schema”. As shown below:
By enabling this option, the service will automatically include missing schema objects from the source database in the migration to the target Azure SQL Database, alongside the data migration.
For more detailed steps, click here..
2) PowerShell, Az cmdlets:
- PowerShell: New-AzDataMigrationSqlServerSchema (Az.DataMigration) | Microsoft Learn
- Az cmdlets: az datamigration | Microsoft Learn
Conclusion
If your target environment is Azure SQL Database, you can leverage Azure Database Migration Service (DMS) to perform a comprehensive migration that includes both database schema and data. This integrated capability ensures that all essential schema objects—such as tables, views, stored procedures, and user-defined types—are accurately deployed to the target Azure SQL Database alongside the actual data. By enabling the schema migration option during configuration, users can streamline the migration process, reduce manual intervention, and maintain consistency between source and target environments.