An offline migration refers to the process where the migration occurs during a period when the application or system using the database is not running or is unavailable. This means there can be substantial downtime, which is usually undesirable in production environments. However, the technique is simpler and less risky compared to an online migration because there are fewer variables to consider during the migration process.
To illustrate the offline migration process, here is a simplified sequence in relation to Azure SQL:
- Stop the application/system that uses the database.
- Backup the data from the source database and save it into a format that Azure SQL can recognize.
- Transfer the backup files to Azure Blob Storage.
- Use Azure SQL Migration Service or SQL Server Management Studio (SSMS) to import the data into the Azure SQL Database.
- Restart the application/system.
An offline migration is most suitable when:
- The application downtime can be tolerated.
- The database is relatively small, and migration time is predictable.
- The application is less complex, or there’s a significant risk or technical challenge associated with an online migration.
Online Migration Strategy
In contrast, an online migration means the application remains available during the migration process. Changes made to the source database during the migration are tracked and replicated to the target database, ensuring very minimal downtime. Understandably, this strategy is more complex and could potentially be more risky if not correctly managed.
Here is a typical process for an online migration:
- Configure an instance of Azure Data Migration Service to connect to both the source and destination databases.
- Initiate the migration, starting with a full database schema migration, then followed by continuous data sync.
- Perform a validation check to ensure the accuracy of the migrated data.
- Once satisfied, switch the application connection to the new database.
An online migration strategy is suitable when:
- Minimal downtime is a must.
- The application has to be available 24/7.
- The database size is large, or unpredictable transfer rates may prolong migration times.
Table for Comparison
| Offline Migration | Online Migration | |
|---|---|---|
| Downtime | High | Minimal |
| Complexity | Low | High |
| Risk Level | Low | High depending on complexity |
| Best for | Predictable small scale migrations | Large scale migrations requiring application’s constant availability |
Conclusion
Choosing between offline and online migration strategy largely depends on the specific needs and constraints of your business. Each strategy has its pros and cons. However, Microsoft Azure offers tools and services that can help to minimize the risks and make both migration strategies more manageable. For instance, Azure’s Data Migration Service (DMS) supports both offline and online migrations and provides guidance throughout the process, ensuring a more secure and efficient migration path.
Practice Test
True or False: Azure Database Migration Service (DMS) can be used to perform offline (one-time) migrations of databases to Azure.
- True
- False
Answer: True.
Explanation: Azure DMS supports both offline and online migration, it’s a fully managed service designed to enable seamless migrations from multiple database sources to Azure Data platforms.
During an online migration, the application remains operational during the migration process.
- True
- False
Answer: True.
Explanation: Online migrations allow for minimal downtime. While the migration takes place, the application remains online, continuing to access the source database until the migration is complete.
Does Azure DMS support migrating from SQL Server to Azure SQL Database or managed instance both offline and online?
- True
- False
Answer: True.
Explanation: Azure DMS supports numerous source-target pairs with the ability to migrate while the application continues to run, minimizing downtime.
Multiple Select: Which services can be used to assist in offline or online database migration to Azure?
- A. Azure Migrate
- B. Azure Site Recovery
- C. Azure DMS
- D. Azure DevOps
Answer: A, B, and C.
Explanation: All of these services support database migration to Azure, but Azure DMS is specifically designed for this purpose and Azure Migrate and Site recovery also enable database migration as part of a broader set toolkits for moving to Azure.
Which Migration strategy is best for small databases or when the applications can afford to be offline during the migration process?
- A. Offline Migration
- B. Online Migration
- C. Hybrid Migration
- D. No Migration
Answer: Offline Migration
Explanation: Offline migrations are simpler and often quicker because the data is simply copied over to Azure in one go. However, it does cause downtime for the application.
Multiple Select: Which features should be considered while evaluating online migration?
- A. Minimal downtime
- B. Consistent copy of data
- C. Complex setup
- D. Lower cost
Answers: A, B, and C.
Explanation: While online migration minimizes downtime and ensures consistent data, it can have a more complex setup due to the need to synchronize the data in real-time.
True or False: Azure DMS supports online migrations only from MySQL to Azure SQL Database.
- True
- False
Answer: False.
Explanation: Azure DMS supports a wide range of source-target pairs including migrations from MySQL, PostgreSQL, SQL Server and others to various Azure data platforms.
Which Migration strategy is more suitable when you can’t afford a long downtime and you have a large database to move to Azure?
- A. Offline Migration
- B. Online Migration
- C. Hybrid Migration
- D. No Migration
Answer: Online Migration.
Explanation: Online migration is implemented through ‘near-real-time’ replication which allows the source and target databases to be in sync, thereby minimizing downtime and is more suitable for large databases.
Single select: To complete an offline or online migration to Azure SQL Database, what permission should the server admin account have?
- A. db_datareader
- B. db_datawriter
- C. db_owner
- D. db_ddladmin
Answer: C. db_owner.
Explanation: The server admin account requires db_owner permissions to complete the migration, which extends to almost all administrative actions.
True or False: The offline migration only allows data transfer to Azure SQL Database.
- True
- False
Answer: False.
Explanation: Offline migration process can be used to migrate databases to Azure SQL Database, Managed Instance, and Azure PostgreSQL and MySQL as well.
In an online migration, after the full load migration, the sync of data changes on the source server with the target server is not required.
- True
- False
Answer: False.
Explanation: In an online migration, after the full data migration, it’s important to keep syncing data changes until the cutover is performed to minimize data loss.
Interview Questions
What is an offline migration strategy in the context of Microsoft Azure?
An offline migration strategy, otherwise known as a one-time migration, involves moving data from the source database to the destination database at once. During the process, the source database may be unavailable or read-only.
What is an online migration strategy in Microsoft Azure?
An online migration allows migrations to occur while the source database remains available so that applications can continue to function during the migration period.
Which Microsoft Azure service is used for performing online migrations?
Azure Database Migration Service (DMS) is used for performing online migrations.
Which migration strategy would you use if your business can’t afford downtime during the migration process?
If the business can’t afford a downtime, the online migration strategy should be used because it allows for continuous usage of the source database during migration.
What is a scenario where offline migration strategy might be a better fit than online migration?
The offline migration strategy might be a better fit when there is a large amount of non-critical data to migrate but a limited network bandwidth. This strategy can also be useful when allowable downtime is possible.
In the context of Azure SQL, what is Data Migration Assistant (DMA)?
The Data Migration Assistant (DMA) is a tool from Microsoft Azure that helps in assessing the readiness of SQL Server databases for migration to Azure SQL Database.
Which SQL Server versions can you migrate using Azure Database Migration Service?
With Azure Database Migration Service, you can migrate SQL Server versions 2005 and newer to Azure SQL Database.
Can Azure Database Migration Service do a Target-Driven migration?
No, Azure Database Migration Service works on a Source-Driven migration strategy, where the source is read and the relevant aspects are applied to the target.
How does Azure’s database migration service ensure data integrity during an online migration?
During an online migration, Azure’s database migration service ensures data integrity by continually checking for changes at the source and replicating these changes at the destination until the migration is completely done.
How can Transactional Replication be used in the Azure migration process?
Transactional Replication can be used as a migration solution for moving data from SQL Server to Azure SQL database. This could be useful for workload migration scenarios requiring low impact, minimum downtime, and the ability to fall back.
In the Azure SQL migration process, what is the purpose of a post-migration validation?
A post-migration validation is used to ensure the successful completion of migration by checking whether data at the destination instance matches data at the source instance.
In what scenario would you use the Azure Database Migration Service over the Data Migration Assistant for SQL migration?
Azure Database Migration Service would be used over Data Migration Assistant when there is a requirement for minimal downtime, for instance, migrating production databases.
What is the Migration Activity type “Offline” in Database Migration Service?
The Migration Activity type “Offline” in Database Migration Service represents a migration activity that induces downtime at the start of the migration.
Can data consistency issues occur after using Azure Database Migration Service for online migrations?
No, Azure Database Migration Service ensures that data remains consistent by continually tracking changes at source during migration so no data inconsistency occurs.
Can Azure Database Migration Service migrate from cross-platform databases like Oracle or MongoDB?
Yes, Azure Database Migration Service supports cross-platform migrations and can migrate from Oracle, MongoDB, and other databases to Azure services.
extutorials.com