SQL Database 新功能剖析 - 透明資料加密與全文檢索搜尋

  • Article

Build 2015 大會推出了許多令人驚豔的新產品及新功能，其中包含 Microsoft Azure平台的 SQL Database（V12）服務，一口氣推出了透明資料加密（Transparent Data Encryption，TDE）、全文檢索搜尋（Full text Search）以及 SQL Database 彈性集區（SQL Database Elastic Pool）等三項新功能（截至本文撰寫為止，上述功能都尚在預覽階段）。

其中前兩項為內部部署的 SQL Server 早就內建的功能，以前只能在地端使用，從現在開始也可以在雲端的 SQL Database 使用透明資料加密來保護資料庫的安全，或是利用全文檢索搜尋讓使用者或應用程式可以針對字元為主的資料，執行全文檢索的查詢。而最後一項的 SQL Database 彈性集區則是一項全新的功能，用來簡化 SaaS 開發人員為有效控制預算及花費在建立、維護以及管理資料庫效能時所付出的成本。

本文假設您已經擁有 Microsoft Azure 訂用帳戶，並熟悉如何透過管理入口網站建立 SQL Database，下一節將直接將重點放在介紹如何在 SQL Database 使用透明資料加密及全文檢索搜尋，至於 SQL Database 彈性集區因涉及的內容較多，將在下一篇以專文來介紹。

透明資料加密（Transparent Data Encryption，TDE）

以往在內部部署的 SQL Server 要使用透明資料加密，必須經過一連串的步驟，從建立主要金鑰、建立受到主要金鑰保護的憑證、建立受憑證保護的資料庫加密金鑰，最後再啟用透明資料加密。

其架構如下圖所示：

除此之外，在將資料庫移轉到其他 SQL Server 時，必須連同加密憑證一起移轉，其中有些小技巧稍不注意就可能導致 SQL Server 拒絕使用該加密後的資料庫。

在 Microsoft Azur PaaS 平台簡化了在 SQL Database 使用透明資料加密的作業，提供三種設定透明資料加密的方式，第一種是使用預覽版 Azure 管理入口網站（截至本文撰寫為止，傳統入口網站尚未支援本功能）來做設定。

請登入預覽版 Azure 管理入口網站，點選欲啟用透明資料加密的 SQL Database，接著點選視窗上方的【設定】，在設定刀鋒視窗點選【 Transparent data encryption PREVIEW 】。

從透明資料加密刀鋒視窗可以看到預設資料加密選項為【關閉】，若您要啟用透明資料加密，只需點選【開啟】接著按上方【儲存】。

Microsoft Azure 便會開始為 SQL Database 加密該資料庫，從下圖紅色方框處可看到目前正在進行資料庫加密的作業。

當下方加密狀態呈現【已加密】就表示該 SQL Database 已經受到透明資料加密技術的保護。

是不是相當簡單？只需要按幾下滑鼠不須撰寫任何 T-SQL 指令碼就可以為資料庫進行透明資料加密。若要查詢資料庫加密的狀態，可以透過動態管理檢視 sys.dm_database_encryption_keys 來查看，目前資料庫的加密狀態以及相關加密金鑰資訊。

第二種啟用透明資料加密的方式為使用 T-SQL，但內部部署的 SQL Server 不同的是，您可以省略建立主要金鑰和憑證的步驟，直接建立資料庫加密金鑰（截至本文撰寫為止，SQL Database 支援的資料庫加密金鑰演算法只有AES_256）並且指定用來加密資料庫的加密金鑰之非對稱金鑰名稱為##ms_tdecertificate##，就可以輕鬆為資料庫進行加密保護。

下列 T-SQL 指令碼示範建立資料庫加密金鑰：

下列 T-SQL 指令碼示範啟用透明資料加密並查詢加密的狀態：

當然，若您要啟用透明資料加密的資料庫數量很多或是想要以自動化方式來完成，透過第三種方法 Azure PowerShell 會是一個比較方便有效率的做法。

在設定之前必須先執行 Switch-AzureMode -Name AzureResourceManager cmdlet 來切換為 Azure Resouce Manager 模組，接著便可以利用 Set-AzureResource cmdlet 來啟用或停用透明資料加密。

下列指令碼示範使用 Azure PowerShell 啟用透明資料加密。

Set-AzureResource -Name current -ApiVersion "2014-04-01-Preview" -ParentResource "servers/伺服器名稱/databases/資料庫名稱" -ResourceGroupName "資源群組名稱" –ResourceType "Microsoft.Sql/servers/databases/transparentDataEncryption" -PropertyObject @{status = "Enabled"}

下列指令碼示範使用 Azure PowerShell 停用透明資料加密。

Set-AzureResource -Name current -ApiVersion "2014-04-01-Preview" -ParentResource "servers/伺服器名稱/databases/資料庫名稱" -ResourceGroupName "資源群組名稱" –ResourceType "Microsoft.Sql/servers/databases/transparentDataEncryption" -PropertyObject @{status = "Disabled"}

除了使用匯出資料層應用程式產生 bacpac 檔案再匯入至另一台 SQL Database 或內部部署的 SQL Server，必須自行設定透明資料加密，並不會自動啟用加密功能之外，當啟用透明資料加密的 SQL Database 在進行異地備援、自助式時間點還原、還原已刪除的資料庫、主動異地備援或複製資料庫時，透明資料加密的設定會自動繼承到目標資料庫。

