FILESTREAM on SQL SERVER 2008.

For storing unstructured data like word doc, images, video files in database on SQL Server 2008. We need to use Filestream.

1.       Need to enable filestream feature if not enable at the time of installation. It can be enable from SQL configuration manager.

        Right Click on the SQL Service then property. then to FileStream tab.

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 box.

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.

2.       Creating a FILESTREAM database. Can be also done through GUI.

e.g  below normal database creation just adding a file stream  file group.

CREATE DATABASE Archive

ON

PRIMARY ( NAME = Arch1,

    FILENAME = 'j:\test\archdat1.mdf'),

FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,

    FILENAME = 'j:\test\filestream1')

LOG ON  ( NAME = Archlog1,

    FILENAME = 'j:\test\archlog1.ldf')

GO

2^nd table:

CREATE TABLE [dbo].[Images](

[ID] uniqueidentifier NOT NULL ROWGUIDCOL PRIMARY KEY,

[Image] [varbinary](max) filestream default NULL

) ON [PRIMARY]

3.       Inserting data into this table. In the below example I have inserting one image w.jpg file into the database.

INSERT INTO [Images]

([ID],[Image])

VALUES

(NEWID(),

(SELECT * FROM OPENROWSET

(BULK 'j:\w.jpg',

SINGLE_BLOB) AS [Image]))

SSIS packesges can be also used to insert this type of data into database.

4.        To view data for the table now.  

If we run

select * from images

It will run successfully but will fetch the image it above format.

I have us SSRS to display the Image.

5.       In BIDS create an SSRS project solution to display the same.