Saturday, November 8, 2008

Filestream storage in MS SQL Server 2008

You can store binary data to the filesystem using the Filestream attribute for a column with the Varbinary(max) data type. A standard Varbinary(max) column has a capacity of 2GB but using Filestream storage extends it to the file system volume size. Filestream data is stored in filestream file groups (they consist of data containers or file system directories). A backup of the database includes filestream data (use a partial backup that excludes the filestream file groups to avoid this). Using filestream storage also enables the use of Win32 interfaces to manipulate data. Deleting a row also removes the associated data from the filestream storage.

Filestream data is not encrypted and uses the system cache instead of the buffer pool. Filestream storage is not enabled by default.

The use of filestream storage is suggested for large (over 1MB) data; storing small amounts of data in the database provides better performance.

Note: When using failover clustering, the file groups must be on a shared storage medium.

1 comment:

Jen said...

The blog is helpfull...
visit also asp.net [c#]