Convert Notes Database to SQL

  • Post author:
  • Post published:January 6, 2021
  • Post category:SQL

Convert Notes database to SQL

The Lialis IBM Notes Database to SQL migrator tool converts Lotus/IBM/HCL Notes database content of any Notes database design to a SQL database.

This tool is designed to convert at high speeds and it is capable of converting the Notes rich text and attachments into SQL blobs. It supports multiple conversion runs where only the updates in Notes are send to SQL. Lialis Notes Database to SQL migrator is a Notes application and the conversion is carried out by the Domino server. With the presence of an easy-to-use wizard, the overall conversion process becomes extremely smooth and quick.

Pricing

Please contact sales@lialis.com for a quote.

Prerequisites

    • This tool 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 the configuration you need a Notes client version 8.5 (or higher)
    • A SQL server (in this post we use Microsoft SQL Express version 2012)

Technical instructions

Preparations

Purchase and Download the Notes database from our website and place a Notes database copy on the Domino server (R85 or R9), sign it with the Domino server ID and set the ACL properly.
The conversion of IBM Notes databases is carried out in 4 phases:

    1. Analyzing the source database
    2. Mapping the Notes database forms to SQL Database tables
    3. Initializing the SQL database
    4. The actual conversion

Configuration

Press the Configuration button below in the tool when it is placed on the Domino server.

To gain access to the configurations.

License

The tool is licensed by the number of Notes databases that you wish to convert to SQL.

SQL server settings

The tool only supports Microsoft SQL server over OLEDB.
To connect to the SQL database it requires the connection string to the server and login credentials of a user with database administration privileges.

Analyze database

The analysis of the Notes Database analysis you wish to convert to SQL starts by clicking (2) ‘Analyze database’ in the navigator. An open dialog Notes database dialog becomes visible where you can select the database you wish to convert. Make sure the Notes database you select is present on the Domino server where you run this tool on.

This example will convert the persons and group documents from a Domino directory (LiaProd’s Directory) to SQL. After selecting the database you wish to convert to SQL the analysis will start. This may take some time depending on the size and complexity of the database.

When the analysis is completed a database document and related form documents will be created in the database under (5) ‘Database overview’.

Create field mapping

Select the Notes database you want to convert to SQL and press button (3) ‘Create field mapping’.

This operation will create field mappings for all forms in the Notes database in this tool.

When the mapping is completed you are able to modify the Notes field to SQL column mapping, for example remove fields not to be converted to SQL, or rename the SQL column name.

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 a value of the type was detected
  4. Mapped Type: SQL server column data type
  5. Hits: Total Number of documents found with this field
  6. Max Elements: Maximum number of values found in the field. 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

 

IBM Notes 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. It is therefore 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 the tool will convert them to text.

Care must be taken that all values on the same “horizontal row” are kept together. Because horizontal screen with limit, some fields may flow values to the next line, so values that belong together may not appear on the same height on the document.

Opening the Notes database form will allow you to set the SQL table name.

Feel free to delete Notes forms from this tool that don’t need conversion to SQL. The result, after deleting certain forms, might be:

\r\n

Initialize database

Make sure the SQL server accepts TCPIP connections. Please check out this post.

This action will delete and recreate the SQL database from scratch.

Select the Notes database and press button (4) ‘Initialize database’.

This operation should go very fast and you must see some messages appearing in the Notes client status bar.

The result of this operation is that the SQL database is created with the tables and columns defined in this tool. For example:

Do not use this initialize database operation after you have converted content to the SQL db because it will wipe all content from SQL.

Starting the conversion agent

Click the big red button to start the conversion background agent for the conversion of the Notes content to SQL. The button will change to the green stop agent button, which will deactivate the background agent. It will not stop the agent if it has already started.

Start agent     Stop agent

The result of a complete run is illustrated in the image below, retrieved from the view (6) Status log.

The delay, mentioned in the beginning of this post, is because no license is applied for this demo.

Considerations

The conversion agent needs at least “Sign or run restricted LotusScript/Java agents” access on the server. Therefore it must be signed with an id that has this access.

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

The agent may run for a long time depending on the size of the database being converted. We advise to have the “Max LotusScript/Java execution time” at at least 1 hour.