Just because IMAGES can be in FS, does that mean we should?

Jaymer

Using MSSQL, for sake of discussion.
Since Aware now allows images OUTSIDE of the db, and to be stored in the File System (since v7 I think), does it really mean that we should do that?
If its IN the db, it gets backed up just fine and can be moved to another server easily.
If its in the FS, then its another backup that has to be done.

I guess I'd wonder which is more reliable/safe - thousands of images (lets say 50,000) in a folder(s) or 50,000 binary images in the db.

(SIDE NOTE: I don't really remember back to v6 (and prior) and what issues were causing the need to store images OUTSIDE the db. But MSSQL is pretty robust and I don't think databases that store images inside the db are crapping out on a daily basis. So what was the issue? Derby?)

In MSSQL, the data is all in 1 file (or could be split into several).
In MYSql, all tables are separate files, so all those images would be in 1 huge file (assuming all images are in the same BO).

I know in MySQL, if you do export scripts using MySQL Workbench, for example, you have to exclude binary fields or else the ascii export gets messed up and cannot imported or loaded into a text editor (that was my prev. experience - maybe its gotten better).

Whats got me thinking about this is I'm exploring another tool which will handle offline access to the db. I can have a mobile app work disconnected from the db and synchronize when the user regains connectivity. I can take an image and it will handle uploading that to MSSQL, but it has no concept of the "Stored in FS" method that Aware uses. Its going to want to store it in a native picture field, so I'm back to considering that as an option, which is why I'm re-thinking this whole thing.

ACDC

Since Aware now allows images OUTSIDE of the db, and to be stored in the File System (since v7 I think), does it really mean that we should do that?

Before this feature came out the size of my database (MySql) was about 70GB. When it came to backups, snap shots, restoring data was a bitch, not to mention migrating the elephant to another server.

Once I converted all my image records to the file system feature, the database was reduced to 4GB.This made the world of difference, It’s now a walk in the park and I have less potential problems with data when moving the DB around and doing snap shot backups, it’s a lot quicker (Moving the “file store” in a separate process and method also allows for DB to be brought up for production before the files store migration is complete)

This alone justifies it for me, the ram required for an optimised DB was reduced immensely as well

Also, having a “file store” in your file system lets you do things without having to access the database or the Aim front end. ie: Run background file conversion utilities, Digitally sign pdf documents, Maintain an active file store mirror as a backup etc. etc.

As to the MSSQL setup , I have no experience whatsoever , although I do recall reading somewhere that the “File Stream” feature in MSSQL was doing image storage in the file system anyway, So the AIM feature sort of brought this feature into being when using MySql.

I don’t believe AwareIM is leveraging the File Stream feature of MSSQL in anyway.

BTW, if you google this topic, there is much discussion about which is the best option

The other point I would like to mention, If you use MSSQL there is a limitation on the database size of 10GB before you start paying through your nose. Does the image size in the DB not count against you in this case

rob_h7

Agree with ACDC

Wait until you have 900,000 records all with a small document attached and you need to make a change to that object. Publishing usually times out as it tries to update the table, hitting that table in any way in your app slows everything down to a crawl, it's just not usable.

Jaymer

Thank you guys. These are both great answers.
Especially the one how images will make it go greater than 10 gig, which will make you have to start paying for MSSQL.

Also, I remember I was improving a clients system in MySQL. An earlier version where if you added a column, It had to copy all the rows to a new table. That has been improved in recent versions. But I added a column to The image file which was multiple gigs, and it took two hours to publish. That was a nightmare. The largest file in the system, with 10,000+ images, that had to be copied one row at a time, packing and unpacking that binary data or whatever it had to do internally. Crazy.