SQL 2008 FILESTREAM storage

SQL Server never is good to storing unstructured data like video, graphics file, MS-Office file etc.  Before SQL server 2008, we have two choices to manage such kind of data mentioned bellow. 

1.    By using VARBINARY(MAX) columns inside the database.

 

2.    Store the data outside the database as part of file system, and include the pointers inside columns that point the file location.

 

Problem that we faced in earlier system

1.    The VERBINARY(MAX) has the 2 GB size limit and can dramatically increases the size of the data base.

 

2.    Storing file in the file system requires a unique naming system. Security is one of the problems and that's why we need NTFS. And required separate backup systems for database and file.

So what the solution

To help resolves those problem Microsoft SQL Server 2008 has introduced FILESTREAM storage. Which is an hybrid approach that combines the best features of previous VERBINARY(AMX) and storing in file system.

FILESTREAM storage is implemented in SQL Server 2008 by storing VARBINARY(MAX) binary large objects (BLOBs) outside of the database and in the NTFS file system. While this sounds very similar to the older method of storing unstructured data in the file system and pointing to it from a column, it is much more sophisticated. Instead of a simple link from a column to an outside file, the SQL Server Database Engine has been integrated with the NTFS file system for optimum performance and ease of administration.

For example, FILESTREAM data uses the Windows OS system cache for caching data instead of the SQL Server buffer pool. This allows SQL Server to do what it does best: manage structured data; and allows the Windows OS to do what is does best: manage large files. In addition, SQL Server handles all of the links between database columns and the files.

The Additional benefits of FILESTREAM

1.    T-SQL can be used in FILESTREAM (SELECT, INSERT, UPDATE, DELETE)

2.    FILESTREAM data is backed up and restore as part of the database file. The options are there that we can backup database without FILESTREAM data.

 

3.    The size of the stored data is only limited by the available space of the file system. Standard VARBINARY(MAX) data is limited to 2 GB.

In what condition we use the FILESTREAM storage

1.    When the BLOB file sizes average 1MB or higher.

 

2.    When fast read access is important to your application.

 

3.    When applications are being built that use a middle layer for application logic.

 

4.    When encryption is not required, as it is not supported for FILESTREAM data.

Implementation of FILESTREAM storage

1.    Enabling the SQL Server instance to use FILESTREAM data

2.    Enabling a SQL Server database to use FILESTREAM data

3.    When creating FILESTREAM-enabled columns in a table, specifying the "VARBINARY(MAX) FILESTREAM" data type.

By default, FILESTREAM storage is not turned on after you install a new SQL Server 2008 instance. If you want to take advantage of it, you must enable it, which is a two step process.

STEP-1

This can be performed by SQL Server 2008 Configuration Manager or by using the sp_filestream_configure system stored procedure.

SQL Server 2008 Configuration Manager

Click on SQL Server Services in the left window, and then in the right window, right-click on the SQL Server instance you want to enable FILESTREAM storage on, choose Properties, and then click on the FILESTREAM tab and follow the instruction over there.

sp_filestream_configure

By SQL Server Management Studio (SSMS)

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

FILESTREAM storage has now been enabled for the SQL Server instance.

STEP-2

The next step is to enable FILESTREAM storage for a particular database.

For existing data base we can use the ALTER DATABASE statements.

CREATE DATABASE FILESTREAM_Database

ON

PRIMARY ( NAME = Data1,

    FILENAME = 'C:\DATA\FILESTREAM_DB.mdf'),

FILEGROUP FileStreamGroup CONTAINS FILESTREAM( NAME = FILESTREAM_Data,

    FILENAME = 'C:\DATA\FILESTREAM_Data')

LOG ON  ( NAME = Log1,

    FILENAME = 'C:\DATA\FILESTREAM_DB.ldf')

GO

After the above code runs, and the database is created, a new sub-folder is created with the name of "FILESTREAM_Data". Inside this newly created folder named "FILESTREAM_Data" is a called "filestream.hdr" and an empty sub-folder called $FSLOG.  The "filestream.hdr" file is used to keep track of the FILESTREAM data.

STEP-3

At this point, our database is FILESTREAM-enabled, and you begin adding new tables that include the VARBINARY(MAX) data type

CREATE TABLE dbo.FILESTREAM_Table

      (    

         ID      UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE ,

         DATA    VARCHAR(100),

         Catalog VARBINARY(MAX) FILESTREAM

      )

Hope you like it.

Posted by: MR. JOYDEEP DAS