Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

The FILESTREAM Data Type in SQL Server 2008 Expand / Collapse
Author
Message
Posted Wednesday, September 24, 2008 7:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 03, 2010 3:56 AM
Points: 124, Visits: 91
Yes, files can be accessed from the file system.

But if you modify they might get corrupt & then SQL server might have problem in accessing them.
Post #575180
Posted Wednesday, September 24, 2008 9:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 15, 2010 7:58 AM
Points: 26, Visits: 81
Nice article, but I have a couple of questions/suggestions:

1. Are there any tests demonstrating the performance difference between FILESTREAM and traditional VARBINARY(MAX) columns? I've seen mention of varbinary being cumbersome in SQL2000 and older, but I was under the impression that it was much better in SQL2005.

2. Files can be accessed through the file system, but is this recommended? For example, would it be acceptable for a web site to directly access FILESTREAM files on the file system? Or are they just there in case someone wants to manually eye-ball one of them?

3. How can I convert existing varbinary(max) columns to use FILESTREAM? Will a simple ALTER TABLE do it?

4. What is the *PRIMARY* motivation for FILESTREAM? Is it for performance, or for accessing DB files from the file system?

Thanks again for taking the time to write the article. It was very informative and obviously got me thinking.

Scott
Post #575294
Posted Wednesday, September 24, 2008 9:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 13, 2014 11:04 AM
Points: 15, Visits: 94
What are the advantages of FILESTREAM over the old NTEXT datatype? NTEXT could also be stored in a separate datafile, backed up with SQL Backup, etc.


Theodore S. Feldman
Post #575295
Posted Wednesday, September 24, 2008 11:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:23 AM
Points: 199, Visits: 136
Hi,

Interesting topic and well written.

For those who are wondering about achieving the same functionality in SQL Server 2005, it should be possible to use using .NET CLR programming (like writing file handling code in .NET).
Never tried, but i guess it should be possible.

Vasant.
Post #575447
Posted Wednesday, September 24, 2008 12:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 06, 2008 6:16 PM
Points: 10, Visits: 20
If that be the case, why would SQL Server permit me to access the file through the OS?

I would then not be inclined to use this feature, particularly since it will corrupt my database.
Post #575466
Posted Wednesday, September 24, 2008 12:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 06, 2008 2:12 PM
Points: 10, Visits: 14
Good article, but a couple of clarifications are in order:

First, FILESTREAM is a storage attribute on a varbinary(max) datatype column. It is not a new datatype. Seems like a trivial difference, but it has important implications. Primarily, client code doesn't have to know anything about FILESTREAM. to a TSQL client, it's just another varbinary(max) column. The ONLY difference a client would notice is that the blobs can exceed 2GB.

Second, there is a supported way to access the individual files UNDER SQL CONTROL. Using the column.GETPATH() function, you get a logical path (not in the physical filesystem) which you use to open the file by calling OpenSqlFilestream. This gets you an open file handle which is enlisted into the current SQL transaction. Any modifications are done as part of that transaction, triggers fire when you close the file, etc.

The physical files are ACLd to the SQL service account only, so unless you're running as that account, or you are a box admin walking on file security, you can't access the files outside of SQL control.




Kevin Farlee
SQL Server Storage Engine PM
Post #575472
Posted Wednesday, September 24, 2008 12:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 06, 2008 6:16 PM
Points: 10, Visits: 20
Kevin:

Is Deepa's statement true that if somebody accesses/modifies the file bypassing SQL Server, it may render the file corrupt?

venki
Post #575485
Posted Wednesday, September 24, 2008 12:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 06, 2008 2:12 PM
Points: 10, Visits: 14
Scott Roberts (9/24/2008)
Nice article, but I have a couple of questions/suggestions:

1. Are there any tests demonstrating the performance difference between FILESTREAM and traditional VARBINARY(MAX) columns? I've seen mention of varbinary being cumbersome in SQL2000 and older, but I was under the impression that it was much better in SQL2005.

We have done extensive perf testing. Generally for blobs under 256K to 1 MB it's quicker to access via TSQL. For larger blobs it's quicker to access the file directly.


2. Files can be accessed through the file system, but is this recommended? For example, would it be acceptable for a web site to directly access FILESTREAM files on the file system? Or are they just there in case someone wants to manually eye-ball one of them?


The files can only be accessed via SQL functions, and under the control of SQL Server. There is a filter driver which verifies that the identity attempting the open has access to that cell in the database. Also, the access happens in the context of the current SQL transaction.


3. How can I convert existing varbinary(max) columns to use FILESTREAM? Will a simple ALTER TABLE do it?

No. Since it requires moving all of the data, you need to ALTER TABLE ADD COLUMN, copy the data, and then delete the old column.

4. What is the *PRIMARY* motivation for FILESTREAM? Is it for performance, or for accessing DB files from the file system?

Performance for accessing large blobs of data (think streaming video out of the DB)

Thanks again for taking the time to write the article. It was very informative and obviously got me thinking.

Scott




Kevin Farlee
SQL Server Storage Engine PM
Post #575486
Posted Wednesday, September 24, 2008 12:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 06, 2008 2:12 PM
Points: 10, Visits: 14
the_rs (9/24/2008)
Kevin:

Is Deepa's statement true that if somebody accesses/modifies the file bypassing SQL Server, it may render the file corrupt?

venki


First, re-read my post about direct file access. It is ONLY allowed under the control of SQL Server. So nobody can access the file outside of SQL's control. Changes are logged and can be rolled back before you commit the transaction, even after you close the file handle. If someone has legitimate access to that cell in the database, and is putting a new value in the FILESTREAM column, then you get whatever they put in, EXACTLY THE SAME as if they put that same value in a Varbinary(max) column. The main difference is that now after you pass the SQL security checks, you can get the data into the column much more efficiently using the file handle.

Kevin




Kevin Farlee
SQL Server Storage Engine PM
Post #575492
Posted Wednesday, September 24, 2008 12:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 15, 2010 7:58 AM
Points: 26, Visits: 81
Kevin Farlee (9/24/2008)

We have done extensive perf testing. Generally for blobs under 256K to 1 MB it's quicker to access via TSQL. For larger blobs it's quicker to access the file directly.

[snip]

The files can only be accessed via SQL functions, and under the control of SQL Server. There is a filter driver which verifies that the identity attempting the open has access to that cell in the database. Also, the access happens in the context of the current SQL transaction.


Hi Kevin,

Thanks for responding. One more point of clarification if you don't mind.

When you say "For larger blobs it's quicker to access the file directly" does that mean "For larger blobs it's quicker to access the file directly via SQL functions, and under the control of SQL Server" (presumably using "column.GETPATH()" and "OpenSqlFilestream")?

Thanks again,
Scott


Edit: I think your previous post answers my question.
Post #575508
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse