Migrating SQL Server to Azure

October 15, 2026
| No Comments

1. Planning

Objectives and Strategy:

  • Define the business objectives and desired outcomes.
  • Determine the migration strategy (e.g., re-host, refactor, re-architect, rebuild, or replace).

Inventory and Assessment:

  • Inventory the existing SQL Server instances and databases.
  • Assess the compatibility and performance requirements.

Governance and Compliance:

  • Make sure compliance with data governance policies.
  • Plan for security and compliance in Azure.

2. Assessment

Tools:

  • Use tools like Microsoft Azure Migrate, Data Migration Assistant (DMA), and Azure Database Migration Service (DMS) to assess on-premises SQL Servers.

Compatibility:

  • Identify any compatibility issues and resolve them.
  • Assess database size, performance characteristics, and dependencies.

Cost Analysis:

  • Estimate the cost of running SQL Server workloads in Azure.

3. Migration

Choosing the Migration Path:

  • Lift and Shift (IaaS): Move SQL Server to Azure Virtual Machines (VMs) for minimal changes.
  • PaaS: Migrate to Azure SQL Database or Managed Instance for fully managed database services.

Backup and Restore:

  • Backup the on-premises databases and restore them in Azure.

Data Migration:

  • Use Azure DMS for data migration with minimal downtime.
  • Perform data validation and integrity checks post-migration.

Testing:

  • Conduct thorough testing to make sure all databases and applications function correctly in Azure.
  • Perform performance testing to validate SLAs.

4. Optimisation

Performance Tuning:

  • Optimise database performance using Azure’s monitoring and tuning tools.
  • Adjust resources based on performance and usage patterns.

Security:

  • Implement Azure security best practices.
  • Use features like Advanced Threat Protection, data encryption, and network security groups.

Monitoring and Management:

  • Set up Azure Monitor and Azure SQL Analytics for continuous monitoring.
  • Use Azure Automation for regular maintenance tasks.

Cost Management:

  • Use Azure Cost Management to monitor and optimise spending.
  • Consider using reserved instances for cost savings.

Additional Considerations

High Availability and Disaster Recovery:

  • Implement high availability using Always On Availability Groups or Azure SQL Database’s built-in features.
  • Set up disaster recovery plans with geo-replication and automated backups.

Training and Support:

  • Train your team on Azure services and tools.
  • Plan for ongoing support and management.

Tools and Services

  • Azure Migrate: For discovery, assessment, and migration.
  • Data Migration Assistant (DMA): For assessment and minor migrations.
  • Azure Database Migration Service (DMS): For large-scale migrations with minimal downtime.
  • Azure Site Recovery: For VM migrations.
  • Azure Monitor: For monitoring performance and availability.
  • Azure Security Centre: For security management and threat protection.

Summary

Moving SQL Server from on-premises to Azure needs planning, a proper assessment and careful execution. Azure’s own migration tools take a lot of the work out of it.

1. Azure SQL Database

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most database management functions such as upgrading, patching, backups, and monitoring without user involvement.

Benefits:

  • Automatic backups and updates.
  • Built-in high availability and disaster recovery.
  • Scaling capabilities based on performance needs.
  • Advanced security features.

Considerations:

  • Best for modern applications that require continuous availability.
  • Limited access to SQL Server features compared to on-premises SQL Server.
  • Suitable for applications with predictable usage patterns.

2. Elastic Pools

Elastic pools are a cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. They allow multiple databases to share a pool of resources.

Benefits:

  • Cost savings by sharing resources among multiple databases.
  • Simplified management of multiple databases.
  • Automatic scaling of resources based on demand.

Considerations:

  • Ideal for SaaS applications, micro services architectures, and multi-tenant applications.
  • Resource consumption must be monitored to avoid performance issues.

3. Managed Instance

Managed Instance is a fully managed service that offers near-complete SQL Server compatibility, providing a smooth migration path for existing SQL Server instances to Azure.

Benefits:

  • High compatibility with SQL Server (e.g., SQL Agent, CLR, cross-database queries).
  • VNet support for network isolation.
  • Built-in high availability and automated backups.

Considerations:

  • Best for migrating large numbers of existing on-premises databases with minimal changes.
  • More expensive compared to single databases and elastic pools.
  • Requires planning for VNet setup and integration with existing infrastructure.

Migration Steps

  • Assessment and Planning:
    • Use the Azure Migrate service to assess your on-premises databases.
    • Determine the target environment (Single Database, Elastic Pool, and Managed Instance).
  • Preparation:
    • Make sure your databases are compatible with the target Azure SQL service.
    • Plan for downtime during the migration process.
  • Migration:
    • Use Data Migration Assistant (DMA) to identify potential issues and recommend changes.
    • Use Azure Database Migration Service (DMS) to migrate with minimal downtime.
  • Post-Migration:
    • Test thoroughly to make sure all applications are functioning correctly.
    • Monitor performance and adjust resource allocations as needed.

Tools and Services

  • Azure Migrate: Assess and migrate your on-premises databases to Azure.
  • Data Migration Assistant (DMA): Assesses compatibility issues and provides recommendations.
  • Azure Database Migration Service (DMS): Assists in the actual migration process.
  • Azure Monitor: Provides insights and monitoring of database performance and health.
  • Azure Cost Management: Helps in tracking and managing costs.

Best Practices

  • Performance Testing: Perform load testing to make sure the migrated database meets performance expectations.
  • Backup Strategy: Have a tested backup and restore plan.
  • Security: Make sure all security practices (e.g., encryption, firewalls) are adhered to in the new environment.
  • Monitoring: Set up monitoring and alerting to detect and address issues promptly.

From my SQL Server DBA handbook.

Setup