Azure SQL Database vs SQL Managed Instance - CLI Comparison

Introduction

When you want to run a SQL database in the cloud, you can choose between Azure SQL Database and SQL Managed Instance. Both let you store and manage data, but they solve different needs depending on how much control and compatibility you want with traditional SQL Server.

When you need a fully managed database with minimal setup and automatic scaling for a new cloud app.

When you want to lift and shift an existing on-premises SQL Server database with minimal changes.

When you need features like SQL Agent jobs or cross-database queries that Azure SQL Database does not support.

When you want to isolate your database in a private network for security reasons.

When you want to reduce management overhead and focus on app development.

Commands

This command creates a new Azure SQL Database named 'exampledb' in the server 'example-sql-server' within the resource group 'example-rg'. The service objective S0 defines the performance level.

Terminal

az sql db create --resource-group example-rg --server example-sql-server --name exampledb --service-objective S0

Expected Output

{

  "databaseName": "exampledb",

  "resourceGroup": "example-rg",

  "status": "Online",

  "location": "eastus"

}

→

--resource-group - Specifies the Azure resource group where the database will be created.

→

--server - Specifies the logical SQL server to host the database.

→

--service-objective - Defines the performance tier of the database.

This command creates a new SQL Managed Instance named 'example-mi' in the resource group 'example-rg' at location 'eastus'. It sets the admin username and password for access.

Terminal

az sql mi create --name example-mi --resource-group example-rg --location eastus --admin-user sqladmin --admin-password StrongP@ssw0rd!

Expected Output

{

  "name": "example-mi",

  "resourceGroup": "example-rg",

  "state": "Succeeded",

  "location": "eastus"

}

→

--name - Names the SQL Managed Instance.

→

--admin-user - Sets the administrator username.

→

--admin-password - Sets the administrator password.

This command shows details about the Azure SQL Database 'exampledb' to verify it was created and check its status.

Terminal

az sql db show --resource-group example-rg --server example-sql-server --name exampledb

Expected Output

{

  "databaseName": "exampledb",

  "status": "Online",

  "edition": "Standard",

  "serviceObjective": "S0"

}

→

--name - Specifies the database name to show.

This command shows details about the SQL Managed Instance 'example-mi' to verify its creation and current state.

Terminal

az sql mi show --name example-mi --resource-group example-rg

Expected Output

{

  "name": "example-mi",

  "state": "Succeeded",

  "location": "eastus",

  "administratorLogin": "sqladmin"

}

Key Concept

If you remember nothing else, remember: Azure SQL Database is a simple, fully managed database for new cloud apps, while SQL Managed Instance offers near full SQL Server compatibility for migrating existing apps with more control.

Common Mistakes

Trying to use SQL Server features like SQL Agent jobs on Azure SQL Database.

Azure SQL Database does not support some SQL Server features, causing failures or missing functionality.

Use SQL Managed Instance if you need full SQL Server feature compatibility.

Creating SQL Managed Instance without configuring a virtual network.

SQL Managed Instance requires a virtual network for deployment, so the creation will fail without it.

Set up a virtual network and subnet before creating the SQL Managed Instance.

Using weak or simple passwords for admin accounts.

Weak passwords reduce security and may be rejected by Azure policies.

Use strong, complex passwords following Azure security guidelines.

Summary

Use 'az sql db create' to create a simple Azure SQL Database for cloud-native apps.

Use 'az sql mi create' to create a SQL Managed Instance for near full SQL Server compatibility.

Verify creation with 'az sql db show' and 'az sql mi show' commands.

Practice

1. Which Azure service provides a fully managed single database with simple setup and maintenance?

easy

A. Azure Cosmos DB

B. SQL Managed Instance

C. Azure Blob Storage

D. Azure SQL Database

Solution

1. Step 1: Understand service purpose

   Azure SQL Database is designed as a simple, fully managed single database service in the cloud.

2. Step 2: Compare with other options

   SQL Managed Instance offers more features and compatibility but is not as simple as Azure SQL Database. Blob Storage and Cosmos DB serve different purposes.

3. Final Answer:

   Azure SQL Database -> Option D

4. Quick Check:

   Simple managed single database = Azure SQL Database [OK]

