Voice of the DBA

Tag: Filetable

  • SQL Saturday #132 Files

    Uploaded here if you need them. These are the PPT deck and the code.

    UnstructuredData.zip

    EncryptionPrimer.zip

  • Creating a Filetable

    How do you create a filetable? I assume you’ve enabled Filestream and created a filegroup for your filestream and filetable data. Then you just do this:

    -- Create a filetable
CREATE TABLE AuthorDrafts 
  AS FileTable
GO

    The only optional part of this statement is the table name. No other options, no columns, no schema needed. The FileTable has a fixed schema, which is mostly metadata about the files that you put in it.

    If I were to select from this table, I’d use this statement. I’m not showing all the columns in the results since there are a lot, but they are in the select.

    -- check the table.
select 
   stream_id ,
          file_stream ,
          name ,
          path_locator ,
          parent_path_locator ,
          file_type ,
          cached_file_size ,
          creation_time ,
          last_write_time ,
          last_access_time ,
          is_directory ,
          is_offline ,
          is_hidden ,
          is_readonly ,
          is_archive ,
          is_system ,
          is_temporary
 from AuthorDrafts;
go

    Most of these are really meta data about the file. If I were to drop a table in the share, I’d see results like this:

    filetable1

    Putting files inside the table is really a drag and drop from Windows. I can get the share name for my filetable from :

    -- check the share
select  FileTableRootPath('dbo.AuthorDrafts');
go

    If I paste this in Explorer, I see my file:

    filetable2

    I can drag and drop, or use any scripting commands (Powershell, VBScript, etc) to move files in and out of this share, and they will appear in my table.

    It’s that easy to start working with FileTables. How you use them in your application? That’s a whole other series of posts. I’ll work on a few examples you can use over time.

  • Create a Filestream Filegroup for Filetables – SQL Server 2012

    Once you’ve enabled filestream, the next step is to add a filegroup to your database to hold the filestream data. This is pretty easy to do, and I’ll show you the SSMS and code versions.

    If you want to know more about these Filestream containers, you can read BOL. Let’s create a simple database:

    -- create a new database
create database UnstructuredData
go

    This is a simple database with my instance defaults in place. It has a single mdf, a single ldf, and the default Primary filegroup. Let’s not add a new filegroup:

    -- add a filestream FG
ALTER DATABASE [UnstructuredData]
  ADD FILEGROUP [FS] CONTAINS FILESTREAM 
GO

    Here I am adding the filegroup (empty) and specifying this as a filestream container. You cannot mix Filestream data and non-Filestream data in the same filegroup in SQL Server 2012.

    To add a file, we can use the ALTER DATABASE command:

    -- Add a file to the Filestream FG
ALTER DATABASE [UnstructuredData] 
  ADD FILE ( NAME = N'UnstructuredFS', 
             FILENAME = N'c:\fs\UnstructuredFS' ) 
     TO FILEGROUP [FS]
go

    Here I am adding a file, which is actually a folder in this case. According to the documentation, the path up to the last folder (c:\fs in this case) must exist, but the last folder (UnstructuredFS) must not.

    You could do all of this in one statement, as shown here:

    CREATE DATABASE [UnstructuredData]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'UnstructuredData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UnstructuredData.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FS] CONTAINS FILESTREAM  DEFAULT 
( NAME = N'UnstructuredFS', FILENAME = N'c:\fs\UnstructuredFS' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'UnstructuredData_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UnstructuredData_log.ldf' , SIZE = 784KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

    This gets you a space for holding your Filestream data. In 2012, you can now have more than one file for Filestream data, so you can separate out your filegroup across different physical locations if you have a need to do so for performance or scalability.

    In the next post, I’ll build a FileTable and store some documents in it.

  • Unstructured Data in SQL Server

    Abstract:

    More and more of our data does not fit neatly into a structured, relational model of rows and columns of data. In this session, you will learn about how SQL Server stores unstructured data, with a special emphasis on how to use Filestream, which integrates SQL Server with the NTFS file system by storing varbinary(max) binary large object data as files stored within the file system. You will also learn about the new SQL Server 2012 filetable feature, which builds on Filestream and provides the ability to read, write, and update Filestream objects directly through the file system. This session is designed for DBAs and developers who need to learn how to manage large quantities of unstructured data.

    This covers SQL Server 2008, R2, and 2012. The basic Agenda:

    • What is unstructured data
    • Filestream
    • FileTable

    There are demos that look at how Filestream works and how FileTable can be used in SQL Server 2012.

    Level: 200

    Length: 60 Minutes

    Demo code: UnstructuredData.zip

    Slides: UnstructuredData.ppt

    Related Posts:

    Presentations:

    You can view my speaking schedule here: http://wp.me/P14wgJ-1tV