Enabling FILESTREAM In The Database

Once you have your server configured for FILESTREAM, you will need to configure your database. The easiest way is to establish it when you first create the database.


CREATE DATABASE FileStreamFTS ON 
PRIMARY ( NAME = FileStreamFTS_Data,
    FILENAME = 'd:\data\FileStreamFTS_Data.mdf'),
FILEGROUP FileStreamFTS_FileGroup1 
  CONTAINS FILESTREAM( NAME = FileStreamFTS_FileGroup,
    FILENAME = 'd:\data\FileStreamFTS_FileGroup')
LOG ON  ( NAME = FileStreamFTS_Log,
    FILENAME = 'd:\data\FileStreamFTS_Log.ldf')
GO

In the above example, I created my database in a directory on my D drive named “D:\Data”. If you examine the folder you ’ll now see two files, the mdf and ldf files. There is also a folder “FileStreamFTS_FileGroup”. It is in this folder that your files will eventually be stored in.

You may be tempted to poking around in the folder, and perhaps even access the stored files directly. My advice to you: don’t. Microsoft has strongly advised against this practice. After all, you went to all the trouble of setting up FILESTREAM to let SQL Server handle this for you, so let it do the handling. If you are curious to dig deeper into how FILESTREAM works behind the scenes, I would recommend Paul Randal’s excellent white paper available at http://msdn.microsoft.com/en-us/library/cc949109.aspx.

The above example shows how to add FILESTREAM capability when you first create your database. That’s great, but what if you already have an existing database you want to add FILESTREAM to? I have just such an existing database, “ArcaneCode”. To add FILESTREAM I need to issue two ALTER DATABASE statements. First, we need to add a FILEGROUP and indicate that file group contains FILESTREAM objects.


ALTER DATABASE ArcaneCode
ADD FILEGROUP ArcaneFileStreamGroup1 
  CONTAINS FILESTREAM
GO

In the second we have to indicate to the FILEGROUP where the FILESTREAM should be stored. Pass in the directory name where you want your files saved as the FILENAME parameter. As with the create statement the directory can not exist, if it does you’ll get an error.


ALTER DATABASE ArcaneCode
ADD FILE  
  (NAME = 'ArcaneFS_Group'
   , FILENAME = 'D:\Data\ArcaneCodeFS'
   )
TO FILEGROUP ArcaneFileStreamGroup1 
GO

This completes the steps needed to create a database with FILESTREAM, or add it to an existing database. In the next lesson we will look at creating tables that use FILESTREAM.

Advertisements

3 thoughts on “Enabling FILESTREAM In The Database”

  1. “If you examine the folder you ’ll now see two files, the mdf and ldf files. There is also a folder “FileStreamFTS_FileGroup”. It is in this folder that your files will eventually be stored in.”
    What I can not believe!

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