SQL Server 2012 FileTable

2014-04-07

12744
0
[SQL]Lab

步驟一:如下圖所示，在SQL組態管理員中，點選左邊SQL Server服務，再點選右方SQL Server右鍵選內容，點選FileStream頁籤，並勾選啟用FileStream功能。

步驟二:執行exec sp_configure ‘filestream access level’,2 ，設定FILESTREAM啟用完整存取。

步驟三:確定FILESTREAM存取權限是否已啟用完整存取。

步驟四:建立料庫時，先到檔案群組頁中，新增一個FileStream的檔案群組。

步驟五:完成步驟四後回到一般選項，新增一個檔案類型是FileStream的檔案，注意!檔案群組請下拉選擇我們在步驟四建立的FILEGROUP。。

步驟六:完成資料庫建立後，請至資料庫屬性的選項頁面，鍵入目錄名稱及非交易式存取設定為FULL。

步驟七:點選資料表按下右鍵，下拉選單中點選新增FileTable。

步驟八:點選新增FileTable後，會產出下圖中的預設TSQL語法。

步驟九:將SQL預設的語法修正成我們想要的。

FILETABLE_DIRECTORY：指定目錄，以做為 FileTable 中儲存之所有檔案和目錄的根目錄。

FILETABLE_COLLATE_FILENAME：指定定序名稱，用於套用至 FileTable 中的 Name 資料行。

指定 database_default，則資料行會繼承目前資料庫的定序。

http://msdn.microsoft.com/zh-tw/library/gg509088.aspx

步驟十:TABLE建好了，看來所有欄位皆由系統自動產生。

步驟十:將檔案轉成binary後寫入TABLE，注意![name]這一個欄位也要寫入歐，且不可重複歐。

步驟十一:已經建好一筆資料嘍。

步驟十二:資料夾也對建立出一筆檔案。

步驟十三:將檔案刪除。

步驟十四:資料不見了，被刪除了。

步驟十五:檔案也被刪除了。

FileTable有一個特殊的功能，可以在特殊的路徑下直接塞入檔案資料，而相對應的資料表會自動產生一筆相對應的資料。而該特殊路徑都是剛剛我們一些關鍵的設定，如下圖。\\電腦名稱\Instance FileStream的共用名稱\資料庫FileStream的目錄名稱\建資料表時的FileTable_Directory 。

上圖中，我有在資料夾貼上一個testfile.txt的檔案(有點小，須注意看)，因此在相對應的資料表，自動產生出一筆資料，很神奇吧。

如果我們在該路徑下在建立一層資料夾(如下圖)，在該資料夾下建立一個檔案，該TABLE會怎樣產生相對應的資料呢?

由下圖可知TABLE會建立一筆Directory的資料，而該資料夾底下的資料則我們可以在該筆資料的parent_path_locator欄位中看到內容是Directory的path_locator內容(如下圖藍色圈選處)。

重頭戲來啦!測試完整備份後，資料刪除後，相對應的檔案是否會消失。

步驟十六:新增三筆資料進資料表。

步驟十七:做一次完整備份。

步驟十八:刪除資料表資料。

步驟十九:已刪除資料表資料，但我們發現實體檔案並沒有被清除。

步驟二十:SQL2012有一個系統預存程序http://msdn.microsoft.com/zh-tw/library/gg492195

sp_filestream_force_garbage_collection

主要功能為強制執行 FILESTREAM 記憶體回收行程，刪除任何不必要的 FILESTREAM 檔案。

如下圖所示，當我們執行完該預存程序後，會產出一筆資料顯示目前資料庫中的FILESTREAM狀況，其中有個num_unprocessed_items欄位值為3，代表有3個未處理的檔案數目。

經過這一次實驗，發現SQL2012 FILETABLE跟 SQL2008 FILESTREAM在復原模式是FULL的狀態下，只要做過完整備份後，再對資料表資料做刪除時，相對應的實體檔案並不會消失，一定要將復原模式改為SIMPLE後，檔案才會消失。

而資料庫復原模式如果是SIMPLE模式，即使做過完整備份，對資料表資料做刪除時，相對應的實體檔案會一併消失。

看來這樣的狀況是正常的，本來以為會不會是BUG，只是搞不懂為何會有這樣的設計呢?

後記: 在凌晨3點鐘剛洗完澡的一個靈光乍現，當DB在simple模式並不會有檔案不會跟資料一起消失的狀況，而FULL模式卻會。那交易紀錄應該是關鍵，這時突然想到資料有LDF存交易紀錄，檔案卻是沒有，因此當SIMPLE時，資料刪了，由於不會記交易紀錄，相對應的檔案也就跟著刪除了，但FULL卻不行，萬一檔案一刪，等一下要還原交易紀錄時不就會形成有資料無檔案。因此我做了個實驗，剛剛刪不掉的檔案在我做了一次交易紀錄備份後就消失了，YA....終於找到解答

2013/10/16補上備份資料庫後，已刪除之資料的實體檔案會被刪除

如下圖所示FILETABLE中有4個檔案。

刪除TABLE中所有資料。

我們可以發現存放實體檔案的資料夾內還是存在檔案，並無一併被刪除。

接下來我們做資料庫備份。

我們再來檢查一下，可以發現檔案都消失了歐

備註:備份資料庫後SQL會刪除已不在FILETABLE中的實體檔案，因為實體檔案已經被備份起來了，但我測試好幾次發現，要讓實體檔案被刪除，只做一次備份並沒有用，我實驗時新增一筆檔案然後再刪除，接下來要當資料庫執行過兩次交易紀錄備份後檔案就會被刪除，只是不曉得為何要這樣流程才可以，但至少讓大家知道了如果要讓用不到的實體刪除，就是記得要交易紀錄備份歐。