SQL Migration to SQL Server on Azure Virtual Machines in Azure Architecture
By: Date: 27/07/2026 Categories: azure Tags:

SQL Migration to SQL Server on Azure Virtual Machines (Azure Architecture)

Migrating SQL databases to SQL Server on Azure Virtual Machines (VMs) allows you to maintain full control over the SQL Server environment while leveraging Azure’s scalability, security, and managed infrastructure. Below is a comprehensive guide covering planning, execution, and optimization within the Azure architecture.

1. Planning & Assessment

A. Inventory & Requirements

  1. Current Environment Assessment:
  • SQL Server Version: Ensure compatibility with Azure-supported versions (e.g., SQL Server 2012 SP3 or later).
  • Database Size: Estimate data size, transaction volume, and growth projections.
  • Workload Type: OLTP, OLAP, Reporting, etc.
  • Dependencies: Linked servers, SSIS packages, jobs, triggers, and applications.
  1. Performance Baseline:
  • Capture CPU, memory, disk I/O, and network usage.
  • Identify peak load times and latency requirements.
  1. License & Compliance:
  • Decide between Bring Your Own License (BYOL) or Pay-As-You-Go licensing.
  • Ensure compliance with regulatory standards (e.g., GDPR, HIPAA).

B. Azure VM Sizing

  • VM Series: Choose based on workload:
  • General Purpose: Ds2_v3, Ds3_v3 (balanced CPU/Memory).
  • Compute Optimized: Fs series (for high CPU workloads).
  • Memory Optimized: Mv2, M series (for in-memory OLTP or large memory needs).
  • Storage:
  • Disks: Use Azure Premium SSD or Ultra Disk for high IOPS/throughput.
  • Storage Account: For backups and logs (consider Azure Managed Disks).
  • Network: Enable Accelerated Networking for reduced latency.

2. Migration Strategies

A. Backup & Restore (Simple & Common)

  1. Generate Backup:
  • Take full/differential backups of source databases.
  1. Upload to Azure Storage:
  • Use azcopy or Azure Storage Explorer to upload backups to an Azure Blob Storage account.
  1. Restore on Azure VM:
  • Install SQL Server on the Azure VM.
  • Restore the backup files from Blob Storage to the VM’s disks.

Pros: Simple, minimal downtime.
Cons: Longer for large databases; downtime equals restore time.

B. Azure Database Migration Service (DMS) – Minimal Downtime

  1. Setup DMS:
  • Create a migration project in Azure DMS.
  • Select SQL Server to SQL Server as the migration scenario.
  1. Configure Source & Target:
  • Source: On-premises SQL Server.
  • Target: SQL Server on Azure VM.
  1. Enable Continuous Data Replication:
  • Data is replicated continuously to the target.
  1. Cutover:
  • Perform final data sync and switch application connections.

Pros: Near-zero downtime, validated during replication.
Cons: Requires DMS setup and network connectivity (ExpressRoute or VPN).

C. Azure Migrate (For Entire VM Migration)

If migrating the entire SQL Server VM (not just the database):

  1. Enable Azure Migrate:
  • Deploy the Azure Migrate Appliance on-premises.
  1. Discover & Assess:
  • Scan on-premises VMs and recommend Azure VM size.
  1. Migrate:
  • Replicate the VM to Azure, then perform a cutover.

Pros: Migrates OS, SQL config, and data in one go.
Cons: Requires agent installation; more complex for pure DB migration.

D. In-Place Upgrade (If Feasible)

  • Upgrade SQL Server version directly on the Azure VM.
  • Use for minor version upgrades (e.g., 2016 → 2019) but not for cross-platform moves.

3. Configuration on Azure VM

A. SQL Server Setup

  1. Install SQL Server:
  • Use Azure Marketplace SQL Server images for automated deployment.
  • Select edition (Express, Standard, Enterprise).
  1. Configure Instances:
  • Set MAXDOP, memory limits, and cost thresholds.
  • Enable In-Memory OLTP if needed.

