Configure SQL Server 2008 for File Stream

Well, from past two days I am working on SQL Server 2008 new feature called File Stream. In the period of SQL Server 2005 when we want to store some files to the database we can have that using varbinary(max) but that approach is not either smart nor popular amongst the developers. So, many developers like me wants to store images on any physical location and keep the file location in the table. But, that have issues too, what if somebody delete the files from physical location ? will  file entries in the database also deleted and what if somebody deleted the records using t/sql will the files on the physical location also deleted.

In nutshell, both the previous approaches have issues. So, this File Stream data type can replace the problem we had before. It will save the file to the physical location and store the stream of of that file to the table. In my opinion, that is the smart approach.

So, let us dig down and see how can we configure file stream to the new SQL Server 2008 instance , Database and then Table.

Getting Your SQL Server 2008 Instance Ready for File Stream:
  • Goto Start > Programs > Sql Server 2008 > Configuration Tools > SQL Server Configuration Manager
  • Now that Configuration Manager is open, right click on the default instance and go to properties.
  • On this form, Go to File Stream Tab and Enable the file stream. See the image below

sc_filestream

Ok, let me brief you the option we have here.

  • Enable FileStream for transact-sql access : This way you can access the file using t/sql
  • Enable FileStream for File I/O streaming access : By checking this you can access the files using IO Stream
  • All remote clients to have streaming access to file stream data : Here you are allowing remote connections to play around with File Stream Files.

Once you finish with this you need to set the access level by run the following query. Please bear in mind that the following query will not work until you set FileStream stuff from Configuration manager.

   1: EXEC sp_configure filestream_access_level, 2 -- 0 : Disable , 1 : Transact Sql Access , 2 : Win IO Access
   2: GO
   3: RECONFIGURE
   4: GO

Or alternatively, you can

  • Go to SQL Server Management Studio
  • Right Click the database server then properties
  • From the properties window select advance and you will see the following screen

sc_filestream2

  •  Now simply select the access level you want for your server.
Enable database to have FileStream Data type:

Now, you have done with the sql server instance configuration. Let’s move to Database Configuration. How can we create a database which is FileStream Supported.

For that there are two options.

  1. Using T-sql
  2. Using Management Studio
Using T-Sql

For that you need to run the following query 

   1: CREATE DATABASE Learning_Db 
   2: ON
   3: PRIMARY ( NAME = LearnDb1,
   4:     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Learning_Db .mdf'),
   5: FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = LearnDb2,
   6:     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Learning_DbStream')
   7: LOG ON  ( NAME = LearnDbLog1,
   8:     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Learning_Db.ldf')
   9: GO

Ok now, notice that along with the Primary and Log file we have one new file group which we use for FileStream. Remember we have discuss above that FileStream will save a file on a physical location and store the stream in the the database which will later use for accessing that file. 

Now, when you go to the location where we create our new database you will see there is a folder (in our case it should be “Learning_DbStream”).  This folder contain all the files of your FileStream.

Using Management Studio

While creating a new database window go to file group, you will find the File Stream section at the bottom. See image below
Note : If your database is already created, you can set FileStream stuff by right click your database and then properties and then file group

sc_filestream3

For using the FileStream, you need to add a File Stream Group here and make it default. Once you add that, Go to the Files and add a new file

  • Give Logical name In our case we have used “LearningDb_FileStream” and then Select  “FileStream Data” from file type.
  • After that, you only need to set the path and that’s it

    sc_filestream4
  • Click Ok, and now your database is ready to play with FileStream. 
Using FileStream Data type in table:

Now, for using the FileStream data type in table you need to create a Unique Column which is off uniqueidentified datatype and a column that uses varbinary(max) to store the Stream. Here is the SQL for that

   1: CREATE TABLE [dbo].[tbl_Files](
   2:     [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
   3:     [SystemNumber] [int] NOT NULL,
   4:     [SystemFile] [varbinary](max) FILESTREAM NULL
   5: ) ON [PRIMARY]
   6:  
   7: GO
Just give this a Go and you are all done with FileStream configuration.

8 Comments

  • What's wrong with using varbinary(MAX)? Who says I'm not smart or popular for using it? Works great for me.

  • @jeff
    It worked good for me as well. but have you every try to store the PDF files which are in MBs. what happen when you just select * from yourtable ???
    The query will take too long to give you the output. as you have MBs of files stored in your table.
    Where as when it comes to file stream thing, SQL Server will only store the connection to the file in your table. The file exist on the NTFS Location but it's managed reference is there.
    Hope I have answered your question

  • I think this is what I am looking for. Not positive. I want to store the physical file on a NAS or server and store the location/path in the database. I would like it so when I query (via stored proc or whatever) I get a result set that "appears" that the image was in the database. I want to use SQL Server express and stay under the 4GB limit. Thoughts? Will the above work?

  • Is the physical file stored in the database? Or is this a fancy automated link that loads the file automatically when queried?

  • @Markus
    First Part : Yes you can, It is actually saving the connection to the physical path of the file (File Stream) which you will access using System.IO. It is more like the old approach where you store the path of the file in the database but using file stream, when you delete the record. The file from that location will delete automatically without bothering you to write some stuff for deleting the associated file. If you still face any challenge just buzz out

  • Hi,
    How can I enable FILESTREAM without using "SQL Server Configuration Manager". I want to make an automatic script that will create new database and enable the filestream.

    Thanks,
    Alon

  • It is not worked for me. full text is enabled but it cannot create databse. the error is:

    Msg 5120, Level 16, State 106, Line 1
    Unable to open the physical file "D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Learning_DbStream". Operating system error -2147024891: "0x80070005(failed to retrieve text for this error. Reason: 1815)".

    please help...

  • Configure sql server 2008 for file stream.. Nice :)

Comments have been disabled for this content.