Scribd Logo
Upload

Welcome to Scribd!

  • 87 views

    FileTable Slides

    Uploaded by

    Mudit Mishra
    The document introduces FileTables in SQL Server 2012, which allow storing files in the file system while retaining benefits of database storage like backups and security. FileTables use FILESTREAM to store file contents on disk while metadata is stored in SQL tables. This provides better performance than BLOB storage and easier access through Windows Explorer. FileTables also support full text search against file contents. Some limitations are the same disadvantages as FILESTREAM like inability to use features like mirroring or encryption. The document concludes with links to resources for learning more about FileTables.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    FileTable Slides

    Uploaded by

    Mudit Mishra
    87 views21 pages
    The document introduces FileTables in SQL Server 2012, which allow storing files in the file system while retaining benefits of database storage like backups and security. FileTables use FILESTREAM to store file contents on disk while metadata is stored in SQL tables. This provides better performance than BLOB storage and easier access through Windows Explorer. FileTables also support full text search against file contents. Some limitations are the same disadvantages as FILESTREAM like inability to use features like mirroring or encryption. The document concludes with links to resources for learning more about FileTables.

    Original Description:

    FileTable Slides.pptx

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document introduces FileTables in SQL Server 2012, which allow storing files in the file system while retaining benefits of database storage like backups and security. FileTables use FILESTREAM to store file contents on disk while metadata is stored in SQL tables. This provides better performance than BLOB storage and easier access through Windows Explorer. FileTables also support full text search against file contents. Some limitations are the same disadvantages as FILESTREAM like inability to use features like mirroring or encryption. The document concludes with links to resources for learning more about FileTables.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    87 views21 pages

    FileTable Slides

    Uploaded by

    Mudit Mishra
    The document introduces FileTables in SQL Server 2012, which allow storing files in the file system while retaining benefits of database storage like backups and security. FileTables use FILESTREAM to store file contents on disk while metadata is stored in SQL tables. This provides better performance than BLOB storage and easier access through Windows Explorer. FileTables also support full text search against file contents. Some limitations are the same disadvantages as FILESTREAM like inability to use features like mirroring or encryption. The document concludes with links to resources for learning more about FileTables.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 21

    Email: jpertell@gmail.

    com
    Twitter: @jayape
    Blog: http://www.pertell.com/sqlservings
    Introduction to FileTables in SQL Server
    John Pertell
    Database Administrator for Dialysis Clinics Inc
    Ill be talking about
    What are FileTables

    Some background stuff (Filestreaming)

    Demos

    SQL 2012 introduces FileTables

    Basically, just a table to store files

    Uses FILESTREAMING to store files on OS

    Access the FILESTREAM files like any other file

    Improved performance

    First the boring stuff
    Theres more and more data being created every day
    Structured (think tabular)
    Unstructured (documents, images, etc)

    Theres more and more need to store unstructured with structured

    So, where do we store the unstructured data?
    Inside the database

    BLOB (Binary Large Object)
    Stored as varbinary or varchar MAX (text or image before 2005)

    Outside the database

    In the file system or cloud
    Path is stored in database
    Database Advantages

    Backups
    Security
    Transactional Consistency

    File System Advantages

    Performance
    SQL 2008 and Later:

    New Option: FILESTREAMING

    Integrates SQL with NTFS file system

    Stores LOBs as varbinary(max) as files in special
    directory

    Files accessed through API or .NET Objects
    Youve got your database in my file system!
    No, youve got your file system in my database!
    FILESTREAM Advantages

    Better streaming performance

    Faster client access through API or .NET Objects

    Transactionally consistent

    Easier backups

    Improved security
    FILESTREAMING Disadvantages

    Cant use in mirroring

    Cant use in database snapshots

    Cant use Transparent Data Encryption (TDE)

    Possibly huge database backups (though you can use filegroup
    backups and compression)

    FILESTREAMING Prerequisites

    Enable FILESTREAMING on instance

    Databases require FILESTREAM file group (on server)

    OS permissions to create a shared location

    Table needs varbinary (MAX) data type column with
    FILESTREAM attribute and uniqueidentifier column with
    ROWGUIDCOL attribute

    Needs SQL Integrated Security for client access
    Storing BLOB Recommendations

    < 256 KB: Use BLOB storage

    > 256 KB AND < 1 MB: Test based on your usage patterns

    > 1 MB: Use FILESTREAMING

    Dont use FILESTREAM for files that are constantly updated

    SQL 2012 introduces FileTables

    Basically, a table to store files

    Uses FILESTREAMING to store in OS

    Created with a standard schema, no need to declare columns

    Stores hierarchically think folders

    Doesnt require Enterprise Edition!
    Using FileTables

    Files accessible through Windows Explorer or applications
    like Word or Notepad

    You can use full text searches against a FileTable

    New Search Property List enhancement to Full Text Search

    New Semantic Search to find similar documents.

    FileTables drawbacks

    Same disadvantages as with FILESTREAMING

    Cant set security on folders to block access.

    Cant alter database objects created at runtime
    (You can rename or add indexes and constraints)

    Cant create in tempdb

    Cant convert existing table to FileTable
    And now on to the demos
    Downloads for FileTables



    Download Semantics database from Microsoft
    http://www.microsoft.com/en-us/download/details.aspx?id=29069

    Download Office Filter Pack from Microsoft
    http://www.microsoft.com/en-us/download/details.aspx?id=17062

    Download Office Filter Pack SP1 from Microsoft
    http://www.microsoft.com/en-us/download/details.aspx?id=26604

    Download PDF iFilter from Adobe
    http://www.adobe.com/support/downloads/thankyou.jsp?ftpID=4025&fileID=3941




    Resources for FileTables
    SQL Server 2012 Books Online - FileTables
    http://msdn.microsoft.com/en-us/library/ff929144.aspx

    SQL Server 2012 Books Online Search Property Lists
    http://msdn.microsoft.com/en-us/library/ee677637(v=sql.120).aspx

    TechNet Library Semantic Search
    http://technet.microsoft.com/en-us/library/gg492075.aspx

    Sven Aelterman on BeyondRelational
    http://beyondrelational.com/modules/24/syndicated/415/sven-aeltermans-
    blog.aspx?tab=Posts&tn=Tags&tv=FileTable

    The Art of SQL Server FILESTREAM by Jacob Sebastian and Sven Aelterman (PDF)
    https://www.simple-talk.com/books/sql-books/the-art-of-sql-server-filestream-by-jacob-sebastian-and-
    sven-aelterman/


    More Resources for
    FileTables
    Bob Beauchemin on SQLSkills
    http://www.sqlskills.com/blogs/bobb/category/filetable/

    My blog posts on FileTables
    http://www.pertell.com/sqlservings/archive/category/sql/sql-2012/filetables/

    Into the Wild...Taming Unstructured Data with Semantic Search
    http://pragmaticworks.com/LearningCenter/FreeTrainingWebinars/PastWebinars.aspx?ResourceId=
    549

    You might also like