Filestream for newbies

Filestream is one of the coolest new functionalites in SQL Server, unfortunately a lot of systems don’t use it, because it is to difficult to use or the application that users use can’t handle filestreams.

This document will try to give a short introduction to newbies on how Filestream is working. Don’t expect a deep technical blog. This is the very simple way of presenting filestreams.

Basically Filestreams are used to store large documents like movies, PDF-files, music and so on, but you could basically store all types of data in a FILESTREAM.

BUT, let’s take a look at it.

1. FIRST WE NEED TO PREPARE THE SQL SERVER / INSTANCE FOR FILESTREAM

In windows

Go to start –> MICROSOFT SQL SERVER –> Configuration –> SQL SERVER CONFIGURATION MANAGER.

Right click the instance that you want to enable filestream on. Choose Properties.

Filestream 001
SQL Server configuration

Depending on the type of access you need, then you should enable filestream for T-sql and/or IO Streaming. In this post I will only use T-SQL.

 
Go into the SQL Server management studio and logon to the Instance you want to enable for filestream.
 
2. FIRST WE WILL CREATE THE DATABASE WE WILL TEST AND WORK WITH:
 

—- CODE SNIPPET START —

 

use master
go

 

if DATABASEPROPERTYEX(‘Filestream’,‘VERSION’)>0
drop database Filestream
go

 

create database Filestream
go

 

 

 

use Filestream
go

 

EXEC sp_configure filestream_access_level,2
RECONFIGURE
go

—- CODE SNIPPET END —-

 

3. NOW WE NEED TO ADD A FILESTREAM FILEGROUP

—- CODE SNIPPET START —-

USE [master]
GO

ALTER DATABASE [Filestream] ADD FILEGROUP [Filestream] CONTAINS FILESTREAM
GO

ALTER DATABASE [Filestream] ADD FILE ( NAME =N’FStream’,FILENAME=N’C:\FStream’) TOFILEGROUP [Filestream]
GO

USE [Filestream]
GO

IF NOT EXISTS(SELECT name FROM sys.filegroups WHERE is_default=1 AND name =N’Filestream’) ALTER DATABASE [Filestream] MODIFYFILEGROUP [Filestream]  DEFAULT
GO 

—- CODE SNIPPET END —-

4. NOW WE ARE READY TO ADD A TABLE AND SOME DATA
A table with a filestream requires a ROWGUIDCOL and all filestream data must be varbinary(max), so the table will look like this (including insertion of sampledata):
 

—- CODE SNIPPET START —-

CREATE TABLE FilestreamFun (
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[Number] INTEGER UNIQUE,
[Name] VARBINARY(MAX) FILESTREAM NULL
)

GO

insert into Records values (NEWID(), 1,CONVERT(varbinary(Max),‘I am no. 1’))
insert into Records values (NEWID(), 2,CONVERT(varbinary(Max),‘I am no. 2’))
insert into Records values (NEWID(), 3,CONVERT(varbinary(Max),‘I am no. 3’)) 

—- CODE SNIPPET END —-

If you now go into c:\then you should have a folder called: FStream, and the content in there is:

At this stage, you don’t have to think about the structure of this folder.

5. RETRIEVING DATA
If you want to see your data, you can write a sql-statement like this

—- CODE SNIPPET START —-

Select *,CONVERT(varchar(50), chart)as converted
from FilestreamFun

—- CODE SNIPPET END —-

It should look something like:

What you can see is that if you don’t convert, then you can’t see your data.

 

Remember that you need to convert to the datatype that you want to see the data in.

 

THE BIG QUESTIONS:
OK, that is nice, but why not just use a link to external documents? Basically you could do that, but then you loose a lot of benefits:

  1. The data is a part of the normal SQL Server backup, so you don’t have to worry about backups if you have setup the SQL Server backup properly.
  2. Performance on large BLOB’s are quite good.
  3. Transactions are treated as a normal datatype, so that you e.g. can rollback.
  4. The data is treated like all other datatypes, so it makes your life much easier (of course there are technical limitations but don’t think about them now).

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s