Migrating SQL Server to Azure SQL Database: A Step-by-Step Guide

Migrating from on-premises SQL Server to Azure SQL Database offers scalability, cost-efficiency, and built-in high availability. However, the process requires careful planning to avoid downtime and compatibility issues.

This guide covers:
✅ Pre-Migration Assessment
✅ Choosing the Right Azure SQL Option
✅ Step-by-Step Migration Methods
✅ Post-Migration Validation
✅ Common Pitfalls & Best Practices

1. Pre-Migration Assessment

A. Check Compatibility Issues

Azure SQL Database has some limitations compared to SQL Server. Verify compatibility using:

1. Microsoft Data Migration Assistant (DMA)

  • Download & Install: Microsoft DMA

  • Steps:

    • Create a new assessment project.

    • Select "Azure SQL Database" as the target.

    • Analyze for:

      • Unsupported features (e.g., SQL Agent Jobs, Cross-DB Queries)

      • Syntax differences (e.g., WITH (NOLOCK) → WITH (READUNCOMMITTED))

2. Azure SQL Migration Extension (VS Code)

  • Useful for automated schema and T-SQL validation.

B. Estimate Costs

Use the Azure Pricing Calculator to compare:

  • Azure SQL Database (DTU/vCore model)

  • Azure SQL Managed Instance (for near 100% compatibility)

  • Azure VM (if lift-and-shift is needed)

2. Choose the Right Azure SQL Option

Option

Best For

Limitations

Azure SQL Database

Cloud-native apps, auto-scaling

Limited SQL Agent, no linked servers

Azure SQL MI

Near 100% compatibility, minimal app changes

Higher cost, longer deployment time

SQL Server on Azure VM

Full control, no code changes

Self-managed HA/backups

💡 Recommendation:

  • Use Azure SQL DB for new cloud apps.

  • Use SQL MI for complex legacy systems.

3. Migration Methods (Step-by-Step)

Method 1: Azure Database Migration Service (DMS)

Best for: Minimal downtime migrations.

Steps:

  1. Prepare Source SQL Server

    • Enable CDC (Change Data Capture) if needed.

    • Backup and restore the database in FULL recovery mode.

    ALTER DATABASE YourDB SET RECOVERY FULL;
BACKUP DATABASE YourDB TO DISK = 'C:\Backups\YourDB.bak';

  2. Set Up Azure DMS

    • In the Azure Portal, create a Database Migration Service.

    • Configure:

      • Source: SQL Server (on-premises)

      • Target: Azure SQL DB

      • Migration Mode: Online (CDC) or Offline

  3. Run & Monitor Migration

    • DMA handles schema migration first.

    • DMS syncs data continuously (for online migrations).

Method 2: Backup & Restore to Azure Blob Storage

Best for: Small to medium databases with downtime tolerance.

Steps:

  1. Upload Backup to Azure Blob

    BACKUP DATABASE YourDB TO URL = 'https://yourstorage.blob.core.windows.net/backups/YourDB.bak'
WITH CREDENTIAL = 'AzureCredential', COMPRESSION;

  2. Restore in Azure SQL DB

    • Use T-SQL (for SQL MI) or Azure Portal (for SQL DB).

    RESTORE DATABASE YourDB FROM URL = 'https://yourstorage.blob.core.windows.net/backups/YourDB.bak'
WITH CREDENTIAL = 'AzureCredential', MOVE 'YourDB_Data' TO 'https://yourstorage.blob.core.windows.net/data/YourDB.mdf';

Method 3: BACPAC Export/Import (Schema + Data)

Best for: Dev/test environments or small DBs.

Steps:

  1. Export from SQL Server (Using SSMS)

    • Right-click DB → Tasks → Export Data-tier Application → Save as .bacpac.

  2. Import to Azure SQL DB

    • Via Azure Portal → SQL Databases → Import Database.

4. Post-Migration Validation

✅ Verify Data Consistency

-- Compare row counts
SELECT COUNT(*) FROM SourceDB.dbo.Table1;
SELECT COUNT(*) FROM AzureDB.dbo.Table1;

✅ Test Application Connectivity

  • Update connection strings to use Azure SQL endpoint.

✅ Optimize Performance

  • Enable Azure SQL Database Auto-tuning.

  • Adjust DTU/vCore allocation based on workload.

5. Common Pitfalls & Best Practices

⚠️ Pitfalls to Avoid

  • Unsupported Features:

    • SQL Agent Jobs → Use Elastic Jobs or Azure Automation.

    • Cross-DB queries → Refactor or use SQL MI.

  • Network Latency: Test app performance from different regions.

  • Firewall Rules: Whitelist Azure IPs in SQL Server.

✅ Best Practices

✔ Use Private Link for secure connectivity.
✔ Monitor with Azure Metrics/Alerts (CPU, DTU usage).
✔ Implement Retry Logic for transient errors.

Conclusion

Migrating SQL Server to Azure SQL Database can be seamless with proper planning.

🔹 For minimal downtime → Use Azure DMS (Online Migration)
🔹 For full compatibility → Choose Azure SQL Managed Instance
🔹 For cost savings → Azure SQL DB (vCore/DTU model)

Comments

Post a Comment

Popular posts from this blog

Common Causes of Slow Queries in SQL Server and How to Fix Them

Slow queries are one of the most common performance issues in SQL Server environments, often leading to frustrated users and degraded application performance. Identifying and resolving these bottlenecks is crucial for maintaining an efficient database system. In this article, we'll explore the most frequent causes of slow queries in SQL Server and provide actionable solutions to address them. 1. Missing or Inefficient Indexes Problem: One of the most common reasons for slow queries is the lack of proper indexes or the existence of inefficient indexes. Symptoms: High table scan operations (shown in execution plans) High logical reads in query statistics Slow performance on WHERE, JOIN, or ORDER BY clauses Solutions: -- Create appropriate indexes CREATE INDEX IX_Customer_LastName ON Customers(LastName) INCLUDE (FirstName, Email); -- For complex queries, consider filtered indexes CREATE INDEX IX_Orders_Recent ON Orders(OrderDate) WHERE OrderDate > DATEADD(year, -1...
Read more

MS SQL Server Performance Optimization: Best Practices & Tips

Microsoft SQL Server is a powerful relational database management system, but as data grows and queries become more complex, performance can take a hit. Optimizing SQL Server performance is crucial for ensuring fast query execution, efficient resource utilization, and a seamless user experience. In this blog, we’ll explore essential MS SQL Server performance optimization techniques, covering indexing, query tuning, memory management, and more. 1. Optimize Indexing Strategy Indexes play a vital role in improving query performance by speeding up data retrieval. Here’s how to optimize them: Use the Right Index Types Clustered Index : Stores data in a sorted structure. Best for primary keys and frequently searched columns. Non-Clustered Index : Improves query performance on frequently filtered columns. Filtered Index : Ideal for queries that filter a subset of data. Avoid Over-Indexing Too many indexes slow down INSERT, UPDATE, DELETE operations. Regularly analyze index usag...
Read more