With Sql Server 2016 you’ll be able to store and return validated JSON from the database. Does this mean you can now consider using Sql Server as a back end to a Node.JS web server? Maybe: but before this becomes a reality you have several chambers to pass through(not necessarily in the order below)
Do I Really Need Sql Server?
Perhaps a document database would be more suitable? You’ll find better support. Node is most often used with NoSql databases like MongoDb. The MongoDb site has some sensible notes under When to Use Mongo.
Is this for a Production Application to be released soon?
There’s no production-ready JSON support: on-premise Sql Server 2016 is at CTP 3 with no release date. There’s no JSON Support in Azure Sql Database as of this date, but watch the space.
Do you need a driver that handles stored procedure calls? If so, you could check this point first to remove the no-hopers and concentrate on drivers that are real candidates. You should also check whether the driver can handle Integrated Security (Trusted Connection).
There haven’t been that many Node.js drivers available for Sql Server, reflecting the popularity of MongoDb and probably also Sql Server’s lack of JSON support until recently. The “mssql” driver on Github from the list I’ve linked above seems the most promising – it includes Sql Server 2016 features. If you’re considering others, keep an eye out for ageing releases, recent activity on GitHub and the number of downloads on the NPM site.
Another reason for the lack of driver development may be Edge.JS, which I discuss in the next section.
Do I Need a Driver?
There’s an alternative: Edge.JS. By calling .NET code from Node using Edge.JS, you can do all the database calls in .NET. You won’t need a Node.JS-to-Sql Server driver.
Deployments to Linux or MS Azure look straightforward, but if you want to deploy to Windows Server/IIS you’ll have to do some research. Using IISNode is one possibility, but the GitHub repo has gone quiet lately, so you’d have to look at the outstanding issues and make your own judgement about whether it’s still alive. I believe the hosting model is quite similar to the one for ASP.NET 5.0, so it might be worth looking at that.
Even if the technical story is fine, someone still needs to consider costs. How much data is involved? Can you offload the “cold” data somewhere? Sql Server can be expensive. “Cloud” or “on-premise”? Would a hybrid solution work?