Convert Notes database to SQL
The IBM Notes Database to SQL migrator tool has been built by Lialis to convert 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.
Please contact firstname.lastname@example.org for a quote.
- 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 a Notes client version 8.5 or higher is needed
- A SQL server (in this post a Microsoft SQL Express version 2012 is used)
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:
- Analyzing the source database
- Mapping the Notes database forms to SQL Database tables
- Initializing the SQL database
- The actual conversion
Press the Configuration button below in the tool when its placed on the Domino server.
To gain access to the configurations.
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.
The Notes Database analysis, of the Notes database to be converted to SQL, is started by clicking 2 Analyze database in the navigator. An open dialog Notes database dialog will be shown where the database to be converted can be selected. 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 to be converted to SQL the analysis will start and may take a 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 the 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.
- Source Field: The field name as it appears in IBM Notes
- Destination Field: Name of the corresponding SQL Column
- 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
- Mapped Type: SQL server column data type
- Hits: Total Number of documents found with this field
- Max Elements: Maximum number of values found in the field. For multi-value fields.
- 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 they will be converted 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:
Make sure the SQL server accepts TCPIP connections, check out this post.
This action will delete and recreate the SQL database from scratch.
Select the Notes database and press the button 4 Initialize database.
This operation should go very fast and you must see some messages appearing in the Notes client status bar, for example:
The result of this operation is that the QSL db 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.
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.
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 that 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. It is advised to have the “Max LotusScript/Java execution time” at at least 1 hour.