次の方法で共有

SQL Server 2016 新功能搶先看 - Stretch Database 詳解

  • [アーティクル]

SQL Server 2016 搶先看又來囉！

回顧系列專文：　

1. SQL Server 2016 新功能搶先看 - Stretch Database / Managed Backup (內含 Demo 畫面)

2. SQL Server 2016 新功能搶先看 - Live Query Statistics / JSON

3. SQL Server 2016 新功能搶先看 - 動態資料遮罩 / 多重 tempdb 資料檔

　

本文將帶大家了解 SQL Server 2016 的 Stretch Database 功能：　

說明

Stretch Database 是 SQL Server 2016 另一個混合雲應用的新利器，讓企業可以把需要封存的歷史資料，透明且安全的（transparently and securely）延展（stretch）到Microsoft Azure，進而享有雲端平台的高度彈性及容易擴展等優點，還可以省去過去為了保有這些資料所需要採購的儲存設備預算。

使用企業級的雲端資料庫（Azure SQL Database）來保存這些平常很少用卻又很重要的歷史資料，不須大費周章的自行把資料搬到 SQL Database ， SQL Server 2016 會在背景幫您將資料移轉到 Azure SQL Database，並保證不會有資料遺失的情況發生，藉由動態管理檢視（Dynamic Management View）也能夠查看移轉資料的狀況，若需要進行疑難排解也可以隨時暫停（或恢復）資料移轉的作業。

值得一提的是，使用 Stretch Database 後，不需修改任何現有的查詢或應用程式，就可以無縫的同時存取本地和遠在雲端上的資料，對於那些可能連原始程式碼都找不到的老舊應用程式，在資料延展到雲端後依然可以正常運作。除了 Azure SQL Database 訂閱的費用外，其他需付出的成本大概只有查詢遠端資料時會有的延遲及耗用網路頻寬。

對 DBA 而言，當資料庫所需的儲存空間不足時，採購儲存設備又曠日廢時的情況下，直接把大型資料表延展到 Azure SQL Database ，立刻就能降低資料表大小，進而降低存取本地端資料所需的 IO 。另外一項附帶的好處是，在資料表啟用 Stretch 功能時，會被設定為唯讀的狀態，您可以藉此保護資料被不小心的修改或刪除；換言之，建議將 OLTP 所需使用的資料表和歷史資料表獨立出來，只針對存放歷史資料的資料表才去啟用Stretch功能。

截至本文撰寫為止，SQL Server 2016 尚在 CTP2 階段，有關 Stretch Database 功能，請以未來所發佈的內容為準，而接下來將為您介紹如何啟用和管理 Stretch Database 。

 

準備工作

啟用 Stretch Database 可以將需要封存的歷史資料表，延伸到 Azure SQL Database 來存放。因此，您必須準備 Microsoft Azure 訂用帳戶。

接下來，在本機伺服器（local Server）執行 sp_configure 來啟用 remote data archive 功能。

指令碼及執行結果如下：

--列出remote data archive 設定目前的內容

EXEC sp_configure 'remote data archive'

GO

 

--啟用remote data archive

EXEC sp_configure 'remote data archive' , '1'

GO

 

--更新config_value所變更的組態選項

RECONFIGURE

GO

 

--再次列出目前的組態

EXEC sp_configure 'remote data archive'

GO

啟用資料庫的 Stretch 功能

開啟 SQL Server Management Studio（SSMS），於物件總管點選想要啟用Stretch的資料庫（必須具備 CONTROL DATABASE 及 db_owner 權限），按滑鼠右鍵選擇【工作 > 啟用資料庫的 Stretch 功能】。

 

在【簡介】步驟最重要的資訊就是告訴您，啟用資料庫的 Stretch 功能後，才可以啟用資料表的 Stretch 功能，若沒問題請按下一步繼續。

 

在【Microsoft Azure 登入】步驟，點選【登入（S）...】來登入 Microsoft Azure。

 

請在【登入 Microsoft SQL Server】對話視窗，輸入具有建立 Azure SQL Database 的 Azure 訂用帳戶的帳號和密碼。

 

若您有多個 Azure 訂用帳戶，在成功連接 Microsoft Azure 後，可以選擇所要使用的訂閱，接著按下一步繼續。

 

目前使用 GUI 的方式啟用 Stretch Database ，每次都需建立新的 Azure SQL Database 伺服器，因此在【Stretch設定】步驟，須輸入下列資訊，輸入完畢後按下一步繼續。

  • Azure 位置：選擇要將Stretch Database延伸到哪個資料中心的Azure SQL Database伺服器。
  • 建立 Azure SQL DB 伺服器認證：輸入用來管理 Azure SQL Database 伺服器的系統管理員登入和密碼。
  • 將防火牆規則設定為連接到 Azure：為安全性考量，預設 Azure SQL Database 伺服器不允許任何外部連線，因此必須為 Database Engine 執行個體的對外 IP 位址，建立 Azure SQL Database 伺服器的防火牆規則；您可以選擇只開放目前的 IP 位址，或自訂一段範圍的 IP 位址。

 

最後在【摘要】的步驟您會看到啟用資料庫的 Stretch 功能的相關設定資訊，若沒問題則按完成來開始啟用資料庫的 Stretch 功能。

 

稍待一會，若一切順利您將會看到如下圖的結果。

 

若要停用資料庫的 Stretch 功能，目前僅支援以 T-SQL ，您可以使用下列指令碼來停用資料庫的 Stretch 功能：

ALTER DATABASE <已啟用Stretch功能的資料庫名稱>

SET REMOTE_DATA_ARCHIVE = OFF

 

當您啟用資料庫的 Stretch 功能，精靈幫您做了許多事情，包含：

  • 建立連接 Azure SQL Database的認證（Credential），認證名稱與 Azure SQL Database 伺服器名稱相同，識別和密碼則是與該伺服器的系統管理員密碼相同。

  • 建立連結的伺服器，用來讓您查詢啟用 Stretch 的資料表時，能同時取的本機伺服器與 Azure SQL Database 的資料。

 

如此一來，當查詢啟用 Stretch 功能的資料表時，就能自動指向本機與遠端的資料，並將查詢結果回傳（如下圖），感覺不太出來所查詢的資料來自於兩個不同的位置。

 

而從執行計畫中可以看到遠端查詢的運算子，代表您所查詢的資料表有部分資料是透過連結的伺服器從 Azure SQL Database 取回來的。

 

基於安全考量，拒絕自行透過連結的伺服器來查詢位於 Azure SQL Database 上的遠端資料。

 

除了使用 SQL Server Management Studio 的 GUI 可以啟用資料庫的Stretch功能外，您也可以透過 T-SQL 或 PowerShell 來達到相同目的，唯一的差別是必須先自行建立認證和 Azure SQL Database 伺服器。

下列範例示範以 T-SQL 來啟用資料庫的 Stretch 功能：

ALTER DATABASE <欲啟用Stretch功能的資料庫名稱>

SET REMOTE_DATA_ARCHIVE = ON (SERVER = Azure SQL Database伺服器名稱)

 

啟用資料表的 Stretch 功能

啟用資料庫的 Stretch 功能之後，Database Engine 並不知道那些資料表需要延展到 Azure SQL Database。因此，在需要【啟用資料表的 Stretch 功能】的資料表上按滑鼠右鍵（必須具備 ALTER 及 db_owner 權限），選擇【Stretch > 啟用 Stretch】。

 

此時會出現詢問是否確定要啟用資料表的 Stretch 功能，按【是】即可啟用該資料表的 Stretch 功能。

 

目前啟用 Stretch 的資料表仍有諸多限制，例如該資料表不能有 Foreign Key、Check 或預設值等條件約束，或是不支援 FileTables 或記憶體最佳化資料表等資料表類型，另外還有部分資料類型不支援（如 XML、tempstamp 等）。

若該資料表違反這些限制，就會發生如下圖的錯誤，而導致啟用 Stretch 功能失敗。

 

此外，您也可以利用 T-SQL 來啟用資料表的 Stretch 功能。例如下列的 T-SQL 指令碼：

ALTER TABLE <欲啟用Stretch功能的資料表名稱>

ENABLE REMOTE_DATA_ARCHIVE WITH ( MIGRATION_STATE = ON )

 

當您順利啟用資料表的 Stretch 功能後，若需要進行問題排除或是暫時停止移轉資料到 Azure SQL Database 所耗用的網路頻寬，可以暫停 Stretch 功能的運作，只要在已經啟用 Stretch 功能的資料表上按滑鼠右鍵，選擇【Stretch > 暫停Stretch】即可。

 

接著按【是】來確定暫停資料表的 Stretch 功能。

 

暫停資料表的 Stretch 功能也可以利用 T-SQL 來完成，例如下列的 T-SQL 指令碼：

ALTER TABLE <已啟用Stretch功能的資料表>

ENABLE REMOTE_DATA_ARCHIVE WITH ( MIGRATION_STATE = OFF )

若要恢復資料表的 Stretch 功能，則是點選【恢復 Stretch】，並在出現確認的對話視窗後按【是】來恢復 Stretch 功能。

 

或是您也可以透過 T-SQL 指令碼來恢復資料表的 Stretch 功能，例如下列的 T-SQL 指令碼：

ALTER TABLE <已啟用Stretch功能的資料表>

ENABLE REMOTE_DATA_ARCHIVE WITH ( MIGRATION_STATE = ON )

 

