SQL Server → MongoDB

📄 Overview

MongoDB is a document store — there is no fixed schema, no tables, and no rows. MigrationBridge bridges the conceptual gap by applying a deterministic mapping from SQL Server's relational model to MongoDB's document model. Collections are created automatically on the target; no DDL is required before running the migration.

Conceptual Mapping

Validated Configuration

This SOP was validated against AdventureWorks2019 — 5 tables, 60,200 documents migrated successfully. Two columns containing hierarchyid and geography were skipped with warnings; all other columns migrated cleanly.

🔌 Prerequisites — SQL Server (Source)

Required Permissions

The MigrationBridge service account requires db_datareader on the source database. No write permissions are needed on the source. Use the principle of least privilege.

-- Create a dedicated migration login (if not already present)
USE [master];
CREATE LOGIN [mb_reader] WITH PASSWORD = N'Str0ngP@ssw0rd!';

-- Add to source database as db_datareader
USE [AdventureWorks2019];
CREATE USER [mb_reader] FOR LOGIN [mb_reader];
EXEC sp_addrolemember N'db_datareader', N'mb_reader';

Required Driver

• Microsoft ODBC Driver 17 for SQL Server (Windows or Linux)
• Download: Microsoft ODBC Driver for SQL Server
• MigrationBridge uses pyodbc internally — the ODBC driver must be installed on the machine running MigrationBridge

Pre-Flight Checks

Run the following queries on the source SQL Server before starting the migration to confirm connectivity and baseline row counts.

-- 1. Confirm SQL Server version (2016 SP1+ required for full JSON support)
SELECT @@VERSION;

-- 2. Confirm the migration login has datareader access
SELECT
    dp.name        AS [DatabasePrincipal],
    r.name         AS [RoleMembership],
    dp.type_desc   AS [PrincipalType]
FROM  sys.database_role_members  drm
JOIN  sys.database_principals    r   ON drm.role_principal_id  = r.principal_id
JOIN  sys.database_principals    dp  ON drm.member_principal_id = dp.principal_id
WHERE dp.name = N'mb_reader';

-- 3. Row count baseline for target tables
SELECT
    SCHEMA_NAME(t.schema_id)                    AS SchemaName,
    t.name                                       AS TableName,
    p.rows                                       AS ApproxRowCount
FROM  sys.tables   t
JOIN  sys.indexes  i  ON t.object_id = i.object_id  AND i.index_id <= 1
JOIN  sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE SCHEMA_NAME(t.schema_id) IN (N'HumanResources',N'Person',N'Sales',N'Production')
ORDER BY SchemaName, TableName;

-- 4. Identify any unsupported column types that will be skipped
SELECT
    SCHEMA_NAME(t.schema_id)  AS SchemaName,
    t.name                     AS TableName,
    c.name                     AS ColumnName,
    tp.name                    AS DataType
FROM  sys.columns c
JOIN  sys.tables  t   ON c.object_id = t.object_id
JOIN  sys.types   tp  ON c.user_type_id = tp.user_type_id
WHERE tp.name IN (N'hierarchyid', N'geography', N'geometry')
ORDER BY SchemaName, TableName, ColumnName;

📄 Prerequisites — MongoDB (Target)

Python Driver

MigrationBridge uses pymongo to communicate with MongoDB. Install it in the MigrationBridge virtual environment:

pip install pymongo[srv]

The [srv] extra is required for Atlas mongodb+srv:// connection strings.

Connection String Formats

Create Target User

Create a dedicated migration user with readWrite on the target database. Connect to MongoDB as an admin user and run:

use admin
db.createUser({
  user: "mb_writer",
  pwd: "Str0ngP@ssw0rd!",
  roles: [
    { role: "readWrite", db: "AdventureWorks2019" }
  ]
})

Test Connectivity with mongosh

Before configuring MigrationBridge, confirm the connection string works from the migration host:

