07-10-06, 18:48 #1Registered User
- Join Date
- Jun 2003
- Longmont, CO
Unanswered: Storing files in Image Columns in 2005
I did some quick Googling and didn't find the article of my dreams here. Any experience with storing files in the db in 2005? Any opinions?
Here's the deal. We have our new 'Enterprise' software being designed and written in St. Petersburg (not FL) and I'm reviewing the design for dealing with letters and forms that are generated as hard copy from our business (by the thousands per month) and sent to clients for review, signature etc. Then they are returned by fax or snail mail.
The current design has all of these being stored in image data type columns in the database--storing saved files of the actual outgoing stuff and saved files of the stuff that comes in (scanned copies or the files from our fax server).
This screams 'BAD IDEA' based on our experience doing this in SQL Server 2000. We get things like 23 page faxes from Dr's offices, large packets of FMLA forms returned, etc. In 2000 they tended to get rather large when stored in the DB and caused us all kinds of headaches when Microsoft changed how it recognized and displayed certain types of files, e.g. tiffs.
Further, the outgoing stuff is largely boilerplate with added fields from the db--name, address, a few dates. My proposed design for outbound notifications and forms was to keep only references to the template used, the date created, and an id to link it to the personal info of the schmoe to whom it was sent--essentially, store which document template and then just enough data to map to the variable content that is pulled from the db. Then you can recreate the file that was printed and mailed at any time in the future, but you don't actually save the .doc (or Word .xml) anywhere, not in the file system, not in the db.
The current developers feel this is way too much work to design--and it is certainly more effort than just generating .docs/.xml files via a merge with Word and then stuffing those into the database. They also argue that having them in the db makes them 'more secure' and more 'accessible' to users around the globe.
Does anyone know if the image storage is any better in 2005 than 2000? The .docs & .tifs we used to store just about tripled in size when we put them in the db in 2000. Plus there were the defrag issues and generally it was an unsatisfying experience.
Does anyone know what the advantages/disadvantages of generating Word .xml files might be? Can they be stored as xml data type in SQL Server? Perhaps this is a better option?
If anyone has the time to digest this, please send opinions.
07-10-06, 19:21 #2Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
I am slacking. 2005 is still sitting on my desk uninstalled.
However I have always been partial to sticking files on the file server and keeping the paths and names of those files in the db. This does nothing for being able to search the content but as you know and have pointed out this has many database size and maintenance benefits. As for the "accesible" argument, I am not sure what their point is. You should be able to retrieve them regardless where they are stored. As for their security point, this only makes since if their database server is more secure than their file server and this begs the question as to why the file server is not secure. Sure you gain another level of security with the authenitcation and authorization that sql server provides but if you have hackers on your file server, things are not good.If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.
07-11-06, 09:42 #3Registered User
Provided Answers: 14
- Join Date
- Jan 2003
You could point out that if all the meta-data of the forms that are sent out is in the database, it is also secure.
I have not personally heard if image storage in 2005 has been improved. I expect it has been tweaked, but I doubt they have any dramatic improvements.
2005 does give you an XML datatype, which may help you a little, as the contents are not only searchable, but indexable, as well. I have not played around with that feature, as I do not have much experience with XPath query syntax. The big problem is getting the contents of the scanned forms/faxed documents into a format where you can save the metadata. Do you happen to have a bunch of data entry folks at your disposal?
07-11-06, 10:10 #4Registered User
- Join Date
- Jun 2003
- Longmont, CO
Thanks for your thoughts, guys. Having stewed this particular tomato overnight I'm thinking that my rebuttal will aim for the following:
1) Outbound stuff that we generate should never exist as a document except in .prn (printer file) format when it is sent to the printer. We should store just what I asked for originally (template id and ids to get merge fields from db). That gives us the ultimate security and reduced storage. It also totally prevents a current problem, which is that the users generate a letter in Word and then manually tweak dates because the data entry was wrong. Then you're really set up for disaster because the schmoe was notified of X date but the db still has the old bad date.
2) Storing the merge document templates (hopefully as xml) from which the outbound stuff is created in the db makes sense as it gives us great control over versions of templates. And there are only a few dozen of them.
3) Inbound stuff is still a huge problem as it will end up as .pdf format files. No, we can't throw data entry at it, we need an image with the actual signature of the schmoe and/or the schmoe's doctor. It would be cool if we could divide the scanned image into just the pertinent snippets (like they do with check images for processing) and store those but that's way out there. All I need is a good reason to keep these guys in the file system and not in the db. Any ideas? I agree with the security assessment, but I don't have a strong argument for this one yet.
Thanks for the help!