B. Storage Configuration

  • Disk Layout:
  • Separate disks for DATA, LOGS, TEMPDB, and BACKUPS.
  • Example:
    • Data: D: on Premium SSD (e.g., P30, 5000 IOPS).
    • Logs: L: on Premium SSD (e.g., P20).
  • Optimize:
  • Use Striping (RAID 0) for larger IOPS.
  • Enable Write-Cache Buffer on Ultra Disks.

C. Networking

  • NSG Rules:
  • Allow inbound ports: 1433 (SQL), 3389 (RDP), 5985/5986 (PowerShell REMI).
  • Private Endpoints:
  • Use Private Link to secure connectivity without public IPs.
  • Load Balancer (Optional):
  • For Always On Availability Groups.

4. Security & Compliance

A. Authentication & Authorization

  • Azure AD Authentication:
  • Enable SQL Server Authentication with Azure AD identities.
  • Firewalls:
  • Restrict access via IP Rules and NSGs.
  • Encryption:
  • In-Transit: Use TLS 1.2+.
  • At-Rest: Enable Transparent Data Encryption (TDE).

B. Backup & Recovery

  • Azure Backup:
  • Schedule backups via SQL Agent or Azure Backup Agent.
  • Retain point-in-time recovery (e.g., daily full + hourly log backups).
  • Recovery Points: Test restore procedures regularly.

C. Monitoring & Auditing

  • Azure Monitor:
  • Collect metrics (CPU, memory, disk I/O) and logs.
  • SQL Audit:
  • Track access patterns and changes.
  • Azure Security Center: For vulnerability assessments.

5. Post-Migration Validation & Optimization

A. Validation

  1. Data Integrity:
  • Run DBCC CHECKDB.
  1. Application Testing:
  • Update connection strings to point to the new VM’s endpoint.
  1. Performance Baseline:
  • Compare pre/post-migration metrics.

B. Optimization

  • Index Tuning: Use Database Engine Tuning Advisor.
  • Query Performance: Analyze slow queries with Extended Events.
  • Resource Governance: Implement Resource Governor for workload isolation.

C. High Availability & Disaster Recovery

  • Always On Availability Groups:
  • Deploy across multiple Azure regions.
  • Azure Site Recovery:
  • Replicate VMs for DR scenarios.

6. Cost Optimization

  1. Right-Size VMs:
  • Use Azure Cost Management to analyze usage.
  • Scale down during off-peak hours (if applicable).
  1. Reserved Instances:
  • Save up to 72% vs. pay-as-you-go for predictable workloads.
  1. Auto-Shutdown:
  • Use Azure Automation to stop VMs outside business hours.

7. Alternatives to Consider

OptionWhen to Use
Azure SQL Managed InstanceWant PaaS with minimal management; compatible with SQL Server features.
Azure SQL DatabaseFor fully managed, serverless databases (SaaS).
Azure Synapse AnalyticsFor analytical workloads (OLAP).

Key Azure Services Used

ServicePurpose
Azure Virtual MachinesHost SQL Server instances.
Azure StorageStore backups and files.
Azure MigrateAssess and migrate on-premises VMs.
Azure DMSOrchestrate database migrations with minimal downtime.
Azure BackupAutomated backups and retention policies.
Azure MonitorPerformance tracking and alerting.
Private LinkSecure private connectivity to SQL Server.

Best Practices Summary

  1. Test in Staging: Validate migration in a non-production environment.
  2. Use Azure Best Practices Guide: Follow Microsoft’s SQL Server on Azure VM guidelines.
  3. Automate: Use PowerShell or Azure CLI for repeatable deployments.
  4. Document: Maintain inventory of VMs, backups, and configurations.
  5. Update Regularly: Apply OS and SQL Server patches.

By following this structured approach, you can ensure a smooth, secure, and cost-effective migration of SQL Server to Azure Virtual Machines while aligning with Azure’s scalable architecture.

Post Views: 240