Storing files in database - share your experience

ACDC

If high levels of security are needed on the stored files, then one has to use the Database, I dont see any other way.

I have run some tests on backing up and restoring blobs in MySQL and the only problem that surfaces is when the files become larger than 1mb.

However if you set the "max_allowed_packet = 20M" (by default it is set to 1M) in the my.ini file of MySql server, this problem goes away and Restore and Backup can be run without a problem

tford

If high levels of security are needed on the stored files, then one has to use the Database, I dont see any other way.

Why do you say this?

ACDC

Well, sometimes you may not have full control over the the file system. If you are committed to security levels e.g.such patient records, imaging etc etc, I think the DB and application rules let you control the security far better .

rgates@ttcdas.com

Tom,

Do you have a sample BSV that I could look at? I'm currently creating a CRM solution. I am currently storing the documents in the database, but from the sounds of it -- I should be storing them at the file level.

I put a rule at the BO level that does an export, but it exports all the attributes and not just the document. Not sure how to just export the document so that I can just embed a link onto the form for viewing the object.

tford

Sample BSV is at http://www.awareim.com/forum/viewtopic.php?p=13269#13269

kklosson

This is an interesting thread. I have a BO that does nothing but store documents related to another BO. It's a key feature. I looked at the sample BSV (though not the code). It looked as though this was constructed to migrate the documents from the BLOB to the file system. My app is still in development and if it's a bad course to store documents in the Document attribute, then I should change my course now. However, I can't really imagine how to implement this in Aware IM. As stated, I have a Case File thing and I need to allow the attachment of many files. This would probably require a file stricture having a file folder for each case file or something.

Is this really a course of action I should consider? And if so, does anyone have a BSV that demonstrates this?

Thanks

RLJB

1.5 years later ... does anyone care to update on their experiences and thoughts?

FYI - we just use Doc attributes and thus store in the DB. No problems so far BUT our DB is just at 1.1 GB - not too big. Are there dangers ahead?

nlarson

at Satuit we use bLOBs to store docs and have a few DB's in the 50+gb range with total client DB's around the 1TB size. ₈₅% is documents.

Performance is fine. Migration is a pain but not really a big a deal. Long-term (for commercial software) I think cost is one critical reason to move docs out of DB blobs. We run the DB storage on Fiber Channel SANs which cost way more than disk space on a NAS. So we're spending big money for storage which is major overkill for file serving, plus we have a warm DR so we have to buy the hardware twice!

In our next major maintenance cycle we will move to use MS SQL's Filestream feature. Nice feature which is unfortunately MS SQL only, but renders this question basically moot and would be transparent to Aware. Hopefully MYSQL will implement it soon.

ACDC

Thanks for this information, storing files outside the db has its issues and I think filestream MS SQL is a good solution, but the question is does AwareIM support this

nlarson

Good question, I had just kind of assumed Aware used blobs for documents. If so FileStream should be A-OK as it is managed as part of the database file system fabric.

Kahuna

Be very useful to know from AwareIM if filestream is supported in MSSQL. I'm about to develop the document handling process for our test build in the system?

Currently in all our developments we use the file system on a secure server location to store all documents and images with a link back to the relevant record.

This causes issues in our current development environment because we need two code bases - one for Web Start Client and another for Web Client. Complications we could do without - so saving with automated Db support would be the ideal - especially considering the compression built in.

Ian

« Previous Page