SQL Server vs MongoDB?

Question

I have been happily using SQL Server for over 5 years and am convinced of the power of the relational model.

However, next week I will be meeting with some folks who are hooked on MongoDB and not in favour of using an RDBMS (of any flavour.)

I want to avoid a "religeous wars" type of discussion so in the last few days, I have trawled the web and read a MongoDB book but it is not easy to find hard facts about the detailed issues relating to "RDBMS or MongoDB" ?

So it would be nice to get some clues as to the "hard" and "non-hype" answers to the questions:

1. Under what circumstances is SQL Server to be preferred to MongoDB?
2. Under what circumstances is MongoDB to be preferred to SQL Server (2008 or 2008 R2)

Has anyone any comparative experience of the two alternative solutions?
And how would you approach such a discussion?

Thanks

Ken

---

Ken Evans

Answer 1

You are right that the NoSQL vs. RDBMS debate is more often more religious than technical. One of the best (and funniest) is the MySQL versus MongoDB youtube animated video that you can easily find with your favorite search engine. Although it contains quite a bit of profane language, the discussion is actually fairly good and worth the watch for your preparation.

When it comes to architecture and technology decisions, I think it is best to first understand the problem you are trying to solve rather than evaluating solutions to problems you don't have. NoSQL has performance and scalability benefits but at the cost of breaking the core ACID principals inherent in any RDBMS. So the question is whether the application at hand has such ACID requirements. If not, MongoDB or other NoSQL solution might be the right tool for the job. Chances are that the right solution is a mix of technologies that include an RDBMS, caching layer and perhaps NoSQL.

I have personally not yet had to resort to NoSQL to meet performance SLAs. SQL Server, coupled with an intelligent caching for large OLTP systems, scales quite nicely.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

Thanks Dan.

I think that for me, the most important point made in the movie is the way the fire-and-forget "feature" of the MongoDB store-to-disk function works. I interpret this to mean 
"If you don't care whether your transaction data is written to disk or not then MongoDB is for you".

To be fair, many of the "features" of MongoDB are mentioned in the book:
"MongoDB The Definitive Guide" by Chodorow and Dirolf.

Here are some quotes (with a little paraphrasing to reduce the amount of text)

P41: "Inserts, removes and updates seem instantaneous because none of them waits for a database response" ... "If the server disappears, the client will happily send some writes to a server that isn't there, entirely unaware of its absence. For some applications, this is acceptable"  (my italics)
I wonder what kind of applications these might be? Any ideas?

(Definitely not for an application that is senstive to Sarbanes-Oxley.)

 

P42: Explains that fire-and-forget is the default option but you can use the "getLastError" method to check if your operation succeded. "The driver waits for the database response and then handles errors appropriately, throwing a catchable exception in most cases. (my italics) This way, developers can catch and handle database errors in whatever way feels 'natural' for their language" 

"The price of performing 'safe' operations is performance: waiting for a database response takes an order of magnitude longer than sending the message."

Wow! - What a revelation!

I'll study the book a bit more and try to come up with some more gems that may help readers to choose their DB.

Thanks again for your quick response.

 Ken

 

---

Ken Evans

Answer 3

These are good points.  If the request must be synchonous in order to notify a client immediately of a problem, the performance cost is higher due to the introduced latency.  Furthermore, if the transaction or message must be durable, physical I/O is needed to ensure data are not lost, which also increases latency. 

Keep in mind that one can "fire and forget", even with traditional RDBMS pesistence.  All that is needed is a queue in between, which may or may not be durable (a.k.a. transactional).  A durable queue can also facilitate asynchronous processing to improve overall performance.

There are cases where fire and forget is appropriate.  Consider the case of gathering click stream details for a large web farm.  The client doesn't care if and when the activity is recorded and it wouldn't be the end of the world if some messages were lost.

 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

A lot of people run exclusively in "Safe Mode" (the drivers call getLastError that) which gives you the same behavior as one would traditionally expect in a database.  How much you care about the data is probably not the litmus test for the product.

My view is:

- if you need SQL (e.g. to hook into microstrategy via ODBC), use sql server.

- the GROUP BY operator in SQL is pretty powerful.  for some reporting cases sql server is best, for others mongodb is best.

- if you need very complex transactions, use sql server.  for example for a general ledger system.  if you need small transactions, mongodb is likely better.  it supports ACID at the single BSON document level; BSON documents can be quite rich so this is often sufficient.   This might fit well with a services oriented architecture where there usually aren't system spanning distributed transactions.

- if you need horizontal scalability (scale+speed), use mongo

- mongo is good at running in cloud environments as it runs well on commodity hardware and commodity networks (albeit it works well on giant servers too)

- if you are using iterative development methodologies or just need to write projects fast, use mongo.  the document-oriented (object-ish) data model fits very well with the way we write programs today and eliminates the 'impedance mismatch' that is often discussed.

there is a use cases page on the mongodb.org site

 

