File stream vs local save in sql server?

Question

my application plays video files after the user has registered (files larger than 100 MB).

• Is it better to store them on my hard drive and save the file path to the database? Or store in the database as a file type?

• When is data stored in a database safer against manipulation and stored in hard?

• How to protect data from manipulation?

Thanks.

Answer 1

There's a really nice article from Microsoft Research called In Blob or Not To Blob .

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

• if your images or documents usually have a size below 256 KB, storing them in the VARBINARY database is more efficient

• if your images or documents tend to be larger than 1 MB, store them more efficiently in the file system (and with the SQL Server 2008 FILESTREAM attribute, they are still under transaction control and part of the database)

• between these two, it depends a little on your use

If you decide to place your photos in a SQL Server table, I highly recommend using a separate table to store these images - do not store the employee photo in the employee table - keep them in a separate table. Thus, the Employee table can remain sparse, medium and very efficient, assuming that you do not always need to select an employee photo as part of your queries.

For filegroups, check out Files and Filegroup Architecture to log in. Basically, you will either create your database with a separate filegroup for large data structures from the very beginning, or add an additional filegroup later. Let me call it LARGE_DATA .

Now that you have a new table to create that should store the VARCHAR(MAX) or VARBINARY(MAX) columns, you can specify this group of files for big 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 entry in filegroups and play with it!

Answer 2

1 - depends on how you define "better." In general, I prefer to store binary assets in a database, so they are copied along with the associated data, but they are cached in the file system. Streaming binary data from SQL Server to request a page is a real performance bonus, and it really doesn't scale.

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

3 is a whole question in itself. Too wide for ...