SQL Server Realtime Push Notifications to Node.JS

Question

I am building a web application that in short is taking fairly poorly structured data in SQL Server and via Node.JS porting it over to MongoDB. The reason I need this is that I need access to data from a fairly poorly written application that is central to an organization that I do not have ability to change code on from which the initial data is getting entered on. Once translated, I can have my application do what the business is looking for.

Right now my application is polling SQL Server every 30 minutes for changes and then updating my MongoDB via Node.JS, and due to the volume of data, it is undesirable to poll much more frequently.

What I need to happen is to have real time notifications from SQL Server pushed to my Node.JS application in some way whether active or passive no Node.JS's end so that it can update my Mongo database.

The node library I am using to get the data is: https://github.com/patriksimek/node-mssql

A few possible ideas I had were:

• Have SQL Server send out a notification of some kind to my NodeJS HTTP service endpoint
• Have NodeJS run a streaming query that will run on my end each time changes are made
• Write an application in C# that watches these changes and pushes them to my NodeJS HTTP endpoint.

There are a few out there that seem to talk about this, but most seem to talk about changes on the data source origination point (which I cannot change), not from SQL Server itself.

Answer 1

I have a similar situation, and after researching, the solution I am going to go with is to write a .Net Core windows service that does nothing but listen for query notifications from SQL Server. When a notification arrives, it will hit a nodejs REST endpoint notifying it of the change, and allowing the nodejs application to query and process the data as needed.

I searched and searched for a way to do the Query Notifications from nodejs, but it seems nothing exists for this. You need a .Net application.

However, that said, another possibility instead of writing a .Net Core windows service (or an ASP.Net application if you want) is to use EdgeJs to actually run a .Net assembly inline with your nodejs application (in the same process).

I have decided not to go that route, and will do the windows service instead. But it should also be viable.

(I also realize this question is 2 years old, so you've probably already completed this project, but I'll leave these answers here for posterity)