I am faced with taking a mvce's for SQL which happens to be the WideWorldImporters Database from Microsoft and migrating it to a NoSQL on mongodb.

The system in question may use certain fractions of the SQLDB lesser than the others, but certainly no clear approach has been formulated except that, we understand, denormalization, constructing views based on how the information is being consumed, central and paramount to our new application on NoSQL.

In this regard, we find it hard to determine from which angle to tackle this mammoth beast of a task in front of us and I am a key member in the taskforce. Any advice on approach to be taken is kindly solicited. All that we have now is we plan on 25% views on the tables and re-use the view creation logic to form mongoose discriminators.

  • 2
    So what do you want to achieve? – JacquesB Mar 8 '18 at 16:34

I am faced with taking a mvce's for SQL which happens to be the WideWorldImporters Database from Microsoft and migrating it to a NoSQL on mongodb.

Question: Why?

You have a perfectly reasonable database running on a pretty solid DBMS. Why do you feel the need to port this to a NoSQL solution?

... no clear approach has been formulated ...

What is the end result that you're trying to achieve?

If it's just to "build something in NoSQL" then porting from a relational DBMS will not give you a "Good" result (and will, therefore, discourage you and/or your Management from [ever] trying to repeat the exercise when, for another purpose, you may be looking at massive benefits).

Choose your battles. Consider NoSQL for a project where it will give you [the most] benefit.

| improve this answer | |

Having made the switch from SQL Server to ElasticSearch I can provide some insight as to what you can expect. We chose ElasticSearch because we needed something with better (more efficient) faceted search capabilities than we had with SQL Server. One of the killer features was the ability to get the counts of documents that matched our unselected facets within the subset of data we were already searching. That and the full text search capabilities were easier to work with.

That said, we had to do the following:

  1. Split the data into documents we needed to search against (i.e. teachings, devotionals, books, events, etc.)
  2. Write our own tool to perform an ETL (Extract, Transform, Load)
  3. Write the complex transform logic that also cleaned up formatting sins from over a decade of maintenance

The first step is your planning step. This is where you design what you put in your document. If you have a hierarchical document that you want to search separately, you have to figure out how much summary information you want to keep in the parent document.

We opted for a reference object that had enough summary information that we did not have to do a second query to get the child objects when we displayed info on screen. You may decide that doesn't work for your project. There's trade-offs, particularly since updates have to be done in multiple locations. In my situation, there are very few updates.

I highly recommend taking the opportunity to clean up your data.

The ETL process I chose worked well for backup from SQL:

  • Create your Model objects, most document database APIs serialize them as JSON or BSON
  • Perform your Extract/Transform to those model objects
  • Serialize the model objects to a BSON file (using the Avro format)

The Restore was a simple upload of the model data to the document database

  • Serialize model objects from BSON file
  • Write them as is to your Document Database

The Backup was a simple download of the model data from the document database

  • Read the records from the Document Database
  • Serialize the model objects to the BSON file

At this point you can back up from the SQL Server and restore to the document database, as well as have a means of backing up and restoring data with the document database as well.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.