반응형

/********************************************************************************************
-- Title : [2012] FileTable(File Stream) 생성 및 활용
-- Reference : databasejournal.com/features/mssql
-- Key word : file table file stream filetable filestream external table
********************************************************************************************/
-- SQL Server 2012 구성관리자

-- FileStream_Access_Level = 2 설정
EXEC sp_configure FileStream_Access_Level, 2;
RECONFIGURE;

exec sp_configure FileStream_Access_Level;

-- FileStream 파일 그룹 포함된 DB 생성
-- drop database ArchiveDB;
CREATE DATABASE ArchiveDB
ON  PRIMARY
( NAME = N'archdat1'
, FILENAME = N'd:\FileStream\archdat1.mdf'
, SIZE = 5120KB
, MAXSIZE = UNLIMITED
, FILEGROWTH = 1024KB
),
FILEGROUP fg_data
( NAME = N'fl_data1'
, FILENAME = N'd:\FileStream\fl_data1.ndf'
, SIZE = 5120KB
, MAXSIZE = UNLIMITED
, FILEGROWTH = 1024KB
),
FILEGROUP fg_FileStream CONTAINS FILESTREAM
( NAME = Arch3
, FILENAME = 'd:\FileStream\fl_filestream'
)
LOG ON
( NAME = N'archdat1_log'
, FILENAME = N'd:\FileStream\archdat1.ldf'
, SIZE = 1024KB
, MAXSIZE = 2048GB
, FILEGROWTH = 10%
)
WITH FILESTREAM
( NON_TRANSACTED_ACCESS = FULL
, DIRECTORY_NAME = N'Dir_ArchiveDB');
GO

-- Filestream Options 확인(FULL)
SELECT DB_NAME(database_id)
     , non_transacted_access
     , non_transacted_access_desc
FROM sys.database_filestream_options
where DB_NAME(database_id) = 'ArchiveDB';
GO

USE ArchiveDB;
go

-- 일반 테이블 생성
-- drop table ft_main;
create table ft_main
( ft_id int not null primary key
, ft_desc nvarchar(100) not null
) on fg_data;

insert into ft_main values (100, '100번');
insert into ft_main values (200, '200번');
insert into ft_main values (300, '300번');
insert into ft_main values (400, '400번');
insert into ft_main values (500, '500번');

-- FileStream 이용한 테이블 생성
-- drop table FileTable;
CREATE TABLE ArchiveTBL AS FileTable
WITH (FileTable_Directory = 'Dir_ArchiveTBL');
GO

-- 서버 속성 확인

-- 공유 확인

-- 확인
SELECT *
FROM ArchiveTBL;

-- 파일 생성
!! echo 1111111111 > \\최명환\mssqlserver\Dir_ArchiveDB\Dir_ArchiveTBL\text1.txt
!! echo 2222222222 > \\최명환\mssqlserver\Dir_ArchiveDB\Dir_ArchiveTBL\text2.txt
!! echo 3333333333 > \\최명환\mssqlserver\Dir_ArchiveDB\Dir_ArchiveTBL\text3.txt

-- 확인
SELECT *
FROM ArchiveTBL;

-- 파일 수정
!! echo 2121212121 > \\최명환\mssqlserver\Dir_ArchiveDB\Dir_ArchiveTBL\text2.txt

-- 확인
SELECT *
FROM ArchiveTBL;

-- update 처리
update ArchiveTBL
set name = 'text2.txt2'
where stream_id = '89E3E8CA-9831-E211-BB64-0026664A86B9';

-- 확인
SELECT *
FROM ArchiveTBL;

!! dir \\최명환\mssqlserver\Dir_ArchiveDB\Dir_ArchiveTBL

-- 파일 열어보기

 


반응형

+ Recent posts