Migrating Lotus Notes data to SQL
Have you been tasked with migrating Lotus Notes data to a relational SQL database, but you’re not familiar with Lotus Notes and are unsure how to proceed?
By Mario Pavic
Here, we give you a low-down on Lotus Notes databases and explain what you need to know to get started with your data migration to SQL or, more precisely, to a relational database format.
What’s a NoSQL Lotus Notes database?
In the most basic explanation, a Lotus Notes database is essentially a file that is saved with the .nsf file extension. Notes databases are normally hosted on a Domino server and accessed via Lotus Notes clients, although a database can also be configured as a web application and accessed through a web browser.
Notes databases differ from SQL systems in that they are based on a document database model, and not a relational database model like in SQL. This means that data in a Notes database is stored in the form of records (more commonly referred to as documents) that are organized in a tree-like structure: main documents and response documents.
Below is an example of a typical Lotus Notes database as viewed in a Lotus Notes client.
On the left side, there is a list of document views. Each document view shows the matching Lotus Notes documents based on some predefined criteria (think SQL views).
Within each view, we can see the view categories along with the parent and child documents (i.e., the response document structure).
A parent document can have multiple responses, and in turn each response document can also have its own response documents. However each response document can have only one parent document.
By looking at the various views and folders, we can learn a lot about the business use cases and what the application was used for, and can then make further plans for migrating the database to SQL.
What’s in a Notes document?
Now that we know how documents are organized in a Notes database, let’s look inside the Notes documents too.
A Lotus Notes document functions as a container for different types of values, such as text, numbers, or dates, organized in key-value pairs, with single or multiple values.
Additionally, Notes documents can contain one or more rich-text fields where users can enter different types of rich-text content, such as tables, images, file attachments, and document links.
In a nutshell, a Notes document can be compared to a row in an SQL table. And you can think of a Notes form as being similar to an HTML page; one which can be designed in such a way to show the database information in any number of ways to meet specific business needs.
From a technical standpoint, each document is identified by a unique ID string, which is called the UNID (think the Primary Key).
A lot of the things that happen in the Notes database revolve directly around the UNID, such as maintaining the parent-child relationships and maintaining the links between documents.
The UNID will also play an important part of the migration to SQL, as it can be used to identify related pieces of information, which will then be linked together through appropriate SQL table relationships.
Access Control List
Another important aspect of the Notes database that needs to be considered is the Access Control List, or ACL for short.
The ACL sets the level of access for a user, where each level determines the tasks that the user is able to perform on a Notes database.
The typical access levels in the ACL include the manager, designer, editor, author, reader, and depositor levels. For security, the ‘No Access’ option is selected by default.
To learn more about ACL, you can read through HCL’s official documentation at: Access levels in the ACL.
User access can be further restricted so that the Notes documents are available only to specific users.
This can be accomplished by adding a Readers Field in the Notes document containing the list of users and/or roles that are allowed to access the document.
To learn more about the Readers Fields, check out this knowledge-base article: Using a Readers field to restrict access to specific documents.
When you’re considering rebuilding the Notes application on SQL, you will need to analyze the ACL setup and decide if any or all of the ACL permissions need to be migrated to the new application too.
Also keep in mind that when you begin to export data from the Notes database, you will only be able to export the data that is accessible to the currently logged-in user.
Therefore, to ensure that all of the Notes data is exported and nothing gets left behind, it is recommended to run the exports with an administrator account with all access levels enabled.
The next step in your Lotus Notes to SQL path
Now that you know the basics, you should spend some more time familiarizing yourself with the Lotus Notes database to get a deeper understanding of the information it stores as well as the business needs.
Ideally, you should discuss the application’s purpose and usage with the application owner or other stakeholders.
This is also a great time to re-evaluate the application and to optimize the new SQL app, e.g., by dropping any information that’s outdated or no longer required, as well as to optimize the processes and workflows within the application.
Once you have a clear understanding of the business needs, you should create a shortlist of all the different types of data that need to be exported from Notes, build your SQL relational models, and create a strategy for migrating the Notes data into your new SQL database.
Your strategy should include provisions for data extraction, transformation, and loading (ETL) to the SQL database.
Lotus Notes to CSV to SQL — if you’re lucky!
Sometimes, on rare occasions when you just need some columns from a Notes view to be extracted from a simple application, you can perform a plain simple extract to CSV. This should be fairly straightforward:
- Open the Notes client, select a view, choose File > Export, save as a Comma Separated Value (CSV), and you’re good to go!
- Import the resulting CSV file into your SQL table.
- Finally, normalize the data into multiple tables if needed.
However, on most occasions, Notes databases will require you to take care of Lotus Notes rich content with rich text and images (RT fields), the document layout (which is an important part of the information context), document links, attachments, and also the document hierarchy.
If you are tasked with preserving all the information from a Lotus Notes database, it will be your responsibility to extract the complete content plus all the metadata to ensure that no historical data is lost.
As the available mechanisms for complete data extraction in Lotus Notes are fairly limited, you will probably need to devise your own ways to perform the extraction. For example, you could write your own extraction code or maybe use third-party utilities that specialize in such extraction.
Once you’ve identified your method of extraction, you should prepare a staging environment where all of the content will be stored temporarily. It’s useful to organize your staging environment by building a suitable folder structure to store the different types of content.
It’s also helpful to extract the Notes documents by saving them in your staging folders with their UNID as the file name. Doing so will enable you to later write transformation scripts or SQL import queries more efficiently.
As the data in Notes documents tends to be loosely structured, it will typically be extracted to XML files. You will thus probably need to further normalize the data to import it into multiple tables in a relational database.
File attachments can be stored as BLOBs in SQL, or hosted on a file server with file links maintained in an SQL table.
SQL database as the final destination for Lotus Notes data
While migrating a Lotus Notes database to SQL is typically never a straightforward proposition, unfortunately, with proper preparation and planning the migration can be completed with minimal cost and effort.
That said, it sometimes pays to truly evaluate if SQL is really the best place to move all your Lotus Notes data to.
Too frequently, administrators are tasked with performing SQL migrations with the simple idea of moving data from one database format to another, without a proper consideration of what such a move actually entails or if it’s indeed the most optimal way of doing things.
For inactive data in legacy Lotus Notes applications, a primary concern may be simply to extract the data from the Notes databases so as to be able to finally decommission the platform. In such cases especially, creating a new SQL database and writing migration scripts might seem like an overkill if the end goal is simply to keep the file in cold-storage for possible future reference or e-discovery.
How to reduce migration complexity and the risk of failure
As a rule of thumb, we always advise our customers to “archive all; then migrate what’s necessary”. That commonly means to archive all inactive data and to migrate only active or the most recent data. Or only the portions of the data in a tabular form for instance. In so many situations, this really helps organizations to speed up their migration efforts, typically unlocking their acute analysis paralysis.
So, why don’t you first jettison the unnecessary baggage — once it has been properly archived — and move on lighter and less stressed? That’s our way to reduce migration complexity and the risk of failure.
Finally, if you decide that moving all your Notes data to SQL doesn’t sound like that good of an idea for your specific scenario, tools like Seascape for Notes can help you to extract the data from your Notes databases quickly and easily. The extracted data can then be kept for long-term archiving purposes or deployed as part of new applications being developed.
Do you have a specific Notes application decommissioning or data archiving project you would like to discuss with us? Please send us your thoughts, comments or questions.
Originally published at https://www.swingsoftware.com.
