I am trying to setup an automatic process to regularly transform and export a sizable MS SQL 2008 database to MongoDB.
There's not really a 1-1 correspondence between tables in SQL and collections in MongoDB -- for instance the Address table in SQL is converted into an assortment baked into each customer's record in Mongo and so forth.
At this time I've got a 3 step process:
- Export all of the relevant servings of the database to XML utilizing a FOR XML query.
- Translate XML to mongoimport-friendly JSON using XSLT
- Import to mongo using mongoimport
The bottleneck at this time appears to become #2. XML->JSON conversion for 3 million customer records (each with demographic info and embedded address and order arrays) takes hrs with libxslt.
It appears difficult to think that there isn't already some pre-built method of doing this, however i can't appear to locate one anywhere.
A) What are the pre-existing utilities I possibly could use to get this done?
B) If no, it is possible to way I possibly could accelerate my process?
C) Shall We Be Held approaching the entire problem the wrong manner?
Another approach is to undergo each table and add information to mongo on the record by record basis and let Mongo perform the denormalizing! For example to include each telephone number, just feel the telephone number table and perform a '$addToSet' for every telephone number towards the record.
You may also do that in parallel and do tables individually. This might quicken things but may 'fragment' the mongo database more.
You might want to add any needed indexes before you begin, otherwise adding the indexes in the finish might be a sizable delay.