|
1 |
Based on LNExtract version 2.17 - December 2025 |
Prerequisites
- Lialis Notes2SQL requires a IBM HCL Domino server version 9 (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 9 (or higher).
- SQL server, for example Microsoft SQL Express or SQL Server Developer.
- Microsoft Excel does not have to be installed on the migration server in the case of Notes database exports to Excel.
High-Level Migration Setup
Notes2SQL must run on a HCL Domino server; it is not a migration tool that executes in a Notes client. We recommend setting up a dedicated Domino server (see prerequisites) to run the Lialis Notes2SQL software, which operates as a Notes application.
Replicate the Notes databases from the production server to this dedicated migration server using push-only replication. Grant the Domino server running the Lialis tooling Reader access to all databases, including all ACL roles.
Its possible to allow the Domino server running Notes 2 SQL tool to access Notes databases on other Domino servers, but then the Notes 2 SQL Domino server must be trusted in the source Domino server document.
This setup ensures that the migration process does not affect the production environment.
Schedule regular replication — for example, once per week — from the production server to the migration server. This allows Notes2SQL update runs to execute smoothly and keep the SQL databases up to date.
Preparations
Make sure your Notes client has this Notes.ini setting: JavaMaxHeapSize=512mb, this is needed to carry out the license validation.
Make sure the Domino server has below Notes.ini settings present:
HTTPJVMMAXHEAPSIZE=256M
HTTPJVMMAXHEAPSIZESET=1
Store a copy of the Notes2SQL software (LNXtract.nsf) on the Domino server (R85 or higher). The file name and Notes db title is not relevant.
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 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.
In some cases, migration runs can take days or even weeks. The Lialis Notes 2 SQL tooling monitors the maximum agent runtime on the Domino server and will automatically stop just before the server terminates the agent. On the next run, it will continue using the updated agent runtime settings. This ensures you don’t have to worry about the Domino agent timeout being shorter than the total migration duration.
Configuration of the Notes2SQL software
Choose Add license to add the Lialis license key we will provide you after you have purchased the software.
Leave it empty if you only want to evaluate the software. Please note that an conversion limit 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.
The agents are now signed with your Notes ID, so its advisable to sign the Lialis Notes to SQL db again with the Domino server ID. In Domino admin client right click on the Notes app and choose sign, then choose active server ID. Then AdminP will do the signing and if you are in a hurry you can issue Domino command tell adminp process all.
Subsequently, select General Settings to configure the SQL server connections. Notes2SQL only supports Microsoft SQL server over OLEDB protocol.
To connect to the SQL server it requires the connection IP address or DNS name to the SQL server and login credentials of a user with SQL database administration privileges.
The test connection will reply an error if it cant reach the Domino server, if there is no message the connection is OK.
Always enable the Experimental option, this option will be removed from the settings in the version 2.18 as it becomes standard.
Its also possible to let the Domino server do the export to SQL, then use below settings. But keep in mind that for very large Notes databases with many fields Excel might run into limitation errors.
Notes database migration to SQL
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.
Analyze the Notes data
In this phase, the software checks every field on every document in the Notes database to determine its details — such as type, content, length, whether it’s multivalue, rich text, etc. This information is required for the next step, where the SQL tables will be created. When this phase is completed, you will find details like the example below in the Notes database record.
Each form in the Notes database is listed under Forms, where you’ll find detailed information about all fields found across all documents.
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.
Please open the Notes db record for details on the forms and fields analyse results.
A field mapping example is displayed in the image below.
Explanation of Source fields scan
Before any Notes document is migrated, the tool first scans all fields in the entire Notes database. Notes is schema-less: every document can have different fields, different datatypes, different multi-value structures, and even rich-text or attachments. To migrate this safely into SQL, the tool needs to figure out exactly which SQL tables and columns are required. So the tool runs a full field analysis and produces a mapping table like the one above. Every row describes one Notes item (field) and how it should look in SQL. Here’s what each column tells you:
Item name – The original Notes field name found in the documents. Notes fields can be system fields (like $UpdatedBy) or custom fields.
SQL column – The name of the SQL column the tool will create. If this is “-”, the field is not migrated (usually because it’s unsupported, irrelevant, or handled differently — e.g., attachments).
Hits – How many documents contain this field. This is important because Notes allows fields to exist in some documents but not others.
SQL datatype – The chosen SQL data type that best matches the Notes datatype. The tool determines this dynamically based on:
Notes value type
Max length
Multi-value count
Special data formats (numbers, dates, names, text lists, etc.)
Examples:
nchar(15) for short fixed text
nvarchar(max) for long or multi-value text
float for numbers
DATETIMES(…) mapped to nvarchar(max) because multi-datetime arrays don’t exist in SQL
Max elements Notes fields can contain multiple values. This column shows the highest number of values the tool found for this field across all documents.
Example: Max elements = 20 → some document had a list of 20 timestamps.
Max size: The maximum string length (or number size) detected across all documents for this field. This is used to determine the SQL column size (e.g. nvarchar(35), nchar(22)).
Notes datatype The original Domino/Notes value type:
TEXT
NAMES
NUMBERS
DATETIMES
RICHTEXT
ATTACHMENT
NOTELINKS
Etc.
This helps explain why the tool picked a certain SQL type and how complex the data is.
Value Changed Indicates whether the field values varied in datatype across documents. Notes is flexible, so a field might be text in some docs but empty or numeric in others. If true, the tool had to normalize or choose a safe SQL type (nvarchar(max)).
Value Data A sample of the actual values found. Useful for understanding what the field represents and debugging odd data structures like:
“STRING|1”
“NUMBERS|1.0”
“STRING|TWAddress”
“STRING|~b741672.jpeg” (attachment names)
Why this analysis is important
Notes has: no strict schema, documents with different field sets, multi-value fields, rich text, embedded files, special system items,
SQL, on the other hand, requires a fixed schema.
This mapping step is what makes it possible to automatically create:
SQL tables
SQL columns
Correct datatypes
Safe column lengths
Attachment tables
Relationship tables
Once the schema is built, the migration agent can stream the Notes data directly into SQL without surprises.
SQL Initialization
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.
SQL Data Migration
Finally, click Start conversion (Once) to send the Notes content to the SQL database. The Domino server will execute this action.
Conversion Modes
Lialis Notes2SQL offers two ways to perform the content conversion:
Start Conversion Once – Executes a single full conversion run.
Use this option when you want to migrate the Notes database content to SQL only once.
Start Conversion – Performs a complete initial conversion and automatically continues with regular update runs.
This mode keeps the SQL database synchronized with the Notes database by processing newly added, modified, and deleted documents.
Reporting
When the migration to SQL is complete, the software automatically generates a migration report comparing the number of Notes documents per form with the corresponding SQL tables to identify mismatches.
If fewer documents appear in SQL, you can use the built-in function that identifies which Notes documents (by Notes Document ID) did not make it into SQL, allowing you to investigate further.
Parallel processing
You can use parallel processing very easily. Simply make copies of LNExtract.nsf and give them numbers in the file name and title, for example: LNExtract 1, LNExtract 2, etc. Then you can load different Notes databases into each LNExtract and let the Domino server run the migration agents in parallel. Of course, make sure the server has enough CPU and RAM to handle the load.
Excel
The software can also export to Excel very well, but you may run into Excel limitations when the number of columns (i.e., Notes fields) becomes very large. We’ve seen this in several projects where the field count exceeded 1,000 columns.
Notes profile documents
Notes profile documents are not exported to SQL, instead the tool has a menu action that will take all profile documents and exports them in text format on your PC.
The result is displayed below
Notes code base
To be clear: the content is migrated to SQL exactly as it exists in the Notes database. Notes application logic is not exported.
This means SQL will not contain: form design details, field formulas or computed logic, subform structures, agent logic or run details, UI-level relationships between documents. For insight into the Notes design and code structure, please refer to our Notes code base AI tooling.
Exporting SQL MIME data
Notes rich text field data is stored in Mime format in SQL columns. The image below displays a SQL column holding Notes rich text data.
Keep in mind that the copy operation in SQL Server Management Studio will only copy 64K of data, so you dont get the full information this way.
Follow below steps to extract all MIME data from SQL and extract the text, attachments images and so on.
Download Mimekit from here : https://www.myget.org/feed/mimekit/package/nuget/MimeKit
Rename the MimeKit.4.14.0.1838.nupkg file to *.zip so you can unzip the package and store the folder on the migration server, for example:
Install Thunderbird browser as its very good to view the eml files we extract from the SQL.
Install Power-shell version 7.5.*
Install Visual Studio code as its great in Power shell running scripts
The code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 |
#MimeKit assembly Add-Type -Path "F:\MimeKit.4.14.0.1838\lib\netstandard2.0\MimeKit.dll" # ========== GENERAL SETTINGS ========== $Server = "servername DNS or IP adress" $User = "notesmigration" $Password = "password" $Dest = "F:\MimeExport\" # output folder # ---------- Prepare output folder ---------- if (Test-Path $Dest) { Write-Host "Clearing export folder: $Dest" -ForegroundColor DarkYellow Get-ChildItem -Path $Dest -Recurse -Force | Remove-Item -Recurse -Force } else { New-Item -ItemType Directory -Path $Dest | Out-Null } # ========== FUNCTION ========== function Export-MimeFromSql { param( [Parameter(Mandatory)][string]$Database, [Parameter(Mandatory)][string]$AttachmentColumn, [Parameter(Mandatory)][string]$TableName, [string]$IdFilter = "" # leeg = alles ) # ---------- QUERY ---------- if ([string]::IsNullOrWhiteSpace($IdFilter)) { $query = @" SELECT [universalid], CAST([$AttachmentColumn] AS varchar(max)) AS [description] FROM [dbo].[$TableName] "@ } else { $ids = $IdFilter.Split(',') | ForEach-Object { "'" + $_.Trim() + "'" } | Where-Object { $_ -ne "''" } $ids = $ids -join ',' $query = @" SELECT [universalid], CAST([$AttachmentColumn] AS varchar(max)) AS [description] FROM [dbo].[$TableName] WHERE [universalid] IN ($ids) "@ } Write-Host "==== START EXPORT ====" -ForegroundColor Cyan Write-Host "Database : $Database" Write-Host "Table : $TableName" Write-Host "Column : $AttachmentColumn" Write-Host "Filtered IDs : $($IdFilter -ne '')" Write-Host "----------------------" # ---------- SQL ---------- $conn = New-Object System.Data.SqlClient.SqlConnection $conn.ConnectionString = "Server=tcp:$Server,1433;Initial Catalog=$Database;User ID=$User;Password=$Password;Encrypt=True;TrustServerCertificate=False;Connection Timeout=300;" $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = $query $reader = $cmd.ExecuteReader() while ($reader.Read()) { $id = $reader["universalid"] $desc = $reader["description"] Write-Host "" Write-Host "---- ID: $id ----" -ForegroundColor Yellow if (-not $desc) { Write-Host "❌ No MIME body found – skipping" -ForegroundColor DarkYellow continue } $bodyLength = $desc.Length Write-Host "MIME size : $bodyLength chars" $folder = Join-Path $Dest $id if (!(Test-Path $folder)) { New-Item -ItemType Directory -Path $folder | Out-Null } $emlPath = Join-Path $folder "message.eml" $textFile = Join-Path $folder "message.txt" $imageFolder = Join-Path $folder "images" if (!(Test-Path $imageFolder)) { New-Item -ItemType Directory -Path $imageFolder | Out-Null } try { # Save EML [System.IO.File]::WriteAllText($emlPath, $desc) Write-Host "Saved EML : message.eml" # Load EML $stream = [System.IO.File]::OpenRead($emlPath) $email = [MimeKit.MimeMessage]::Load($stream) $stream.Close() # Body text $emailBody = $email.TextBody if (-not $emailBody) { $emailBody = $email.HtmlBody } if ($emailBody) { $emailBody | Out-File $textFile -Encoding UTF8 Write-Host "Saved body text : message.txt ($($emailBody.Length) chars)" } else { Write-Host "⚠ No text/html body found" } # Attachments $attachments = @($email.Attachments) Write-Host "Attachments : $($attachments.Count)" foreach ($attachment in $attachments) { $fileName = $attachment.ContentDisposition?.FileName if (-not $fileName) { $fileName = $attachment.ContentType.Name } if (-not $fileName) { continue } $attachmentPath = if ($fileName -match "\.jpg$|\.png$|\.gif$|\.jpeg$") { Join-Path $imageFolder $fileName } else { Join-Path $folder $fileName } $fileStream = [System.IO.File]::Create($attachmentPath) $attachment.Content.DecodeTo($fileStream) $fileStream.Close() $fileSize = (Get-Item $attachmentPath).Length Write-Host " - extracted : $fileName ($fileSize bytes)" } } catch { Write-Host "❌ ERROR: $($_.Exception.Message)" -ForegroundColor Red } } $reader.Close() $conn.Close() Write-Host "" Write-Host "==== EXPORT FINISHED ====" -ForegroundColor Green } Export-MimeFromSql ` -Database "db1" ` -AttachmentColumn "BodyAttachment" ` -TableName "Attachment" ` -IdFilter "FC9AB6400318D8FBC1257B4A002DB108,01863107BA827B0DC1257E5900287831" Export-MimeFromSql ` -Database "db2" ` -AttachmentColumn "Body" ` -TableName "Memo" ` -IdFilter "" #leave blank to export all data from this table |
The result of this script will look like this:
Challenges
The software has been successfully executed on very large Notes databases — for example, NSF files of around 100 GB, databases with millions of documents, and Notes form designs containing up to 1,500 fields. The tooling is therefore well-hardened.
However, every new project brings new challenges, because each Notes environment has its own unique complexities when migrating to SQL.
