1 / 10

Filestream Data Storage

Filestream Data Storage. Filestream storage. Storing large binary objects in databases is suboptimal Large objects take buffers in database memory Updating large objects cause database fragmentation In file system however, "update" is delete and insert

licia
Download Presentation

Filestream Data Storage

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Filestream Data Storage

  2. Filestream storage • Storing large binary objects in databases is suboptimal • Large objects take buffers in database memory • Updating large objects cause database fragmentation • In file system however, "update" is delete and insert • "Before image" in an update is not deleted immediately • Storing all related data in a database adds • Transactional consistency • Integrated, point-in-time backup and restore • Single storage and query vehicle

  3. Filestream Implementation • A filegroup for filestream storage is declared using DDL • Filestream storage is tied to a database • The filegroup is mapped to a directory • Must be NTFS file system • Caution: Files deleteable from file system if you have appropriate permissions • VARBINARY(MAX) columns can be defined with FILESTREAM attribute • Table must also have UNIQUEIDENTIFIER column • Filestream storage not available for other large types • Data is stored in the file system

  4. Programming with Filestreams • Filestream columns are available with SQL methods • If SQL is used, indistinguishable from varbinary(max) • Filestream can be accessed and modified using file IO • PathName function retrieves a symbolic path name • Acquire context with • GET_FILESTREAM_TRANSACTION_CONTEXT • Use OpenSqlFilestream to get a file handle based on • File Name • Required Access • Access Options • FilestreamTransaction context

  5. Filestream demo

  6. Filestream behaviors • Streaming programming model available using • System.Data.SqlTypes.SqlFileStream - .NET • OpenSqlFilestream – SQL Native Client • Also available using a PInvoke wrapper with .NET • Streaming supported with ReadCommitted isolation level • File is required for handle, so to insert • Insert a zero-length value • Retrieve path and transaction context • Write using streamed IO

  7. Programming with Filestream demo

  8. Review • SQL Server stores relational and non-relational data • Filestream storage stores BLOBs on file system • Buffer, fragmentation savings • Accessed using File IO • Best of both worlds: • Transactional consistency • Integrated, point-in-time backup and restore • Single storage and query vehicle • NTFS benefits • High-performance Win32 streaming

  9. Resources • Whitepaper: Managing Unstructured Data with SQL Server 2008 http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_unstructured.mspx

  10. © 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

More Related