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