SQL Server FILESTREAM limitation

sql sql-server tsql sql-server-2008 filestream
pencilslate picture pencilslate · Sep 14, 2009 · Viewed 14.8k times · Source

I am looking at FILESTREAM attribute in SQL Server to store files in it. I understand it stores the files on hard drive and stores the file pointer/path information in DB. Also, maintains transactional consistency in the process.

There also seems to be a limitation "FILESTREAM data can be stored only on local disk volumes" for the FILESTREAM attribute.

If i anticipate my web app to store 200,000 images of 1-2mb each, i would require around 200gb of hard drive space to store the images. Since, the FILESTREAM requires all data to be stored only on local disk as per the limitation, it would be impossible to store millions of files on a single hard drive, as the storage requirements would be extremely large.

Is my understanding of the limitation correct or am i missing anything here?

If this limitation is correct, i would instead store it in db as plain blob and cluster my DB for increase in storage requirements, which doesn't seem to be possible with FILESTREAM.

Please share your thoughts!

UPDATED:
Few more questions regarding FILESTREAM:-

  1. How to handle data recovery in case of data container corruption?
  2. Can we just backup the DB without the file system data? [assuming data is in SAN, which need not be moved]
  3. I would like to back up or restore the DB and just remap the filegroup path information [that maps to SAN]. Is this possible?

Answer

Jeff Mc picture Jeff Mc · Sep 14, 2009

FILESTREAM does not actually require local storage, just not SMB network storage. An iSCSI or Fiber Channel SAN works fine to store FILESTREAM data. You can also have multiple filestream file groups per table, essentially partitioning your data. If you are strictly targeting sql server 2008 there is very little reason to not use filestream for large binary data. There is a Microsoft whitepaper describing filestream partitioning here.

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More
SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username nvarchar(100), AS BEGIN DECLARE @TopRelatedItemId uniqueidentifier; SET @TopRelatedItemId = ( SELECT top(1) RelatedItemId FROM …

Read More