What's the best way to store binaries in the database with the current Sybase version 16?
asked 05 Dec '14, 08:28
I have used option 1 when it was the only choice. LOTS OF HEADACHES accompany this method: files get deleted, renamed, moved and the links are useless. Database migrations to upgraded servers, backups and restores, get out of sync. Etc.
I have used option 2 for several years now with SQLA and HIGHLY RECOMMEND this approach. SQLA handles large blobs very efficiently and you have a reliable ACID data set.
You may say the large db and log files are a downside, but... you ARE going to have that space used somewhere, so why not use it for the database file and save MANY other headaches/maintenance issues. We have many gigabytes of blobs in our databases and would not change our approach.
answered 05 Dec '14, 11:35
I'm not aware of any extensions related to blobs in v16, and I could not even imagine how a database could "manage" externally stored data ACID-compatible as if it was stored internally - the database engine cannot fully control the contents of external files...
Some topics that might be related:
(As a different topic: LOAD TABLE has had several enhancements in newer versions w.r.t. to logging "external data" - here as the source data - to make it recoverable but I guess that's not the issue here...)
Having said that, I guess the questioned "best way" really depends on your personal trade-off between your points 1 and 2.
We offer our users both Option 2 (the default) and Option 1. Only our larger users tend to go for Option 1.
We manage all access to the blobs via the database server - absolutely no direct client access. Which of the two methods is in use is transparent to applications as all access is via the same database stored procedures. This method of controlling access seems to avoid the missing / moved files issues that Bill mentions as only the database server has access to the files. We compress all usefully compressible blobs (ie not jpeg, pdf etc) at the client end and store the compressed version - this seems faster and minimises bandwidth.
We've found little difference in performance between the two methods. We haven't used any of the new file handling options in v16.
If clients chose the external route they of course have to deal with the backup / synchronisation issues you mention.
answered 05 Dec '14, 11:56
Option 2 (store the blobs in the database) is the best method for all the reasons other people present.
If you prefer to store your blobs on a separate drive for performance reasons, you can store the blobs in separate tables and assign those tables to a separate DBSPACE. That might help if you have certain processes that perform heavy I/O on the blobs... a separate drive keeps that I/O separate from other data. Think carefully before creating multiple DBSPACE files, however; their existence makes administration more complex.
Another reason to store blobs in separate tables is MobiLink synchronization. MobiLink synchronizes whole rows, so if the blob columns don't change much then storing them in separate tables reduces the amount of unnecessary synchronization traffic.
answered 07 Dec '14, 12:34