MongoDB to SQL Server: 2 Easy Methods to Load Data

Moving data from MongoDB to SQL Server destination can be challenging because MongoDB data is a non-relational database. Converting MongoDB to SQL Server involves exporting and importing MongoDB data into SQL Server tables. Any such data movement must first define a target relational data model in SQL Server, then develop a transformation that can take the data in MongoDB and transform it into the target data model.

This blog will discuss the basics of MongoDB and SQL Server and the three ways to migrate data from MongoDB to SQL Server. All the methods have been presented in a detailed form with relevant examples so that you can evaluate the two and select the best suited for your organization’s needs.

What is MongoDB? An Overview

MongoDB is a popular open-source, document-oriented NoSQL database that stores data in flexible, JSON-like documents. Since its release in 2009, MongoDB has become one of the most widely used databases for modern applications that require scalability, flexibility, and high availability.

MongoDB leverages a binary form of JSON known as BSON to store and transmit document data. BSON extends JSON with additional data types like dates and binaries while retaining the flexible structure. Documents in MongoDB can embed arrays, other documents, and data in a nested format. This hierarchical representation allows complex relationships and varied data schemas within the same database.

A key advantage of the document storage approach in MongoDB is that it does not enforce strict schemas or require predefined record layouts. Developers can store different shaped documents in a single collection dynamically as needs change. This schemaless design makes it simpler to model data and make iterative changes compared to rigid SQL table designs, which depend on predefined columns.

What is SQL Server? An Overview

Microsoft SQL Server is a trusted relational database management system (RDBMS) that has existed since the late 1980s. At its core, SQL Server provides a robust database engine optimized for processing transactional workloads and executing complex analytical and data transformation tasks.

The database engine consists of several key components working together – a relational engine that handles query parsing and optimization, a storage engine responsible for all data storage/retrieval, and a buffer manager enabling fast data access. These build on advanced features for in-memory tables, column store indexing, and partitioning to deliver performance.

Beyond the database itself, SQL Server includes a rich set of tightly integrated additional services. SQL Server Integration Services (SSIS) offers a graphical workflow tool for building ETL data pipelines. SQL Server Analysis Services (SSAS) enables developers to create multidimensional cubes and data models for fast analytics. Finally, SQL Server Reporting Services (SSRS) provides enterprise reporting and dashboard capabilities.

Why do you need to move data from MongoDB to SQL server?

The advantages of moving data from MongoDB to SQL Server are as follows:

• SQL Server has always thrived on its ability to share data with a whole range of different databases and data services.
• Getting data out of MongoDB and into an RDBMS can aid one in layer analytical or reporting tools on top.
• SQL can also be used to conduct advanced analytics functions on data such as filters, joins, merges, and aggregation.

Methods to Move Data from MongoDB to SQL Server

You may employ custom scripts or ETL (Extract, Transform, Load) processes to efficiently export MongoDB to SQL Server. Here are the different methods to Connect MongoDB to SQL Server.

Method 1: Moving Data from MongoDB to SQL Server using Hevo

With Hevo, you can replicate your MongoDB data in SQL Server using these 2 simple steps:

Step 1: Configure MongoDB as your Source Connector

• Click PIPELINES in the Navigation Bar.
• Click + CREATE in the Pipelines List View.
• In the Select Source Type page, select MongoDB as the source.
• Specify the MongoDB connections settings as follows:

Step 2: Select SQL Server as your destination

• Click DESTINATIONS in the Navigation Bar.
• Click + CREATE in the Destinations List View.
• In the Add Destination page, select SQL Server.
• In the Configure your SQL Server Destination page, specify the following:

Hevo will then move your data from MongoDB to MS SQL Server in a consistent, reliable, and secure manner.

Hevo automatically maps your NoSQL MongoDB data to its relevant tables in SQL Server, giving you access to your data in real-time. Sign up for a free no-risk 14-day trial with Hevo for a smooth data transfer. 

For more information of MongoDB to SQL Server Integration, refer to the Hevo documentation:

• MongoDB source connector
• SQL Server as Destination

Method 2: Moving Data from MongoDB to SQL Server by Manually Building ETL Scripts

Follow the steps below to move data from MongoDB to SQL Server manually:

• Step 1: Extract your Data from MongoDB
• Step 2: Transform the Data
• Step 3: Load your Data

Step 1: Extract your Data from MongoDB

• Use the mongodump command to create a copy/backup of your Mongodb data to a specified directory. The mongodump command can take the following syntax:

 mongodump -- dbpath/data/db/ -- out/data/backup/

