Introduction to Filestream in SQL Server 2012

Introduction to Filestream in SQL Server 2012

Filestream feature is not new in SQL Server 2012, it is introduced in SQL Server 2008. Last week I visited one company where they are storing some document in physical disk on server and stores its location in database with appropriate records. This is one of the very old approach and nothing wrong in it but problem starts when you decide to change location of folder because of any reason. You have to update all pointers of location stored in database. Apart from this problem, you have to take manual backup of document folder as it won’t come along with SQL Server database backup.

You can have another approach too, you can store documents / images etc. in VARBINARY fields in SQL Server It creates an issue of performance though It saves you from taking manual backup of document because when you take backup of database, you will have all your document inside the backup in form of VARBINARY field but still performance is a big issue in this case. Apart from that there is one more limitation that we can’t store file / document which is bigger then 2GB.

I have written articles about how to use BLOB (Varbinary) fields.

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C# (Click Here to read more)

Insert and Update image field in SQL Server 2008/2005 (Click Here to read more)

Filestream solve your problem regarding performance and backup. Filestream can store document / image / video / music etc. in NTFT file system though it is accessible right from from the SQL Server as well as from the WIN32 API and .NET class. You will gain performance if you access your filestream data from .NET class or from WIN32 API, by doing this, you can reduce overhead from SQL Server. When you will take full backup of database, you can have all your filestream files in backup at the same time you are eliminating 2GB limitation of VARBINARY field.

There are some disadvantages hovering around filestream too like mirroring doesn’t support filestream data but you can ignore this because mirroring itself deprecated from SQL Server 2012 version. Apart from that there is one more major disadvantage which is you can’t apply Transparent Data Encryption (TDE) on filestream data. These are two major disadvantage but I can ignore it as I will have less overhead on my SQL Server due to filestream, my data backup issue get solved and I wouldn’t have limitation of 2GB. These are the main advantage which force me to choose this feature.

You can enable filestream while installing SQL Server instance but if you have not enabled it at the time of installation, let us see how can we enable it in our existing SQL Server instance.

1.) Open “SQL Server Configuration Manager” from “Start | All Programs | SQL Server 2012 | Configuration Tools “. Once you have “SQL Server Configuration Manager” open, move to the SQL Server instance, right click on it and click on “Property”.

1SQLServerConfigurationManager

2.) Once you click on “Property”, you will get instance’s property. Move to the “FILESTREAM” tab and follow the setting which marked in red rectangle in following screen capture.

2FilestreamProperty

Once you setup property, open SQL Server Management Studio (SSMS) and execute following script to allow access to TSQL as well as file I/O for filestream.

EXEC sp_configure Filestream_access_level,2
GO
RECONFIGURE
GO

Once you setup permission for filestream at instance level, let us create one database with filestream enable filegroup.


CREATE DATABASE ExtremeAdviceFileStream ON PRIMARY

(

NAME = ExtremeAdviceFileStream_data,

FILENAME = N'D:\TestDB\ExtremeAdviceFileStream_data.mdf'

),

FILEGROUP ExtremeAdviceFileStream_FS CONTAINS FILESTREAM

(

NAME =ExtremeAdviceFileStream_FILESTREAM,

FILENAME = N'D:\TestDB\ExtremeAdviceFileStream_FS'

)

LOG ON

(

NAME = ExtremeAdviceFileStream_LOG,

FILENAME = N'D:\TestDB\ExtremeAdviceFileStream_log.ldf'

);

GO

Well, this is how you can enable filestream. I will cover few exercises for filestream in coming articles very soon.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

4 thoughts on “Introduction to Filestream in SQL Server 2012

  1. Mr. Wharty's Ramblings

    “but problem starts when you decide to change location of folder because of any reason. You have to update all pointers of location stored in database”

    Not really. If you only store the file name in the database and then store the folder location as a UNC in a config file or a table in the database, you only need to update the pointer once.

    Filestream also comes with some inconveniences/limitations which may make it more of a hassle to implement/maintain.

  2. Pingback: Create table with Filestream column in SQL Server 2012

  3. Pingback: Milestone of 500+ article

Comments are closed.