Are you trying to load data from Elasticsearch to SQL Server? If yes, then you are in the right place. Loading data from Elasticsearch to SQL Server can provide a backup of your Elasticsearch data and help you to restore your data in case of failure. A fast and efficient disaster recovery mechanism like this is crucial for real-time data migration.

With real-time data migration, you can analyze your data in business analytics platforms and form better decisions. In this blog, you will learn about Elasticsearch, SQL Server, and two different approaches to connecting Elasticsearch with SQL Server. You will also go through all the limitations of manual methods for Elasticsearch SQL Server integration. 

Elastic Search SQL Server Method 1: Elasticsearch to SQL Server Using Hevo Data

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. Hevo’s pre-built integration with MySQL, SQL Server along with 150+ Sources (including 40+ free Data Sources) will take full charge of the data transfer process, allowing you to set up MySQL to SQL Server migration seamlessly and focus solely on key business activities.

Sign up here for a 14-Day Free Trial!

The steps to load data from Elasticsearch to SQL Server using Hevo Data are as follows:

  1. Authenticate and connect Elasticsearch as your data source.

Click here to read more about using ElasticSearch as a source connector in Hevo.

2. Connect the Microsoft SQL Server as a destination to load your data. 

Click here to read more about using SQL Server as a destination connector with Hevo.

This is how simple it is to integrate Elastic Search in SQL Server with Hevo. Let’s look at some salient features of Hevo:

  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.

Elasticsearch SQL Server Method 2: Elasticsearch to SQL Server Using Manual Method

Migrating your data from Elasticsearch helps you to extract data for in-depth data analysis in other business analytics platforms. It also helps in data restoration and backup. You can use a variety of ETL (Extract, Transform, Load) processes to load data in and out of Elasticsearch storage. 

In this method, you will use ‘elasticdump’ to export the data from Elasticsearch as a JSON file and then import it into SQL Server. Follow these steps to migrate data from Elasticsearch to SQL Server:

Step 1: Extract Data from Elasticsearch

In this step, you will download ‘elasticdump’ and then use it to export Elasticsearch data. It is assumed that you have Node.js installed on your computer.  

  1. Use the following command to download ‘elasticdump’ globally.
npm install elasticdump -g
  1. To extract data from your Elasticsearch, you can use the following command in CMD:
 elasticdump   --input=http://username:password@localhost:9200/company/employee/ 
Elasticsearch to SQL Server: Extracting data using Command Prompt
Image Source: Self
  1. The output file will be created at your desired destination. The following image shows the JSON file created using ‘elasticdump’.
Elasticsearch to SQL Server: JSON Data
Image Source: Self

Step 2: Import Data into SQL Server

For this blog, I have used SQL Server Management Studio 18. To import the JSON file created in the last step, you need to perform the following queries:

Declare @JSON varchar(max)  //creates a variable for storing data

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:UsersOSHIelasticdata.json', SINGLE_CLOB) as j
// loads entire JSON file data in variable

 WITH (firstName nvarchar(50) '$',
       age int '$._source.age',
       experience int '$._source.experienceInYears')
//Puts data in the table
Elasticsearch to SQL Server: SQL Query
Image Source: SQL Editor

The following screenshot shows the loaded data in SQL Server.

Elasticsearch to SQL Server: SQL Server Table
Image Source: SQL Editor

Voila! You have successfully loaded data from Elasticsearch to SQL Server. Now you can use SSAS for the analysis of your data and get better insights from it.

Limitations of Using Manual Method

When you are migrating your data from Elasticsearch to SQL Server using a manual method, there is no real-time data migration. As you are exporting your data by creating a JSON file on your computer and then loading it in SQL Server, the need for real-time data doesn’t get fulfilled. 

When you are dealing with a huge volume of data that is increasing exponentially, then this method will fail to provide insights into the latest data. 

Steps to Insert Data in Elasticsearch

In this section, you will learn how to create an index and insert values in Elasticsearch. You can use Postman for this process. Elasticsearch refers to the database as an index, table as a type, and row as a document.  The REST API format of Elasticsearch is as follows:


Here, HTTP methods can be GET, PUT, POST, and DELETE. The hostname is ‘localhost’, and the port is ‘9200’ by default. Index refers to your database name, and the type refers to your table name. The action can be search, create, etc.

Let’s see the steps to insert data in Elasticsearch:  

  1.  To check your Elasticsearch on the Postman, use the following URL in ‘GET’ and click ‘Send’.

The following output will appear on your screen.

Elasticsearch to SQL Server: Start Elasticsearch
Image Source: Postman
  1. To create an index, use the following URL with ‘PUT’. Here, ‘company’ refers to the name of the index.

