MS SQL+ ELASTICSEARCH

Elastic Stack Logstash
1

Hy everybody!

I am new in elasticsearch and beginner in docker also. I need to know what is the best way to connect an ms sql data base to an elastichsearch docker instance?

Thanks for Help!

2

Means you want to sync MSSQL data into elasticsearch ?
If yes, the best way is to use logsatsh with JBBC input
Something like this may help

input {
  jdbc {
    jdbc_driver_library => "/opt/mssql-jdbc-7.5.1.jre9.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://sql1:1433.database.windows.net"
    jdbc_user => "******"
    jdbc_password => "******"
    statement => "SELECT * from TestDB.Inventory;"
  }
}
3

Thank you!

Somewhere can i find a yaml for this solution?

4

by yaml you means a docker-compose configuration ?

5

Yes definitely,

6

You can check more details here
I will suppose you have already an MSSQL database running on a host and you want to sync it to a new Es instance with logstash.

The logstash config looks like this

input {
		jdbc {
				#> jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
				jdbc_connection_string => "jdbc:sqlserver://XXXXX;databaseName=XXX;integratedSecurity=false;"
				jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
				jdbc_user => "user"
				jdbc_password => "*****"
				jdbc_driver_library => "/etc/logstash/mssql-jdbc-6.4.0.jre9.jar"
				statement => "SELECT id,name FROM TestTable where id> :sql_last_value "
				tracking_column => "id"
				use_column_value => true
				clean_run => true
				last_run_metadata_path => "/path/.logstash_jdbc_last_run"
		}
}
output {
    elasticsearch {
        hosts => ["http://elasticsearch:9200"] 
    }
    stdout {
        codec => rubydebug
    }
}

You will need to get the jdbc driver corresponding to your MSSQL instance here

Here is an example of docker-compose that can be used,

version: '3.8'
services:
  elasticsearch:
    image: docker.elastic.co/elasticsearch/elasticsearch:7.10.1
    container_name: elasticsearch
    environment:
      - node.name=elasticsearch
      - cluster.name=elasticsearch
      - bootstrap.memory_lock=true
      - "ES_JAVA_OPTS=-Xms512m -Xmx512m"
      - xpack.security.enabled=false
      - discovery.type=single-node
    ulimits:
      memlock:
        soft: -1
        hard: -1
    volumes:
      - data01:/usr/share/elasticsearch/data
    ports:
      - 9200:9200
    networks:
      - elastic
      
  logstash:
    image: docker.elastic.co/logstash/logstash:7.10.1
    container_name: logstash
    depends_on:
      - elasticsearch
    environment:
      LS_JAVA_OPTS: "-Xmx256m -Xms256m"
    command: logstash -f /etc/logstash/conf.d/logstash-mssql.conf
    volumes:
      - ./logstash/logstash-mssql.conf:/etc/logstash/logstash-mssql.conf
      - ./logstash/mssql-jdbc-6.4.0.jre9.jar:/etc/logstash/mssql-jdbc-6.4.0.jre9.jar
    ports:
      - 9600:9600
    networks:
      - elastic
      
           
volumes:
  data01:
    driver: local

networks:
  elastic:
    driver: bridge

Hope this can help

7

Thank you so much!
It helps a lot.

8

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.