眼尖的讀者應該會發現，不管資料表的 Stretch 狀態為何，停用 Stretch 的選項都是保持停用無法點選的狀態，目前停用資料表的 Stretch 功能的步驟建議如下：

1. 暫停資料表的 Stretch 功能。

2. 建立與要停用 Stretch 資料表相同結構描述的新資料表。

3. 使用 INSERT INTO…SELECT FROM 來複製資料。

4. 移除啟用 Stretch 功能的資料表。

5. 將2所建立的資料表名稱改為和4的資料表名稱。

一旦啟用資料表的 Stretch 功能，Database Engine 便會在背景開始將資料搬移到 Azure SQL Database，透過查詢 sys.dm_db_rda_migration_status 動態管理檢視，可看到有多少筆資料已經被移轉到 Azure SQL Database，以及花了多少時間等資訊，若移轉過程發生錯誤也可以在這裡看的到。

 

管理啟用 Stretch 功能的資料庫和資料表

當執行個體中有多個資料庫（或資料表）啟用 Stretch 功能，該如何快速列出這些資料庫（或資料表）？

sys.databases和sys.tables系統目錄檢視有您所需要的資訊。

下列 T-SQL 指令碼示範如何列出啟用 Stretch 功能的資料庫與資料表。

--列出啟用Stretch功能的資料庫

SELECT name,is_remote_data_archive_enabled

FROM sys.databases

WHERE is_remote_data_archive_enabled = 1

 

--列出啟用Stretch功能的資料表

SELECT name,is_remote_data_archive_enabled,

remote_data_archive_migration_state,remote_data_archive_migration_state_desc

FROM sys.tables

WHERE is_remote_data_archive_enabled = 1

 

執行結果如下圖所示：

再者，若要列出本機的啟用 Stretch 功能所對應的遠端資料庫與資料表，則可查詢 sys.remote_data_archive_databases 和sys.remote_data_archive_tables 系統目錄檢視。

下列 T-SQL 指令碼示範如何列出遠端資料庫與資料表等資訊。

--列出遠端資料庫的相關資訊

SELECT * FROM sys.remote_data_archive_databases

 

--列出遠端資料表的相關資訊

SELECT remote_table_name,b.name as local_table_name

FROM sys.remote_data_archive_tables a

JOIN sys.tables b ON a.object_id = b.object_id

執行結果如下圖所示：

 

若要得知本機伺服器與遠端端點（Remote Endpoint）的資料筆數及使用空間大小，可以透過 sp_spaceused 系統預存程序搭配 @mode 參數來查詢。

下列指令碼示範查詢啟用 Stretch 功能的資料表（CustomersStretch），分別在遠端/本機的資料筆數及使用的儲存空間大小。

--列出遠端端點所使用的儲存空間大小

exec sp_spaceused @objname = N'CustomersStretch',@mode = 'remote_only'

--列出本機伺服器所使用的儲存空間大小

exec sp_spaceused @objname = N'CustomersStretch',@mode = 'local_only'

 

備份和還原啟用 Stretch 的資料庫

由於啟用資料庫及資料表的 Stretch 功能後，部分資料會被移轉到 Azure SQL Database，因此進行資料庫備份時，只會備份尚在本機伺服器中的資料，遠端資料並不會被備份，但 Stretch Database 的設定資訊會被備份下來，好讓您在還原資料庫時能夠很快速的恢復相關 Stretch 的設定。

還原資料庫時不需重新建立相關的認證與連結的伺服器，但必須執行 sys.sp_reauthorize_remote_data_archive 系統預存程序來恢復與 Azure SQL Database 的連線。

 

下列指令碼示範如何在還原資料庫後與 Azure SQL Database 恢復連線，以讓原本啟用 Stretch 功能的資料庫與資料表能夠繼續運作。

EXEC sys.sp_reauthorize_remote_data_archive

@azure_username = N'Azure SQL Database伺服器系統管理員名稱',

@azure_password = N'Azure SQL Database伺服器系統管理員密碼'

執行結果如下圖所示：

Comments

  • Anonymous
    July 06, 2015
    SQL Server 2016 搶先看， 這次要帶大家認識 Always Encrypted 新功能！

    回顧系列專文：　
    1. SQL Server 2016 新功能搶先看 - Stretch
  • Anonymous
    July 14, 2015
    SQL Server 2016 搶先看，這次要帶大家認識 Temporal Tables 新功能！
    回顧系列專文：　

    SQL Server 2016 新功能搶先看 - Stretch
  • Anonymous
    July 27, 2015
    The comment has been removed
  • Anonymous
    August 07, 2015
    The comment has been removed
  • Anonymous
    September 24, 2015
    The comment has been removed
  • Anonymous
    September 29, 2015
    The comment has been removed