• Use the bsondump command to convert the dump files from BSON to JSON format to prepare the file for a direct load into SQL Server.

You can also use a MongoDB to SQL converter tool, users can transform MongoDB collections into equivalent SQL tables, preserving the structure and integrity of the data.

Do you want to export data into JSON format? Find more information on MongoDB to JSON export here.

Step 2: Transform the Data

You have to be very careful to ensure that the data types in MongoDB are accurately transformed into their respective MS SQL Server equivalent. This is especially important as MongoDB is non-relational and may pose a challenge to developers unfamiliar with non-traditional databases.

You may also have to flatten out or un-nest your MongoDB data as there is a high probability that some of the data will be nested

Step 3: Load your Data

You can use the OPENJSON function to parse the JSON into a SQL Server table with the JSON properties as columns. 

Example code:

SELECT book.*
FROM OPENROWSET (BULK 'C:JSONBooksooks.json', SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn)
WITH( id nvarchar(100), name nvarchar(100), price float,
pages_i int, author nvarchar(100)) AS book

Will return a result of a table with Id, Name, price, pages_i, and Author as columns.

Limitations of Manually Loading Data from MongoDB to SQL Server ETL

• Time-Consuming: You must write up a lot of code to complete this process. This may not be particularly favorable for fast-paced organizations meeting tight deadlines.
• Real-time Data Load Limitations: The above method would work best if you want to load data occasionally. If you want to stream data from MongoDB to SQL Server in real-time, you must perform the above steps repetitively by setting up a cron job. This would need additional code to be written. 
• Resource Intensive: This method requires a lot of engineering bandwidth and may not be particularly well-suited to small organizations that may not have expert data developers
• Difficulty with Data Transformations: Data transformations have to be performed manually, which is a tedious process. Furthermore, there is no way to perform quick data transformations like time and data changes, currency conversions, etc.

Method 3: Moving Data from MongoDB to SQL Server using SSIS

There are three ways to import data from MongoDB to SQL Server using SSIS. These are as follows:

1. Using Third-party Connectors
2. Using a Script Component
3. Using the ODBC connection

1) Using Third-party Connectors

There are different commercial source components provided by companies such as CDATA and ZappySYS.

2) Using a Script Component

Users without coding experience may not prefer this solution since it involves a lot of coding for creating a script.

3) Using the ODBC Connection

There are several processes to be followed to create a MongoDB ODBC data source and then connect MongoDB to SQL Server:

• Install Microsoft Visual C++ 2015 Redistributable
• Install MongoDB BI Connector
• Install MongoDB ODBC Driver
• Generating drdl Schema File
• Start MongoDB BI Connector Service
• Define an ODBC Data Source
• Reading Data using ODBC Source

A) Install Microsoft Visual C++ 2015 Redistributable

First, you need to install the Microsoft Visual C++ 2015 redistributable since it is a prerequisite for the MongoDB BI Connector.

B) Install MongoDB BI Connector

First, download and install the MongoDB ODBC BI Connector for Windows for importing data from MongoDB to SQL Server using SSIS.

Connecting to MongoDB via ODBC is not established on the database engine service. It requires a service called MongoSQL that is installed within the BI connector.

C) Install MongoDB ODBC Driver

The third step in the process of importing data from MongoDB to SQL Server using SSIS is to install the MongoDB BI connector ODBC Driver.

Because you are using SSIS and Visual Studio only reads connections for the 32-bit platform, you must also install the 32-bit drivers if you are using a 64-bit installation.

D) Generating drdl Schema File

Because MongoDB is a NoSQL database, you must first define a relational schema file that allows the ODBC driver to read documents data relationally into import data from MongoDB to SQL Server using SSIS.

Within the BI connector, there is a tool called mongodrdl.exe.

You can generate a .drdl file from an existing connection within your database using the following command:

mongodrdl -d <databasename> -c <collection name> -o <output name>

In the following example, the host address is not specified since this tool takes by default “localhost:27017”. In this case, drdl schema is generated from DemoCollection1.

If you open the generated file using a text editor, then it will look like the following:

The image above displays the Generated drdl schema content.

E) Starting the BI Connector Service

After installing the BI connector, navigate to the installation directory and create a new file named “mongosqld.config.” You must use a text editor to access this file and copy the following lines:

net:
bindIp: “127.0.0.1”
port: 3307
systemLog:
path: ‘C:Program FilesMongoDBConnector for BI2.14logsmongosqld.log’
quiet: false
verbosity: 1
logRotate: “rename”
processManagement:
service:
name: “mongosql”
displayName: “MongoSQL Service”
description: “MongoSQL accesses MongoDB data with SQL”
schema:
path: ‘C:DemoCollection1.drdl’

