This will do the following:
- Create instances of mssql, elasticsearch, logstash, and kibana
- Download WorldWideImporters sample database backup and restore it
- Create an index in elasticsearch with the Invoices table using logstash jdbc input with a scheduler to sync it based on the LastUpdatedWhen field
git clone https://github.com/idemery/mssql-elasticsearch-demoDownload the mssql jdbc driver from https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server
Copy the jdbc jar file mssql-jdbc-9.2.1.jre8.jar to mssql-elasticsearch-demo/logstash/
cd mssql-elasticsearch-demo docker-compose build docker-compose upThat's it. You should be able to see the containers firing up, mssql will wait 15 seconds at build for sql server to be ready before restoring the backup.
Navigate to http://localhost:9200/idx_wwi_invoices/_search?pretty=true&q=*:*&size=100 to make sure the index is created and loaded with data.
Start using Kibana on http://localhost:5601/ and create a new index pattern from Analytics > Discover using index name idx_wwi_invoices
Password used for the demo db is qweQWE123
. ├── docker-compose.yml ├── logstash │ ├── config │ │ └── logstash.config │ ├── Dockerfile │ └── mssql-jdbc-9.2.1.jre8.jar └── mssql └── DockerfileFROM mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04 ENV SA_PASSWORD "qweQWE123" ENV ACCEPT_EULA "Y" USER root ADD --chown=mssql:root https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak /var/opt/mssql/backup/wwi.bak USER mssql RUN /opt/mssql/bin/sqlservr --accept-eula & (echo "awaiting mssql bootup for 15 seconds" && sleep 15 && echo "restoring.." && /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'qweQWE123' -Q 'RESTORE DATABASE WideWorldImporters FROM DISK = "/var/opt/mssql/backup/wwi.bak" WITH MOVE "WWI_Primary" TO "/var/opt/mssql/data/WideWorldImporters.mdf", MOVE "WWI_UserData" TO "/var/opt/mssql/data/WideWorldImporters_userdata.ndf", MOVE "WWI_Log" TO "/var/opt/mssql/data/WideWorldImporters.ldf", MOVE "WWI_InMemory_Data_1" TO "/var/opt/mssql/data/WideWorldImporters_InMemory_Data_1"') FROM docker.elastic.co/logstash/logstash:7.13.2 RUN rm -f /usr/share/logstash/pipeline/logstash.conf USER root COPY mssql-jdbc-9.2.1.jre8.jar /usr/share/logstash/logstash-core/lib/jars/mssql-jdbc-9.2.1.jre8.jar input { jdbc { jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver" jdbc_connection_string => "jdbc:sqlserver://sql1:1433;databaseName=WideWorldImporters;user=sa;password=qweQWE123" jdbc_user => "sa" jdbc_password => "qweQWE123" jdbc_paging_enabled => true tracking_column => "unix_ts_in_secs" use_column_value => true tracking_column_type => "numeric" schedule => "*/59 * * * * *" statement => "SELECT [InvoiceID] ,[CustomerID] ,[BillToCustomerID] ,[OrderID] ,[DeliveryMethodID] ,[ContactPersonID] ,[AccountsPersonID] ,[SalespersonPersonID] ,[PackedByPersonID] ,[InvoiceDate] ,[CustomerPurchaseOrderNumber] ,[IsCreditNote] ,[CreditNoteReason] ,[Comments] ,[DeliveryInstructions] ,[InternalComments] ,[TotalDryItems] ,[TotalChillerItems] ,[DeliveryRun] ,[RunPosition] ,[ReturnedDeliveryData] ,[ConfirmedDeliveryTime] ,[ConfirmedReceivedBy] ,[LastEditedBy] ,[LastEditedWhen], DATEDIFF_BIG(ms, '1970-01-01 00:00:00', LastEditedWhen) AS unix_ts_in_secs FROM [WideWorldImporters].[Sales].[Invoices] WHERE (DATEDIFF_BIG(ms, '1970-01-01 00:00:00', LastEditedWhen) > :sql_last_value AND LastEditedWhen < getdate()) " } } filter { mutate { copy => { "invoiceid" => "[@metadata][_id]"} remove_field => ["invoiceid", "@version", "unix_ts_in_secs"] } } output { # stdout { codec => "rubydebug"} elasticsearch { hosts => [ "elasticsearch:9200"] index => "idx_wwi_invoices" document_id => "%{[@metadata][_id]}" } } version: '2.2' services: mssql: build: ./mssql container_name: sql1 restart: always ports: - 1433:1433 networks: - elastic volumes: - mssql:/var/opt/mssql elasticsearch: image: elasticsearch:7.13.2 container_name: elasticsearch environment: - cluster.name=docker-cluster - bootstrap.memory_lock=true - discovery.type=single-node - "ES_JAVA_OPTS=-Xms512m -Xmx512m" ulimits: memlock: soft: -1 hard: -1 volumes: - data:/usr/share/elasticsearch/data restart: always depends_on: - mssql ports: - 9200:9200 networks: - elastic logstash: build: ./logstash environment: LS_JAVA_OPTS: "-Xmx256m -Xms256m" ports: - 5001:5001 container_name: logstash restart: always networks: - elastic depends_on: - elasticsearch volumes: - ./logstash/config:/usr/share/logstash/pipeline kibana: image: docker.elastic.co/kibana/kibana:7.13.2 environment: SERVER_HOST: 0.0.0.0 ELASTICSEARCH_HOSTS: http://elasticsearch:9200 container_name: kibana depends_on: - elasticsearch ports: - 5601:5601 networks: - elastic volumes: data: driver: local mssql: driver: local networks: elastic: driver: bridge