locked
Is filestream or Blob best for small database with word and other docs RRS feed

  • Question

  • Hello,

    I have limited database experience 

    I was putting a database together (its a C# application MSSQL)  the application will handle a few hundred customers records and save  maybe a couple of thousand  word docs/images/other doc files wondered if the way to go was blob or filestream, I see the medium and larger databases seem to go for filestream but just wondered as not much mentioned about smaller dbs. ?

    I do not think  security/disk space/super fast access  will be a big issue.

    thanks 

    Saturday, August 15, 2015 2:14 PM

Answers

  • Filestream is by far the best recommended path for you.  I have personally seen too many applications that started as small have rapid growth and become very big due to blob storage. Its always a best practice to keep binary files outside of your primary filegroup and filestream helps with that.

    • Proposed as answer by SQLGru Saturday, August 15, 2015 8:27 PM
    • Marked as answer by Charlie Liao Monday, August 24, 2015 5:38 AM
    Saturday, August 15, 2015 2:32 PM
  • If you are using SQL Server 2012 or later, you might consider a FileTable, which is built on top of the filestream feature.  Files stored in a FileTable can be exposed via a UNC path so that the appear as normal files stored on the file system and also allow access via T-SQL.  

    See https://msdn.microsoft.com/en-us/library/ff929144.aspx.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Saturday, August 15, 2015 7:58 PM
    Answerer

All replies

  • Filestream is by far the best recommended path for you.  I have personally seen too many applications that started as small have rapid growth and become very big due to blob storage. Its always a best practice to keep binary files outside of your primary filegroup and filestream helps with that.

    • Proposed as answer by SQLGru Saturday, August 15, 2015 8:27 PM
    • Marked as answer by Charlie Liao Monday, August 24, 2015 5:38 AM
    Saturday, August 15, 2015 2:32 PM
  • Thanks , after your suggestion got round to doing a little more research and seems like the the way to go.

    Cheers

    Saturday, August 15, 2015 6:31 PM
  • If you are using SQL Server 2012 or later, you might consider a FileTable, which is built on top of the filestream feature.  Files stored in a FileTable can be exposed via a UNC path so that the appear as normal files stored on the file system and also allow access via T-SQL.  

    See https://msdn.microsoft.com/en-us/library/ff929144.aspx.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Saturday, August 15, 2015 7:58 PM
    Answerer