# Local instance
mongosh "mongodb://mb_writer:Str0ngP@ssw0rd!@localhost:27017/AdventureWorks2019?authSource=admin"

# Atlas
mongosh "mongodb+srv://mb_writer:Str0ngP@ssw0rd!@cluster0.xxxxx.mongodb.net/AdventureWorks2019"

# Cosmos DB Mongo API
mongosh "mongodb://accountname:primarykey@accountname.mongo.cosmos.azure.com:10255/?ssl=true&replicaSet=globaldb&retrywrites=false"

After connecting, run a quick sanity check:

db.runCommand({ ping: 1 })
// Expected: { ok: 1 }

Open MigrationBridge and navigate to Connections. Configure the source (SQL Server) and target (MongoDB) connection profiles. Both profiles are saved locally and encrypted at rest.

Click Run Assessment after saving connection profiles. MigrationBridge scans the source catalog and produces a readiness report before any data is moved.

What the Assessment Checks

• All tables accessible under the configured login
• Column data types — flagging unsupported types
• Row count estimates per table (for progress estimation)
• Deprecated types (TEXT, NTEXT, IMAGE) — auto-upgraded to String / BinData
• Computed columns — included read-only in migration
• Identity columns — value preserved as a plain field

Unsupported Type Handling

NULL Field Handling

SQL Server NULL values have two treatment modes in MigrationBridge:

Set the preferred mode under Settings > MongoDB > Null Field Behavior before running migration. The default (omit) produces leaner documents and is idiomatic MongoDB.

After assessment, MigrationBridge displays the full table list. Use the checkboxes to include or exclude tables.

Collection Naming Convention

MigrationBridge creates MongoDB collections named SchemaName_TableName by default. Examples:

• dbo.Customers → collection dbo_Customers
• HumanResources.Employee → collection HumanResources_Employee
• Sales.SalesOrderHeader → collection Sales_SalesOrderHeader

To override collection names, use Settings > MongoDB > Collection Name Template. Available tokens: {schema}, {table}.

Click Start Migration. MigrationBridge streams rows from SQL Server in configurable batch sizes (default 1,000 rows per batch) and issues insertMany calls to MongoDB. Progress is shown per table.

After migration completes, validate document counts and spot-check data integrity using mongosh or MongoDB Compass.

Document Count Verification

// Connect to target database
use AdventureWorks2019

// Count documents per collection
db.HumanResources_Employee.countDocuments({})
// Expected: 290

db.Person_Address.countDocuments({})
// Expected: 19614

db.Sales_SalesOrderHeader.countDocuments({})
// Expected: 31465

db.Production_Product.countDocuments({})
// Expected: 504

db.Sales_SalesOrderDetail.countDocuments({})
// Expected: 8327

// Quick total across all collections
db.getCollectionNames().forEach(function(c) {
  print(c + ": " + db[c].countDocuments({}))
})

Spot-Check Data Integrity

// Inspect a sample document from HumanResources_Employee
db.HumanResources_Employee.findOne()
// Confirm: OrganizationNode field is ABSENT (was hierarchyid, skipped)
// Confirm: BirthDate is ISODate, not a string

// Verify SalesOrderHeader totals match SQL Server
db.Sales_SalesOrderHeader.aggregate([
  { $group: { _id: null, totalRevenue: { $sum: "$TotalDue" } } }
])
// Compare against SQL Server:
// SELECT SUM(TotalDue) FROM Sales.SalesOrderHeader;

// Verify NULL fields are omitted
db.Production_Product.findOne({ Color: { $exists: false } })
// Should return a product where Color was NULL in SQL Server

// Verify UUID fields are preserved as strings
db.Person_Address.findOne({}, { rowguid: 1 })
// rowguid should be a string like "C55EDB73-23A5-4E00-B0A2-..."

// Check for any skipped-column indicator in a document
db.Person_Address.findOne({}, { SpatialLocation: 1 })
// SpatialLocation should NOT appear in the document