Answer 5

Hi dmerr,

Thanks very much for your response.
I'm now trying to get a better understanding about "serious" architectural and technical differences.

For example, I recently read a project objectives statement that said: 

The objective, then, is for us to convert the graph-like structure of the data model (which has entities and relationships that do not follow a hierarchy) into the flat key-value structure of a search index.

The term "graph like structure of the data model" refers to the database schema 

So, I interpret their problem as being that they have some data in an RDBMS and some "data" that is not in an RDBMS. And (for reasons that I don't know) perceive a need to get the data out into a NoSQL environment so that they can use it "more efficiently"

So what I'm trying to understand is under what circumstances does this make sense?

Can you recommend a book that goes into the details?

Thanks

Ken

 

 

---

Ken Evans

Answer 6

I was looking into this recently after listening to the Telerik Platform key Note.

1. If you have to update records, data, a document in MongoDB speak it takes orders of magnitude longer in MongoDb

2. If you need to handle Office Documents for example I could not find a working code example for Word, Excel or PowerPoint or Pdf  but there are unanswered questions on Stack Overflow if someone knows they are not telling.

3. What about integration between systems you will have to do or write any of this through Web / WCF / Web API Service(s) or by calling one.

4. What about reporting these will have to be written there is no SRSS a web developer is required just to write a report.

5. What about Data Warehouse this is also not going to fly in the traditional sense. Again a lot of programming will be required to achieve this type of functionality.

6. FaceBook and Google have a mass of developers do you (its looking expensive to me already)

7.  It looks green it looks like its not really suited for traditional business apps Like an Accounting Package or a Large POS system or a CRM or a gap application that does some of this for a business or actually fills this gap or some other gap or edge case between existing systems.

8.  It is probably is more appropriate to Compare a No Sql db like MongoDB to Microsoft's Azure SQL Server offering at which point it comes down to hosting costs.

9. If you can get it from Azure for the same cost or lower you might be able to negotiate that with MS if you are a larger enterprise with team(s) of Developers and if you have the skills in  house with better knowledge and support and I would argue better and more mature tools and a more secure backing why choose MongoDB

10. Consulting hype around FaceBook,  Google, Twitter type technology is exactly that these are one way record insert scenarios with no transactional and durability requirements.

11. If you were to build apps a certain way and tune SQL Server a certain way it might perform just as well

12. A successful business idea like FaceBook or like Google does not mean the technology is applicable for a particular scenario unless you are looking to build the same type of system with the same needs.

13. About the impedance mismatch with Entity Framework Telerik ORM and the like its almost moot however having to write everything and program everything you lose over the course of a complete system. I worked with ODBM's in the pas and this was a huge issue for them getting data in and out and reporting all had to be coded in unproductive and inefficient again unless you have a lot of programmers.

14. Horizontal Scalability not comparing Apples with Apples look at SQL Azure

15. You can run SQL Server on Commodity hardware the MS platform grew up running on commodity (micro computers !!) hardware if you do not need ACID Transactions durability etc you could mirror Asynchronously to whatever through ADSL. I think some of the propeller head consultants and bloggers out there could take your head clean off if you are not in possession of the facts. 1000 propeller heads does not equal one person that actually knows what they are doing or talking about. That is not to say FaceBook and Google are not successful businesses that make astute use of appropriate technology. Also having programmed on ODMS systems they are easier and do give you more productivity however the reporting and DTS / ETL are an issue in an Enterprise. its a bit like the PHP vs ASP.NET or JSP debate if you need to integrate with other systems PHP doesn't have the same stack offering and support as more established enterprise Tech. If the system is going to be the platform then PHP and NoSQL like FaceBook or Google or the like is a reasonable choice and everyone is going to integrate with you but even then you have to develop and expose those services (APIs). Saying that if you have PHP on Oracle or SQL Server or My SQL the integration and reporting aspect may be countered by the RDBMS.

Answer 7

Hi Jason,

Thanks for your post. I registered for the recent Telerik webinar but the link did not work.

Regarding MongoDB. I found this interesting and detailed report by a person who appears to be a "bruised" user of MongoDB.

And  I recently had an intense email exhange with a Ruby On Rails expert who told me that "fat client" is the new paradigm so I should forget stored procedures because "all you need" is an api to talk to the data store.

Amongst other things (and paraphrasing a bit) he also said that despite my 44 years in the computer industry, 20+ years of data modeling and explaining the relational model to folks, that I just don't understand the new paradigm and that "people don't believe rational cost-based arguments". and "...your experience is not a good fit for today's markets because Rails is the mainstream state-of-the-art "

Ken

---

Ken Evans

Answer 8

No one mentioning the sales pitch info?

reporting, integration services, training, analysis services? Mirroring, all the other kitchen sinks you get with SQL server... That list doesn't stop it seems... SQL mail... Operations manager integration..

even if you use only one or two of these addons, it can save an arm or a leg...

Answer 9

nice post