MongoDB to SQL Server: 2 Easy Methods to Load Data

Are you thinking of migrating from MongoDB to SQL, a more traditional relational database environment? If so, then you have come to the right place! 

Moving data from MongoDB to SQL Server destination can be quite a challenge because MongoDB data is a non-relational database. Any such data movement must involve first defining a target relational data model in SQL Server, and then developing a transformation that can take the data in MongoDB and transform it into the target data model.

This blog will discuss three ways of achieving data migration 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 one that is best suited for your organization’s needs.

What is MongoDB?

MongoDB is an Open-Source, Document Database Management Platform. It is a Non-Relational Database that stores its Objects (Documents) in a JSON-like format known as BSON.

BSON (or Binary JSON) is a Binary Serialization of JSON with extensions that allow for the representation of non-JSON Data Types. For example, BSON supports a ‘Date’ Data Type, which would be otherwise stored as a String Type in JSON.

The basic unit of data in MongoDB is a set of Key-Value Pairs that allow Documents to have different fields and functions. BSON is used instead of SQL to access data from the Database due to its Non-Relational Architecture.

For more information on MongoDB Documents and BSON Data Type, you can follow MongoDB’s official documentation.

Key Features of MongoDB

• Query and Index Support: MongoDB provides support for Ad-hoc and Document-based Queries. Furthermore, you can index any field in the document. 
• MapReduce: MongoDB supports MapReduce for Data Processing. MapReduce consolidates large volumes of data into useful aggregated results.
• Multiple Server Support: MongoDB can run over multiple Servers. This duplication can insulate the system against Hardware Failure.
• Scalability: MongoDB has exceptional scalability when compared to traditional Relational Database options. It supports Horizontal Scaling (sharding) which makes it possible for you to include additional Instances with increased capacity when the need arises.

What is Microsoft SQL Server?

SQL Server is a Microsoft-developed Relational Database Management System with the primary function of storing and retrieving data. SQL Server runs on both Windows and Linux environments.

SQL Server supports two flavors of SQL to query its data – ANSI SQL, which is the standard implementation of SQL, and T-SQL (Transact – SQL) which is Microsoft’s implementation of SQL. T-SQL provides further capabilities of Variable Declaration, Exception Handling, etc. 

Key Features of SQL Server

• Secure Encryption: SQL Server has a secure enclave technology that extends data trust to the Server side, thus securing the data from malware.
• Database Engine: SQL Server has a Database Engine that handles Transaction Processing, Storage, etc.
• SQL Server WSS Writer: SQL Server WSS Writer allows you to back up and restores your Data Files when SQL Server is not running.
• SSAS, SSRS, and SSIS: SQL Server provides these 3 services which provide Analytics, Business Reporting, and Data Integration Services respectively.

Find more information on Microsoft SQL Server’s best data tools for 2022 here.

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

• Method 1: Moving Data from MongoDB to SQL Server by Manually Building ETL Scripts.
• Method 2: Moving Data from MongoDB to SQL Server using SSIS
• Method 3: Moving Data from MongoDB to SQL Server using Hevo.

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

Prerequisites

Listed below are the prerequisites for moving data from MongoDB to SQL Server by manually building ETL Scripts:

• Working knowledge of MongoDB and JSON/BSON.
• A fully set up SQL Server database.

Now, that you are all done with the prerequisites, follow the steps below to manually move data from MongoDB to SQL Server:

• 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.

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 who are not used to working with non-traditional databases.

Documentation on MongoDB data types can be found here: https://docs.mongodb.com/manual/reference/bson-types/ 

SQL Server data types can be accessed through this link:

https://docs.microsoft.com/en-us/sql/t-sql/data-types-transact-sql?view=sql-server-ver15

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.

The book data is from:

https://github.com/tamingtext/book/blob/master/apache-solr/example/exampledocs/books.json

Limitations of Manually Loading Data from MongoDB to SQL Server ETL

• Time-Consuming: You have to write up a lot of code to complete this process. This may not be particularly favorable for fast-paced organizations that have to meet tight deadlines.
• Real-time Data Load Limitations: The above method would work best if you are looking to load data once in a while. If you are looking to stream data from MongoDB to SQL Server in real-time, you have to 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 2: 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. For information on importing data from MongoDB to SQL Server, you can visit here.

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.

If you run the package, 31 rows will be exported, as illustrated in the image given below.

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

Hevo allows you to ETL MongoDB to SQL Server without writing any code. Hevo is fully managed, thus, requires very little time to implement and set it up. You can replicate your MongoDB data in SQL Server using these 2 simple steps:

• Step 1: Authenticate and connect to your MongoDB database.
• Step 2: Connect and move your data to SQL Server.

Sign up here for a 14-Day Free Trial!

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

Additionally, Hevo will also take care of can also move data from a variety of sources including SDKs, Cloud applications, Databases, and more. 

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. 

More Reasons to Try Hevo:

• Minimal Setup: Hevo is fully managed and automated and so requires minimal effort on your part to set up
• Scalability: Hevo perfectly handles data from a wide variety of sources like analytics applications, sales, and marketing applications, databases, etc. at any scale. Thus, it is able to help you scale your data infrastructure to meet your growing needs. 
• Real-time: Hevo’s real-time streaming architecture ensures that you can move your data instantly. Thus, enabling you to gain real-time data insights
• Reliable Data Load: Hevo has a fault-tolerant architecture. This ensures that your data loads are reliable, consistent, and done with minimal data loss.
• Simplicity: Hevo is easy to use and intuitive. Thus, using Hevo ensures that your data is transferred in just a few clicks.

Conclusion

This article provided you with an in-depth understanding of MongoDB, the No-SQL Database, Microsoft SQL Server, and various methods which you can use to migrate data from MongoDB to SQL Server.

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.

You can also visit the following links to have a comprehensive understanding of MongoDB vs SQL Databases, MongoDB vs PostgreSQL, and MongoDB vs MySQL.

Visit our Website to Explore Hevo

Hevo Data offers a faster way to move data from 150+ data sources such as SaaS applications or Databases such as MongoDB, Oracle, etc. into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share with us your understanding of moving data from MongoDB to SQL Server in the comments section below. We would be delighted to hear from you!