# Elasticsearch real time data sync with sql server

**URL:** <https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760>\
**Category:** Elasticsearch\
**Created:** [August 12, 2020, 5:11pm UTC](https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760 "2020-08-12T17:11:09Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Coder\_Cub](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/coder_cub/32/45327_2.png) [@Coder\_Cub](https://discuss.elastic.co/u/Coder_Cub)\
**Post date:** [August 12, 2020, 5:11pm UTC](https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760/1 "2020-08-12T17:11:10Z")

</div>

I am new to Elastic Search and I am trying to figure it out below scenarios

1. How/which tool to sync/insert data into Elastic search when new record is created in SQL Server?(Near Real time)
2. How to transformation for legacy data into elastic search?

PS: I am using Elastic 7.8 version

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [August 12, 2020, 5:31pm UTC](https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760/2 "2020-08-12T17:31:26Z")

</div>

> [@Coder\_Cub](#):
>
> How/which tool to sync/insert data into Elastic search when new record is created in SQL Server?

If your database has a sequence or timestamp that can be used to identify new records then the jdbc input can track [state](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_state).

---

<div class="post-metadata">

**Author:** ![Coder\_Cub](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/coder_cub/32/45327_2.png) [@Coder\_Cub](https://discuss.elastic.co/u/Coder_Cub)\
**Post date:** [August 12, 2020, 5:56pm UTC](https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760/4 "2020-08-12T17:56:59Z")

</div>

Thanks Badger for the response. jdbc input through Logstash right?

I read that Logstash has performance issues. Is there any alternative way?

Can case 1 be resolved with beats? If yes, can you please send me a link which I can follow.

I am very new to ELK stack, please do not mind if my questions are silly

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [August 12, 2020, 6:05pm UTC](https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760/5 "2020-08-12T18:05:55Z")

</div>

> [@Coder\_Cub](#):
>
> Thanks Badger for the response. jdbc input through Logstash right?

Well you posted in the logstash forum so I assumed you wanted to use logstash 😃

I do not think there is a beat that can do this.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [August 12, 2020, 6:31pm UTC](https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760/6 "2020-08-12T18:31:01Z")

</div>

The performance of Logstash depends a lot on the configuration so I disagree with your statement. I suspect Logstash is your best bet in order to get data replicated in near real time.

---

<div class="post-metadata">

**Author:** ![christiancj](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@christiancj](https://discuss.elastic.co/u/christiancj)\
**Post date:** [August 13, 2020, 12:53pm UTC](https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760/7 "2020-08-13T12:53:17Z")

</div>

logstash is a good option, I have been using logstash with idbc input for about 8 months with no issues. I recommend tune your SQL query to have index in sql side and as @Badger mentioned, use a timestamp to track deltas change after last execution, something that worked for me is use a limited time range for my where condition, i.e. get only rows modified in the last 10 mins

```auto
WHERE ....
AND DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), *my-modify-date-time-sql-column*) >= DATEADD(mi,-10,GETDATE())

```

I have a sync close to real time, logstash pipeline running every 5 mins.

good luck

---

<div class="post-metadata">

**Author:** ![Coder\_Cub](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/coder_cub/32/45327_2.png) [@Coder\_Cub](https://discuss.elastic.co/u/Coder_Cub)\
**Post date:** [August 13, 2020, 6:32pm UTC](https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760/8 "2020-08-13T18:32:32Z")

</div>

Thank you @Badger, @Christian_Dahlqvist, @christiancj. I will work on POC using logstash for any insert/update or delete in table.

For scenario 2, where I have to transform all data(millions of records) to docs. For this do you recommend any tool or should I loop through all records and create docs?

Thanks again All.

---

<div class="post-metadata">

**Author:** ![christiancj](https://avatars.discourse-cdn.com/v4/letter/c/258eb7/32.png) [@christiancj](https://discuss.elastic.co/u/christiancj)\
**Post date:** [August 13, 2020, 7:28pm UTC](https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760/9 "2020-08-13T19:28:47Z")

</div>

@Coder_Cub use Logstash for your 'migration' load, leaving the where condition of your sql input statement open to pull ALL the millions of docs. I've migrated close to 4M of sql rows to elasticsearch in couple of hours using Logstash, the time may vary if you have a transformation process in logstash (mutate, grok, etc..) and analyzer(s) in your elastic index, and use a second pipeline to catch your deltas (new inserts/updates)

As tip, use a unique Id column from SQL to define your document Id in ElasticIndex, this is the value that logstash will use to find the object in Elastic and update if the doc already exist (thinking on update scenarios).

i.e.

```auto
output {
	elasticsearch {
		hosts => ["server1:9200","server2:9200"]
		document_id => "%{your_sql_unique_identifier_column}"
	
		index => "my_index_sample"
		doc_as_upsert => true
		action => "update"
	}
}

```

---

<div class="post-metadata">

**Author:** ![Coder\_Cub](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/coder_cub/32/45327_2.png) [@Coder\_Cub](https://discuss.elastic.co/u/Coder_Cub)\
**Post date:** [August 16, 2020, 3:05am UTC](https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760/10 "2020-08-16T03:05:21Z")

</div>

Awesome. Thank you so much @christiancj

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 13, 2020, 3:05am UTC](https://discuss.elastic.co/t/elasticsearch-real-time-data-sync-with-sql-server/244760/11 "2020-09-13T03:05:23Z")

</div>

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