Managing Files with SQL Server 2008's FILESTREAM Feature : Page 2

Enabling FILESTREAM

By default, the FILESTREAM feature is disabled, so before you can use it, you must configure both the server and database instances using the following steps:

Figure 1. Enabling FILESTREAM: You must enable FILESTREAM access for the desired SQL Server Instance before configuring the file stream access level for the database instance.

1. To enable FILESTREAM for the server instance, open up the SQL Server Configuration Manager and right-click on "SQL Server Services" from the list of services, then click Open. You'll see a list of servers; right-click the SQL Server instance on which you want to enable FILESTREAM and select "Properties" from the context menu. Navigate to the "FILESTREAM" tab and check the "Enable FILESTREAM for Transact-SQL access" option (see Figure 1). You can also enable FILESTREAM for file I/O streaming access from this tab.
2. To enable FILESTREAM for the database instance, execute the system stored procedure sp_configure and set the filestream_access_level parameter to 2, as shown below:

   EXEC sp_configure filestream_access_level, 2
   GO
   RECONFIGURE
   GO
   

The valid values for the filestream_access_level parameter are:

• 0—Disables FILESTREAM support for this instance. This is the default value.
• 1—Enables FILESTREAM for Transact-SQL access.
• 2—Enables FILESTREAM for Transact-SQL and Win32 streaming access.

After completing the server and database instance configurations, the next step is to create the actual database in which the data will reside. Because FILESTREAMs are specifically created for storing binary data in file systems, you use the CREATE DATABASE statement to create a special FILEGROUP and mark it as a stream:

   CREATE DATABASE FILESTREAMExample
   ON
   PRIMARY ( 
       NAME = FILESTREAMExample_Primary,
       FILENAME = 
        'c:\Projects\DevX\Data\FILESTREAMExample.mdf'),
   FILEGROUP FILESTREAMGroup CONTAINS  FILESTREAM ( 
       NAME = FILESTREAMExample_FileGroup,
       FILENAME = 'c:\Projects\DevX\Data\FILESTREAMExample')
   LOG ON ( NAME = FILESTREAMExample_Log,
       FILENAME =  
        'c:\Projects\DevX\Data\FILESTREAMExample.ldf')
   GO

Next, create a table, designating one of its columns as type VARBINARY(MAX) FILESTREAM:

   CREATE TABLE Product
   (
     ProductID INT  NOT NULL  PRIMARY KEY,
     Name VARCHAR(50) NOT NULL,
     Picture VARBINARY(MAX) FILESTREAM  NULL,
     RowGuid UNIQUEIDENTIFIER  NOT NULL  ROWGUIDCOL
     UNIQUE DEFAULT NEWID()
   )
   GO

The preceding table definition assigns the Picture column as the varbinary(max) column, with the FILESTREAM attribute enabled. Note that the table with a FILESTREAM column must have at least one non-null unique ROWGUID column.

Any binary data you store inside the Picture column is not accessible from the file system; the only way to work with this binary data is through the standard CRUD (INSERT, UPDATE, and DELETE) SQL statements. The following example inserts a single row into the Product table.

INSERT INTO Product VALUES(1, 'Bicycle', 0x00, default)


   GO

That inserts a new row where the ProductID is 1, the Name contains Bicycle, the Picture column value is NULL, and the RowGuid column contains some default GUID value. Now you can retrieve this row from a .NET application, and read, overwrite, or append to its contents.

Previous Page

Next Page