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:

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.

Migrationa

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

Migrationa

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

Migrationa

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

Migrationa

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

Migrationa

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.

14 thoughts on “Database Migration from on-prem to Azure SQL

Add yours

  1. Nice article..a must read for those who are looking to move away from traditional expensive on-prim infrastructure.👍

    Like

  2. Really nice one Sumit.
    Its very great to see a brief and quick description of all these different migration approaches on one page. It can really help people to compare and choose the one best suited for them.

    Like

  3. Aw, this was an incrediƅly nice рost. Ⴝpending some timе and actual effort to create a good article…

    but what can I ѕay… I hesitate a lot and don’t seem to
    get anything done.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: