Overview of FileStream
“FileStream” is a new feature introduced in SQL Server 2008 to store unstructured binary files into the database. FileStream Storage implemented as a varbinary(max) column in which data is stored as BLOB in the filesystem. To Specify the column should store data on the filesystem, specify the FILESTREAM attribute on varbinary(max) column. This causes database engine to store all data in the filesystem but not in the databaes file.
a) VarBinary(MAX) datatype to store unstructured information, whereas it has a limitation of 2 GB file. The limitation is not applicable for FileStream.
b) FileStream datatype can be queried through T-SQL
c) Binary data stored as individual files outside the database.
d) These individual files accessed through Win32 API file operations.
How we stored unstructured information before SQL2008?
Earlier we follow different methodologies to store unstructured information like word documents, images, etc into database like, we will keep all those unstructured files in a folder on the operating system and will provide path of the file to a database field with varchar datatype.
After that using LOB (Large Object Data), we can store that unstructured information within the database itself, but it has large overhead while querying LOB tables. LOB Datatypes are TEXT, NTEXT, VARCHAR(MAX), XML, VARBINARY, IMAGE Etc., And it has limitation to manage only 2 GB data.
Usage
1. SQL Server 2008 or later versions are required to make use of FileStream
2. Filegroups created for FILESTREAM must be on shared resources for fail-over clustering.
3. Storage volume must be greater than 1 MB to use FileStream
4. It has faster read access than LOB
5. Each Row data should be maintained by unique row Id.
Limitations
1. Database Mirroring is not supported.
2. TDE is not supported.
3. Database Snapshots are not supported.
Example:
Enable FileStream
1) Open Configuration Manager
2) Select SQL Server DB Engine – Properties – Click on FileStream Tab
3) Check Enable FileStream for Transact-SQL
4) If you want to read and write Filestream data from windows, Click Enable FileStream for file I/O Streaming Access.
From SSMS
Sp_configure ‘filestream access level’, 2
0 – Disable FileStream Support
1 — Enable FileStream for T-SQL
2 —Enable Filestream for T-SQL and Win32 Streaming access.
Step1
Sp_configure ‘filestream access level’, 2
Reconfigure
CREATE DATABASE TestFileStream On Primary
(Name = TestFileStream_Data,
FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\TestFileStream.mdf'),
FileGroup FileStreamGroup Contains FILESTREAM(Name = FileManagement_Group, FileName = 'C:\Temp\Images_Data')
Log On (Name = TestFileStream_Log,
FileName = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\TestFileStream.ldf')
Use TestFileStream
create table ImageInfo (ImageId UniqueIdentifier
Not Null ROWGUIDCOL PRIMARY KEY,
ImageDescription Varchar(200) Null,
ImageData Varbinary(Max) FileStream Default Null)
-- Variable to Store ImageData
DECLARE @img AS VARBINARY(MAX)
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK 'C:\ShadesOfBlue.jpg', SINGLE_BLOB ) AS x
-- Inserting Images Into Table
INSERT INTO ImageInfo (ImageId, ImageDescription, ImageData)
SELECT NEWID(), 'Green Shadow', @img
-- To Verify the FileStream Path From T-SQL
Select Name, Physical_name, type_desc from TestFileStream.sys.database_files
-- Backup FileStream Database will automatically backup the contents of the Image information
BACKUP DATABASE TestFileStream TO DISK ='C:\TEMP\TestFileStream.BAK'
-- Restoing of a FileStream Enabled Database will helps to restore the image folder to File System, Before Restore Run the below command to verify the information about the files
RESTORE FILELISTONLY FROM DISK ='C:\TEMP\TestFileStream.BAK'
-- Drop Database will drop the attached target folder at the file system.
DROP DATABASE TestFileStream
No comments: