Migrating On-Premises SQL to Azure: Choosing Between VM, MI, and SQL Database

There comes a defining moment in every Junior DBA’s career when the senior leadership team walks into the room and announces: "We are shutting down our local data centre and migrating all production SQL Servers to Microsoft Azure."

Suddenly, you are flooded with cloud terminology. You are told you need to choose the right target architecture, evaluate "IaaS vs. PaaS," and figure out if an existing application will break if it moves to a fully managed cloud database.

Azure does not offer just one single way to run SQL Server. It offers three completely distinct "Flavors," each presenting a completely different trade-off between administrative control and automated management. Picking the wrong option can either leave your company overpaying for infrastructure or force developers to completely rewrite their application code.

Let’s demystify Azure's SQL options using real-world analogies, map out a clear cloud decision tree, and review the exact pre-migration checks you need to run.

1. The Real-World Analogy: Finding a Place to Live

To understand the difference between Azure’s three primary database service models, imagine your company needs to find housing for its data.

Option 1: SQL Server on Azure Virtual Machines (IaaS)

This is the equivalent of Renting a Raw Piece of Land. The landlord (Azure) provides the physical space, electricity, and fences. However, you must bring your own mobile home, hook up the plumbing, patch the roof when it leaks, and configure the security system yourself.

• The Reality: This is Infrastructure-as-a-Service (IaaS). You get 100% control over the operating system, but you still have to do all the heavy lifting.

Option 2: Azure SQL Managed Instance (PaaS)

This is the equivalent of Buying a Suburban Townhouse. The homeowner's association (Azure) automatically handles the external roofing, structural foundations, and landscaping. Inside your walls, however, you have complete freedom to use your own furniture, set up multi-room intercom networks, and configure custom security gates.

• The Reality: This is Platform-as-a-Service (PaaS). It provides full instance-level SQL compatibility (SQL Agent, Cross-Database queries) while completely automating OS patching and backups.

Option 3: Azure SQL Database (PaaS)

This is the equivalent of Staying in a Fully Serviced Luxury Hotel Suite. You don't own the walls, you don't care about the plumbing, and the maid service automatically changes the sheets daily. You just show up with your luggage (your data tables) and use the room. However, you cannot knock down a wall to join two suites together, and you must follow the hotel's strict operational rules.

• The Reality: This is a fully managed, single-database PaaS container. It is highly scalable and cost-effective, but it drops instance-level features like the master database system catalogs and direct cross-database queries.

2. Diagram 1: The Control vs. Management Spectrum

Before picking a migration path, you must understand the inverse relationship between administrative control and automated maintenance.

3. Deep-Diving the Three Cloud Flavors

A. SQL Server on Azure VMs (The Lift-and-Shift Route)

When you migrate to an Azure VM, you are simply running standard SQL Server inside a cloud-hosted virtual machine.

• When to choose it: If your application relies deeply on third-party OS-level software agents, requires direct access to the underlying Windows File System (C:\ or D:\ drives), or utilizes legacy SQL Server versions (like SQL 2012 or 2014) that cannot be modernized yet.

• The Downside: You are still on call at 3:00 AM if an operating system patch fails, a disk volume fragments, or a high-availability cluster drops offline.

B. Azure SQL Managed Instance (The Enterprise Sweet Spot)

Managed Instance (MI) gives you the best of both worlds. It looks and behaves exactly like an on-premises SQL Server instance, but Microsoft handles the underlying infrastructure.

• When to choose it: If you have an enterprise application that executes native SQL Server Agent Jobs, utilizes Linked Servers to talk to other instances, or runs heavy Cross-Database Queries (SELECT * FROM DatabaseA..Table JOIN DatabaseB..Table). You can migrate your databases using a simple native backup and restore operation directly from an Azure Blob Storage URL.

• The Downside: Provisioning times can be slower, and the baseline cost is higher, making it less suitable for tiny, isolated apps.

C. Azure SQL Database (The Modern Cloud-Native Path)

Azure SQL Database abstracts away the concept of a "Server Instance." You are provisioning an isolated database container that can automatically scale its compute power up and down based on application demand (Serverless tier).

• When to choose it: Perfect for brand-new web application developments, microservices architectures, or isolated SaaS databases requiring massive elastic scale at a low entry price point.

• The Downside: Zero instance-level capabilities. You cannot run cross-database joins, native SQL Agent jobs do not exist, and you cannot access the underlying file system. If your legacy code relies on these features, migrating here will require a massive code rewrite.

4. The Cloud Architectural Decision Matrix

Use this quick-reference blueprint scorecard to determine which cloud tier aligns with your current application requirements:

5. Diagram 2: The Migration Decision Tree Flowchart

To quickly triage a fleet of servers, follow this logical workflow path to isolate your ideal cloud target.

6. The Pre-Migration Triage Script

Before telling your project managers that a database is ready to move to Azure SQL Database (Single DB), you must audit the system catalog views to see if the database utilizes features that are explicitly blocked in that cloud tier.

Run this script directly against your local database to scan for the presence of cross-database dependencies or locked features:

-- Check for cross-database three-part or four-part object names inside views and procedures
SELECT 
    OBJECT_NAME(referencing_id) AS [ReferencingObjectName],
    type_desc AS [ObjectType],
    referenced_database_name AS [BlockedCrossDatabaseTarget],
    referenced_entity_name AS [TargetObjectName]
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL 
  AND referenced_database_name <> DB_NAME() -- Finds references outside the local database boundary
UNION ALL
-- Check if the database contains schemas or features reserved for instance-level deployment
SELECT 
    name AS [ObjectName],
    type_desc AS [ObjectType],
    'INSTANCE_FEATURE_RELIANCE' AS [BlockedCrossDatabaseTarget],
    'Assembly/Extended_Procedure' AS [TargetObjectName]
FROM sys.objects 
WHERE type_desc IN ('CLR_STORED_PROCEDURE', 'EXTENDED_STORED_PROCEDURE');
GO

How to Action the Results

• If the script returns zero rows: Your database is completely isolated. You can safely migrate it directly to the highly cost-effective Azure SQL Database model.

• If the script returns multiple rows: Your code has hardcoded dependencies on external database containers. You should bypass the single database model and target Azure SQL Managed Instance instead to ensure your code runs seamlessly post-migration without rewriting application logic.

By understanding how Azure splits its computing structures between IaaS and PaaS, and knowing how to audit your schemas for architectural blockers, you can confidently steer your enterprise cloud migrations without risking broken application connections or blowing through your infrastructure budget.

Is your organization planning a shift toward cloud infrastructure, or are you already managing database workloads inside Azure? Do you lean toward the simplicity of PaaS Managed Instances or the raw control of cloud Virtual Machines? Let's talk cloud architecture strategies in the comments below!