SQL Server 2008 FileStream vs Normal Files

Question

I am creating an application like youtube to store videos and I need some advice.

Should I use SQL Server FileStream to store the video files or should I store them somewhere on the hard disk and record the path as a varchar(MAX) inside SQL Server?

Which is recommended and why?

Do you recommend something else apart from both these? Please feel free to tell me but please tell me why too.

Thank you so much.

Answer 1

The FILESREAM type has the advantage of providing transparent transactions while still storing large files on the file system. The drawback is that it is proprietary and if you decide to change database this solution might be less portable to other databases. So providing an objective answer to this question is impossible IMHO.

Answer 2

I would store them outside, simply because there isn't an immediate and pressing need to store them inside. Also, videos are big, and you might need to run them through some other encoding etc steps, which may or may not like SQL Server. Using basic files also gives you the opportunity to spread that load around any number of file servers, rather than a single SQL Server.

Re the path; don't store the full path - only store some path relative to an external root that you configure in your app. That way, you can relocate all the files and just change a single site setting, rather than having to do a big UPDATE. For example, in the DB I might store foo/bar/20110404_27.mpg, and then later combine that with my site-setting of \\myfileserver\share (using Path.Combine).