MongoDB → SQL Server Migration

📄 Overview & Document-to-Relational Mapping

MongoDB is a document-oriented NoSQL store; SQL Server is a relational engine. MigrationBridge bridges the gap by flattening each collection into a SQL table and mapping BSON field types to SQL Server column types. The table below summarises the structural transformation rules applied during every migration run.

🔹 Prerequisites — Source (MongoDB)

Python Driver

MigrationBridge uses pymongo to connect to MongoDB. Ensure it is installed in the MigrationBridge Python environment:

pip install pymongo[srv]

The [srv] extra is required for Atlas mongodb+srv:// connection strings. For plain TCP connections it is optional but harmless.

Connection String Formats

Required MongoDB Permissions

• readAnyDatabase — read access across all databases (or at minimum read on the target database)
• clusterMonitor — required to call rs.status() and tail the oplog for Change Streams

// Grant in mongosh (admin database)
db.grantRolesToUser("migrationbridge", [
  { "role": "readAnyDatabase", "db": "admin" },
  { "role": "clusterMonitor", "db": "admin" }
])

Replica Set Requirement

MongoDB Change Streams (live replication) require the source to be a replica set or sharded cluster. Standalone mongod instances do not support Change Streams. Full-load-only migrations work against standalone instances.

Pre-Flight Checks

// 1. Connectivity ping
db.adminCommand({ "ping": 1 })
// Expected: { ok: 1 }

// 2. Replica set status (required for Change Streams)
rs.status()
// Look for: "stateStr" : "PRIMARY" in the members array
// If standalone: rs.status() returns an error -- Change Streams unavailable

🎯 Prerequisites — Target (SQL Server)

SQL Server Permissions

The migration account needs the following privileges on the target database. Least-privilege option is listed first; db_owner is the broadest option and easiest to grant in dev/test.

ODBC Driver

MigrationBridge uses pyodbc. ODBC Driver 17 for SQL Server (or later) must be installed on the MigrationBridge host. Verify:

python -c "import pyodbc; print([x for x in pyodbc.drivers() if 'SQL Server' in x])"
# Expected output includes: ['ODBC Driver 17 for SQL Server']

Download: Microsoft ODBC Driver for SQL Server

Pre-Flight Space Check

-- Check free space in the target database data files
SELECT
    name         AS logical_file,
    CAST(size / 128.0 AS DECIMAL(10,2)) AS allocated_MB,
    CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS DECIMAL(10,2)) AS used_MB,
    CAST((size - FILEPROPERTY(name, 'SpaceUsed')) / 128.0 AS DECIMAL(10,2)) AS free_MB
FROM sys.database_files
WHERE type_desc = 'ROWS';

Version Check

SELECT @@VERSION;
-- Minimum supported: SQL Server 2016 (13.x)
-- SQL Server 2016+ required for JSON_VALUE / OPENJSON support
-- SQL Server 2017+ recommended for STRING_AGG and improved JSON perf

Open MigrationBridge and navigate to the Connections tab. Configure the source (MongoDB) and target (SQL Server) connection profiles.

Click Run Assessment on the Assessment tab. MigrationBridge connects to MongoDB, iterates each collection, and samples up to the first 1,000 documents to build a candidate SQL schema. No data is written to SQL Server at this stage.

How Schema Inference Works

• MigrationBridge scans the first 1,000 documents per collection (configurable via migrationbridge.conf key schema_sample_size).
• For each field encountered, the BSON type is mapped to a SQL Server type (see Type Conversions).
• If a field appears in fewer than 50% of sampled documents, the resulting column is marked NULL (nullable). Fields present in ≥50% are inferred as NOT NULL unless a null value is observed.
• The widest compatible type wins when a field holds mixed types across documents (e.g., some documents have Int32, others have String for the same field → column becomes NVARCHAR(MAX)).

Field Name Sanitization

