1 언제 써먹나?

2 기본 설정

3 데이터베이스 생성하기

4 테이블 생성하기

5 FILESTREAM directory structure - where do the GUIDs come from?

6 참고자료

• 데이터가 1MB를 넘을 때
  
• 빠른 읽기를 요구할 때
  
• 미들티어 어플리케이션의 개발 요구사항이 있을 때
  
  

1. SQL Server Configuration Manager 실행
   
2. FileStream을 사용할 SQL Server 인스턴스를 찾아 마우스 오른 클릭 -> 속성
   
3. FileStream 탭에서 허용할 만큼 체크를 하고 확인 클릭
   
   

use master
go
exec sp_configure 'filestream access level', 2
go
reconfigure
go

• c:\filestream (명령프롬프트에서 mkdir c:\filestream)
  
  

use master
go
if  exists (select name from sys.databases where name = 'filestreamdb')
	drop database filestreamdb
go

use master
go

create database filestreamdb 
on primary
(
	name = data01
,	filename = 'c:\filestream\data01.mdf'
),
filegroup filestream_fg contains filestream 
(
	name = filestream_data01
,	filename = 'c:\filestream' --요기를 주의해서 살펴봐라. c:\filestream\file 이 아니다. 
)
log on
(
	name = log01
,	filename = 'c:\filestream\log01.ldf'	
)

/*
메시지 5591, 수준 16, 상태 1, 줄 1
FILESTREAM feature is disabled.

만약 이런 메시지를 접하게 되면 이제까지의 과정 중 빠뜨린 부분이 있다는 뜻이 된다. 
*/

use filestreamdb
go
create table dbo.filestreamtable
(
  fs_id uniqueidentifier rowguidcol not null unique,
  fsdata varbinary(max) filestream
);

insert into filestreamtable
values(newid(), cast ('inserting data into filestreamtable........' as varbinary(max)))
go


select 
	fs_id
,	CONVERT(varchar(50), fsdata) fsdata 
from filestreamtable
/*
fs_id                                fsdata
------------------------------------ --------------------------------------------------
E1C9F831-7305-4C18-954A-5DFEFC5DD550 inserting data into filestreamtable........
*/

declare @txcontext varbinary(max)
begin transaction
select @txcontext = get_filestream_transaction_context()
print @txcontext
commit
--결과: 0x247E3F6CCDE6524FB5F20CA80003177B

SELECT o.name AS [Table],
    cp.name AS [Column],
    p.partition_number AS [Partition],
    r.rsguid AS [Rowset GUID],
    rs.colguid AS [Column GUID]
FROM sys.sysrowsets r
    CROSS APPLY sys.sysrscols rs
    JOIN sys.partitions p ON rs.rsid = p.partition_id
    JOIN sys.objects o ON o.object_id = p.object_id
    JOIN sys.syscolpars cp ON cp.colid = rs.rscolid
WHERE rs.colguid IS NOT NULL AND o.object_id = cp.id
AND r.rsguid IS NOT NULL AND r.rowsetid = rs.rsid;
GO 

• FILESTREAM directory structure - where do the GUIDs come from?(http://www.sqlskills.com/BLOGS/PAUL/post/FILESTREAM-directory-structure-where-do-the-GUIDs-come-from.aspx)
  
  

• How It Works: FileStream (RsFx) Garbage Collection(http://blogs.msdn.com/b/psssql/archive/2011/06/23/how-it-works-filestream-rsfx-garbage-collection.aspx)
  
• How to Backup and Restore a SQL Server FILESTREAM Enabled Database(http://www.mssqltips.com/tip.asp?tip=1854)
  
• Creating a SQL Server 2008 FILESTREAM Enabled Database and Using INSERT, UPDATE and DELETE statements to manage FILESTREAM Data(http://www.mssqltips.com/tip.asp?tip=1850)
  
• FILESTREAM Setup and Testing without Visual Studio(http://www.sqlservercentral.com/articles/FILESTREAM/67668/)
  
• Implementing SQL Server 2008 FILESTREAM functionalit(http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1356593_mem1,00.html)
  
• FILESTREAM Storage in SQL Server 2008(http://technet.microsoft.com/en-us/library/cc949109.aspx)
  
• Streaming Data Into SQL Server 2008 From an Application(http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/)
  
• How to use FileStream feature in an ASP.NET Web Application that uses Forms or Anonymous authentication(http://blogs.msdn.com/psssql/archive/2009/05/06/how-to-use-filestream-feature-in-an-web-application-that-uses-forms-or-anonmous-authentication.aspx)
  
• FILESTREAM Storage in SQL Server 2008(http://msdn.microsoft.com/en-us/library/cc949109.aspx)
  
• Working with FILESTREAM using VB.NET(http://www.databasejournal.com/features/mssql/article.php/3802241/article.htm)
  
• FILESTREAM garbage collection(http://www.sqlskills.com/BLOGS/PAUL/post/FILESTREAM-garbage-collection.aspx)