Database Migration from on-prem to Azure SQL

Why should you migrate your on-prem database to cloud: 

Here are following factors, you would consider when migrating an on-prem database to cloud:

• You want to modernize current IT asset base.
• You want to prepare your database platform for future needs.
• You want to lower your database infrastructure costs.
• You want to increase Business Agility.

Migration Phases:

If you are migrating a mission critical database to cloud, you would need to plan it carefully. Following plan covers most of the different migration phases:

Server/Database Build on Azure:

Every organisation has their own standards, however you can consider following best practices to build your server/database on Azure:

• Provision Azure SQL as per performance baseline.
• Use following link to calculate the DTU required on Azure managed Instance.      https://dtucalculator.azurewebsites.net/
• New provisioning option, vCore can be used to map CPU with on-prem servers.
• Restrict SQL Logins and database hardening for better security.
• Setup maintenance jobs as per Microsoft best practices using Azure Automation.
• Improve database performance with optimal configuration.
• Migrate the database swiftly with minimal business impact (within agreed downtime).
• Setup proactive monitoring on servers for efficient Event Management.
• Environment failure testing (Acceptance Testing) : Execute all possible environment failure scenarios to simulate the failure/disaster situations in controlled way and document their fixes and behavior for operational maturity.

Different Database Migration methods:

Here are different database migration methods. You can choose most suitable option as per your database availability and business requirements.

• With Downtime
  • Migration using Data Migration Assistant
  • Migration with Backup and Restore using Azure Blob
  • Migration with Import and Export Data Tier using SSMS
  • Deploy Database from On-prem to Azure SQL Directly through SSMS
• Without Downtime
  • Migration using Azure DMS (Data Migration Service)

Brief summary of each Database migration method:

Migration using Data Migration Assistant:

• Evaluate if database is ready to migrate and produce a readiness report
• Provide recommendations for how to remediate migration blocking issues
• Recommend the minimum Azure SQL Database SKU based on performance counter data of existing database
• Perform the actual migration of schema, data and objects (server roles, logins, etc.)
• Useful for small and less critical databases
• Downtime is required
• SQL Database 2012+ supported.

Migration with Backup and Restore using Azure Blob:

• Use Database Migration Assistant tool “Assessment” for following reasons:
  • Detects compatibilities issues
  • Recommends performance and reliability improvements
• Then, use Backup and Restore database using Azure Blob-
  • Ease of Use -Traditional way to migrate the databases using native backups and restores
  • Migration of schema, data and objects
  • Useful for multiple small/big databases
  • Downtime is required
  • Direct migration from SQL Server 2005+ databases

Migration with Export and Import Data Tier using SSMS:

• First, use Database Migration Assistant tool for “Assessment” for following reasons:
  • Detects compatibilities issues
  • Recommends performance and reliability improvements
• Then, use Import and Export Data Tier using SSMS:
  • Migration using bacpac files
  • Migration of schema, data and objects
  • Useful for small databases and less critical databases
  • Downtime is required

Deploy Database from On-prem to Azure SQL Directly through SSMS:

• First, use Database Migration Assistant tool for “Assessment” for following reasons:
  • Detects compatibility issues
  • Recommends performance and reliability improvements
• Then, use Deploy Database from On-prem to Azure SQL Directly through SSMS:
  • Direct migration from On premise server to Azure SQL
  • Migration of schema, data and objects
  • Useful for small databases and less critical databases
  • Downtime is required

Migration using Azure DMS (Data Migration Service):

• Fully Managed Service for Seamless Migrations
• Leverages Data Migration Assessment tool for following assessment
  • Detects compatibility issues
  • Recommends performance and reliability improvements
• Fire and Forget Migration
• Migration of schema, data and objects
• Useful for big databases and business critical databases
• No or less downtime is required
• Multiple Database engine supported

Benefits of Database Migration on Azure:

Here are the following benefits of migrating an on-prem database to Cloud:

• Flexibility and scalability.
• Improved supportability.
• Increased up-time.
• Enabler for improved RPO (Recovery point objective) & RTO (Recovery time objective).
• Excellent Performance in latest version of SQL Server.
• Better Security.
• Improved Index Management.

Potential challenges of Database Migration on Azure:

While your specific environment will determine the challenges that apply to you, there are some general drawbacks associated with database migrations on cloud that you will want to consider.

• Compliance requirements migrating your sensitive data on cloud.
• Latency when using database on clouds.
• If your hardware is controlled by someone else, you might lose some transparency and control when debugging performance issues.
• Shared resources which might impact database performance.
• Uncontrolled downtime.

I tried to cover all the important factors involved in a database migration, please leave a comment if you have any feedback or question.

Advertisement