Transact-SQL(T-SQL) #2 - FILESTREAM

"Transact-SQL(T-SQL) #1"에서는 데이터 형식을 알아보았습니다. 데이터베이스를 설계할 때 대부분의 RDBMS에서 제공하는 데이터 형식인 VARCHAR, TEXT등 사용해도 되지만, T-SQL에서 지원하는 NCHAR, NVARCHAR, VARCHAR(MAX) 데이터 형식을 사용하여 데이터베이스의 성능을 향상하고 할 수 있습니다.

이번에는 T-SQL에서 지원하는 기능 중 하나인 FILESTREAM에 대해 블로깅 하려고 합니다.

 

 

1. 데이터베이스와 파일을 따로 관리하는 방법

웹에서 사용하는 게시판을 직접 구현하는 데 필요한 데이터 타입은 어떤 것이 있을까요?

 

게시판의 글쓰기 화면입니다. 크게 제목, 본문내용, 첨부 파일로 구성되어있습니다. 제목과 본문내용은 텍스트이므로 VARCHAR 데이터 형식을 사용하면 됩니다. 그럼 첨부 파일은 어떻게 저장할까요?

 

 

위 그림은 첨부 파일을 관리하는 테이블의 예입니다. 구현방법에 따라 차이는 있겠지만 보통 웹 서버에 저장된 첨부 파일의 FullPath와 원래 첨부파일명을 저장합니다.

 

2번째 행의 데이터를 보면 사용자가 게시판에 업로드한 파일의 이름은 "그림1.png"이고 해당 파일은 웹서버의 "bbs/88-03-04/6661_bo_region_c7_6.png"에 저장되어 있습니다.

파일명을 바꿔서 저장하는 이유 중 하나는 폴더 내에 동일한 이름의 파일이 생성되면 안 되기 때문입니다.

 

이 방식으로 게시판을 구현한다면 첨부 파일은 데이터베이스와는 별개로 관리해야 해서 추가적인 작업이 필요하게 됩니다.

 - 파일명 중복 문제 해결

 - 게시물 삭제 시 첨부 파일 삭제 기능 구현

 - 파일의 무결성 검사 필요

 - 하드디스크 용량 문제 등으로 인하여 첨부 파일 이전 시추가 작업 필요

 - 웹 서버 이전 시 첨부 파일 이동 관련 설계 필요

 

파일 또한 텍스트, 숫자, 시간 데이터형식과 같이 데이터베이스에서 같이 관리한다면 더 효율적일 것입니다.

 

2. 바이너리 데이터 형식

T-SQL은 파일을 저장할 수 있는 VARBINARY 데이터 형식을 지원합니다. 이제 게시판의 첨부 파일을 VARBINARY 데이터 형식을 사용해 저장해 보겠습니다.

 

첨부 파일 필드를 보면, 파일의 이진값이 직접 데이터베이스에 저장된 것을 알 수 있습니다. 이 방법으로 게시판을 구현한다면 위에서 언급했던 5가지의 문제가 모두 해결됩니다.

파일명 중복 문제, 파일 삭제 문제를 고민하지 않아도 되고 웹 서버 이전 혹은 복제 시 데이터베이스만 복사하면 됩니다.

 

하지만 이 방법의 단점이 있습니다. 파일의 저장이 많아지면 데이터베이스의 전체 용량이 커지고 성능이 저하될 수 있습니다.

하나의 게시물을 작성하는 데 있어 제목과 본문에 저장되는 텍스트의 크기는 MB 단위 이상의 첨부 파일에 비하면 작은 편입니다. 데이터베이스에 저장된 텍스트, 숫자, 시간 데이터 보다 파일 데이터의 크기가 커서 전체 데이터베이스의 크기가 커진다면 관리하기가 비효율적이며, 용량이 큰 파일에 대한 접근이 많아지면 데이터베이스 성능도 저하됩니다.

 

T-SQL은 이런 단점을 해결해주는 FILESTREAM을 제공합니다.

 

3. FILESTREAM

FILESTREAM은 이진 데이터를 텍스트, 숫자, 날짜 데이터 형식이 저장되는 데이터베이스 파일과는 별개의 저장소에 저장하는 기법입니다.

사용자는 기존 VARBINARY 데이터형식을 똑같이 쓰고 읽으면 되고 DBMS가 파일을 따로 관리해주는 방식입니다.

 

파일을 VARBINARY 형식으로 관리하기 편하고 데이터베이스와 따로 관리하면서 데이터베이스의 성능저하를 막을 수 있습니다.

 

다음은 FILESTREAM의 사용 방법입니다.

 

1) DBMS에서 FILESTREAM 사용 설정

 

SQL Server을 처음 설치 할 때 FILESTREAM 사용을 추가할 수 있으며, 이후에도 SQL Server 구성관리자 설정 창에서 FILESTREAM 사용 여부를 설정할 수 있습니다. 

 

2) FILESTREAM 을 지원하는 데이터베이스 생성

 

D:\SQLSERVER 폴더에 BSSM 데이터베이스를 생성하는 예제입니다. 데이터베이스, FILESTREAM, 데이터베이스 로그를 각각 생성했습니다.

 

3) FILESTREAM 을 지원하는 테이블 생성

 

FILESTREAM 을 사용하기 위해서는 ROWGUIDCOL 타입의 필드가 있어야됩니다. Idx는 데이터를 관리하기 위한 인덱스 필드이며 FILESTREAM 이 사용 가능한 VARBINARY(MAX) 타입의 FIle 필드를 생성했습니다.

 

4) BINARY 데이터 쓰기

 

ROWGUIDCOL 형식의 필드는 newid() 함수로 매번 새로운 Id 값을 INSERT하면 됩니다.

위 소스는 D:\1.png파일을 VARBINARY에 쓰기 위한 예입니다.

쿼리를 실행한 결과는 다음과 같습니다

 

5) 데이터 사용

 

INSERT 한 이미지 파일은 VARBINARY 데이터 형식으로 불러와 사용할 수 있습니다.

 

6) 데이터 확인

 

2) 에서 생성한 FILESTREAM 의 위치로 접근하면 다음과 같이 바이너리 파일이 저장되어 있습니다.

 

파일을 열어보면 저장했던 그림파일과 같은 데이터임을 확인할 수 있습니다.

 

4. 마치며

시스템을 구현할 때 FILE을 데이터베이스와 별개가 아닌, 데이터베이스에서 하나의 데이터 형식으로 쉽게 관리하는 VARBINARY  데이터 형식의 사용 방법과 데이터베이스의 성능저하를 막을 수 있는 FILESTREAM 사용 방법을 알아보았습니다.