1

Is there any way to listen to SQL Server database changes (e.g. insert, update) from my node.js application?

I want to keep my elasticsearch index synchronized with my database. And I can't figure out how to achieve this.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Zaid Waseem
  • 341
  • 1
  • 4
  • 13
  • You surely could use triggers but there must be some simpler choices too I take. – matcheek Sep 18 '19 at 18:30
  • Can you please elaborate? – Zaid Waseem Sep 18 '19 at 18:35
  • Take a look at CLR triggers, https://learn.microsoft.com/en-us/sql/database-engine/dev-guide/clr-triggers?view=sql-server-2014 Then at message passing tool, like RabbitMQ, https://www.rabbitmq.com/ so essentially, we are talking abut Trigger CLR sending messages through RabbbitMQ and nodejs receiving it. This blog post might be helpful. https://nielsberglund.com/2017/02/11/rabbitmq---sql-server/ and here, https://github.com/nberglund/RabbitMQ-SqlServer – matcheek Sep 18 '19 at 18:43
  • 1
    Simpler solution might be just polling. – matcheek Sep 18 '19 at 18:45
  • How can polling be done on server side? – Zaid Waseem Sep 18 '19 at 18:51
  • It can't. If initiated on the MSSQL then that will be pushing, not polling. If polling then then the action would be initiated on the nodejs every x seconds, minutes or hours. – matcheek Sep 18 '19 at 18:54
  • Oh ok. So basically I'll be checking the databae every x seconds for any updates. Is that so? – Zaid Waseem Sep 18 '19 at 18:55
  • Yes. That's a simpler alternative to pushing described earlier. – matcheek Sep 18 '19 at 18:57
  • Is there an efficient way to fetch just the newly added data from a table? – Zaid Waseem Sep 18 '19 at 19:02
  • UTC timestamps might just do. But there might be even simpler way, take a look at the Service Broker https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-service-broker?view=sql-server-2017 And this npm package https://www.npmjs.com/package/sqlssb – matcheek Sep 18 '19 at 19:08
  • 1
    Thanks, you helped a lot!! – Zaid Waseem Sep 18 '19 at 19:17
  • This might also be interesting, though a bit outdated: https://github.com/No9/sqlcdcstream – Val Sep 19 '19 at 05:44

0 Answers0