file stream vs local save in sql server?

Question

my application play videos files after that user they are registered .(files are larger than 100 MB ) .

1. Is it better to do I store them on the hard drive and Keep file path in database ? Or do I store in database as File Stream Type ?

2. When data is stored in the database, are more secure against manipulation vs with stored in hard ?

3. How to provide data security against manipulation ?

Thanks .

Answer 1

There's a really good paper by Microsoft Research called To Blob or Not To Blob.

Their conclusion after a large number of performance tests and analysis is this:

• if your pictures or document are typically below 256K in size, storing them in a database VARBINARY column is more efficient

• if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient (and with SQL Server 2008's FILESTREAM attribute, they're still under transactional control and part of the database)

• in between those two, it's a bit of a toss-up depending on your use

If you decide to put your pictures into a SQL Server table, I would strongly recommend using a separate table for storing those pictures - do not store the employee foto in the employee table - keep them in a separate table. That way, the Employee table can stay lean and mean and very efficient, assuming you don't always need to select the employee foto, too, as part of your queries.

For filegroups, check out Files and Filegroup Architecture for an intro. Basically, you would either create your database with a separate filegroup for large data structures right from the beginning, or add an additional filegroup later. Let's call it LARGE_DATA.

Now, whenever you have a new table to create which needs to store VARCHAR(MAX) or VARBINARY(MAX) columns, you can specify this file group for the large data:

 CREATE TABLE dbo.YourTable
     (....... define the fields here ......)
     ON Data                   -- the basic "Data" filegroup for the regular data
     TEXTIMAGE_ON LARGE_DATA   -- the filegroup for large chunks of data

Check out the MSDN intro on filegroups, and play around with it!

Answer 2

1 - depends on how you define "better". In general, I prefer to store binary assets in the database so they are backed up alongside the associated data, but cache them on the file system. Streaming the binary data out of SQL Server for a page request is a real performance hog, and it doesn't really scale.

1. If an attacker can get to your hard drive, your entire system is compromised - storing things in the database will offer no significant additional security.

3 - that's a whole question in its own right. Too wide for Stack Overflow...