全文檢索搜尋（Full text Search）

全文檢索搜尋可以讓使用者或應用程式針對 SQL Server 資料表中以字元為基礎的資料（char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary(max)），執行全文檢索查詢。

Build 2015 大會正式宣布 SQL Database 開始支援和內部部署的 SQL Server 一樣的全文檢索搜尋功能，不論是 Basic、Standard 或 Premium 哪個服務層，只要將 SQL Database 升級到 V12 就可以使用這項功能來增進關鍵字為基礎（keyword-based）的搜尋體驗。

SQL Database 的全文檢索搜尋是 SQL Server 2014 全文檢索搜尋的子集合，既然是子集合就表示功能上還是稍微有一些限制，像是不支援安裝或使用協力廠商的篩選（Filters），無法也不需要自行管理篩選背景程式主機處理序（fdhost.exe）的設定和無法使用語意搜尋、同義字和搜尋屬性清單。

本文使用預覽版入口網站所提供的範例資料庫（AdventureWorksLT[V12]）來示範如何在 SQL Database 中使用全文檢索搜尋。

1. 建立全文檢索目錄

下列 T-SQL 敘述示範建立全文檢索目錄。

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT

GO

2. 建立全文檢索索引

下列 T-SQL 指令碼示範針對 SalesLT.Product 資料表的 Name 資料行建立全文檢索索引。

CREATE UNIQUE INDEX idx_Product ON SalesLT.Product(ProductID)

GO

 

CREATE FULLTEXT INDEX ON SalesLT.Product(Name) KEY INDEX idx_Product ON ftCatalog;

GO

3. 擴展（Population）全文檢索索引

下列 T-SQL 指令碼示範啟用 SalesLT.Product 資料表的全文檢索功能並開始建立和維護全文檢索索引。

ALTER FULLTEXT INDEX ON SalesLT.Product ENABLE

GO

 

ALTER FULLTEXT INDEX ON SalesLT.Product START FULL POPULATION

GO

4. 使用全文檢索述詞進行查詢

下列 T-SQL 指令碼示範使用 CONTAINS 來搜尋產品名稱包含 Red 和 48 的產品。

SELECT Name,StandardCost,ListPrice Price FROM SalesLT.Product

WHERE CONTAINS(Name, 'Red And 48')

GO

執行結果如下：

5. 監視全文檢索搜尋

下列 T-SQL 指令碼示範查詢全文檢索索引狀態以及其內容。

SELECT * FROM sys.dm_fts_index_population

SELECT * FROM sys.dm_fts_index_keywords( DB_ID('tde'), OBJECT_ID('SalesLT.Product'))

執行結果如下：

基本上在 SQL Database 和在內部部署的 SQL Server 使用全文檢索搜尋作法幾乎相同，而 Microsoft Azure 更提供全文檢索搜尋以外的搜尋服務— Azure 搜尋服務（Azure Search），用來讓開發人員很容易來建立具有更優質搜尋體驗的網站或行動應用程式，可以透過 indexers for Azure SQL Database 來與 SQL Database 整合，讓 Azure 搜尋可以搜尋存放在 SQL Database 中的資料。除此之外，Azure 搜尋服務支援許多不同的資料格式和來源，不僅限於 SQL Database。

一樣是具備搜尋功能，該如何選擇使用全文檢索搜尋或 Azure 搜尋服務，以下就搜尋範圍、功能、資料位置、縮放或成本等面向來比較這兩者有何差異（如下表）。

 

SQL Database 全文檢索搜尋

Azure 搜尋服務

資料位置

查詢以字元為基礎（character-based）的資料是存放在 SQL Database

搜尋的資料來自多種資料來源，不僅限於 SQL Database

移轉

移轉內部部署已經啟用全文檢索搜尋的資料庫到 SQL Database

N/A

縮放和效能

將全文檢索搜尋結果和交易資料存在相同地方，有利於與資料表或檢視聯結

基於縮放和效能的考量，可隔離搜尋與 OLTP 的工作負載

功能差異

全文檢索搜尋功能與 SQL Database 整合，不需支付使用額外服務的費用

提供網站或行動應用程式全面管理的搜尋服務，造就豐富的搜尋體驗，如客製化評分模型、自動完成建議、多面向搜尋（faceted search）或醒目提示（hit highlighting）

結論

隨著 SQL Database V12 的功能逐漸完整，越來越接近內部部署的 SQL Server，又能夠享有雲端服務的超大規模以及彈性等優勢，或許在未來您規劃關鍵任務應用系統的劃關聯式資料庫時，可以善用這項 PaaS 服務來達成業務連續性以及提高生產力。 

參考資料

1. Announcing Transparent Data Encryption for Azure SQL Database

2. TDE in Azure SQL Database

3. Transparent Data Encryption with Azure SQL Database

4. Full-Text Search is now available for preview in Azure SQL Database

5. Connecting Azure SQL Database to Azure Search Using Indexers

6. Full-Text Search

7. Transparent Data Encryption (TDE)

8. What's new in SQL Database V12