Note: Specified directories must exist.

Now, it’s time to install and activate this service. After moving the current directory to the BI connector binaries folder, open a command prompt as administrator and run the following commands:

mongosqld install –config “<BI connector installation directory>mongosqld.config”
net start mongosql

The above image showcases the code for creating the mongosql service.

F) Defining an ODBC Data Source

After installing the ODBC driver, the next step to import data from MongoDB to SQL Server using SSIS is to define a system DSN within Windows.

To do that, the following steps are needed to be followed:

• Step 1: Navigate to “Control Panel“.
• Step 2: Select the “Administrative Tasks” option. Then click on the “ODBC data sources administration” option.
• Step 3: “ODBC Data Source Administrator” dialog box appears. Now, in the System DSN tab, click on the “Add” button.

• Step 4: In the “Create New Data Source” dialog box that appears, select the MongoDB Unicode driver.
• Step 5: Click on the “Finish” button.

• Step 6: In the MongoDB ODBC Data Source Configuration dialog box that appears, specify a data source name, enter the mongosql service address (the port specified in the configuration file, not the database engine address), and the database name as shown in the image below:

• Step 7: Now, you would receive a “Connection successful” message after clicking on the “Test” button.
• Step 8: Click on the “OK” button in the Test Result dialog box. Now, click on “OK” in the MongoDB ODBC Data Source Configuration window.

Because Visual Studio only reads 32-bit ODBC data sources, if you use a 64-bit installation, the same steps must be repeated within the 32-bit ODBC data sources.

G) Reading Data using ODBC Source

The steps for reading data using ODBC Source are as follows:

• Step 1: Open Visual Studio and create a new Integration Services project.
• Step 2: Add a new connection on the Connection Managers tab, and then select ODBC connection from the connection managers list.

• Step 3: To provide the destination database name, add an OLE DB connection manager for the SQL Server destination.
• Step 4: Further, within the Control flow, add a Data Flow Task.
• Step 5: You also need to include an ODBC Source to read from MongoDB and an OLE DB destination to select (or create) the destination table in the Data Flow Task.

The above image illustrates the process of selecting the MongoDB collection within the ODBC Source.

The above image illustrates the process of creating a destination table from the OLE DB destination component.

Limitations of using SSIS for MongoDB to SQL Server Migration

• SSIS is better suited for batch data transfers but not ideal for constantly syncing live data streams. You’d need to schedule frequent package executions or develop additional logic for real-time updates.
• Depending on data volume and complexity, SSIS data transfers might not be the most efficient method. Large datasets or intricate transformations could lead to slow processing times.
• Setting up SSIS packages for data migration involves configuration and potentially scripting knowledge. This can require additional development effort compared to some specialized tools.

Common Challenges Faced During Migration

• Schema and Data Type Mismatches
  • Challenge: MongoDB is a NoSQL database using a flexible, schema-less data model, whereas SQL Server uses a rigid, predefined schema. This mismatch makes it hard to map MongoDB’s dynamic schema and diverse data types—like arrays or nested documents—into structured tables and columns in SQL Server.
  • Impact: Inaccurate/incomplete data mapping can result in problems in data integrity, loss of data, and further querying and analytical issues.
• Data Transformation Complexity
  • Challenge: Most of the data in MongoDB requires a huge amount of transformation to fit into the relational structure in SQL Server. For example, nested documents and arrays in MongoDB have to be flattened or divided into several tables with relationships in SQL Server.
  • Impact: More complex transformations offer more possibilities for failure and add new challenges to the ETL process, which may relate to delays or additional development.
• Performance and Scalability Issues
  • Challenge: Migrating vast volumes of data from MongoDB to SQL Server can severely stress performance and scalability in any case, either because your data is not efficiently indexed, or the migration process itself is unoptimized with regard to performance.
  • Impact: Poor performance during migration can cause disruptions longer than expected or slow data loads, or sometimes even business operations at large.

Conclusion

Moving data from MongoDB to SQL server gives more structure to the data ensuring data quality and consistency owing to SQL Server’s relational data model.

This article provided you with an in-depth understanding of the three methods which you can use to migrate data from MongoDB to SQL Server:

• Automated look like Hevo
• Manually Building ETL Scripts
• Using SSIS

MongoDB or SQL Server may be used by enterprises and organizations of all sizes that need to scale out large amounts of data and traffic. If you want to use data quickly and with a lot of flexibility, a no-code platform like Hevo is the way to go. Sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

Frequently Asked Questions MongoDB to SQL Server