Storing Files inside SQL server database : Good or BAD idea ?
-
Hello ! I'm creating a vb.net program that will use a sql server database. But for a lot's of record there's a document or image attached. I know that I can save the file on a folder and keep on database only the file path. But I've read something about FileStream storage in Sql server 2008 , or the possibility to save an entire file on a Varbinary(MAX) filed. So I'm asking if is a good idea to save files inside the database ? Thank you !
As PIEBALDconsult pointed out it depends on the requirements. In most of the cases I would use a filestream because it also makes backing up much easier and offers transactional features. Few resources if you're interested in filestream - Enable and Configure FILESTREAM[^] - How to store and fetch binary data into a file stream column[^]
-
That depends on what the app has to do with the files. Storing them in the database is a good idea in many cases, but offers no benefits in others. One benefit of using the database is that you can implement versioning of the files. Another is that it makes hacking the files more difficult.
The application need only to open the file ( and display its content on a control , or open with default programs on windows ). Also if the user want to change the file that is attached to a record ( for example has saved the wrong file and needs to save another file ). So , to be more clear , the application only save a file an open it , but there's no need to modify this file.
-
As PIEBALDconsult pointed out it depends on the requirements. In most of the cases I would use a filestream because it also makes backing up much easier and offers transactional features. Few resources if you're interested in filestream - Enable and Configure FILESTREAM[^] - How to store and fetch binary data into a file stream column[^]
-
The application need only to open the file ( and display its content on a control , or open with default programs on windows ). Also if the user want to change the file that is attached to a record ( for example has saved the wrong file and needs to save another file ). So , to be more clear , the application only save a file an open it , but there's no need to modify this file.
Then it sounds like you want the file system.
-
Then it sounds like you want the file system.
The problem that I have using filesystem , is that sometimes I need to backup and restore the database.And in this case is difficult to put all the files in the backup. lso is the problem with the synchronization , I mean when the database has a reference to a file , but this file is deleted or renamed on file system.
-
And what about performance using the file contents on these cases : - Saving the file on a folder - Using FileStream - Saving on a VARBINARY(MAX) fields. ( Of course supposing that the file may be on whatever size ).
-
The problem that I have using filesystem , is that sometimes I need to backup and restore the database.And in this case is difficult to put all the files in the backup. lso is the problem with the synchronization , I mean when the database has a reference to a file , but this file is deleted or renamed on file system.
Then database it is.
-
In the second link I posted, there are some measurements in the end. Have a look at those.
-
I prefer streaming files in database. In many cases it payed back - specially in backups.
-
I worked mostly with pictures with up to 4MB, but tried up to 1GB files (MySQL). Pay attantion to setting of database to allow large files.
-
I´ve learned a lot from here: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=4630&lngWId=10