Collectives on Stack Overflow. Learn more. Storing Documents as Blobs in a Database - Any disadvantages? Ask Question. Asked 13 years, 3 months ago. Active 6 years, 2 months ago.
Viewed 48k times. The requirements for my document management system were: Must be secure from theft by simple copying of directories, files etc. Must be secure against traditional virus infection infection of physical file Must be fast to retrieve The repository must not be visible to casual directory browsing users etc.
Improve this question. Add a comment. Active Oldest Votes. Improve this answer. Jacco Jacco As with any large dataset, have a server that you put in and out of replication to take snapshots of the database for backup.
How would this be any different with BLOBs? There is no difference between images vs any other BLOB data. Also, most web developments do not have big BLOB data, other than images. It is very easy to exceed this limitation. Bill the Lizard Bill the Lizard k gold badges silver badges bronze badges. From my experience, some issues were: speed vs having files on the file system. Documents will remain relative small, but I will keep this in mind, maybe having two databases on seperate servers or something like that.
A better solution for storing BLOBs must exist. What are the best practices? Mitch Wheat Mitch Wheat k 42 42 gold badges silver badges bronze badges. Robert Vabo Robert Vabo 4 4 silver badges 9 9 bronze badges. Mike Clarke. David Robbins David Robbins 9, 7 7 gold badges 49 49 silver badges 81 81 bronze badges. Improve this answer. Mostly only Write and Read. No need to overwrite the images. It is up to you, with blob, images are easier to organize, delete etc, but as most people use file system, when you have problems, there will be more people to help you.
I built a test website before, when there are more than 10 images extracted from database, sometimes, one or two of them failed to display, this confirmed that get images from database is slower. Show 3 more comments. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password.
Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually.
Visit chat. Linked Related 5. One case took up 7 full double sided DVDs worth of space. I converted it to store the pictures in the file system, and have the database point to the location.
I absolutely agree. The FileStream service keeps it all working. Instead of using a fileserver, I inherited a production application that used filestream technology instead of persisting user and system-generated files in a separate fileserver and just use metadata at the database level to point to the actual file.
The previous architect s just used all the shining and shimmering features of SQL including TVFs and SVFs and implemented our application using these technologies.
Boy oh boy, years later, it now has become a ticking time bomb and the application now is almost on its technological limits. We eventually encountered the same problems that this article has stated, huge and slow backups and restores, terribly large data files, etc, and our application has been in production for a few years now that it will be difficult to move the tens of thousands of binary files out of the filestream.
I also remember that any network related error would bring the whole filestream service down and we also have to clean orphaned entries in the filestream because of this. I feel your pain. The more data you have, the harder it is to migrate out of the database. It can be done but it has risks associated with it and needs to be handled with care.
If the data is immutable old partitions can be marked readonly — you take a one off backup and can exclude from your regular backups. On the restore side you can look at online piecemeal restores.
A couple of years ago we had discussion I was on L3 support with my colleague service manager during transition of a new part of sofware done by project team.
Developers, testers were consultants. Fortunately architects were permanent employees. There will be no thinking how it can impact the service in 1 month, 1 year or longer.
And consultant will focus only on now because is paid for it. Full weekly backup with retention 15 day long and expensive , daily differential, log every 15 min. AlwaysOn asynchronous replication database between primary site and DR site. Everything OK, as long as there is no need for a complete restore e.
Table partitioning in our case would not bring benefits, due to how the data are structured and updated…. And what about replication on DR site? File sync tool? We have a project that store files in BLOB.
Everything in one place! But I understand the concerns and the cons of doing it that way. How can you be sure that your database is always in sync with the filesystem storage? First, in the database, save pointers to the files, not the files themselves. A common technique is to use UNC paths, and store the path name in a field in the database.
Second, when you save files to the file system, always store new revisions in new paths or as new names. That way, if you need to restore the database to a prior point in time, you can do that without having to restore the file system.
If you want to discuss architectures like that more deeply for an upcoming project, feel free to click Consulting above. Hope that helps! This work if you have to restore the database. You might have more files in the filesystem than what the database really need. But if you inverse the problem and you have to restore the filesystem to another point-in-time, now you need to do something on your database because it have data that point to files that no longer exists. I guest I will have to restore the database to the same point-in-time too.
Alexandre — read the second to last paragraph in the post. Thanks Brent. All that batch automation spitting out. Thank god they moved it all to that MS cloud based Sharepoint service.
We stored pdfs in blobs. It solves the problem of keeping the myriad of locations and hierarchical structures and the infrastructure required to manage those out of the equation.
0コメント