14

We have a large news-oriented site that has high web traffic. The architecture is your often seen DB - Repo Layer - Services Layer - Asp.Net MVC. The problem that we've been seeing is around read performance. It turns out that all this DDD domain object stuff is great, in theory, for business rules, but has made life harder when it comes to optimizing read performance.

As a solution, I'm considering something entirely new (for us): using noSQL. I'd like to use a noSQL database for data being presented on our website. We can't get rid of our SQL Server (at least not anytime soon), but it seems to me that a practical step would be to use Mongo as a query database for all new development.

My question is whether it is possible to use SQL Server as your database of record and Mongo as your query database together?

So when one of our editors updates a record, the data will be stored in SQL Server. This is necessary, because there is too much legacy code that cannot be re-written overnight.

But when a viewer on the web site views an article or a list of articles, I'd like to take advantage of the performance of Mongo versus SQL Server. In order to keep data somewhat current, let's say 15 minutes old or less, SQL Server data would need to refresh Mongo. RDBMS have replication tools for operations like this and I'm wondering if there exists anything to do the same from SQL Server to Mongo. Lync server, maybe?

9
  • 3
    Possible? Of course it is possible, as two separate data stores. What exactly are you asking here?
    – Oded
    Commented Dec 19, 2011 at 18:41
  • But can you use them together? With the Mongo DB acting basically as a read-only cache for the data?
    – John
    Commented Dec 19, 2011 at 18:42
  • 1
    Again, of course you can "use them together". But it is not clear what that means. So long as you have some sort of update mechanism for mongo, you are good to go. See the posts of Udi Dahan - but it is up to you to define the mechanism.
    – Oded
    Commented Dec 19, 2011 at 18:45
  • 1
    We never did move to MongoDB, however it looks like next year that we may. One transitional way that we've have done is to store JSON in varchar fields. From everything that I've read, there is no good way to move data back and forth between NoSQL and SQL -- it's all going to need to be custom, especially since the NoSQL databases that are out there vary greatly in how they do things.
    – John
    Commented Nov 6, 2014 at 20:12
  • 1
    @John if you are looking to stick with relational and are willing to move away from SQL Server, you might want to look at Postgresql and its json integration. Thus, storing data in a json column which can then be manipulated within the database.
    – user40980
    Commented May 6, 2015 at 17:01

2 Answers 2

Reset to default
13

You've run into a problem that many have before you...a database optimized for reading is seldom good for write efficiency and vice versa. One approach that has evolved from this read-write impediment is CQRS (Command Query Responsibility Segregation). Despite Wikipedia linking the two together CQRS and CQS are technically different. CQS just demands that a method either make a change (command) or ask information (query) never both.

CQRS takes it a step further and specifies that you have a separate model for Queries and Commands. This single step enables things like separating your read and write database. Which is what you want to do.

I can't say that I'm an expert on Mongo or configuring it to work with SQL Server. But from my understanding, people use Mongo as a denormalized view of their transactional database. Updating Mongo from the transactional db might come down to running a SQL Agent. Or having a separate service to poll the database.

An even better alternative would be to have your Command service fire an event whenever an update is made. You would then have a service that listened for that event and updated the MongoDB with that information. That is the fundamental approach to Event Sourcing (search for Event Sourcing on the page).

Greg Young, one of the thought leaders in the DDD world is currently writing a book in the Fowler Signature Series on CQRS called Event-Centric (it used to be called CQRS). Fowler has written a post on his bliki describing the approach

3
  • +1 CQRS fits well here. Use events to populate and update a document database used for building your views, and leave your sql database as it is.
    – quentin-starin
    Commented Dec 19, 2011 at 20:16
  • We haven't adopted a CQRS system, however I have paid attention to what Greg, Udi and others have written. A big part of CQRS isn't a specific platform, but just thinking about commands and queries separately. I don't think that Greg ever did end up writing a book, though Microsoft Patterns and Practices did release one.
    – John
    Commented Nov 6, 2014 at 20:15
  • 1
    The only thing I'd add to this answer is: If your use case is specifically around using this with MS SQL and MVC, have you considered BrightstarDB instead of MongoDB for the NoSQL portion? It has Entity Framework and LINQ compatibility, which might ease the transition for you.
    – CrazyPyro
    Commented Mar 17, 2015 at 17:38
1

Yes. On my current project, we're getting data and storing it in SQL Server, and then building search indices using Lucene/Solr and storing those in MongoDB. Populating MongoDB is done with a custom loader, though -- no SQL Server replication or automatic refresh.

3
  • Okay I'm just curious, why not directly populate mongo? Are you under the same constraint of having to use both?
    – yati sagade
    Commented Dec 19, 2011 at 19:39
  • Our existing SQL Server cannot be rewritten overnight. My thought is that this is a small, but useful baby step.
    – John
    Commented Dec 19, 2011 at 19:58
  • @yatisagade: Right. We have reports that have to run against SQL Server, but we have a global search app that uses the Lucene indexes.
    – TMN
    Commented Dec 21, 2011 at 12:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.