Migrating Lotus Notes to SQL Server: mapping documents to tables

The inventory script finished at two in the morning and told me our twenty-year-old NSF contained not one schema but three. Documents saved before 2008 had one set of items, documents from the middle years another, recent ones a third — same form name on all of them. SQL Server was going to want one answer, and the database had nine thousand opinions.

That tension — a bag-of-items document store on one side, tables with columns on the other — is the whole project. The export itself is a solved problem; any tool can walk an NSF and emit rows. What follows is where the mapping decisions actually live, from a ~9,000-document workflow application I took to .NET with a SQL Server backend.

Columns come from documents, not from the form

The form designer shows you what a new document would get. It says nothing about what the existing nine thousand are carrying. A Notes document keeps every item it was ever saved with, under whichever revision of the form existed that year — fields that occur only in 2003–2007, fields renamed twice, one field whose name contains a typo made around 1998 that could never be fixed because thousands of documents already had it. The typo goes in the mapping table like everything else.

So the schema comes from an item census: scan all documents, collect item names, types, value counts, fill rates. Cluster by generation. The SQL schema is designed against the union, with an explicit rule per generation — convert, default, or consciously drop. Anything built from the current form design alone loses data without telling you.

Multi-value items want child tables

Notes lets any field hold a list. Approvers, categories, linked batch numbers — one item, five values, no ceremony. SQL has three ways to take this and two of them are traps. Concatenating with semicolons means every future query does string surgery. Columns named Approver1 through Approver8 work until the ninth approver.

The boring correct answer is a child table per genuinely multi-valued item: document_id, position, value. Position matters — in Notes workflows the order of a names list often is the approval order, and flattening it to a set quietly rewrites the business process. Where a multi-value field turned out to hold one value in 99% of documents, I kept it as a column and moved the exceptions into the child table during cleanup, each one eyeballed. There were eleven.

The response hierarchy is already a foreign key

Notes documents form trees: main document, responses, responses-to-responses. Under the hood each response carries $REF — the 32-character UNID of its parent. That is a parent key wearing a trench coat.

The mapping is mechanical — parent_id column, foreign key, done — but two things bite. First, UNIDs must survive the migration as a column (unique-indexed), because every doc link anyone ever pasted into an email or another document addresses the UNID, and because your own migration needs the old identity to be re-runnable. Second, orphans exist: responses whose parent was deleted years ago. Our database had a few dozen. Decide their fate in the mapping spec — adopt, archive, or drop — not at 2 a.m. when the foreign-key constraint fails.

Rich text is a file format, attachments are a separate estate

I covered rich text in detail in what actually breaks in a Notes migration: treat it as a document tree, convert to disciplined HTML, extract embedded objects. For the SQL mapping the decision is narrower — rich text does not go into SQL Server beyond the HTML body. Images, attachments and OLE objects go to real file storage with reference rows.

One trap deserves its own paragraph. If the Domino server uses DAOS, the NSF you copied for testing does not contain the attachments at all — it contains tickets pointing into a directory of .nlo files on the server. Ours held 32 GB across roughly sixty thousand of them. A migration rehearsed against a bare NSF copy reports success and delivers a database where every paperclip icon leads nowhere.

Names are not users

Every author, approver and reader in the NSF is recorded as a hierarchical Notes name — CN=Dana Levy/OU=QA/O=ACME — not as a login your new system knows. People married, transferred departments, retired in 2011 and are still named on open documents. SQL wants a users table with stable IDs; your HR system joins to Notes names only by string matching, if at all.

Budget a name-resolution pass as a first-class migration table: Notes name → canonical person → new system account, with a human owner for the unmatched residue. It looks like a utility script. It is two weeks.

Reader fields become row-level security, or your migration is a breach

Reader and author items make documents invisible at the engine level; export them to SQL without reproducing that and on day one everyone reads everything — confidential cases included. Nothing will look broken. In SQL Server the honest equivalent is row-level security predicates driven by the same person/role mapping as above, tested by logging in as sampled real identities on both systems and diffing what each one sees.

The short version of the mapping

In the NSF	In SQL Server
Form (all historical generations)	Table designed against the item union
Item	Column, with per-generation conversion rule
Multi-value item	Child table with position
Response / $REF	parent_id FK; UNID kept as unique column
Rich text	HTML body + files in storage, reference rows
DAOS attachment	File in storage — only reachable via the server
Notes hierarchical name	Row in a name-resolution table
Reader / author fields	Row-level security predicates
Document history item	Verbatim text, appended-to, never rewritten

The last row is deliberate. Our application carried its whole audit trail as one ever-growing text item per document. The temptation is to parse it into neat event rows. Parse a copy if you want the analytics — but keep the original text column byte-for-byte, because it is the only record auditors already trust, and the parser will be wrong about 1997.

The export tool finished its part in an afternoon. The mapping spec — the real deliverable — was forty pages, and every page was one of the rows above arguing with twenty years of actual documents.

This is the kind of mapping our Lotus Notes / Domino migration service produces before any code is written — and the parity harness then proves the result against the live NSF, document by document. If you are sizing such a move, start with the assessment.