Best practices for exporting mongo collections to SQL Server

Question

we are using MongoDB (on Linux) as our main database. However, we need to periodically (e.g. nightly) export some of the collections from Mongo to a MS SQL server to run analytics.

I am thinking about the following approach:

1. Backup the Mongo database (probably from a replica) using mongodump
2. Restore the database into a Windows machine where Mongo is istalled
3. Write a custom made app to import the collections from Mongo into SQL (possibly handling any required normalization).
4. Run analytics on the Windows SQL Server installation.

Are there any other "tried and true" alternatives?

Thanks, Stefano

EDIT: for point 4, the analytics is to be run on SQL Server, not Mongo.

Answer 1

Overall looks fine, but i can suggest two things:

1. Skip backup/restore steps and read data directly from linux mongodb, because it will be harder and harder to backup/restore database as it will grow.
2. Instead of custom made app use Quartz.net for nightly export, it is easy to use and can solve any other schedule tasks.

Also i can suggest look into such new approaches as cqrs and event sourcing, that's basically allow to avoid export tasks. You can just handle messages and store data into two data sources (linux mongodb, windows sql server) in real time with small delay, or even analyze data from messages and store in mongodb.