MongoDB allows field names that are not valid SQL Server identifiers. MigrationBridge applies the following sanitization rules automatically:

_id Field Handling

The _id field is excluded automatically from schema inference and from the migration. It is never written to SQL Server. A new surrogate key column _row_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY is added to every table instead.

On the Select Objects tab, choose which collections to migrate. Each collection maps to a SQL Server table of the same name under schema dbo.

Click Start Migration on the Migration tab. MigrationBridge processes each selected collection sequentially: schema inference → CREATE TABLE → bulk insert. Progress is shown in real time in the log panel.

IDENTITY_INSERT Handling

MigrationBridge creates tables with an INT IDENTITY(1,1) surrogate key. Row values are inserted with IDENTITY_INSERT OFF (the default), meaning SQL Server auto-assigns the _row_id. The tool does not use SET IDENTITY_INSERT ON — there are no source identity values to preserve since _id is excluded.

After migration completes, validate row counts match the MongoDB source, run integrity checks, and spot-check field mapping on a sample of documents.

Row Count Validation

-- Quick row counts for all migrated tables
SELECT
    t.name                                  AS table_name,
    p.rows                                  AS row_count
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    t.is_ms_shipped = 0
ORDER BY p.rows DESC;

-- Total row count across all user tables
SELECT SUM(p.rows) AS total_rows
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  t.is_ms_shipped = 0;
-- Expected for AdventureWorks2019: 759,240

DBCC CHECKDB

-- Run integrity check on the target database (read-only, no repair)
DBCC CHECKDB (N'AdventureWorks2019') WITH NO_INFOMSGS, ALL_ERRORMSGS;
-- A clean run returns: "DBCC execution completed. If DBCC printed error messages, contact your system administrator."
-- No error messages = pass.

Spot-Check: Document Field Mapping

-- Compare a known document from MongoDB against its SQL row
-- MongoDB source (mongosh):
--   db.Product.findOne({ Name: "Mountain-100 Black, 38" })

-- SQL Server target:
SELECT TOP 5 *
FROM   dbo.Product
WHERE  Name = 'Mountain-100 Black, 38';

-- Verify nested JSON field (if ProductModel was a sub-document):
SELECT
    Name,
    JSON_VALUE(ProductModel, '$.Name') AS model_name,
    JSON_VALUE(ProductModel, '$.ModifiedDate') AS model_modified
FROM dbo.Product
WHERE ProductModel IS NOT NULL;

MigrationBridge creates tables and loads data but does not create indexes, foreign keys, or statistics. These must be added manually after migration for production use.

Add Indexes

-- Example: non-clustered index on a high-cardinality column
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID
    ON dbo.SalesOrderHeader (CustomerID)
    INCLUDE (OrderDate, TotalDue);

-- Example: index for Order Detail lookup by header
CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_SalesOrderID
    ON dbo.SalesOrderDetail (SalesOrderID)
    INCLUDE (ProductID, OrderQty, UnitPrice);

UPDATE STATISTICS

-- Update statistics on all user tables after bulk load
EXEC sp_updatestats;

-- Or target a specific table:
UPDATE STATISTICS dbo.SalesOrderDetail WITH FULLSCAN;

JSON Path Index (SQL Server 2016+)

Columns holding serialised nested documents or arrays (NVARCHAR(MAX) JSON strings) benefit from computed column indexes. Use this pattern to index a specific JSON path for fast lookups:

-- Step 1: Add a computed column that extracts the JSON path value
ALTER TABLE dbo.Product
    ADD ProductModel_Name AS JSON_VALUE(ProductModel, '$.Name');

-- Step 2: Create an index on the computed column
CREATE NONCLUSTERED INDEX IX_Product_ModelName
    ON dbo.Product (ProductModel_Name)
    WHERE  ProductModel_Name IS NOT NULL;

-- Now this query uses the index:
SELECT * FROM dbo.Product WHERE JSON_VALUE(ProductModel, '$.Name') = 'HL Road Frame';

