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?
Best Answer
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