Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Will H
Yak Posting Veteran
56 Posts |
 Posted - 2010-10-06 : 19:57:43
|
| I'm in a group that uses a large amount of files, txt, office-docs, pdfs, etc for testing purposes. I'm building a database to keep track of these files and where they live, as well as arbitrary file metadata. The original idea was to keep the files where they are, and just store the path+name in the database. We would write code that queried the db, recieved the file paths, and the process would open up the files based on the path and consume from there. Sounds ok, but if a path doesn't match up or if someone moves/renames the files, things break.The alternate idea was to store the entire file contents in the DB in some sort of binary format. This would remove a dependency on data stored in the db versus the files on the file-share. I'm intrigued, but haven't much experience in storing arbitrary binary data in a database. Are there any pitfalls or design considerations to watch out for? How do you actually go about getting the file on disk to the bytes in the database (and vice-versa)? Are SQL Server implementations generally up to task in storing this information? The files aren't huge (small-20 MB) and we have around ~10000 different files. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-07 : 02:31:27
|
| I don't have any experience of this as we use the store-the-path approach too. Only thing I can contribute is that if you put the files IN the database your full backup will include ALL of them. There is no incremental backup policy you can adopt. You may be sweeping all-documents-to-tape-every-time at present, so perhaps no change in that sense. However, the Archive attribute at the file level does provide you with an option to use a more structured backup approach (so called hierarchical backups IIRC??) in future, if you needed too. No such luck with SQL Server data, although Compressed Backup might help.Larger Database = both Larger Backup Files (including Log backups) and also More CPU to make a Full BackupYou could adopt differential backups in SQL Server, but for safety you will want a Full Backup reasonable often (once a week would be the minimum I would feel comfortable with) |
 |
|
|
|
|
|
|
|
Storing binary data, file contents, in db