Post-Migration Checklist

⚡ Live Change Streams — MongoDB → SQL Server

MigrationBridge supports continuous replication from MongoDB to SQL Server using MongoDB Change Streams. This enables near-real-time synchronisation after the initial full load completes.

Full-Load-First Sequence (Critical Order)

The correct sequence for zero-data-loss live replication is:

1. Save the current oplog LSN (resume token) before the first row is inserted into SQL Server.
2. Run the full load — migrate all collections to SQL Server.
3. Start tailing the Change Stream from the saved resume token. This ensures no changes made during the full load are missed.

MigrationBridge handles this sequence automatically when Live Replication mode is selected. Do not start the Change Stream consumer manually before the full load completes — doing so risks missing or double-applying changes.

Oplog Retention: 30-Day Window

For reliable Change Stream resumability (e.g., after a MigrationBridge restart), configure a minimum 30-day oplog retention:

// MongoDB 4.4+ -- resize oplog to 50 GB and enforce 720-hour (30-day) minimum retention
db.adminCommand({
  replSetResizeOplog: 1,
  size: 51200          // size in MB (50 GB)
})

db.adminCommand({
  replSetResizeOplog: 1,
  minRetentionHours: 720   // 30 days = 720 hours
})

// Verify
db.adminCommand({ replSetGetConfig: 1 }).config.settings

DML Replication: INS / UPD / DEL

MigrationBridge tails the Change Stream and applies the following operations to SQL Server in real time:

Validation Test

// MongoDB (mongosh) -- insert a test document
db.Product.insertOne({
  Name: "_MigrationBridge_Test",
  ProductNumber: "MB-TEST-001",
  ListPrice: 0.00
})

// Wait ~2-5 seconds, then check SQL Server:
SELECT * FROM dbo.Product WHERE Name = '_MigrationBridge_Test';
-- Expected: 1 row returned

// Update in MongoDB:
db.Product.updateOne(
  { Name: "_MigrationBridge_Test" },
  { $set: { ListPrice: 9.99 } }
)

// Verify update replicated:
SELECT ListPrice FROM dbo.Product WHERE Name = '_MigrationBridge_Test';
-- Expected: 9.99

// Cleanup:
db.Product.deleteOne({ Name: "_MigrationBridge_Test" })
DELETE FROM dbo.Product WHERE Name = '_MigrationBridge_Test';

⇄ Type Conversions

The following table shows how MigrationBridge maps MongoDB BSON types to SQL Server column types during schema inference and data load.

⚠️ Known Issues & Limitations

General

Cosmos DB Mongo API

Atlas

🔀 UI Busy / Not Responding

During high-throughput bulk copy operations, the MigrationBridge UI may display "Not Responding" in the Windows title bar. This is expected behaviour and does not indicate a crash or error. The migration continues running on a background thread; the UI thread is temporarily blocked by dashboard refresh polling.

How to Confirm the Migration Is Still Running

• Open Task Manager (Ctrl+Shift+Esc) → Details tab. Look for python.exe — if CPU usage is above 0%, the migration is active.
• Tail the live log file at C:\MigrationT\migration_logs\ — new lines are written every few seconds during active migration.
• Check SQL Server row counts on the target table to confirm rows are being inserted.

Fix: Change 143

This behaviour was addressed in MigrationBridge Change 143 (included in v1.3.0). The fix implements two improvements:

• Reduced poll batch size — the background thread now processes rows in smaller batches, yielding the GIL more frequently and allowing the UI thread to update between batches.
• Deferred dashboard refresh — the live statistics panel (rows/sec, progress bar) now updates every 2 seconds instead of after every batch commit, reducing UI thread contention during sustained high-throughput copy.

If you are running a version prior to v1.3.0, upgrade to resolve the Not Responding behaviour. Upgrade instructions: User Guide → Upgrading MigrationBridge.