Freigeben über

SQL Server 2008 Filestream FAQs

  • Artikel

I get a lot of questions about how filestream works with the other new features in SQL Server, so here’s what I know.

First for those that don’t know, filestream is a new data type in SQL server 2008 that  handles large unstructured data files like they are in the database. The clever bit is that it also allows them to be streamed out very quickly using win32 api as though they were files on a file system. 

This is done by creating a special filestream filegroup, which actually creates a directory tree for the files to be stored but they now have filenames of guids controlled by SQL server. Filestream thus differs from Varbinary(max) where the data is chopped up into SQL Server 8k pages which then have to be stuck back together for retrieval. 

General FAQs:   

How do Transactions work?   You can actually roll back a transaction where you have attempted to update a filestream object and it will revert to the previous version.  This is because the update will copy in the new object and preserve the original until you commit.  Needless to say lots of disk space could be needed here for example if your are storing HD videos in your database. 

How are Backups affected? Backing up and restoring the database is exactly the same. This is important as it allows the structured part of the row to be exactly in step with the filestream object after a restore.  Additionally because it is filegroup it can be backed up by itself or excluded form other backups as required.

What if I need my Filestream data in a high availability scenario?

  • Mirroring. Most importantly Mirroring isn’t supported (so no filestream filegroup will be allowed on the principal or mirror), so either you continue to use Varbinary(max) in the same way as in SQL Server 2005 for your unstructured files, or you go for…
  • Clustering. This is supported, but the filestream filegroup must be on a shared disk (so maybe you have to rely on your storage to give you high availability here). I would add that 2 node clusters are allowed in standard edition.
  • Log shipping  is also supported, but both ends must be running SQL Server 2008 with Filestream turned on.

SQL Server 2008 interoperability with other new features..

What about backup compression? Backup compression doesn’t work with filestream.  generally the files will already be compressed (think jpeg, divx etc.)

What about Transparent Data Encryption? TDE doesn’t apply to filestream and in any case is only in enterprise edition.  If you need to protect the files in a filestream filegroup use the Encrypted Files System (EFS) .btw filestream filegroups can only be created on NTFS volumes.

Can I uses Integrated Full Text Search (IFTS) with Filestream? Yes in exactly the same way as you can for normal varbinary (max) data in SQL Server 2005/8.  So you must have a unique identifier for each row and a separate column on each row to identify the type of data being stored (pdf, xlxs, docx, html and so on) for the ifilters to work.

Which editions of SQL Server does it work with? Filestream applies to SQL Server Express up to Enterprise edition. btw the filestream filegroup does not count as part of the 4Gb limit to the size of an Express database and these special filegroup can be as large as you like. 

For further reading go to TechNet’s Books on Line for SQL Server 2008

Technorati Tags: SQL Server 2008,filestream,high availability,full text search,backup,restore

Comments

  • Anonymous
    October 28, 2008
    PingBack from http://mstechnews.info/2008/10/sql-server-2008-filestream-faqs/

  • Anonymous
    November 28, 2008
    NOTE :このエントリーは、Andrew FryerさんのBlogに投稿された「 SQL Server 2008 Filestream FAQs 」をAndrewさんの許可を頂いて意訳しています。 私は

  • Anonymous
    November 29, 2009
    Excellent article - exactly what I wanted to know.  Thanks:-)

  • Anonymous
    April 15, 2010
    Hi , Looks like you are an expert in using FILESTREAM attrib of SQL Server 2008. Let me explain the issue that I am facing now. I have a number of video files stored in the server machine as FILESTREAM type. Now, I have a number of VC++ clients that want to stream the stored data into a video panel in the screen. We initially used DirectShow. As you know, DiretShow requires aan absoluate file name so that it can stream the contents to the output screen. But, OpenSqlInputStream does not give a file name, it provide only a handle to a logical file path. Even the Media Player Control could not help because it also requires a file name. The only solution before us is to write a utlity in server that converts the FILESTREAM objects into physical files and then pass the file names to DirectShow or Media Player control for streaming. Is there any better solution for this? Are there any custom filters available that can connect FILESTREAM to DirectShow? thanks in advance Vinodh  (vinodh.nair@ust-global.com)

  • Anonymous
    September 08, 2010
    The comment has been removed

  • Anonymous
    April 17, 2011
    What about backup compression? Backup compression doesn’t work with filestream.  generally the files will already be compressed (think jpeg, divx etc.) --> Actually, per the CSS engineers, backup compression does work with FILESTREAM. See blogs.msdn.com/.../how-it-works-file-stream-compression-with-backup-restore.aspx for their blog post.