Hint: Simple managed single DB? Think Azure SQL Database [OK]

Common Mistakes:

• Confusing SQL Managed Instance with Azure SQL Database
• Choosing storage services like Blob Storage
• Selecting Cosmos DB which is NoSQL

2. Which option correctly describes a key feature of SQL Managed Instance in Azure?

easy

A. Limited SQL Server compatibility

B. Only supports single databases

C. Full SQL Server compatibility with network control

D. No support for SQL Server Agent

Solution

1. Step 1: Identify SQL Managed Instance features

   SQL Managed Instance offers full SQL Server compatibility and allows more control over network settings.

2. Step 2: Eliminate incorrect options

   It supports SQL Server Agent and multiple databases, so options A, B, and C are incorrect.

3. Final Answer:

   Full SQL Server compatibility with network control -> Option C

4. Quick Check:

   Full compatibility + network control = SQL Managed Instance [OK]

Hint: Full SQL Server features? Choose SQL Managed Instance [OK]

Common Mistakes:

• Thinking SQL Managed Instance has limited compatibility
• Believing it does not support SQL Server Agent
• Confusing single database support with Azure SQL Database

3. Given an application requiring SQL Server Agent jobs and linked server support, which Azure service will work without modification?

medium

A. Azure SQL Database

B. SQL Managed Instance

C. Azure Table Storage

D. Azure Data Lake

Solution

1. Step 1: Check feature support

   SQL Managed Instance supports SQL Server Agent jobs and linked servers, unlike Azure SQL Database.

2. Step 2: Match application needs

   The app needs these features without changes, so SQL Managed Instance fits best.

3. Final Answer:

   SQL Managed Instance -> Option B

4. Quick Check:

   SQL Server Agent + linked servers = SQL Managed Instance [OK]

Hint: Needs SQL Server Agent? Pick SQL Managed Instance [OK]

Common Mistakes:

• Assuming Azure SQL Database supports SQL Server Agent
• Confusing storage services with database services
• Ignoring linked server requirements

4. A developer tries to migrate an on-premises SQL Server database with cross-database queries to Azure SQL Database but faces errors. What is the likely cause?

medium

A. Azure SQL Database does not support cross-database queries

B. SQL Managed Instance does not support cross-database queries

C. On-premises SQL Server uses unsupported data types

D. Azure SQL Database requires manual schema conversion

Solution

1. Step 1: Understand cross-database query support

   Azure SQL Database does not support cross-database queries natively, causing errors.

2. Step 2: Compare with SQL Managed Instance

   SQL Managed Instance supports cross-database queries, so it's not the cause.

3. Final Answer:

   Azure SQL Database does not support cross-database queries -> Option A

4. Quick Check:

   Cross-database queries missing in Azure SQL Database [OK]

Hint: Cross-database queries fail? Check Azure SQL Database limits [OK]

Common Mistakes:

• Blaming SQL Managed Instance for cross-database query issues
• Assuming all data types are supported without checking
• Ignoring Azure SQL Database feature limits

5. You need to move a legacy app using SQL Server features like linked servers, SQL Server Agent jobs, and cross-database queries to Azure with minimal changes. Which service should you choose and why?

hard

A. SQL Managed Instance, because it supports full SQL Server features and network control

B. Azure SQL Database, because it is simpler and fully managed

C. Azure Cosmos DB, because it supports multiple data models

D. Azure Blob Storage, because it stores large amounts of data

Solution

1. Step 1: Identify required SQL Server features

   The app uses linked servers, SQL Server Agent jobs, and cross-database queries which require full SQL Server compatibility.

2. Step 2: Match features to Azure services

   SQL Managed Instance supports these features and network control, enabling minimal changes during migration.

3. Final Answer:

   SQL Managed Instance, because it supports full SQL Server features and network control -> Option A

4. Quick Check:

   Legacy SQL Server features need SQL Managed Instance [OK]

Hint: Legacy SQL Server features? Use SQL Managed Instance [OK]

Common Mistakes:

• Choosing Azure SQL Database despite missing features
• Confusing Cosmos DB or Blob Storage as SQL replacements
• Ignoring network control needs