FileStream with SQL Server

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.