A
abku14
Now, you can use Azure Database Migration Services (DMS) to perform schema migration while migrating to Azure SQL DB from the Azure Portal.
The new Schema migration capability in the Azure Portal for 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.34.8675 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:
-- 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';
Note: Currently it is available in DMS via the Azure Portal.
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:
How to select Schema Migration option?
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 below. 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 via Azure Portal.
Helpful links
To troubleshoot DMS migration failing, visit Known issues, limitations, and troubleshooting - Azure Database Migration Service.
To learn more about DMS, visit Azure Database Migration Service | Microsoft Azure.
Continue reading...
The new Schema migration capability in the Azure Portal for 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.34.8675 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';
Note: Currently it is available in DMS via the Azure Portal.
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?
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 below. 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 via Azure Portal.
Helpful links
To troubleshoot DMS migration failing, visit Known issues, limitations, and troubleshooting - Azure Database Migration Service.
To learn more about DMS, visit Azure Database Migration Service | Microsoft Azure.
Continue reading...