In this article we understand the Data Migration Assistant in SQL Server and how it helps when upgrading a SQL Server instance to Azure.
The Data Migration Assistant in SQL Server helps detect compatibility issues between the data platform in use, the more recent edition of SQL Server, and Azure databases, that you might be willing to update to. On the basis of this detection, it suggests the user a modern data platform to which they should update for better functionality of the database. Along with this suggestion, it also recommends the user regarding the performance improvements that will be needed for operating in the target environment. Thus allowing you to shift schemas, data, as well as all the uncontained objects from the current server to the target server. Given below are all the capabilities of the feature.
All the on premises SQL Server instances that are to be migrated to Azure SQL databases are assessed for any potential issues that may affect the migration process. Along with the migration blocking issues, it is also able to detect which features are partially supported, and which ones are completely unsupported in the SQL server instance being used. Once these issues are detected, DMA also helps users with providing solution for dealing with them. The provided solutions contains multiple approaches, recommendations, and mitigation strategies.
DMA is able to discover issues that not only disrupt the migration process, but also those that disrupt the on premises upgrade process for SQL Server. These are called the compatibility issues and are ranked in the given order.
- Breaking Changes
- Behavior Changes
- Deprecated Changes
Discovering New Features
Along with discovering compatibility and other issues, DMA can also discover the new features that are present in the target SQL platform, and will prove to be beneficial for the database, once the upgrade is done. These are also known as feature recommendations and are ranked in the following order.
Migrating On premises Instance to Modern Instance
An on premises SQL Server instance can be migrated to a compatible and modern instance that is either hosted on Azure Virtual Machine (AVM), or on premises, and can also be accessed using the on premises network that is in use. You can access the AVM through VPN, and other technologies, as for the migration workflow, it can be used for migrating the components given below.
- Data and Users
- Server roles
- Database schema
- SQL Server logins and Windows Logins
Once the process of migration is complete applications can successfully establish the connection with target SQL databases. Making use of the DMA feature to ensure that the SQL Server edition in use is upto date and compatible with the platform it is supposed to be used with is crucial for a flawless user experience. Incompatibility between the two will not only lead to a non-user friendly atmosphere, but might also end up being the cause of an unexpected disaster. To ensure that you so not lose data due to one silly mistake, always make use of the Data Migration Assistant when to planning to migrate SQL Server databases. In the event you suspect data loss, run a specialized mdf repair tool.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover access and sql recovery software products. For more information visit https://www.datanumen.com/