Confirm Collections Exist

db.getCollectionNames()
// Expected output:
// [
//   "HumanResources_Employee",
//   "Person_Address",
//   "Production_Product",
//   "Sales_SalesOrderDetail",
//   "Sales_SalesOrderHeader"
// ]

Create Indexes

Indexes are not auto-migrated. After verifying counts, create indexes based on your application's query patterns. Below are recommended indexes for AdventureWorks2019 collections:

use AdventureWorks2019

// HumanResources_Employee — query by LoginID or BusinessEntityID
db.HumanResources_Employee.createIndex({ LoginID: 1 }, { unique: true })
db.HumanResources_Employee.createIndex({ JobTitle: 1 })

// Person_Address — query by City, StateProvinceID, or PostalCode
db.Person_Address.createIndex({ PostalCode: 1 })
db.Person_Address.createIndex({ City: 1, StateProvinceID: 1 })

// Sales_SalesOrderHeader — query by CustomerID, OrderDate, Status
db.Sales_SalesOrderHeader.createIndex({ CustomerID: 1 })
db.Sales_SalesOrderHeader.createIndex({ OrderDate: -1 })
db.Sales_SalesOrderHeader.createIndex({ Status: 1, OrderDate: -1 })

// Sales_SalesOrderDetail — query by ProductID or SalesOrderID
db.Sales_SalesOrderDetail.createIndex({ SalesOrderID: 1 })
db.Sales_SalesOrderDetail.createIndex({ ProductID: 1 })

// Production_Product — query by ProductNumber or Name
db.Production_Product.createIndex({ ProductNumber: 1 }, { unique: true })
db.Production_Product.createIndex({ Name: 1 })

// Verify indexes
db.Sales_SalesOrderHeader.getIndexes()

Atlas Search (Atlas Deployments Only)

If the target is MongoDB Atlas, create Atlas Search indexes for full-text search capability. This replaces SQL Server full-text indexes:

// In Atlas UI: Search → Create Search Index → JSON Editor
// Example: full-text search on Production_Product.Name and Description
{
  "mappings": {
    "dynamic": false,
    "fields": {
      "Name": { "type": "string", "analyzer": "lucene.standard" },
      "ProductNumber": { "type": "string" }
    }
  }
}

Shard Key Recommendations (Sharded Clusters)

For large collections on a sharded MongoDB cluster, choose shard keys carefully. Poor shard key selection causes hotspot shards:

Enable Document Validation (Optional)

MongoDB is schema-less by default. If you want to enforce field types post-migration, add a JSON Schema validator:

db.runCommand({
  collMod: "Sales_SalesOrderHeader",
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["SalesOrderID", "CustomerID", "OrderDate", "TotalDue"],
      properties: {
        SalesOrderID: { bsonType: "int"    },
        CustomerID:   { bsonType: "int"    },
        OrderDate:    { bsonType: "date"   },
        TotalDue:     { bsonType: "double" }
      }
    }
  },
  validationLevel: "moderate",
  validationAction: "warn"
})

🔁 Live CDC Streaming — SQL Server → MongoDB

MigrationBridge Live CDC mode captures ongoing changes from SQL Server's Change Data Capture mechanism and streams them to MongoDB as upsert and delete operations. Use this for zero-downtime cutovers or continuous synchronization.

Step 1 — Enable CDC on SQL Server Source

-- Enable CDC on the database
USE [AdventureWorks2019];
EXEC sys.sp_cdc_enable_db;

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

-- Enable CDC on each source table (example: Sales.SalesOrderHeader)
EXEC sys.sp_cdc_enable_table
    @source_schema       = N'Sales',
    @source_name         = N'SalesOrderHeader',
    @role_name           = NULL,
    @supports_net_changes = 1;

