Convert Notes databases to SQL

Companies that wish to decommission their Domino infra often want to replace Notes databases with off the shelve apps (e.g. holiday request, hour registration, etc) or enterprise ERP, CRM applications. As part of this replacement, you often have a need to convert Lotus Notes content to the target system. One of the ways to do this is to convert the Lotus Notes database content to a SQL database.

To cater this need we offer the Lialis Notes2SQL software that automatically converts the content of any Notes database design into SQL. The main features of the Notes2SQL software are:

  • It is a Notes application. The HCL Domino server carries out the conversion
  • Carries out high speed Notes database content migrations to SQL
  • Converts the Notes rich text and attachments into SQL MIME
  • Supports delta conversion runs (only the updates made in the Notes databases are converted to SQL)
  • Runs scheduled conversions (update) of multiple Notes databases to SQL
  • Has an easy-to-use wizard to run smooth conversions.

The conversion of IBM Notes databases is carried out in 4 phases:

  1. Analyze the source HCL Notes database
  2. Map the Notes database forms and fields to SQL Database tables and columns
  3. Initialize the SQL database and create the necessary SQL tables and columns
  4. Convert the Notes content to the SQL Database

The image below gives a schematic overview of how our Notes2SQL software migrates Lotus Notes to SQL Server.As an example only a few Notes field types are displayed in this image. However, our tool supports all Notes field types.

 

Lialis Notes to SQL basics

Rich text conversion details

A Notes document can hold attachments, images, links, text and many more rich text content. For example see images below.

The Notes rich text field is stored in SQL as MIME in a SQL field. See image below.

Exporting the rich text content can be carried out in different ways, for example with Power Shell – example.

The result is displayed in the images below.

 

Introduction movie

 

Software prerequisites

    • Notes2SQL runs on a IBM HCL Domino server version 8.5 (or higher)
    • The Domino server must run on a Windows 2012 server (or higher)
    • For configuration of the Notes2SQL tool you need a Notes client version 8.5 (or higher)
    • A SQL server (in this post we use Microsoft SQL Express version 2012)

 

Download an evaluation version of Notes2SQL

 

Pricing

The Lialis Notes2SQL software pricing is based on number of NSFs to convert to SQL (notessql candidates).
Please click this this contact link to receive an offer. Kindly don’t forget to indicate in the comments field the number of NSFs in scope.

 

Technical instructions

 

Preparations

Store a copy of the Notes2SQL software (Notes2SQL.nsf) on the Domino server (R85 or R9). Subsequently, sign it with the Domino server ID and set the ACL properly (Domino server and Domino admin must have manager access with delete and the role Admin attached).

The Notes2SQL conversion agent needs at least “Sign or run restricted LotusScript/Java agents” access on the Domino server. Therefore the Notes2SQL NSF must be signed with a Notes ID that has this access.

The conversion agent uses Lotus Connectors which are only available if DECS is installed on the Domino server. This is the case when you run a default enterprise Domino server R9.

The agent may run for a long time depending on the size of the database that you convert. We advise to have the “Max LotusScript/Java execution time” set at at least 6 hours.

Configuration of the Notes2SQL software

Choose Add license to add the Lialis license key we will provide you. Leave it empty if you only want to evaluate the software. Please note that an increasing conversion delay is applicable in this evaluation version.

 

Then click the big red button to start the IBM Domino conversion background agent for the conversion of the Notes content to SQL. After clicking, the button will turn to green.

Start agent Stop agent

 

Subsequently, select General Settings to configure the SQL server connections. Notes2SQL only supports Microsoft SQL server over OLEDB protocol.
To connect to the SQL database it requires the connection string to the SQL server and login credentials of a user with database administration privileges.

 

Once this is done please click Add Notes Application to choose the Notes NSF file you wish to convert to SQL

 

Then select the Notes database and press Start Application Analysis

The Domino server will automatically take care of the Notes to SQL migration duties. This takes around 10 minutes, depending on the Notes database number of documents in the design.

You complete the preparations when the status changes to “Fields mapped“. This basically means that the Notes2SQL field mappings for all forms in the Notes database are in place.

Now you are able to modify the Notes field to SQL column mapping. For example, remove Notes fields that you don’t want to convert to SQL, or rename the SQL column name. You may also choose to disable the conversion of Notes documents with certain forms to SQL.

In below example the Notes database only contains documents stored with one form. If the database uses more forms you may disable the conversion of certain forms.

The image below shows how the Notes2SQL software maps the Notes fields with SQL columns.

The Form mapping document has 7 columns to map the IBM Notes fields to SQL columns.

  1. Source Field: The field name as it appears in IBM Notes
  2. Destination Field: Name of the corresponding SQL Column
  3. Datatypes(hits): Data types of the field found in the source database separated by a \’|\’ if multiple data types are detected for a single field. And between parentheses the number of documents where the software detects a value of the type
  4. Mapped Type: SQL server column data type
  5. Hits: Total Number of documents the software finds with this field
  6. Max Elements: Maximum number of values the software finds in the field. This is applicable for multi-value fields.
  7. Max Size: Data length of the field in bytes. Some table names might be reserved words in SQL, like Group. In this case you must change the mapping name before you initialize the SQL database. For example, change Group to GroupNotes

The Lialis Notes2SQL software does not enforce data types of fields. A field can be set as a text field on a form, but can be updated with numeric values in code without causing problems. Therefore, it is possible that a field contains a text value in one document and a number in another. This is not possible in SQL. When mapping these fields to SQL our tool will convert them to text.

Please make sure that all values on the same “horizontal row” are kept together. This is because in a horizontal screen with limit, some fields may flow values to the next line. As a result values that belong together may not appear on the same height on the document.

At this stage in the process you are almost ready to start the conversion. Next in line is to click Start SQL Initialization to create an empty SQL database with all the required tables and columns.

 

After a few minutes an empty SQL database has been created by the Domino server as you can see below.

 

Finally, click Start conversion to send the Notes content to the SQL database. The Domino server will execute this action.

 

 

Important Notice

Please don’t use the ” initialize database operation’ after you have converted content to the SQL database. Reason is that it will wipe all content from SQL! Obviously this is something you want to avoid.

 

Conclusion

The Lialis Notes2SQL software is an easy-to-use tool to convert Notes database to SQL:

  1. As an intermediate step to convert content to off the shelve apps (e.g. holiday request, hour registration, etc) or enterprise ERP, CRM applications
  2. For reporting purpose. For example, use the SQL data in your Datawarehouse or connect directly to the SQL server with analytical tools such as Power BI

We offer you an evaluation version should you wish to evaluate the software for a subset of data.

In case you wish to speak with one of our solution specialists; please hit this contact button.

Note: in case you have a large Domino environmemt that you are looking to decommssion, this Notes2SQL software will probably not cover all your migration needs. For example, you might want to simply archive legacy Notes databases to platforms such as BOX, Google Drive or SharePoint. Or, in case databases are still actively in use, create substitutes in SharePoint. For all these conversion options we offer solutions. You can find more info on our website under solutions.

Thanks for your interest and we look forward assisting you in your journey off Notes.