Position:Home > Mina、Netty、Twisted一起学(八):HTTP服务器

Mina、Netty、Twisted一起学(八):HTTP服务器

up vote 1 down vote favorite 2

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.

c# sql-server mongodb nosql ravendb share asked Jul 17 '15 at 19:37 jrizzo 675 2 9 26 add a comment  | 

3 Answers 3

active oldest votes up vote 3 down vote accepted

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/
share edited Aug 2 '16 at 13:54 answered Jul 17 '15 at 21:09 benjamin moskovits 2,951 1 4 13      beware though Mongo is still not 'transactional'. It has introduced some semantics such as $isolated but even 3.0.4 does not support operations like rollbacks. –  ThrowsException Jul 20 '15 at 15:07      When I start a transaction in SQL Server, do two things (two inserts/updates) either both succeed or both fail (rollback) and I reliably know if it succeeded or not. In Mongo that is definitely not the case. If there are no rollbacks that means when something fails I, the developer, have to figure out what happened and take action myself. To quote the great Spongebob Squarepants, "Good luck with that." I don't understand how anyone could use Mongo for Production systems if its not ACID based. For reporting or research maybe... –  benjamin moskovits Jul 20 '15 at 16:12      Mongo has a different way to think about things. Mainly the idea that documents are atomic and describe your entities without the need for joins. However that may not be the case and yes and you may end up needing more than one collection to correctly model your data. While Mongo is not ACID there are several patterns to ensure data is written reliably –  ThrowsException Jul 20 '15 at 17:07      To a degree we are talking apples and oranges here because atomic writes are reliable in Mongo and since a document (in Mongo) may contain the equivalent of multiple record types that is all you need. But it can be very, very tricky setting up a payment system using Mongo as a data store whereas in SQL Server it is much easier. –  benjamin moskovits Jul 20 '15 at 18:00 add a comment  |  up vote 3 down vote

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.

share edited Jul 27 '16 at 4:50 Pranithan T. 24 8 answered Jul 17 '15 at 20:42 ThrowsException 1,625 6 21 add a comment  |  up vote 1 down vote

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

share answered Sep 29 '15 at 8:16 Jovan MSFT 3,496 1 18 23 add a comment  | 

Your Answer

  draft saved draft discarded

Sign up or log in

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Post as a guest

Name Email discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged c# sql-server mongodb nosql ravendb or ask your own question.

Recommend:Netty 实现HTTP文件服务器

一,需求 文件服务器使用HTTP协议对外提供服务。用户通过浏览器访问文件服务器,首先对URL进行检查,若失败返回403错误;若通过校验,以链接的方式打开当前目录,每个目录或文件都以超链接的形式展现,可递归访问,并下载文件。   二,关

Your Answer
(Ctrl+Enter To Post)   
    Copyright © 2015-2017 163JAVA.COM All Rights Reserved.