If the request returns true in acknowledgment, then you have successfully created your index.

Elasticsearch to SQL Server: Create Index
Image Source: Postman
  1. To post data for the index, you can use the following command in ‘POST’.

Here, ’employee’ is your type, and ‘_create’ refers to the action. You need to place your data in the following format in the ‘Body’ as ‘raw’.

Key: value

An example of the following format is as follows:

Elasticsearch to SQL Server: Elasticdump
Image Source: Postman

You can repeat this step to add data in Elasticsearch using Postman. 

Benefits of Transferring Your Data from Elasticsearch to MS SQL Server

Connecting Elasticsearch to MS SQL Server has several benefits. Here are some of the use cases:

  1. Advanced Analytics: Microsoft SQL Server’s extensive data processing capabilities allow you to run complex queries and perform data analysis on your Elasticsearch data, deriving insights that would not be feasible within Elasticsearch alone.
  2. Data Consolidation: If you’re using various sources in addition to Elasticsearch, synchronizing to Microsoft SQL Server allows you to centralize your data for a more complete picture of your operations, as well as set up a change data collection process to ensure that there are no data conflicts in the future.
  3. Historical Data Analysis: Elasticsearch has limitations with historical data. Syncing data to Microsoft SQL Server enables long-term data retention and trend monitoring over time.
  4. Data Security and Compliance: Microsoft SQL Server includes robust data security capabilities. Synchronizing Elasticsearch data to Microsoft SQL Server protects your data and enables enhanced data governance and compliance management.
  5. Scalability: Microsoft SQL Server can manage massive amounts of data without compromising performance, making it a perfect alternative for developing enterprises with expanding Elasticsearch data.

Before wrapping up, let’s cover some basics.

Introduction to Elasticsearch

Elasticsearch to SQL Server: Elasticsearch
Image Source: Elastic

Elasticsearch is a distributed, open-source analytics and search engine for all types of data like numerical, textual, structured, etc. It was developed on Apache Lucene in 2010. Elasticsearch is known for its REST APIs, scalability, speed, distributed nature, etc.

It is also considered as a central component of Elastic Stack, also known as ELK Stack (Elasticsearch, Logstask, Kibana). Elasticsearch allows you to search, store, and analyze huge volumes of data in real-time.

It can achieve fast responses because it uses indexes for searching. Elasticsearch is widely used in application search, website search, security analysis, business analytics, log analytics, enterprise search, application performance monitoring, etc. 

Related: Looking to replicate data from Elasticsearch to Databricks? Our blog on Elasticsearch to Databricks provides you with two simple and effective methods to achieve this seamless integration. If you’re new to Elasticsearch and want to learn how to ingest data effortlessly, check out our blog on how to ingest data to Elasticsearch.

Introduction to SQL Server

Elasticsearch to SQL Server: SQL Server
Image Source:

Microsoft SQL Server is an RDMS (Relational Database Management System). It is a database server that allows data storage and retrieval capabilities by other applications. It supports ANSI SQL and also comes with its own SQL language, T-SQL (Transact SQL).

SQL Server Management Studio (SSMS) is the interface tool for SQL Server. You can analyze your data using SQL Server Analysis Services (SSAS), generate reports using SQL Server Reporting Services (SSRS), and carry out any ETL operations using SQL Server Integration Services (SSIS). SQL Server also offers features like separate security privileges, reduces cost, maintains standby servers, etc. 

Learn insights and step-by-step instructions on how to export data from MySQL to CSV format, enabling you to effectively manage and analyze your data.


Pre-requisites to connect Elasticsearch to SQL Server:

  1. Download and install Elasticsearch. 
  2. Download and install Microsoft SQL Server. You also need to download SQL Server Management Studio.
  3. Node.js installed on your computer. 


In this blog, you have learned about Elasticsearch, SQL Server, and two different approaches to loading data from Elasticsearch to SQL Server. You also came across various limitations of the manual method. So, if you want to automatically load your data from Elasticsearch to SQL Server in real time, try Hevo Data.

Hevo Data is a No-code Data Pipeline. It supports pre-built integrations from 150+ data sources at a reasonable price. Hevo will fully automate your data flow from Elasticsearch to SQL Server in real-time.


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 look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of loading data from Elasticsearch to SQL Server in the comment section below. 

Oshi Varma
Freelance Technical Content Writer, Hevo Data

Driven by a problem-solving ethos and guided by analytical thinking, Oshi is a freelance writer who delves into the intricacies of data integration and analysis. He offers meticulously researched content essential for solving problems of businesses in the data industry.

No-code Data Pipeline for SQL Server