There are, effectively, two options for storing your files. It's worth considering what the reasoning behind these methods are before you make your choice. The first option is to keep your files in the file system, and just store the location in the database (also including information such as the original filename).
- If there's a potential that you are going to move to a different database, then this is a simpler option than recreating the files from the database, and reuploading them in the new database format.
- Storing the files gives you more choices for retrieving the files, without having to write your own custom extraction routines.
- If you're using the express edition of SQL Server 20005, there's a 2GB limit to it's size. This would soon fill up if you were using files.
- Depending on what you want to do, e.g. just have a link to the file on a web page, this could require you to have to extract the files and write them to a temporary location which would need clearing up. If you just store the file on the disk, you don't have to worry about this, and the link will be quicker to generate.
The alternative is to store the file in the database. Here are some reasons for this option:
- If you have an archive strategy in place, e.g. removing old files, then it's a lot more work to have to maintain two change points which are independent of each other. In other words, if you store the file on the disk and you move it, you also have to update the database - but what happens if the database update fails? You have broken the connection between the file and the data row that points to it.
- If you upload files to storage, you have to ensure that the filenames are unique. This would mean saving the file with a randomly assigned name (probably a GUID), and storing a mapping in the database between the two. If the file is in the database, there is no need to change the filename.
- Storing the file on the file system requires write permissions to the file storage location. Granting this access may not always be possible.
So, whichever option you choose is up to you, once you have weighed up the above considerations.