Notes 2 SQL Manual

Based on LNExtract version 2.17

1	Based on LNExtract version 2.17

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 client-based migration tool. 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.

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 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.

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 IP address 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.

Notes database migration

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.

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.

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.

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.

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.

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 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 Mine data

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 PS 7.5.*

Install Visual Studio code as its great in Power shell running scripts

The code

#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

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

Notes 2 SQL – LNExtract Admin Manual

Prerequisites

High-Level Migration Setup

Preparations

Configuration of the Notes2SQL software

Exporting SQL Mine data

or email us

Prerequisites

High-Level Migration Setup

Preparations

Configuration of the Notes2SQL software

Exporting SQL Mine data

You Might Also Like

Microsoft SharePoint O365 alternative for Notes Domino Journal, Bulletin Board and Notebook

Alternative platforms to replace Lotus Notes applications

Notes database Content Extractor

Let’s meet!

or email us