Store NoSQL data on SQL Server?

Question

So ignore the fact that we should just use a NoSQL DB - client infrastructure requirements are getting the way.

We have data that obviously belongs in a non-relational model, but we have to use SQL Server 2014 for persistence. Is there a way to use the library for something like RavenDB or MongoDB with SQL Server for persistence? For instance, storing JSON or BSON in a SQL Server table but using Mongo or Raven to query and serialize it?

We were initially about to just store JSON data in a column, but I figured there had to be a more elegant solution. I saw that RavenDB supports SQL Server replication, but it doesn't look like it can be used for its primary persistence component.

We are running a C# ASP.NET MVC web app. The front end is a KnockoutJS SPA, so it would be happy to bind to JSON data.

Solution 1

For a much more sophisticated discussion of storing JSON in SQL Server as relational data and extracting it back as JSON please see this wonderful article by Phil Factor (thats the name he goes by), Producing JSON Documents from SQL Server queries via TSQL,https://www.simple-talk.com/sql/t-sql-programming/producing-json-documents-from-sql-server-queries-via-tsql/ .

Be careful about storing JSON as varchar with full text indexes or as xml type (which is not the same as JSON) with xml indexes. There can be severe performance issues when doing inserts on even a million row table so test carefully with realistic, for you, numbers of rows to see if an XML or varchar solution works for you.

If all you are going to do is stuff JSON data into varchars and back again then you should have no problems. Until the latest version of Mongo (3.04 or so) Mongo was not transactional based and one client of mine was always losing data and that caused a world of finger pointing. If the version of Mongo that you are using is not ACID compliant be very, very careful.

I am amending this answer because SQL Server 2016 now supports JSON in a big way. According to Microsoft it was one of the most requested features. Please see the following two articles:

• https://msdn.microsoft.com/en-us/library/dn921897.aspx
• https://blogs.msdn.microsoft.com/jocapc/2015/05/16/json-support-in-sql-server-2016/

Solution 2

I don't know if this answers your question but what I've done in situations like this is to hybridize my SQL database. I store the JSON in something like an nvarchar(max) and add columns to the table that I will search on. So something like

• my JSON document

  {Name: "name", CreatedBy: "name", Date: "date", other attributes... }
  

• Table structure. Add columns that will be searched on as well as the entire JSON object

  NAME | DATE | CREATED_BY | JSON
  

It's not a pretty approach but it's worked so far.

Solution 3

Since you already have Sql Server 2014, maybe you can migrate to 2016 version. In 2016 version you will have new functions for handling JSON data (they will be available in CTP3 in November). Here you can find some additional info JSON support in Sql Server 2016 and How to combine JSON and relational data in Sql Server 2016