Setting up FILESTREAM and FileTable in SQL Server

In my previous blog post I wrote about what FILESTREAM and FileTables are. In this post I explain how to set FILESTREAM and FileTables up.

To enable and change FILESTREAM settings

  1. On the Startmenu, select All Programs, select the SQL Server folder, select Configuration Tools, and then click SQL Server Configuration Manager.
  2. In the list of services, right-click SQL Server Services, and then click Open.
  3. In the SQL Server Configuration Managersnap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
  4. Right-click the instance, and then click Properties.
  5. In the SQL Server Propertiesdialog box, click the FILESTREAM
  6. Select the Enable FILESTREAM for Transact-SQL access check box.
  7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name
  8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
  9. Click Apply.
  10. In SQL Server Management Studio, click New Queryto display the Query Editor.
  11. In Query Editor, enter the following T-SQL code:
EXEC sp_configure filestream_access_level, 2

RECONFIGURE
  1. Click Execute.
  2. Restart the SQL Server service.

Next we need to add a File Group to the DB with FILESTREAM and then add a File into the File Group.

USE MASTER
GO

ALTER DATABASE ImageFilesDB
ADD FILEGROUP ImageFG1 CONTAINS FILESTREAM;
GO

ALTER DATABASE ImageFilesDB 
ADD FILE 
(
    NAME = ImageStorage,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ImageStorageFG1.ndf'
)
TO FILEGROUP ImageFG1;
GO

Once you have created the File Group and add a file you will need to set the FILESTREAM File Group as the default in the database properties. (Right-click the database, select Properties, select Filegroups in the left hand pane, check the default checkbox in the FILESTREAM section)
FileGroupProperties
Next you need to check whether non-transaction access is enabled on the database.

SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc
FROM sys.database_filestream_options;
GO

If it is not enabled on the database we need to enable it

ALTER DATABASE ImageFilesDB
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'ImageTable')
Once non-transactional access is enabled on the database we can create a FileTable

CREATE TABLE ImageStore AS FILETABLE 
    WITH ( 
          FileTable_Directory = 'ImageTable',
          FileTable_Collate_Filename = database_default
         );
GO

Now that everything is set up the last thing to do is to add files to our FileTable. There are two methods of doing this, either through T-SQL code or by exploring the FileTable from SQL Management Studio.

T-SQL method:

DECLARE @img AS VARBINARY(MAX)
SELECT @img = CAST(BulkColumn AS VARBINARY(MAX)) 
FROM OPENROWSET(BULK 'C:\temp\testimage.jpg', SINGLE_BLOB ) AS x
 
INSERT INTO dbo.ImageStore(name, file_stream)
SELECT 'testimage.jpg', @img
 
SELECT * FROM dbo.ImageStore

queryresults
Management Studio Method:

  1. Expand the FileTables node under Tables in your database
  2. Right-Click the FileTable you created
  3. Select Explore FileTable Directory

explorefiletable
A shared network location will open up where you can paste images/files into.
filesystemfilelocation

FileStream and FileTable in SQL Server

FileStream and FileTable are used to store unstructured data in the database. You can store BLOBs such as Word documents, PDFs and images.

FileStream
FileStream was introduced in SQL Server 2008 as a way to store BLOBS in a table on a relational database. FilesStream is not a datatype and is enabled as an attribute on a VARBINARY(MAX) column. FileStream allows us to manage our unstructured data and stores them as files on the file system.

The FileStream column is not cached in the SQL Server buffer cache and is cached in the Windows NT system cache. This means that the SQL Server buffer pool is not used by FileStream data, therefore the memory available for query processing is not reduced.

FileStream allows transaction consistency between the other data stored in the database. You are able to backup and restore the data stored in the file groups which contain FileStream data. The full-text search functionality on FileStream columns works the same way as it would on a VARBINARY(MAX) column.

When storing a file/image on a FileStream enabled column, only a pointer to the file is stored in the table and the actual file is stored in the Windows NTFS.

FileTable
FileTable was introduced in SQL Server 2012 and builds on top of existing FileStream capabilities. FileTable allow you to store directory hierarchies in a SQL Server database, and addresses the requirements for non-transactional access and Windows application compatibility for file-based data. FileTable is a specialized table with a fixed schema. FileTable can also be queried and updated through normal T-SQL commands.

For non-transactional file access for Windows based API applications the file and directory data in a FileTable is exposed through a Windows shared location. This looks like a normal network share location with its files and directories to the Windows application.

Compare FileStream and FileTable

