Native File Streaming with SQL Server

In this dynamic session, Lenni will teach you the ins-and-outs of unstructured data storage and native file streaming with FILESTREAM (introduced in SQL Server 2008) and FileTable (added in SQL Server 2012). Learn how to program FILESTREAM using T-SQL and, for maximum performance, using the SqlFileStream class in C#. We’ll also cover the hierarchyid data type, which casts a hierarchical structure over every FileTable, and exposes a functional Windows file system accessible to users and applications that is stored entirely in the database.

Transcript

1. Native File Streaming with SQL Server Leonard Lobel
2. About Me  Leonard Lobel  CTO & Co-Founder  Sleek Technologies, Inc.  Principal Consultant  Tallan, Inc.  Microsoft MVP  SQL Server  .NET consultant and trainer  Speaker  Author  Programming since 1979  Contact  Email: lenni.lobel@sleektech.com  Blog: lennilobel.wordpress.com  Twitter: @lennilobel sleek technologies
3. Agenda  FILESTREAM  FileTable
4. Database CustomerId LastName Picture varbinary(max) 'Doe' 0x3B0E95AE3B292F0B… 236 'Sally' 'Smith' 0xF3000EEF2932002C… BLOBs in the Database Data file group 'John' 235 FirstName
5. CustomerId FirstName LastName Picture varchar(max) 235 'John' 'Doe' 'C:\files\doe.jpg' 236 'Sally' 'Smith' 'C:\files\smith.jpg' File System Files BLOBs Outside the Database Database Data file group SQL Server Client app
6. Database Data file group File System Files CustomerId FirstName LastName Picture varbinary(max) FILESTREAM 235 'John' 'Doe' 0x3B0E95AE3B2F020B… 236 'Sally' 'Smith' 0xF3000EEF293039A2… BLOBs Using FILESTREAM (T-SQL)
7. CustomerId FirstName LastName Picture varbinary(max) FILESTREAM 235 'John' 'Doe' 0x3B0E95AE3B2F020B… 236 'Sally' 'Smith' 0xF3000EEF293039A2… BLOBs Using SqlFileStream Database Data file group File System Files
8. Demo  Getting Started with FILESTREAM
9.  Start a database transaction  INSERT new row  Store a zero-length binary value for the BLOB column(s)  Retrieve BLOB path name and transaction context  Call PathName() function on varbinary(max) FILESTREAM column  Call GET_FILESTREAM_TRANSACTION_CONTEXT  Obtain values using an OUTPUT clause in the INSERT statement  Instantiate SqlFileStream object  Pass BLOB path name and transaction context to constructor Storing BLOBs with SqlFileStream Database transaction 12 'John' 'Doe' 0x NTFS transaction Empty file Path Name Txn Context SqlFileStream
10.  Start a database transaction  INSERT new row  Store a zero-length binary value for the BLOB column(s)  Retrieve BLOB path name and transaction context  Call PathName() function on varbinary(max) FILESTREAM column  Call GET_FILESTREAM_TRANSACTION_CONTEXT  Obtain values using an OUTPUT clause in the INSERT statement  Instantiate SqlFileStream object  Pass BLOB path name and transaction context to constructor  Write to the stream  Then close it Storing BLOBs with SqlFileStream Database transaction 12 NTFS transaction SqlFileStream 'John' 'Doe' 0x
11.  Start a database transaction  INSERT new row  Store a zero-length binary value for the BLOB column(s)  Retrieve BLOB path name and transaction context  Call PathName() function on varbinary(max) FILESTREAM column  Call GET_FILESTREAM_TRANSACTION_CONTEXT  Obtain values using an OUTPUT clause in the INSERT statement  Instantiate SqlFileStream object  Pass BLOB path name and transaction context to constructor  Write to the stream  Then close it  Commit the database transaction  Automatically commits the NTFS file system transaction Storing BLOBs with SqlFileStream Database transaction 12 NTFS transaction SqlFileStream 'John' 'Doe' 0xFE3BC0...
12.  Start a database transaction  SELECT existing row  Don’t include the BLOB column(s)  Retrieve BLOB path name and transaction context  Call PathName() function on varbinary(max) FILESTREAM column  Call GET_FILESTREAM_TRANSACTION_CONTEXT  Instantiate SqlFileStream object  Pass BLOB path name and transaction context to constructor  Read from the stream  Then close it  Commit the database transaction Retrieving BLOBs with SqlFileStream Database transaction 12 SqlFileStream Path Name Txn Context 'John' 'Doe' 0xFE3BC0...
13. Demo  Programming with SqlFileStream
14. Thick Client SqlFileStream Application Local Network Client App SQL Server Data access Console, Windows Forms, WPF, Windows Service
15. Client/Server SqlFileStream Application Remote Network Local Network Client App SQL Server Server App Data access Web Browser, Windows 8/10 App, Mobile ASP.NET Web Server, Web API Controller
16. Middle Tier N-Tier SqlFileStream Application Back End Presentation Layer SQL Server Client App WCF Service Server App Data access
17. FileTable Schema Column Name Data Type Description stream_id uniqueidentifier ROWGUIDCOL Unique row identifier file_stream varbinary(max) FILESTREAM BLOB content (NULL if directory) name nvarchar(255) Name of file or directory path_locator hierarchyid Location of file or directory within the file system hierarchy creation_time datetimeoffset(7) Created last_write_time datetimeoffset(7) Last modified last_access_time datetimeoffset(7) Last accessed is_directory bit 0 = file, 1 = directory is_offline bit Storage attributes is_hidden bit is_readonly bit is_archive bit is_system bit is_temporary bit
18. Database Files Data file group stream_id name path_locator file_stream 27D8D4AD-D100-39… 'Financials' 0xFF271A3562… NULL 78F603CC-0460-73… 'ReadMe.docx' 0xFF59345688… 0x3B0E956636AE3B2F020B… 207D4A96-E854-01… 'Budget.xlsx' 0xFD0011039A… 0xF3F359000EEF293039A2… is_directory 1 0 0 … … … … FileTable Access
19. FileTable Access via T-SQL stream_id name path_locator file_stream 27D8D4AD-D100-39… 'Financials' 0xFF271A3562… NULL 78F603CC-0460-73… 'ReadMe.docx' 0xFF59345688… 0x3B0E956636AE3B2F020B… 207D4A96-E854-01… 'Budget.xlsx' 0xFD0011039A… 0xF3F359000EEF293039A2… is_directory 1 0 0 … … … … Database Files Data file group
20. FileTable Access via SqlFileStream stream_id name path_locator file_stream 27D8D4AD-D100-39… 'Financials' 0xFF271A3562… NULL 78F603CC-0460-73… 'ReadMe.docx' 0xFF59345688… 0x3B0E956636AE3B2F020B… 207D4A96-E854-01… 'Budget.xlsx' 0xFD0011039A… 0xF3F359000EEF293039A2… is_directory 1 0 0 … … … … Database Files Data file group
21. stream_id name path_locator file_stream 27D8D4AD-D100-39… 'Financials' 0xFF271A3562… NULL 78F603CC-0460-73… 'ReadMe.docx' 0xFF59345688… 0x3B0E956636AE3B2F020B… 207D4A96-E854-01… 'Budget.xlsx' 0xFD0011039A… 0xF3F359000EEF293039A2… is_directory 1 0 0 … … … … hierarchyid varbinary(max) FILESTREAM FileTable Access via Windows File System Database Files Data file group
22. stream_id name path_locator file_stream 27D8D4AD-D100-39… 'Financials' 0xFF271A3562… NULL 78F603CC-0460-73… 'ReadMe.docx' 0xFF59345688… 0x3B0E956636AE3B2F020B… 207D4A96-E854-01… 'Budget.xlsx' 0xFD0011039A… 0xF3F359000EEF293039A2… is_directory 1 0 0 … … … … Database Files Data file group
23. stream_id name path_locator file_stream 27D8D4AD-D100-39… 'Financials' 0xFF271A3562… NULL 78F603CC-0460-73… 'ReadMe.docx' 0xFF59345688… 0x3B0E956636AE3B2F020B… 207D4A96-E854-01… 'Budget.xlsx' 0xFD0011039A… 0xF3F359000EEF293039A2… is_directory 1 0 0 … … … … Server machine name Database Files Data file group
24. stream_id name path_locator file_stream 27D8D4AD-D100-39… 'Financials' 0xFF271A3562… NULL 78F603CC-0460-73… 'ReadMe.docx' 0xFF59345688… 0x3B0E956636AE3B2F020B… 207D4A96-E854-01… 'Budget.xlsx' 0xFD0011039A… 0xF3F359000EEF293039A2… is_directory 1 0 0 … … … … Server instance FILESTREAM share name Database Files Data file group
25. stream_id name path_locator file_stream 27D8D4AD-D100-39… 'Financials' 0xFF271A3562… NULL 78F603CC-0460-73… 'ReadMe.docx' 0xFF59345688… 0x3B0E956636AE3B2F020B… 207D4A96-E854-01… 'Budget.xlsx' 0xFD0011039A… 0xF3F359000EEF293039A2… is_directory 1 0 0 … … … … Database name Database Files Data file group
26. stream_id name path_locator file_stream 27D8D4AD-D100-39… 'Financials' 0xFF271A3562… NULL 78F603CC-0460-73… 'ReadMe.docx' 0xFF59345688… 0x3B0E956636AE3B2F020B… 207D4A96-E854-01… 'Budget.xlsx' 0xFD0011039A… 0xF3F359000EEF293039A2… is_directory 1 0 0 … … … … FileTable name Database Files Data file group
27. stream_id name path_locator file_stream 27D8D4AD-D100-39… 'Financials' 0xFF271A3562… NULL 78F603CC-0460-73… 'ReadMe.docx' 0xFF59345688… 0x3B0E956636AE3B2F020B… 207D4A96-E854-01… 'Budget.xlsx' 0xFD0011039A… 0xF3F359000EEF293039A2… is_directory 1 0 0 … … … … FileTable rows Database Files Data file group
28. Demo  FileTable
29. Questions?
30. Thank You!  Contact me  lenni.lobel@sleektech.com  Visit my blog  lennilobel.wordpress.com  Follow me on Twitter  @lennilobel  Thanks for coming! 