Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
1  
Possible? Of course it is possible, as two separate data stores. What exactly are you asking here? –  Oded Dec 19 '11 at 18:41
    
But can you use them together? With the Mongo DB acting basically as a read-only cache for the data? –  John Dec 19 '11 at 18:42
    
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 Dec 19 '11 at 18:45
    
The "some sort of update mechanism" . . . is the question that I'm asking. Is there something? –  John Dec 19 '11 at 18:51
    
Nothing standard - you will have to build your own. –  Oded Dec 19 '11 at 18:53

2 Answers 2

up vote 6 down vote accepted

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

share|improve this answer
    
+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. –  qes Dec 19 '11 at 20:16

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.

share|improve this answer
    
Okay I'm just curious, why not directly populate mongo? Are you under the same constraint of having to use both? –  yati sagade Dec 19 '11 at 19:39
    
Our existing SQL Server cannot be rewritten overnight. My thought is that this is a small, but useful baby step. –  John Dec 19 '11 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 Dec 21 '11 at 12:56

Your Answer

 
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 or ask your own question.