Feature FileStream Solution FileTable Solution
Single story for management tasks Yes Yes
Single set of services: search, reporting, querying, etc Yes Yes
Integrated security model Yes Yes
In-place updates of FILESTREAM data No Yes
File and directory hierarchy maintained in the database No Yes
Windows application compatibility No Yes
Relational access to file attributes No Yes

Table reference: https://msdn.microsoft.com/en-US/library/hh403405.aspx

Pros for using FileTable:

  • Windows API compatibility for file data stored within a SQL Server database. This includes the following:
    • Non-transactional streaming access and in-place updates to FILESTREAM data.
    • A hierarchical namespace of directories and files.
    • Storage of file attributes, such as created date and modified date.
    • Support for Windows file and directory management APIs.
  • Integrated storage and data management capabilities such as backup.
  • Integrated services such as full-text search and semantic search over data and metadata.
  • Ease of administration and policy management over the unstructured data.
  • Ensures transactional consistency between the unstructured data stored in the NT file system and the structured data stored in the table
  • Allows SQL to take advantage of the NTFS I/O streaming capabilities
  • Buffer pool is not used therefore the memory available for query processing is not reduced

Cons for using FileTable:
FileTables do not support memory-mapped files such as NotePad and Paint. You are not able to use these applications on the same server as SQL Server to open the files stored in a FileTable, however, you can use these applications from a remote computer to open files that are stored in a FileTable.

NOTE:
In order to use FileStream or FileTable you will need to enable FileStream on the instance of SQL Server, then enable it on the database. I shall go into more details on setting up FileStream and FileTable in my next blog post.

Preparing for the MCSA and MCSE Exams

I recently took the microsoft exams to get my MCSA and MCSE in BI and spent quite a few hours preparing for the exams. I thought I would share how I prepped for the exams.

Microsoft has a Virtual Academy and it is FREE!

To sign up for the virtual academy, you can sign up with your Microsoft account. If you do not have a Microsoft account, you can create one and then use it to sign up.

On the virtual academy you can take online courses and add them to a learning plan. This allows you to track your progress as you progress through the courses.

You are able to see your ranking against other users in your country and in the world.

Each course has a video for each module, you can watch it online or download it in different quality formats. There is a slide presentation you can view online or download for each module.

Once you complete watching the video for each module there is a mini assessment that consists of 5 questions that you can answer to test your knowledge.

To make sure you add the correct course to your learning plan you should first go to the SQL certifications page on Microsoft learning. Select the number listed below “Required exam” (It’s a blue block)

This shall take you to the page where you can schedule the exam. Scroll down to the bottom of this page to the section that says “Preparation Options”

Expand the “Self-paced training” and click on the link. This link will redirect you to the Microsoft Virtual Academy (MVA) course.
examprep
You shall see a button that says Learning Plan, click on the button and select the check-box and click apply.
learningplan
Once it has been added to your Learning Plan, the course shall appear in your dashboard area. You can select the course and start your learning!

succeed

Running Visual Studio as a different user

When you are working as a consultant at clients and your machine is not on their network and you need to develop in Visual Studio with the credential they provide, it can be quite handy to run your Visual Studio with the Windows credentials they provide.

There are a few ways of doing this, in the post I shall list two different methods and how to achieve this.

Option 1:
Shift + Right-click on the Visual Studio icon and select “Run as a different user”

A pop up will come up for you to insert the Windows Credentials. Fill in the details and click ok, Visual Studio will then open up running as the user you entered.

NB: type the username as domain\username
runas

Option 2:
Step 1:
Open up Run

Step 2:
Type in the following:

runas /netonly /user:domain\username “C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe”

Step 3:
Set the domain and username for the Windows Account details you want to run Visual Studio as. Also make sure that the path specified is correct for your laptop (depends on where you installed Visual Studio)

Step 4:
A CMD prompt will popup asking you to type in the password. You need to type in the password for the Windows Account that you are impersonating.

NB! While you are typing the password in, it will not show that you are typing in anything.

SQL OUTPUT Clause

For my first post I thought I would share about the SQL OUTPUT clause which returns information from modified rows (INSERT, DELETE, UPDATE).

This can be quite useful for returning information to confirmation screens in an application or for auditing and archiving the modified rows.

Inserted and Deleted tables are two memory-resident tables that reside within SQL server and are used with the OUTPUT clause. Whenever any DML (INSERT, DELETE, UPDATE) statement is executed, these tables are populated.

