網頁

▼

2021/03/05

SQL SERVER 儲存 BLOB 檔案 BCP, CLR, OLE 功能差異


這一陣子遇到需要將 BLOB 欄位轉成實體檔案的需求，主要應用在發送電子郵件上，不查還好，一查也是水很深的技術問題。

 

前提：必須開啟 SQL SERVER 存取實體磁碟的權限

SQL SERVER 存取實體磁碟工作大多屬底層控制，而 DML 操作是在 Instance 下處理，並不會直接對實體磁碟控制，之前 SQL Injection 等資訊安全事件頻傳，為了安全性，SQL SERVER 對實體磁碟的控制選項預設值是關閉的，所以如果必須對實體磁碟控制，就必須先將其開啟。

文章目錄

控制方案

依照【stackoverflow】社群裡的【fastest way to export blobs from table into individual files】討論文章所說，可以分為以下三類：

傳統操作：BCP 和 xp_cmdshell

BCP 是 SQL SERVER 所有版本都有附帶的操作程式，xp_cmdshell 則是 SQL SERVER 對實體層控制的 T-SQL 指令，範例程式如下：

sp_configure 'show advanced',1
reconfigure;
GO
-- 開啟
sp_configure 'xp_cmdshell',1
reconfigure;
GO

SET @bcpCommand = 'bcp "SELECT blobcolumn FROM blobtable WHERE ID = ' + CAST(@FileID AS VARCHAR(20)) + '" queryout "' + @FileName + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand

優點

  • 所有版本皆可使用。

缺點

  • Blob 直接轉譯為 Binrary 文字檔。

必須再對其文字檔做格式轉換。

而且因功能不受限制，所以官方不推薦使用。

OLE 自動化（OLE Automation）

OLE Automation 功能使 SQL SERVER 也可以像 VB 一樣控制 OLE 物件，語法如下：

sp_configure 'show advanced',1
reconfigure;
GO
-- 開啟
sp_configure 'Ole Automation Procedures', 1;  
reconfigure;
GO
declare @init int
declare @file varbinary(max) = CONVERT(varbinary(max), N'your blob here')
declare @filepath nvarchar(4000) = N'c:\temp\you file name here.txt'

EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
EXEC sp_OASetProperty @init, 'Type', 1; 
EXEC sp_OAMethod @init, 'Open'; -- Calling a method
EXEC sp_OAMethod @init, 'Write', NULL, @file; -- Calling a method
EXEC sp_OAMethod @init, 'SaveToFile', NULL, @filepath, 2; -- Calling a method
EXEC sp_OAMethod @init, 'Close'; -- Calling a method
EXEC sp_OADestroy @init; -- Closed the resources

優點

  • 所有版本皆可使用。
  • 轉出的檔案為原始上傳格式，不需再經過轉換。

缺點

  • OLE 物件不正常關閉下會導致 SQL SERVER 資源被佔用，進而造成 Memory Leak 情形。

CLR 函式（CLR Function）

SQL SERVER 2016 以上支援，其特色是可以使用 .NET framework 所支持的語言開發 CLR 函式庫來補足 SQL SERVER 未涵蓋的功能。

利用 C# 開發的 CLR 函式片段及 T-SQL 的呼叫方式

優點

  • 執行效能佳。
  • 轉出的檔案為原始上傳格式，不需再經過轉換。

缺點

  • 有版本限制。
  • 須具備其它語言的技術基本掌握。


總結

SQL SERVER 在實體操作上必須依賴外部工具，而操作外部工具又需要依賴執行 SQL SERVER 個體的帳號權限，可謂限制多多，但若不考慮權限限制又會有資安上的潛在問題。

如何拿捏則考驗 DBA 的智慧。

OLE Automation 可以算是三個選項中較為簡易的解決方案，就選擇是你了！


和你分享 ❤️


See also



沒有留言:

張貼留言