You are previewing The Art of SQL Server FILESTREAM.
O'Reilly logo
The Art of SQL Server FILESTREAM

Book Description

Storing large object data in the database offers a number of benefits, but introduces performance challenges. Conversely, storing large object data in the file system has overriding performance advantages, but fails to offer some of the basic data integrity, security and manageability features that are required for business data, and which SQL Server provides. Up to now, most people have adopted file system storage by necessity, and often struggled to overcome the associated shortcomings. This is exactly where SQL Server's new FILESTREAM feature fits in. We hope this book will get you started, quickly, with FILESTREAM, and then help you master all essential aspects of programming and administering FILESTREAM-enabled databases.

Table of Contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Table of Contents
  5. About the Authors
  6. Acknowledgements
  7. Introduction
  8. Chapter 1: Storing and Managing Unstructured Data
    1. Structured Data
    2. Unstructured Data
      1. CLOB data
      2. BLOB Data
    3. Storing Unstructured Data
      1. Storing Large Objects in SQL Server
      2. Storing Large Objects in the File System
      3. Database or File System?
    4. Enter FILESTREAM
      1. A First Look at FILESTREAM
      2. When to Use FILESTREAM
    5. Summary
  9. Chapter 2: Getting Started with FILESTREAM
    1. The FILESTREAM Data Container
    2. Understanding the FILESTREAM Filegroup
    3. Creating FILESTREAM-Enabled Databases
      1. Creating a New FILESTREAM-Enabled Database
      2. Enabling an Existing Database for FILESTREAM
    4. Creating a Table with FILESTREAM Columns
      1. Using SSMS Table Designer to Create a Table with FILESTREAM Columns
      2. Using T-SQL to Create a Table with FILESTREAM Columns
      3. Using T-SQL to add FILESTREAM Columns to an Existing Table
      4. Converting VARBINARY(MAX) Columns to FILESTREAM and Vice Versa
      5. Tables and FILESTREAM Filegroups
      6. FILESTREAM Filegroup Queries
    5. FILESTREAM Data Manipulation Using T-SQL
      1. Inserting a Row with FILESTREAM Data
      2. Updating and Deleting FILESTREAM Data
      3. FILESTREAM and Triggers
    6. Advanced FILESTREAM DDL
      1. The FILESTREAM Data Container and Partitioned Tables
      2. Creating a Database with Multiple FILESTREAM Filegroups
    7. Disabling FILESTREAM Storage on a Database
    8. Summary
  10. Chapter 3: Accessing FILESTREAM Data from Client Applications
    1. What is Streaming?
    2. Understanding Streaming Access to FILESTREAM Data
      1. Step 1: Starting a Transaction
      2. Step 2: Retrieving the Logical Path Name and Transaction Context
      3. Step 3: Opening the FILESTREAM Data File
      4. Step 4: Reading and Writing FILESTREAM Data
      5. Step 5: Closing the FILESTREAM Data File
      6. Step 6: Closing the Transaction (COMMIT/ROLLBACK)
    3. Data Manipulation Using the Streaming APIs
      1. Inserting a New Record Into a FILESTREAM-Enabled Table
      2. Replacing the FILESTREAM Data Completely
      3. Partial Updates to FILESTREAM Data
      4. Reading Information from the FILESTREAM Store
      5. Deleting the BLOB Data Stored in a FILESTREAM Column
      6. Deleting a Row from a FILESTREAM-Enabled Table
    4. Lab 1: Reads, Writes and Partial Updates
      1. Handling Multiple FILESTREAM Columns and Rows
    5. Understanding the Logical Path to a FILESTREAM Data File
      1. Formatting PathName()
      2. PathName() and ROWGUIDCOL
    6. SqlFileStream Class Reference
      1. Instantiating a SqlFileStream Object
    7. OpenSqlFilestream API Reference
    8. Summary
  11. Chapter 4: FILESTREAM with Entity Framework and LINQ to SQL
    1. Lab 2: FILESTREAM and Entity Framework
      1. Rebuilding the Sample Database
      2. Lab 2a: A Simple EF Application Using T-SQL Access
      3. Lab 2b: Using SqlFileStream and Entity Framework Together
    2. Lab 3: FILESTREAM and LINQ to SQL
      1. Creating a New Console Application Project
      2. Adding a LINQ to SQL Class
      3. Defining Object Relational Mappings
      4. Adding a New Row
      5. Querying FILESTREAM Data Using LINQ to SQL
    3. Summary
  12. Chapter 5: FILESTREAM with ASP.NET and Silverlight
    1. Lab 4: Uploading Files to a FILESTREAM Database from an ASP.NET Web Page
      1. Creating a New ASP.NET Web Application
      2. Adding a Database Connection String
      3. Designing the Data Entry Page
      4. Writing the “Code-Behind”
      5. Running the Application
    2. Lab 5: Deploying and Configuring a FILESTREAM Web Application on IIS
      1. Running Visual Studio under an Administrator Account
      2. Publishing the Web Application into a New Virtual Directory
      3. Configuring IIS to Use SQL Server Integrated Security
      4. Running the Application
    3. Lab 6: Creating a Web Handler to Serve Images from a FILESTREAM Database
      1. Creating a Stored Procedure to Retrieve FILESTREAM Data
      2. Creating the Web Page to Serve Images
      3. Running and Testing the Application
    4. Lab 7: Displaying Thumbnail Images of Items from a FILESTREAM Database on an ASP.NET Web Page
      1. Creating the Stored Procedure to Fetch Item Information
      2. Adding a New Web form and Data Grid
      3. Adding a Data Source
    5. Lab 8: Using SqlFileStream in an N-Tier Scenario
      1. Creating the Visual Studio Solution and Projects
      2. Writing the Domain Object Code
      3. Writing the Data Access Code
      4. Creating an HTTP Handler to Serve Images
      5. Adding Remote Calling Capability
      6. Testing the Application from Visual Studio
    6. Lab 9: Playing a Video Stored in a FILESTREAM Database on a Silverlight Application
      1. Setting up the Data
      2. Creating a Silverlight Application
      3. Creating a Video Handler
      4. Adding and Configuring MediaElement
      5. Running and Testing the Application
    7. Summary
  13. Chapter 6: FILESTREAM with SSIS and SSRS
    1. FILESTREAM and SSIS
    2. Lab 10: Loading BLOB Values into a FILESTREAM Column Using SSIS
      1. Setting up the Sample Database
      2. Creating the SSIS Package
    3. Lab 11: Exporting BLOB Values from a FILESTREAM Column Using SSIS
      1. Creating the SSIS Package
      2. Setting the Output Folder
      3. The Execute SQL Task
      4. The Foreach Loop Container
      5. The Script Task
    4. Lab 12: Displaying FILESTREAM Data in SSRS Reports
    5. Summary
  14. Chapter 7: FILESTREAM Database Administration
    1. FILESTREAM and Database Transaction Isolation Levels
      1. Read Committed, Repeatable Read and Serializable Isolation Levels
      2. Read Uncommitted Isolation Level
      3. Snapshot Isolation Level
      4. Summary Of FILESTREAM Behavior under Transaction Isolation levels
    2. Detaching and Attaching FILESTREAM Databases
    3. FILESTREAM and Garbage Files
      1. Garbage Files and Recovery Models
      2. FILESTREAM Garbage Collection and Tombstone Tables
    4. FILESTREAM Data Corruption and DBCC Checks
      1. Corruption Caused by Missing FILESTREAM Data Files
      2. Corruption Caused by Orphaned Files
      3. Corruption Caused by Deleting the Garbage Files Manually
    5. Querying FILESTREAM Databases
    6. FILESTREAM Data and Space Management
      1. Changing the FILESTREAM Filegroup Location
      2. Adding a New Partition to Share the FILESTREAM Storage Load
    7. Migrating FILESTREAM Data
      1. SSMS Scripting
      2. The Database Publishing Wizard
    8. Summary
  15. Chapter 8: Backup and Restore for FILESTREAM Databases
    1. Creating and Populating the Sample Database
    2. Backing up FILESTREAM Databases
      1. Full Backups
      2. Differential Backups
      3. Transaction Log Backups
    3. Restoring FILESTREAM Databases
      1. Restoring from a Full Backup
      2. Point-in-Time Restore
    4. File Backups and Piecemeal Restores
    5. Data and Backup Compression for FILESTREAM Databases
    6. Beware of Garbage Files in FILESTREAM Backups
    7. Summary
  16. Chapter 9: Investigating FILESTREAM Databases
    1. Relevant System Catalog Views
      1. Sys.All_Columns, Sys.Columns and Sys.System_Columns
      2. Sys.Computed_Columns
      3. Sys.Identity_Columns
      4. Sys.Dm_Repl_Traninfo
      5. Sys.Tables
      6. Sys.Internal_Tables
      7. Sys.Partitions
      8. Sys.Data_Spaces
      9. Sys.Database_Files
      10. Sys.Filegroups
      11. Sys.System_Internals_Partition_Columns
      12. Sys.System_Internals_Partitions
      13. Sys.Filestream_Tombstone_*
    2. Useful FILESTREAM Metadata Queries
      1. SQL Server Instance-Level Queries
      2. Database-Level Queries
    3. FILESTREAM-Related DMVs
      1. Sys.Dm_Filestream_File_Io_Handles
      2. Sys.Dm_Filestream_File_Io_Requests
      3. Sys.Dm_Tran_Active_Transactions
    4. FILESTREAM and Wait Types
      1. Fs_Fc_Rwlock
      2. Fs_Garbage_Collector_Shutdown
      3. Fs_Header_Rwlock
      4. Fs_Logtrunc_Rwlock
      5. Fsa_Force_Own_Xact
      6. FSAGENT
      7. Fstr_Config_Mutex
      8. Fstr_Config_Rwlock
    5. Summary
  17. Chapter 10: Integrating FILESTREAM with Other SQL Server Features
    1. FILESTREAM and Replication
      1. Replicating FILESTREAM Columns
      2. Maximum Replication Data Size
      3. FILESTREAM Replication and the UNIQUEIDENTIFIER Column
      4. Managing the FILESTREAM Replication Flag Using T-SQL
      5. Replication Log Reader and the FILESTREAM Garbage Collector
      6. FILESTREAM and Synchronization Methods
      7. Replicating Databases with Multiple FILESTREAM Filegroups
      8. FILESTREAM and the Different Replication Types
    2. FILESTREAM and Log Shipping
    3. FILESTREAM and Full-Text Indexing
    4. FILESTREAM and Database Snapshots
    5. FILESTREAM and Change Data Capture
    6. FILESTREAM and Data Compression
    7. FILESTREAM and Transparent Data Encryption
    8. FILESTREAM and Database Mirroring
    9. FILESTREAM and High Availability Disaster Recovery
    10. Summary
  18. Chapter 11: FileTable
    1. Introduction to FileTable
    2. Filetable Concepts
      1. Fixed schema
      2. Creating Folder Structures Using the HIERARCHYID Data Type
      3. Root Folder
      4. Non-Transactional Access
      5. Filetable Namespace
      6. Filetable Security
    3. Setting Up the Server For Filetable
    4. Creating a Database with Support for Filetable
    5. Creating a Filetable
      1. Create a Filetable Using T-SQL
      2. Create a Filetable Using SSMS
      3. Work with a Filetable Using SSMS
      4. Adding Constraints to the Filetable
      5. Restrictions on Creating Filetables
    6. Accessing a FileTable with Windows Explorer and Other Client Applications
      1. Creating Files
      2. Creating Folders
      3. Memory-Mapped Files
      4. Empty Database Folder
    7. Programming Filetable
      1. Adding Rows Using T-SQL
      2. Using .NET File I/O APIs
    8. Managing Filetables
      1. T-SQL Functions
      2. Catalog and Dynamic Management Views
      3. Stored Procedures
    9. Advanced Filetable Uses
      1. Full-Text Search
      2. Semantic Search
    10. Investigating Filetable
    11. Summary
  19. Chapter 12: Planning, Configuration and Best Practices
    1. Planning
    2. Optimizing your Storage Configuration for FILESTREAM
      1. Keep FILESTREAM data Containers on a Separate Disk Volume
      2. Disabling Short File Names
      3. Compressing FILESTREAM Data
      4. Configuring NTFS Cluster Size
      5. Disabling the Indexing Service
      6. Configuring Antivirus
      7. Disabling the Last Access Time Attribute
      8. Configuring the Disks with the Correct RAID Level
      9. Regular Disk Defragmentation
    3. Setting up FILESTREAM for Remote Access
      1. Configuring the Client Computer for Remote Access
      2. Configuring the Client Application for Remote Access
    4. Best Practices for FILESTREAM Development
      1. Use FILESTREAM Streaming APIs to Read and Write FILESTREAM Data
      2. Avoid Small Partial Updates
      3. Keep an Additional Column to Store the Type of File or Extension
      4. Identifying the Type of File from a File Header
      5. Add a Content-Type Column on FILESTREAM Tables
      6. Add a Timestamp or Date Column to Track Last Modified Date for Cache Control
      7. Use a Computed Column to Retrieve the File Size
      8. Keep a Default Constraint on FILESTREAM Columns
    5. Summary
  20. Appendix A: Configuring FILESTREAM on a SQL Server Instance
    1. Understanding FILESTREAM Configuration and Access Levels
      1. FILESTREAM Access Levels
    2. Enabling and Configuring FILESTREAM During Installation
      1. Enable FILESTREAM for Transact-SQL access
      2. Enable FILESTREAM for file I/O Streaming Access
      3. Allow Remote Clients to have Streaming Access to FILESTREAM Data
    3. Enabling and Configuring FILESTREAM after Installation
      1. Configuring FILESTREAM at the Windows Level
      2. Configuring FILESTREAM at the SQL Server Instance Level
    4. Verifying FILESTREAM Configuration
      1. Verifying Windows-Level FILESTREAM Configuration Settings
      2. Verifying SQL Server Instance-Level FILESTREAM Configuration Settings
      3. Using SSMS to Check that FILESTREAM is Enabled
      4. Using T-SQL to Check that FILESTREAM is Enabled
    5. Disabling the FILESTREAM Feature
    6. Advanced Installation and Configuration Options
      1. Unattended SQL Server Installation and FILESTREAM Configuration
      2. Enabling and Configuring FILESTREAM from the Command Line
      3. Configuring FILESTREAM Access Level from the Command Line
      4. Setting up FILESTREAM on a Failover Cluster
    7. Summary