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.