SQL Server → Azure Cosmos DB Migration

Architecture Overview

Azure Cosmos DB for NoSQL stores data as JSON documents in containers, replacing SQL Server tables. Understanding the mapping between relational and document concepts is critical before any migration work begins.

Relational → Document Concept Mapping

Prerequisites — Source: SQL Server

Required Permissions

ODBC Driver Check

Verify the correct ODBC driver is installed on the migration host:

-- PowerShell: list installed ODBC drivers
Get-OdbcDriver | Where-Object { $_.Name -like "*SQL*" } | Select-Object Name, Platform

Required: ODBC Driver 17 for SQL Server or ODBC Driver 18 for SQL Server.

Pre-Flight T-SQL Queries

Run these on the source instance before launching the tool:

-- 1. Verify connectivity and SQL Server version
SELECT SERVERPROPERTY('ProductVersion')  AS ProductVersion,
       SERVERPROPERTY('Edition')        AS Edition,
       SERVERPROPERTY('EngineEdition')   AS EngineEdition,
       @@SERVERNAME                       AS ServerName;

-- 2. Row counts for all user tables in the source database
SELECT
    s.name                          AS SchemaName,
    t.name                          AS TableName,
    p.rows                          AS RowCount,
    SUM(a.total_pages) * 8 / 1024    AS TotalSizeMB
FROM     sys.tables       t
JOIN     sys.schemas      s  ON s.schema_id  = t.schema_id
JOIN     sys.indexes      i  ON i.object_id  = t.object_id AND i.index_id <= 1
JOIN     sys.partitions   p  ON p.object_id  = i.object_id AND p.index_id  = i.index_id
JOIN     sys.allocation_units a ON a.container_id = p.partition_id
GROUP BY s.name, t.name, p.rows
ORDER BY TotalSizeMB DESC;

-- 3. Identify unsupported column types that need special handling
SELECT
    OBJECT_SCHEMA_NAME(c.object_id) AS SchemaName,
    OBJECT_NAME(c.object_id)        AS TableName,
    c.name                           AS ColumnName,
    tp.name                          AS DataType,
    c.max_length, c.is_nullable
FROM   sys.columns  c
JOIN   sys.types    tp ON tp.user_type_id = c.user_type_id
WHERE  tp.name IN ('hierarchyid', 'geometry', 'geography',
                    'sql_variant', 'xml', 'image',
                    'text', 'ntext')
ORDER BY SchemaName, TableName;

-- 4. Check for tables without a primary key (complicates id assignment)
SELECT
    s.name  AS SchemaName,
    t.name  AS TableName
FROM   sys.tables  t
JOIN   sys.schemas s ON s.schema_id = t.schema_id
WHERE  t.object_id NOT IN (
    SELECT parent_object_id
    FROM   sys.key_constraints
    WHERE  type = 'PK'
)
ORDER BY SchemaName, TableName;

Prerequisites — Target: Azure Cosmos DB

Python SDK Installation

The Migration Tool requires the Azure Cosmos DB Python SDK:

pip install azure-cosmos==4.7.0 azure-identity==1.16.0 pyodbc==5.1.0

Verify installation:

python -c "import azure.cosmos; print(azure.cosmos.__version__)"

Connection String Format

Retrieve from Azure Portal → Cosmos DB Account → Keys:

AccountEndpoint=https://<account-name>.documents.azure.com:443/;AccountKey=<primary-or-secondary-key>==;

For managed identity (recommended for production), use the endpoint URI only — no key required.

Azure Portal — Database Creation Steps

1. Open the Azure Portal and navigate to your Cosmos DB account.
2. Select Data Explorer → New Container.
3. Enter the Database id (create new or use existing).
4. Enter the Container id (maps to source table name).
5. Set the Partition key (e.g., /CustomerId).
6. Choose throughput: Autoscale (recommended) or manual RU/s.
7. Click OK. Container is ready within seconds.

RBAC Assignment (Azure CLI)

az cosmosdb sql role assignment create \
  --account-name  <cosmos-account> \
  --resource-group <rg-name> \
  --role-definition-name "Cosmos DB Built-in Data Contributor" \
  --principal-id  <managed-identity-object-id> \
  --scope          "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.DocumentDB/databaseAccounts/<cosmos-account>"

Network Requirements

Step 1 — Configure Connections

