SQL Server2008のFILESTREAMを使う

SQL Server2008の新機能であるFILESTREAMを使ってみます。FILESTREAMは、BLOB(SQL Serverでの型はvarbinary(max))のデータをデータベース内ではなく、ファイルシステム上のファイルとして保存する機能です。

これまで、画像やPDFなどの保存は、

ファイル名のみDBに保存しファイルはファイルシステム上に保存
まるごとBLOBに保存

という方法が多かったですが、その中間的な方法で、メリットしては、

データサイズが大きい場合に通常のBLOBに比べてパフォーマンスがよい
ファイルについてもトランザクション内で操作される。
ファイルサイズの制約がない（Express EditionでもDBサイズの制約外）
SQLからもWin32APIからのアクセスできる

ということがありますが、使う上での制約もいくつかあります。

ファイルとしてアクセスする場合には、ファイルのUNCパスを取得した上でWin32API経由でアクセスする必要があり、通常のファイルほどは簡単に使えなそうです（いずれ試そうと思っていますが）。特にC＃など.NET系はよいですが、Javaからではあまりメリットはないかもしれません。。

FILESTREAMを有効にする

FILESTREAMは通常のインストールでは無効になっているので、インストール後に有効にする必要があります。以下は、SQL Serverのインストール後にFILESTREAMを有効にする方法です。
その前に、サービスの起動などSQL Serverのサービスについての設定はすべてSQL Server構成マネージャから行う必要があります。管理ツールのサービスからだと関連するサービスの設定が行われない場合があるようなので注意が必要です。

まず、[スタート] -> [すべてのプログラム] -> [Microsoft SQL Server 2008] -> [構成ツール] で [SQL Server 構成マネージャ]を起動します。
SQL Serverのインスタンスのプロパティを開きます。
[FILESTREAM] タブにて、[Transact-SQL アクセスに対して FILESTREAM を有効にする] をチェックする。Windows から FILESTREAMを使用する場合には、[ファイル I/O ストリームアクセスに対して FILESTREAM を有効にする] をチェックしてWindows 共有の名前を入力します。
SQL Server Management Studio で以下のクエリを実行する。

　　　EXEC sp_configure filestream_access_level, 2
　　　RECONFIGURE

FILESTREAMを有効にする（コンフィグレーションファイル）

インストール時にFILESTREAMを有効にすることもできます。私はコンフィグレーションファイルを使ってインストールすることが多いのですが、以下のような記述を追加します。

（略）
; FILESTREAM 機能を有効にするレベル (0、1、2、または 3)。 
FILESTREAMLEVEL="2"
;Windows共有名
FILESTREAMSHARENAME="sqlexpress"
（略）

SQLEXPRADV_x86_JPN.exe /CONFIGURATIONFILE=ファイル名

データベースの作成

FILESTREAMを使用するデータベースを作成します（MSDNのサンプルより）。

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

FILEGROUPでFILESTREAMの保存先パスを指定します。"c:\data\"まで作成しておき"filestream1"フォルダは存在しない状態で実行する必要があります。このクエリを実行するとc:\data\filestream1は以下のようになります。

テーブルの作成

FILESTREAMを含むテーブルを作成します。

CREATE TABLE Archive.dbo.Records
(
	[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
	[SerialNumber] INTEGER UNIQUE,
	[FileData] VARBINARY(MAX) FILESTREAM NULL
)

ここで、

ROWGUIDCOL属性を持つ列が必須
FILESTREAMはVARBINARY(MAX)列の属性として指定

します。SerialNumberは、データ操作のために追加してあります。

文字列データを追加してみます。

INSERT INTO Archive.dbo.Records(Id, SerialNumber, FileData)
	VALUES(newid(), 1, CAST('TEST' AS VARBINARY(MAX)))

データを取り出します。

SELECT Id, SerialNumber, CAST(FileData As varchar(max)), FileData.PathName() FROM Records

"\\マシン名〜"が、ファイルのパス（論理UNCパス）で、これを使ってC#などからファイルを操作するようです。

28FF9E7B-37E0-4E8F-A4B4-9BC66E2C5009, 1, TEST ,\\マシン名\sqlexpress\v1\Archive\dbo\Records\FileData\28FF9E7B-37E0-4E8F-A4B4-9BC66E2C5009

JDBC経由で使う

データ型自体はvarbinary(max)なので、JDBCで使用する場合はBLOBと同様で特にFILESTREAMであることを意識する必要はないようです。
Working with SQL Server 2008 Filestream using v1.2 JDBC driver

フルテキスト検索を使う

これから。

azuki note