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.