Microsoft has announced that Azure Database Migration Service (DMS) now includes a generally available schema migration feature that simplifies moving data by automatically including schemas, tables, indexes, views and other database objects. With this feature, Microsoft hopes to reduce manual effort and ensure consistency between source and target databases.
This integrated approach means that administrators do not need to manually create each database object anymore, whether schemas, indexes, or stored procedures, on the new Azure SQL Database before migrating over the data. This will help to save significant time and drastically reduce the chance of human error, which can be a pain point in complex migrations.
The new schema migration feature also allows users to migrate missing schema objects along with either selected tables or all tables in a single step. The Redmond giant also points out that the feature is specifically for migrations to Azure SQL Database.
The new feature has strict prerequisites; for example, the user must have SHIR version 5.37.8767.4 or above installed and the source SQL Server requires db_owner permissions for extensive access. The announcement also says that the target Azure SQL DB user must be a member of four specific server-level roles, including ##MS_DatabaseManager## and ##MS_LoginManager##, which gives the user power to create and delete databases and logins.
The previously mentioned permissions are a prerequisite for the feature to work. It recommends using the ##MS_DatabaseManager## server role over the dbmanager database-level role. Once granted, these permissions allow users to manage logins, create databases, and read all catalog views.
Microsoft doesn’t mention how long you’ll need to provide high-level permissions, but they’ll likely be able to be revoked after migration. This can help to reduce any security incidents that could occur from granting extensive permissions. If you want to learn more about using this feature, check out the instructions in Microsoft"s announcement.
Image via Depositphotos.com