How to access existing data from legacy Lotus Notes / Domino applications?
Use ODBC to expose the underlying tables of a Domino application.
Create a Linked Server in MS SQL Server to abstract the environment from the ODBC connection.
The steps below are for setting up the connection for a 64-bit SQL Server. The steps for 32-bit SQL Server would be different.
Perform the following steps on the computer that runs the database server.
A. Download and install Lotus Domino Server
- Go to http://www.ibm.com/developerworks/downloads/ls/lsndad/ and choose Download Trial.
- You'll have to login with an IBM login. Create one if you don't have it.
- In the next screen choose "IBM Domino Enterprise Server V9.0.1 Social Edition Multiplatform English Trial eAssembly" and click continue.
- After confirming the legal stuff, you'll get to a screen where you should download the file "DOMI_SRV_9.01WIN_64_EN_FULL_-TRIAL.exe (935 MB)".
- Install the downloaded file.
- Choose to configure it as a "Domino Utility Server" when asked.
- You can use the defaults for everything.
B. Configure Lotus Domino Server
Even though you will not actually use the server. You need to configure it as it it was a real server.
- Double click "IBM Domino Server" on your desktop and follow the wizard. Just click next until the last step.
- You'll just need to set some passwords.
- You can use the defaults for everything else.
- Disable the Lotus Domino services to make sure they don't start when you restart the machine. You really need to this, otherwise the odbc connection will not work.
C. Download and install NotesSQL ODBC drivers
- Go to http://www14.software.ibm.com/webapp/download/nochargesearch.jsp?cat=&q0=&pf=&k=ALL&pn=&pid=&rs=&S_TACT=104CBW71&status=Active&S_CMP=&b=&sr=1&q=IBM+ODBC+Driver+for+Notes%2FDomino&ibm-search.x=0&ibm-search.y=0
- Click the only option that shows on the page and, in the following page, click Continue.
- You may have to login and confirm the legal stuff again.
- Download the file "ODBC_ND_9.0_W64_EN.zip (97 MB)".
- Install it. You can use all defaults.
D. Configure authentication for NotesSQL
- Open the "NotesSQL Authentication List Manager" by opening Windows Explorer in the NotesSQL installation folder and double clicking Nsql_ALM.exe.
- Fill in the "Path to NOTES.INI..." as in the screenshot below, click "Display list" and then, "Add user..."
- Set the "Location of Notes ID" as set below and click OK.
- Click "Save List" and then "Close".
E. Change your system Path variable
- Go to the "Control Panel".
- Open "System".
- Click "Advanced system settings" on the left.
- Click "Environment Variables".
- In "System variables" double click "Path".
- Add ";C:\Progra~1\IBM\Domino\data;C:\Progra~1\IBM\Domino;C:\NotesSQL" to the end of the Value and click OK.
F. Configure the ODBC data source pointing to a local file or to a server
- Open to ODBC Data Source Administrator.
- Go to the "System DSN" tab and click "Add...".
- Select "Lotus Notes SQL Driver (*.nsf)" and click Finish.
- Fill in details as seen in the screen below (in this scenario I'm connecting to a local file on the same location as the DB server).
- Click OK and close the ODBC Data Source Administrator.
G. Configure the Linked Server in MS SQL Server
- Open SQL Server Management Studio and connect to your Database.
- Expand "Server Objects" and right click "Linked Servers".
- Select "New Linked Server" and fill in the form as seen in the screenshot below.
- Click OK and your Linked Server is ready!
H. Test it
- Create a new query window and test it by using, as an example SELECT * from openquery(NOTES, 'select * from Person') and you should get some nice results.
I. Create Views to allow introspection with Integration Studio
You may create views by using, for example CREATE VIEW LN_Person AS SELECT * from openquery(NOTES, 'select * from Person'). This will allow you to then create introspect and create a connection using Integration Studio.
J. Change the permissions of the OSRuntime database user
For the OutSystems applications to be able to access the data of the linked server in runtime you need to change the permissions of the OSRuntime database user. The environment uses this user to access data for the running applications.
As a proof of concept, give this user sysadmin permission to check that everything works. We currently do not know what are the exact permissions needed.