Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The FILESTREAM Data Type in SQL Server 2008


The FILESTREAM Data Type in SQL Server 2008

Author
Message
Deepa Gheewala
Deepa Gheewala
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
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.
Scott Roberts-430649
Scott Roberts-430649
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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
tfeldman
tfeldman
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 112
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
Vasant Raj
Vasant Raj
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 137
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.
the_rs
the_rs
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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.
Kevin Farlee
Kevin Farlee
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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
the_rs
the_rs
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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
Kevin Farlee
Kevin Farlee
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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
Kevin Farlee
Kevin Farlee
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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
Scott Roberts-430649
Scott Roberts-430649
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search