Launch the Migration Tool and configure source and target connections. All credentials are stored encrypted in the local session and are never written to disk.

Step 2 — Partition Key Assessment

The assessment engine analyzes each source table and recommends a partition key based on cardinality, query pattern sampling, and write distribution. Review and override recommendations before proceeding.

Partition Key Assessment — AdventureWorks Sample

Skipped / Unsupported Column Types

Step 3 — Table Mapping & Migration Options

Configure the mapping between source tables and target containers. Set the partition key, throughput, and per-table options before starting the migration run.

Migration Options

Pre-Migration Checklist

• Partition key reviewed and approved for all containers
• Target database and throughput settings confirmed
• Network connectivity tested (SQL Server port 1433, Cosmos DB port 443)
• Migration service account permissions validated
• Checkpoint directory writable
• Source tables backed up or snapshot taken
• Change window approved with stakeholders

Step 4 — Execute Migration

Click Start Migration to begin the bulk data movement. Monitor the live log panel. The migration is resumable via the checkpoint file if interrupted.

Step 5 — Post-Migration Validation

Validate document counts and data integrity across all migrated containers. Use the Data Explorer queries, the Python SDK script, and T-SQL cross-validation.

Cosmos DB Data Explorer Queries

-- Count all documents in SalesOrderHeader
SELECT VALUE COUNT(1) FROM c

-- Spot-check a specific customer
SELECT * FROM c WHERE c.CustomerId = 29825

-- Verify field presence (no nulls on SalesOrderId)
SELECT VALUE COUNT(1) FROM c WHERE IS_NULL(c.SalesOrderId) = true

Expected Document Count Table

Python SDK Verification Script

from azure.cosmos import CosmosClient

ENDPOINT = "https://he-cosmos-prod.documents.azure.com:443/"
KEY      = "<account-key>"
DB_NAME  = "AdventureWorks_Cosmos"

client = CosmosClient(ENDPOINT, credential=KEY)
db     = client.get_database_client(DB_NAME)

containers = ["SalesOrderHeader", "SalesOrderDetail",
              "Product", "Person", "Employee"]

for name in containers:
    container = db.get_container_client(name)
    count     = list(container.query_items(
        "SELECT VALUE COUNT(1) FROM c",
        enable_cross_partition_query=True
    ))[0]
    print(f"{name:30s}  documents: {count:,}")

T-SQL Cross-Validation (Source)

-- Run on source SQL Server to confirm expected row counts pre-cut-over
SELECT 'Sales.SalesOrderHeader'  AS TableName, COUNT(*) AS RowCount FROM Sales.SalesOrderHeader
UNION ALL
SELECT 'Sales.SalesOrderDetail'  ,             COUNT(*) FROM Sales.SalesOrderDetail
UNION ALL
SELECT 'Production.Product'      ,             COUNT(*) FROM Production.Product
UNION ALL
SELECT 'Person.Person'           ,             COUNT(*) FROM Person.Person
UNION ALL
SELECT 'HumanResources.Employee' ,             COUNT(*) FROM HumanResources.Employee;

Step 6 — Cut Over & Post-Migration

Index Policy Review

Cosmos DB indexes all properties by default. For large containers, a custom indexing policy reduces RU overhead on writes. Example policy for SalesOrderHeader:

{
  "indexingMode": "consistent",
  "automatic": true,
  "includedPaths": [
    { "path": "/CustomerId/?" },
    { "path": "/OrderDate/?" },
    { "path": "/Status/?" }
  ],
  "excludedPaths": [
    { "path": "/LineItems/*" },
    { "path": "/_etag/?" }
  ]
}

RU/s Scaling Steps

1. During migration: set throughput to maximum needed (monitor CloudWatch / Azure Monitor).
2. After migration and validation: scale down containers to steady-state RU/s.
3. Enable Autoscale for variable workloads to avoid manual scaling.
4. Set an alert if normalized RU consumption exceeds 80% for >5 minutes.

Change Feed Consumer (Application Cut-Over)

To detect and react to new writes after cut-over, configure a Change Feed processor:

from azure.cosmos.aio import CosmosClient
from azure.cosmos import PartitionKey

async def process_changes(docs):
    for doc in docs:
        print(f"Changed document id={doc['id']}, partition={doc.get('CustomerId')}")

# Attach processor to SalesOrderHeader container
# See: https://aka.ms/cosmos-change-feed-processor

Decommission Checklist

• Application connection strings updated to Cosmos DB endpoint
• All read/write traffic verified routing to Cosmos DB (zero SQL Server queries)
• Cosmos DB document counts match final SQL Server row counts
• SQL Server database set to READ_ONLY for 72-hour observation period
• Alerts configured on Cosmos DB (RU throttle, availability, latency)
• Backup policy enabled on Cosmos DB account (periodic or continuous)
• SQL Server database backup taken and archived before decommission
• SQL Server database drop / decommission approved by Change Advisory Board

Live CDC Sync (Change Data Capture)

For near-zero downtime migrations, enable SQL Server CDC on the source database to capture ongoing changes while the initial full-load runs. After full-load completes, replay CDC events into Cosmos DB before cutting over application traffic.

Enable CDC on Source Database

-- Step 1: Enable CDC on the database
USE AdventureWorks2019;
EXEC sys.sp_cdc_enable_db;

-- Step 2: Enable CDC on target tables
EXEC sys.sp_cdc_enable_table
    @source_schema   = N'Sales',
    @source_name     = N'SalesOrderHeader',
    @role_name       = NULL,
    @supports_net_changes = 1;

EXEC sys.sp_cdc_enable_table
    @source_schema   = N'Sales',
    @source_name     = N'SalesOrderDetail',
    @role_name       = NULL,
    @supports_net_changes = 1;

-- Step 3: Verify CDC is enabled
SELECT name, is_cdc_enabled
FROM   sys.databases
WHERE  name = N'AdventureWorks2019';

Full-Load-First Sequence

1. Record the current CDC LSN (Log Sequence Number) before starting the full load.
2. Execute the full-load migration (Step 4) while CDC continues capturing changes.
3. After full-load completes, replay CDC change events from the saved LSN forward.
4. Drain the CDC queue until lag reaches near-zero.
5. Pause writes on the source (maintenance window), apply final CDC events, cut over.

-- Capture the starting LSN before full load begins
DECLARE @start_lsn binary(10);
SET @start_lsn = sys.fn_cdc_get_min_lsn(N'Sales_SalesOrderHeader');
SELECT sys.fn_cdc_map_lsn_to_time(@start_lsn) AS StartLSNTime, @start_lsn AS StartLSN;

CDC Operation Mapping

CDC Monitoring Queries

-- Monitor CDC capture latency
SELECT
    ct.capture_instance,
    ct.source_schema,
    ct.source_table,
    sys.fn_cdc_map_lsn_to_time(ct.start_lsn) AS CaptureStartTime,
    js.latency                                 AS LatencySeconds,
    js.tran_count                              AS UnprocessedTrans
FROM   cdc.change_tables          ct
JOIN   cdc.dm_cdc_log_scan_sessions js
    ON js.session_id = (SELECT MAX(session_id) FROM cdc.dm_cdc_log_scan_sessions)
ORDER BY ct.source_schema, ct.source_table;

Type Conversions Reference

The Migration Tool applies the following type mappings when transforming SQL Server rows to JSON documents.

Sample Output — SalesOrderHeader Document

{
  "id":              "75123",
  "SalesOrderId":    75123,
  "CustomerId":      29825,
  "OrderDate":       "2014-06-01T00:00:00.0000000Z",
  "DueDate":         "2014-06-13T00:00:00.0000000Z",
  "ShipDate":        "2014-06-08T00:00:00.0000000Z",
  "Status":          5,
  "OnlineOrderFlag": false,
  "SalesOrderNumber":"SO75123",
  "PurchaseOrderNumber": "PO18850127500",
  "AccountNumber":   "10-4020-000676",
  "SubTotal":        "880.3484",
  "TaxAmt":          "70.4279",
  "Freight":         "22.0087",
  "TotalDue":        "972.7850",
  "Comment":         null,
  "rowguid":         "79b65321-39ca-4115-9cba-8fe0903f12e6",
  "ModifiedDate":    "2014-06-08T00:00:00.0000000Z",
  "_migrationRunId": "a3f9c2d7",
  "_migratedAt":     "2026-07-05T09:14:49Z"
}

Known Issues & Limitations

Known Issues — Severity Summary

UI Busy States & Checkpoint Behavior

During long-running migration operations, the tool enters a busy state and displays a progress indicator. The following behaviors apply: