Originally published at migrationparity.com.
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.
Top comments (0)