How to store large ordered data?

While transitioning to a NoSQL store, what are some proven strategies in dealing in a world with multiple data stores and keeping data in synch?

• We are transitioning our core, customer facing applications from a traditional RDBMS to a NoSQL data store--currently targeting MongoDB. For a number of reasons, doing a full transition off of SQL is far too expensive in time/cost as well as in risk.  That's simply out of the cards.  So we're looking at a world where some of our models will live in Mongo and some of the data that they reference would still be in SQL.  As a hypothetical example, orders in Mongo referencing customer or shipping details in SQL. Is it best to reference IDs in our Mongo documents?  (Making document retrieval more complicated since we now have to pull data from SQL + Mongo).  Or should we just include the data and bodge together some sort of service that keeps that data synched?  (Meaning data on our document store may at times be stale)

• Answer:

  You say, the following almost as a throwaway line: Meaning data on our document store may at times be stale But it's actually key to the whole premise of using something like MongoDB. MongoDB is eventually consistent, so at some level you are accepting the possibility of stale data. Is it best to reference IDs in our Mongo documents? This is the common pattern. Your Mongo documents will have integers "pointing to" data in SQL. Likewise your SQL will have "ObjectIds" stored as strings in the DB. ...Or should we just include the data and bodge together some sort of service that keeps that data synched? For you own sanity, you will need to wrap some logical wrappers around data that crosses boundaries. Maybe this means writing a join provider in Linq or it means writing  query methods that combine the data seamlessly. In terms of keeping data synched, you no longer have any transactions so you'll have to rely on Queues. Updates across DBs will end up on an update queue. You'll then write services/cron jobs that pull down items on the Queue and update the database. You'll need this because you won't have transactions that you can roll back. And this may result in temporarily stale data.