-- Repeat for each table that needs live sync
EXEC sys.sp_cdc_enable_table
    @source_schema        = N'Sales',
    @source_name          = N'SalesOrderDetail',
    @role_name            = NULL,
    @supports_net_changes = 1;

-- Verify capture instances
SELECT capture_instance, source_schema, source_table, start_lsn
FROM  cdc.change_tables;

Step 2 — Full-Load First, Then Enable CDC

The recommended sequence for a zero-downtime migration:

1. Enable CDC on SQL Server source (Step 1 above)
2. Record the current LSN (log sequence number) before starting full load
3. Run full table migration with MigrationBridge (Steps 1–5 of this SOP)
4. After full load, start CDC streaming from the recorded LSN
5. Let CDC catch up until lag approaches zero
6. Pause writes on SQL Server, let CDC drain, verify counts, cut over application

-- Record current LSN before starting full load
SELECT sys.fn_cdc_get_max_lsn() AS CurrentMaxLSN;
-- Store this value; pass it to MigrationBridge CDC Start LSN field

Step 3 — Configure CDC Streaming in MigrationBridge

How Change Events Map to MongoDB Operations

CDC Validation Queries

-- Check CDC capture lag (SQL Server side)
SELECT
    ct.capture_instance,
    ct.source_table,
    sys.fn_cdc_get_max_lsn()  AS CurrentLSN,
    ct.start_lsn              AS CaptureStartLSN
FROM  cdc.change_tables ct;

-- Count pending changes not yet sent to MigrationBridge
SELECT COUNT(*) AS PendingChanges
FROM  cdc.dbo_SalesOrderHeader_CT  -- adjust per capture instance name
WHERE __$start_lsn > 0x0000E2A400000A1E0001;  -- replace with last processed LSN

🔄 Type Conversion Reference

MigrationBridge applies the following type coercions when converting SQL Server column values to BSON document fields.

⚠ Known Issues & Limitations

Multi-Document Transactions

_id Auto-Generated

MongoDB auto-generates an ObjectId for _id on every inserted document. The SQL Server primary key is preserved as a regular field (e.g., SalesOrderID) but is not used as _id. If your application expects _id to equal the source PK, enable Settings > MongoDB > Map PK to _id — this sets _id to the source PK value.

No JOINs on Target

MongoDB does not enforce foreign keys or support SQL-style JOINs. Related data that was normalized across tables in SQL Server must be handled via:

• Application-level lookups (multiple queries)
• MongoDB $lookup (aggregation pipeline — available but not recommended for high-frequency operations)
• Document embedding (denormalization) — recommended for one-to-few relationships

Document Order Not Guaranteed

MongoDB does not guarantee insertion order for queries without an explicit sort(). Application queries that relied on SQL Server's clustered index order must add explicit sort directives.

16 MB BSON Document Limit

Cosmos DB Mongo API Compatibility

🖥 UI Behavior During Migration

During an active migration or CDC streaming session, MigrationBridge enters a busy state. The following UI behaviors are by design:

• Start Migration button disabled — prevents duplicate migration runs. The button re-enables when the current run completes or is stopped.
• Connection fields locked — source and target connection settings cannot be edited mid-migration. Stop the migration first, then reconfigure.
• Table selection locked — the table checklist is read-only during migration. Changes take effect on the next run.
• Progress bar — updates per-batch. For large tables the bar may appear to stall between batches; this is normal for wide rows with type conversion overhead.
• Log auto-scrolls — the log panel auto-scrolls to the latest entry. Click inside the panel to pause auto-scroll and review earlier entries; scroll to the bottom to resume.
• Pause / Resume — the Pause button halts batch submission at the next batch boundary. In-flight batches complete before the pause takes effect. The resume continues from where the last committed batch ended.
• Window close during migration — MigrationBridge will prompt: "A migration is in progress. Closing will stop it. Continue?" Confirm to stop; the partially migrated data remains on the target (safe to re-run with deduplication enabled).