The results of the INSERT statement are stored in the Insert table and the results of the DELETE statement are stored in the Delete table. An UPDATE statement is essentially delete and insert operations combined, therefore the deleted value is written to the Delete table and the new value is written to the Insert table.

On a side note, you cannot directly query Inserted and Deleted tables to see what data they are currently holding, but you can use them with the OUTPUT clause as well as with Triggers.

The below examples will show you how to use the OUTPUT clause for DML statements. This will just print the results on screen.

First let us create a table and insert some values:

IF OBJECT_ID('dbo.Music') IS NOT NULL
DROP TABLE dbo.Music
GO
CREATE TABLE dbo.Music
(
   ID			  INT IDENTITY(1,1) CONSTRAINT PK_Music_ID PRIMARY KEY
  ,SongName	VARCHAR(200) NOT NULL
  ,Artist		VARCHAR(50) NOT NULL
)
GO

INSERT INTO dbo.Music (SongName, Artist) 
VALUES	 ('Symphony No.9', 'Wolfgang Amadeus Mozart')
        ,('Don Giovanni','Wolfgang Amadeus Mozart')
        ,('Gabriel''s Oboe','Yo-yo Ma')
        ,('Inception: Dream is Collapsing','Hans Zimmer')
        ,('Cello Suits','Johann Sebastian Bach')
GO

SELECT * FROM dbo.Music
GO

Example: Using the OUTPUT clause with the INSERT statement

INSERT INTO dbo.Music (SongName, Artist)
OUTPUT INSERTED.ID, INSERTED.SongName, INSERTED.Artist
VALUES ('Swan Lake', 'Pyotr Ilyich Tchaikovsky');
GO

SELECT * FROM dbo.Music
GO

Example: Using the OUTPUT clause with the DELETE statement

DELETE FROM dbo.Music
OUTPUT DELETED.ID, DELETED.SongName, DELETED.Artist
WHERE ID=5;
GO

SELECT * FROM dbo.Music
GO

Example: Using the OUTPUT clause with the UPDATE statement

UPDATE dbo.Music
SET Artist = 'Ludwig van Beethoven'
OUTPUT DELETED.Artist, INSERTED.Artist
WHERE ID = 1;
GO

SELECT * FROM dbo.Music;
GO

The next few examples will show you how to store the results in tables, temp tables and table variables.

Example 1 – table:
Inserting the data return from an OUTPUT clause into a table can be done using an OUTPUT INTO clause. Keep in mind that you first need to create the target table which must have the same number of columns and data types that match the source table.

IF OBJECT_ID('dbo.Music_Inserted') IS NOT NULL
DROP TABLE dbo.Music_Inserted
GO
CREATE TABLE dbo.Music_Inserted
(
   ID			  INT IDENTITY(1,1) CONSTRAINT PK_Music__Inserted_ID PRIMARY KEY
  ,SongName	VARCHAR(200) NOT NULL
  ,Artist		VARCHAR(50) NOT NULL
)
GO

INSERT INTO dbo.Music ( ID, SongName, Artist)
OUTPUT INSERTED.* INTO dbo.Music_Inserted 
VALUES (5, 'Duniya', 'Piyush Mishra');
GO

-- Result of Music_Inserted table and base table.
SELECT * FROM dbo.Music_Inserted;
SELECT * FROM dbo.Music;
GO

Example 2 – temp table:
With a temp table you will need to do the same as above and create the temp table first and then use the OUTPUT INTO clause to insert the data into the temp table.

IF OBJECT_ID('tempdb..#Music_Deleted') IS NOT NULL
DROP TABLE dbo.#Music_Deleted
GO
CREATE TABLE dbo.#Music_Deleted
(
   ID			  INT 
  ,SongName	VARCHAR(200) NOT NULL
  ,Artist		VARCHAR(50) NOT NULL
)
GO

DELETE FROM dbo.Music
OUTPUT DELETED.* INTO dbo.#Music_Deleted
WHERE ID IN (4,5);
GO

-- Result of temporary table and base table.
SELECT * FROM dbo.#Music_Deleted;
SELECT * FROM dbo.Music;

Example 3 – table variable:
You will declare a table variable with the same structure as the source table.

DECLARE @Music_Deleted TABLE
(
   ID			  INT 
  ,SongName	VARCHAR(200) NOT NULL
  ,Artist		VARCHAR(50) NOT NULL
)

DELETE FROM dbo.Music
OUTPUT DELETED.* 
INTO @Music_Deleted
WHERE ID IN (1,2);

-- Result of table variable
SELECT * FROM @Music_Deleted;