BLOB or external storage...
-
If you had the opportunity to re-design a large system's DB layer, that beside pure data contains a fairly large amount (hundred of thousands every year) of files, would you use BLOBs or external storage? The facts are known about those files: 1. Most of them are PDF, DOC(X) and image (JPEG, TIFF, BMP) 2. No search inside the files are required 3. Files are acceded mostly (95% of the time) directly 4. History of files are important
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
"It never ceases to amaze me that a spacecraft launched in 1977 can be fixed remotely from Earth." ― Brian Cox
-
If you had the opportunity to re-design a large system's DB layer, that beside pure data contains a fairly large amount (hundred of thousands every year) of files, would you use BLOBs or external storage? The facts are known about those files: 1. Most of them are PDF, DOC(X) and image (JPEG, TIFF, BMP) 2. No search inside the files are required 3. Files are acceded mostly (95% of the time) directly 4. History of files are important
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
Blobs put heavy weight on the tables, the bigger they are the slower the operations. Also if anything corrupts on one file you'd have to restore a DB table instead of a single file or directory. Also it would be more scalable as it would allow you to use different backend servers to store the files and have a rough load balancing/availability service without heavily involving the DB server, all at the advantage of performance.
Geek code v 3.12 GCS d--- s-/++ a- C++++ U+++ P- L- E-- W++ N++ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t++ 5? X R++ tv-- b+ DI+++ D++ G e++>+++ h--- r++>+++ y+++* Weapons extension: ma- k++ F+2 X
-
If you had the opportunity to re-design a large system's DB layer, that beside pure data contains a fairly large amount (hundred of thousands every year) of files, would you use BLOBs or external storage? The facts are known about those files: 1. Most of them are PDF, DOC(X) and image (JPEG, TIFF, BMP) 2. No search inside the files are required 3. Files are acceded mostly (95% of the time) directly 4. History of files are important
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
I would go for external storage. You can set up a series of meta tables with locations/mappings of drives and folders and store the files outside of the database. You could also create a series of stored procedures to check that the files are in the locations held in the database, every day or so, reporting back on discrepancies - just to catch the rare occasion where a file has been moved or deleted.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
If you had the opportunity to re-design a large system's DB layer, that beside pure data contains a fairly large amount (hundred of thousands every year) of files, would you use BLOBs or external storage? The facts are known about those files: 1. Most of them are PDF, DOC(X) and image (JPEG, TIFF, BMP) 2. No search inside the files are required 3. Files are acceded mostly (95% of the time) directly 4. History of files are important
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
What are you most comfortable with? I would personally go for BLOBs. Pros:
- You get one point of backup.
- Lowered chance of tampered or missing files.
- You only need to set permissions in one place
Cons:
- If badly implemented performance will suffer
- If your database is stored on a filesystem you will get an overhead
- No direct access to files :rolleyes:
There's a paper[^] from MS on the subject
Wrong is evil and must be defeated. - Jeff Ello
-
If you had the opportunity to re-design a large system's DB layer, that beside pure data contains a fairly large amount (hundred of thousands every year) of files, would you use BLOBs or external storage? The facts are known about those files: 1. Most of them are PDF, DOC(X) and image (JPEG, TIFF, BMP) 2. No search inside the files are required 3. Files are acceded mostly (95% of the time) directly 4. History of files are important
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
Just to confuse matters, you might also want to look at file tables: :) FileTables (SQL Server)[^] SQL Server 2012 FileTable: My first experience[^] Using FileTables in SQL Server 2012[^]
In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Just to confuse matters, you might also want to look at file tables: :) FileTables (SQL Server)[^] SQL Server 2012 FileTable: My first experience[^] Using FileTables in SQL Server 2012[^]
In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Cool.
Wrong is evil and must be defeated. - Jeff Ello
-
If you had the opportunity to re-design a large system's DB layer, that beside pure data contains a fairly large amount (hundred of thousands every year) of files, would you use BLOBs or external storage? The facts are known about those files: 1. Most of them are PDF, DOC(X) and image (JPEG, TIFF, BMP) 2. No search inside the files are required 3. Files are acceded mostly (95% of the time) directly 4. History of files are important
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
If you are using SQL SERVER 2012 then what do you say about using File Table
