Lotus DB to SQL Server migration

My company has a database in IBM's Lotus DB. It was designed long back. Now, the contract is ending, and we need to transfer it to SQL Server. The problem is that it is a huge organization, and since these databases are very old...we are not able to track the person who designed it. Right now, we just have the application which was build using the lotus DB. Is there a way we 开发者_运维问答can migrate this to SQL Server? Are there any good tools available to migrate the data to SQL Server?

There are no tools that can auto-magically translate a non-relational unstructured LN DB into an SQL schema. Lotus Notes is like "NoSQL"

As Domino is un-structured, you need to manually check the views that will give you an indication of the relationships between documents and what referential integrity constraints you need to apply. You can start by looking at the sorted order of the views, and the view formulas they are using. You then need to consider the complexity of the UI in the form design and how you will replicate that.

In many cases you will need to create your own specific unique keys. Depending on the complexity, this may not be a huge task. The real work will start when you see the size of the relational design and UI technology to mimic what Lotus Notes does natively.

Here is a high level approach to migrating data.

1/ Analyse the views and identify the sorted order as an indication of keys.

2/Look at the forms to see how many fields you need to export.

3/ Draw up your new design for data and the UI. This will help you find gaps between Lotus Notes data and functionality with the new system.

4/ Design exports of documents from Lotus Notes into flat files or you can load them straight into the tables in the next step.

5/ Load the raw data into a set of "triage" tables for data cleansing as you'll find some fields may be null or empty. These tables usually don't have much in the way of constraints or rules, because Lotus Notes data will generally not conform to it. So allow for it.

6/ You should now be able to identify all the known gaps in the data and make adjustments to tweak the data or change your final design.

7/Design your final loading scripts to move the data from Triage to the production quality relational system.

This is the joy of system migrations, that is, to go from one technology to another and still give users the same system. Users generally don't take that news well when you show them the price tag associated with it....

Your basic problem is that Notes isn't a relational database so you're going to have fun analysing the data and designing the MS SQL schema. Google for NotesSQL, it's an ODBC driver that makes Notes views "look" like tables to an RDBMS. If you've someone who understands lotusscript you can write your own export routines directly in the db's, but from your post I'm guessing you don't.

Best of luck :-)

A simple approach to a Lotus Notes database in a SQL perspective is to see a document/form as a record and a view as a table. Each document has an unique key, the universalID. This key is sometimes saved and used to link documents. The only relation that is supported in Lotus is the response document structure.

The NotesSQL ODBC driver is an option, but if it is a small database with not to many views you can use the export option, or maybe even copy to table.

If this is a big migration involving multiple Lotus databases you will need to understand the NoSQL/Lotus Notes concept and probably some lotus script.

It is important to understand that there is no fixed definition for a document (record). The form is used to populate a document, but there can be extra items on any document. You can investigate the items on individual document items using the document properties viewer.

Bear in mind that transferring the data is only going to be one part of getting the application working on MS SQL. The Notes / Domino app will have other logic that will have to be rewritten for you to do anything meaning full with the data you move.

What contract is ending? AFAIK you can continue to use the server software you have. Another option is to upgrade the Domino server, renowned for its stability. Yet another option is to export the data you need via File/Export and then select CSV or other some type.