Integrating Elasticsearch with MS SQL, Logstash, and Kibana

Introduction

MS SQL Server holds the data in relational form or even multi-dimensional form (through SSAS) and proffers several out-of-the-box search features through Full Text Search (FTS).

However, the search function of the modern-world applications has many complexities. The search specifications are hybrid and the queries demand full-scale searching over massive data sets. A better solution is required to perform such advance level of searches and that is where Elasticsearch grabs attention from technology experts.

Elasticsearch is a substantial REST HTTP service that enables scaling of operations even up to thousands of queries per second. Its features, such as Facets and Aggregation framework, assist in resolving many data analyses related issues as well. Hence, integration of Elasticsearch with any relational database can be proved a powerful value addition to the application.

How can we Integrate Elasticsearch with MS SQL?

The entire integration process of MS SQL and Elasticsearch along with Data-collection and log-parsing engine – Logstash, analytics and visualization platform – Kibana is described here in five simple steps.

Step 1: Environment Setup

Please find the directions to setup the integration environment with their purposes (where applicable):

  • Download and install Java using the URL https://java.com/en/download - Set the Java path in Path Environment variable and set JAVA_HOME to "C:\Program Files\Java\jre1.8.0_151"

    Purpose: Elasticsearch provides a Java API and it executes all operations in an asynchronous manner using the client object. The client object can cumulatively execute all operations in bulk. The Java API is used in order to execute all APIs in Elasticsearch.

  • Download SqlServerJDBC Driver using the URL https://www.microsoft.com/en-us/download/details.aspx?id=55539

    Purpose: Since Elasticsearch is developed in Java, we need to install JDBC driver in order to be connected with SQL Server

  • Extract the driver to "C:\Program Files".

  • Copy sqljdbcauth.dll from "C:\Program Files\sqljdbc6.0\enu\auth\x64" and paste to the location "C:\Program Files\Java\jre1.8.0_151\bin".

    Purpose: This will authorize Java to access JDBC driver

  • Add "C:\Program Files\sqljdbc_6.0\enu\auth\x64" to the Environment variable Path.

    Java environment variable

    Purpose: Environment variables are set to enable processes such as:

    • Allowing access to command line tools
    • Enabling other tools to interact with SDKs more easily

Step 2: Elasticsearch Setup

Please find instructions to perform the setup for Elasticsearch:

Step 3: Logstash Setup

Please find instructions to perform the setup for Logstash:

  • Download Logstash in ZIP format and extract it to the C drive using the URL https://www.elastic.co/downloads/logstash

    Purpose: Logstash enables the application to collect data from different systems. Moreover, it normalizes different schemas. It enables you to keep the data gathered from various systems into a common format. As a result:

    • You can interact with data collected from different systems simultaneously. Additionally, you can compare data sets or even see how they influences one another
    • Visualization tools such as Kibana and analytics engines such as Elasticsearch can make the best out of complex data
  • Add jre7 and jre8 to "C:\Program Files\sqljdbc_6.0\enu" using the URL http://www.java2s.com/Code/Jar/s/Downloadsqljdbc420jar.htm

  • Create a file having the name logstash.conf, add this file under the "logstash/bin" folder.

  • Open Command prompt with Administrator rights, navigate to the "nssm\win64" folder and write nssm install Logstash

    nssm install Logstash

  • Navigate to the Logstash folder and provide argument as below:

    nssm service installer Logstash

    nssm service installer Logstash dependency

  • Open logstash.conf using Notepad (or any other text editor) and add following configuration:

input {  
    jdbc {
        # SqlServer jdbc connection string to our database, employeedb
        #  "jdbc:sqlserver://HostName\instanceName;database=DBName;user=UserName;password=Password" 
        jdbc_connection_string => "jdbc:sqlserver://localhost\SQLExpress;database=employeedb;user=sa;[email protected]"
        # The user we want to execute our statement as
        jdbc_user => nil
        # The path to our downloaded jdbc driver
        jdbc_driver_library => "C:/Program Files/sqljdbc_6.0/enu/jre8/sqljdbc42.jar"
        # The name of the driver class for SqlServer
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        # Query for testing purpose
        statement => "SELECT * from employee"
    }
}
output {  
    stdout { codec => json_lines }
}
  • Navigate to Logstash bin folder from command prompt and provide command "logstash -f logstash.conf". It should return Query result:

    Logstash query result

Step 4: Kibana Setup

Please find instructions to create setup for Kibana:

  • Download Kibana and Unzip to C: drive.

  • Go to the folder of Kibana "C:\kibana-5.6.4-windows-x86\config", remove '#' icon from kibana.yml for uncommenting the properties of Kibana

    Kibana configuration

  • Install Kibana using NSSM

    nssm install Kibana

  • Navigate to Kibana folder as mentioned below:

    nssm installer service Kibana

    nssm service installer Kibana dependency

To check whether Kibana installed or not, explore the local host URL: http://localhost:5601/app/kibana#/management/kibana/index?_g=( )

Step 5: Connecting Elasticsearch with the Application

Install the Nest plugin to utilize Elasticsearch in Visual Studio. To enable database operations through Elasticsearch it is also required to attach ElasticsearchCRUD plugin using the URL https://www.nuget.org/packages/ElasticsearchCRUD/. Download the DLL files and provide it a reference.

Conclusion

In this article, I have described the systematic process of integrating Elastic Stack (Elasticsearch, Logstash, and Kibana) with MS SQL database to make the best out of data sets. I have also tried to share the purpose of each action wherever it is applicable.

These steps are not necessarily limited to MS SQL database, however. As long as you download the relevant drivers, you can integrate Elasticsearch with any other database by following the same procedure.

Due to its excessive data management capabilities, Elasticsearch has potential to deal with the modern era of data eruption challenges, I believe. No wonder, the high-end clientele including Netflix, Uber, Dell, BBC, LinkedIn and eBay, is leveraging it. However, it is still in a young phase and much better is yet to come.

About the Author

Mayank Gaur is a Sr. Software Engineer at Azilen technologies and has about 5 years of experience along with distinctive expertise over .Net and SQL Server. Mayank holds adequate skills for converting ideas into reality along with solution oriented attitude.