Hello friends;
Recently, I had a discussion with Aware staff regarding this subject and I thought I share my two cents on this here, so others can also offer the thoughts and experience, so we all can benefit and do the right thing.
Storing PDFs in DB
PROS:
By having a good DB design, I can have a PDF very close to it’s data attributes in the database and due to having the power of SQL engine behind me to find/search any PDF, it makes a lot of sense to store it in DB.
When building app, Aware has built in features to store, fetch, display the PDF or DOC file very seamlessly. A huge plus going this way.
Since the PDF is stored in DB, if I delete a record, the PDF is gone too, since it’s not sitting outside somewhere else to be deleted.
CONS:
*I don’t have enough experience to know, once the number of records gets high with large BLOB inside of them, how the performance of SQL engine will become. And how it would impact SQL Server’s resources and CPU when storing and retrieving lots of BLOBS.
File system storage is much cheaper than SQL DB storage. So, storing large PDF files and lots of them, can cost a lot for monthly hosting vs. if the PDFs were stored in file system.
Database size can grow very large with BLOBS stored in them, VS. if just regular data was stored.
Storing PDFs in File system
PROS:
Database remains small, less resources used and we can backup & restore DB and PDFs separately.
DB monthly costs will substantially go down.
I can then store all the PDFs as individual files in the hosting file system that Aware’s Server has knowledge of that location that will be stored in DB.
CONS:
When a PDF is uploaded, and is NOT going to be stored in DB as BLOB, I assume from Aware’s point of view, I have to manage where that PDF must be stored, it’s [unique] name and location should be determined to store in the DB.
I then have to create and maintain folders to store all these external files.
Whenever a DB record is deleted, I have to make sure the actual PDF file is also removed for file system.
I’m not sure how much automation is built into Aware, when a PDF is stored in File system, vs. if the PDF was stored in BLOB, when it comes to display to user? Do we have the same convenience in display and upload in saving in file system vs. BLOB system?
These are some of the preliminary thoughts I had just to see the direction to go to design this system.
Note: MSFT also offers another solution that has the advantages of both solutions combined called "FILESTREAM".
http://technet.microsoft.com/en-us/library/bb895234%28v=sql.105%29.aspx
However, this will require some changes made to the actual SQL scripts that Aware generates when tables are created. It's a minor addition. Perhaps Aware Support can look into this to offer this as an option for those who want to use MS SQL and use this option.
Anyway, your thoughts and experience is welcomed.
