Hi Everyone
I have been evaluating the options of using Notes with an MS SQL Server 2000/2005 back end to store data for a Lotus Notes Design Test database for the auto industry.
Each test needs about 5-20 parts, each with 5 or so fields. So, for 15,000 tests (I call these “Parent” documents) each with up to 20 parts (child documents) and about 6 different major design types, you get to nearly 2,000,000 records before you know where you are, that I wish to search and display on the parent documents when open.
The client does not have LEI, nor DECS, nor ND7 with DB2, and I think that a Notes db with anything over 200,000 docs wil get bloated (I have considered the regular archival approach too).
I am evaulating at LSX, LS:DO, OLEDB, ODBC etc to add the power of MS SQL table storage, and SQL view querying . . . just wondering if anyone out there has a sample app for me to look at. I have seen many samples of code on forums, and other how to retrieve/set data in SQL SERVER dbs, with these technologies, but need some REAL WORLD experiences.
I am sure someone MUST have been down this path before, does anyone have a sizeable app in production that they can offer lessons learned? eg. to create a new row in SQL using LSDO I have discovered you need to create an ODBC Record Set of all the existing rows (could be large!) before adding another row to it (not ideal!).
Any suggestions/comments welcome.
Paul
Subject: Anyone with a Notes app with an MS SQL Backend in Production?
My suggestion would be to investigate writing a “broker” in .NET (using ADO.NET) and using Proposion’s N2N product as a go-between. P’raps Stephen Walch might step in here to maybe confirm this approach?
Subject: Anyone with a Notes app with an MS SQL Backend in Production?
Hi Paul,
We have a number of applications that are used in production with over 500,000 documents. One application gets goods receipts from SAP, combines information from our quality system and calculates a supplier quality rating for viewing on the internet. Another application moves about 15,000 rows, each month, contained in 40 spreadsheets into MS SQL server for processing. Consolidated results are moved back to Notes where execs can securely access the information while travelling.
The key to making an application with a large number of documents is to keep your views few and simple, and host the application on a domino server with plenty of power. Make sure you have established the document retention time before you put the db into production, 'cause if you leave it 'till later you WILL get performance issues – build in an archive process from the start.
LEI is a world-class product and it has been very stable and productive. Why don’t you build in the cost of an LEI server to your project and then be able to connect to MANY different data sources. Support for LEI from IBM has been excellent. You might also check out the Enterprise Integration forum.