Some concept:
http://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx
varbinary(max) is used to store small object within table, filestream varbinary(max) is used to store big object on file system.
Step by step to configure filestream:
http://technet.microsoft.com/en-us/library/bb933995(v=sql.105).aspx
step 1: Enable FILESTREAM
a. Enable fielstream with SQL server configuration manager.
b. Run the following command with SQL server management studio query window:
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
step 2: Create a FILESTREAM-Enabled Database
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\data\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
FILENAME = 'c:\data\filestream1')
LOG ON ( NAME = Archlog1,
FILENAME = 'c:\data\archlog1.ldf')
GO
step 3: Create a Table for Storing FILESTREAM Data
use Archive;
go
CREATE TABLE Archive.dbo.Records
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[SerialNumber] INTEGER UNIQUE,
[Chart] VARBINARY(MAX) FILESTREAM NULL
)
GO
Management:
method 1: Managing FILESTREAM Data by Using Transact-SQL
INSERT INTO Archive.dbo.Records
VALUES (newid (), 1, NULL);
GO
select * from Archive.dbo.Records
INSERT INTO Archive.dbo.Records
VALUES (newid (), 2,
CAST ('' as varbinary(max)));
GO
INSERT INTO Archive.dbo.Records
VALUES (newid (), 3,
CAST ('Seismic Data' as varbinary(max)));
GO
UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' as varbinary(max))
WHERE [SerialNumber] = 2;
DELETE Archive.dbo.Records
WHERE SerialNumber = 1;
GO
-- Get the text file content and insert into SQL server as varbinary(max);
insert into Archive.dbo.Records
select
NEWID(), 4,
* FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
select * from Archive.dbo.Records
-- This can get the content in text file.
select CONVERT(varchar(100),chart) from Archive.dbo.Records
After performing the above codes, we found the following files and folders:
method 2: Managing FILESTREAM Data by Using Win32
DECLARE @filePath varchar(max)
SELECT @filePath = Chart.PathName()
FROM Archive.dbo.Records
WHERE SerialNumber = 3
PRINT @filepath
DECLARE @txContext varbinary(max)
BEGIN TRANSACTION
SELECT @txContext = GET_FILESTREAM_TRANSACTION_CONTEXT()
PRINT @txContext
COMMIT
FILESTREAM data is not encrypted even when transparent data encryption is enabled.
Encryption is not supported on FILESTREAM data.
Only the account under which the SQL Server service account runs is granted NTFS permissions to the FILESTREAM container. We recommend that no other account be granted permissions on the data container.