SQL 2016 Stretch database-2

2018-04-07

延展資料庫 Stretch database 是SQL2016 開始提供一腳在雲端一腳在地端的的混合雲的資料庫解決方案，尤其針對地端空間或是運算能力不足的情境特別能夠派上用場。目前已經成功地應用在全世界的政府以及民間各種產業，解決外部稽核時硬體調度的困難、金融業重要交易資料的混合雲建置(又要符合法規的冗長保存期限又要防止磁帶還原失敗的風險)、政府重要政策資料的混合雲建置(又要兼具便民的隨時查詢需求又要解決線上資料庫的空間不足)、ISV廠商不須要變更查詢或應用程式即可移動資料而且延展資料在雲端會自動備份降低管理複雜性、維運部門夜間資料庫備份因為DB太大(為了遷就許多偶爾才會被查詢的歷史資料)常常備份失敗…等惱人問題

本篇是延續上一篇StretchDB的介紹，不討論精靈(Wizard也是在後面幫你下指令)，更進一步討論如何透過指令的方式，細部地操作雲/地的資料分配比重，以及查詢效能的優化。更詳細的指令操作，請參考官網說明。當然雲地間的資料是可以依照使用者的需求任意搬移，但最常見的情境是地端是線上的 Hot data，雲端是歷史的 Cold data，以下將按照這個情境來示範

A. Turn it on：就像水或電一樣會有一個總開關，但好在這個指令，是不需要重啟SQL Server就可以生效的

EXEC sp_configure 'remote data archive' , '1';  
GO

RECONFIGURE;  
GO

B. Operation：應用指令切換 StretchDB 啟動/終止，以及將延展資料搬上雲(Outbound)/投回地端(Inbound)/暫停(Paused)
ps.雖然這個功能叫StretchDB 但是它的單位是Table而非是DB，提供了更細緻與精準的資料顆粒度，所以你需要指定Table名稱

ALTER TABLE XXX
Set(REMOTE_DATA_ARCHIVE = ON/OFF 
   (MIGRATION_STATE = INBOUND/OUTBOUND/PAUSED))
GO

===================================================================
USE <database>; 
GO  

CREATE MASTER KEY ENCRYPTION BY PASSWORD='<password>'; 
GO

CREATE DATABASE SCOPED CREDENTIAL <db_scoped_credential_name>  
WITH IDENTITY = '<identity>' , SECRET = '<secret>' ;
GO

ALTER DATABASE <database name>  
    SET REMOTE_DATA_ARCHIVE = ON  
        (  
            SERVER = '<server_name>' ,  
            CREDENTIAL = <db_scoped_credential_name>  
        ) ;  
GO

若你還是卡住，要如何套用在你環境中的條件，我再給你一些靈感…
CREATE DATABASE SCOPED CREDENTIAL azure
WITH IDENTITY = 'sqladmin', SECRET = 'MyPassWord123'
SERVER = 'server_name.database.windows.net',
CREDENTIAL = azure

C.上述的指令，就是上一篇應用精靈幫你在背後完成的指令操作。一旦 enable StretchDB 預設是以 Outbound的方式把大部份的資料都上雲，搬移的筆數是每個批次以Replication 的機制搬移9999筆，以及一些在背景運行配套的措施，包含了：

External Data Sources物件(名稱為 StretchServer_DBM_唯一UID)
Linked Servers(repl_distributor, TCP_AzureSQLDBName;1433, CloudApp.Net…等物件做雲/地端的 Replicatoin)
Database master key(將來要還原延展資料庫會需要這個Key，例如資料庫意外毀損時…)

D. 若想要知道延展資料庫雲/地的資料分佈，除了用StretchDB 的Dashboard UI，也可以參考以下指令，了解更細節的資訊

EXEC sp_spaceused
@objname = N'[dbo].[TableName]', mode = 'REMOTE_ONLY', @oneresultset = 1
GO

EXEC sp_spaceused
@objname = N'[dbo].[TableName]', mode = 'LOCAL_ONLY', @oneresultset = 1
GO

E. 接下來我們將介紹 Filter/Block predicate 與自訂函數來自訂雲/地的資料分佈比率，其設計理念你可以參考SQL Server Partition 功能的分割函式概念。時間是最直覺易理解的，所以以下將透過時間為傳入參數實作一個Table-valued function格式的自訂函數，再加上 is_eligible 函式標記哪些資料可以被移動，以及 with SchemaBinding 防止資料被別人意外變更

CREATE OR ALTER FUNCTION dbo.fn_stretchpredicate(@CREATED_DATE datetime)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT 1 AS is_eligible WHERE @CREATED_DATE < CONVERT(datetime, '2014/1/1', 111)
GO

用Cross apply指令來比對，這批歷史資料是否可以被移轉至 AzureSQLDB

SELECT COUNT(*) as batch_number, is_eligible
FROM TableName CROSS APPLY fn_stretchpredicate(CREATED_DATE)
GROUP BY is_eligible
GO

執行搬移動作(將原來的指令加上 FILTER_PREDICATE = 自訂函數 fn_strechpredicate(時間) 即可)

ALTER TABLE
SET( REMOTE_DATA_ARCHIVE = ON
   ( FILTER_PREDICATE = dbo.fn_stretchpredicate(CREATED_DATE),
     MIGRATION_STATE = OUTBOUND))
GO

F.檢查 DMV中是否有套用使用者自訂函數，以及雲/地端的資料分佈是否有符合我們的預期？

SELECT * FROM sys.remote_data_archive_tables
GO

=============================================

SELECT COUNT(*) AS [大於 2014/1/1 之後資料量]
FROM xxx
WHERE CREATED_DATE >= CONVERT(datetime, '2014/1/1', 111)
GO

由於資料庫的查詢需要載入記憶體，所以隨著雲端的資料量愈大等待的時間也會愈久，但是比起你在年度稽核期間，去把封存(Archive)的資料從磁帶、硬碟櫃…等還原回來，StretchDB可以解決地端準備機器、空間、還原失敗、壞軌修復…等一大堆問題。
既然我們已經透過篩選函式將冷熱資料分別在雲地，我們的查詢是可以視狀況選擇範圍的，雲地一起查 or 只查詢地端(Hot data)。

首先是對信任連線的開關，由於預設的查詢模式是 LOCAL_AND_REMOTE，所以當你不想把這個混合雲 Table給別人查詢時，你可以下這個預存程序 sp_rda_deauthorize_db；若你要恢復給人查詢就是下 sp_rda_reauthorize_db
其次是查詢範圍，透過預存程序 sp_rda_set_query_mode LOCAL_ONLY 指令，你可以限縮只查詢地端的資料。sp_rda_set_query_mode DISABLED 等同於 sp_rda_deauthorize_db，以下是完整的預存程序(含參數)的用法說明

sp_rda_set_query_mode [ @mode = ] @mode [ , [ @force = ]  @force ]

其三是Schema維護，當你移除雲端的Column，只能用預存程序 sp_rda_reconcile_columns 將它補回去，但是刪除的資料並不會自動補回來，要人工去做
其四是索引維護，當你需要優化雲端的查詢，你可以透過預存程序同步一份地端的索引至雲端 sp_rda_reconcile_indexes
最後是連線測試 sys.sp_rda_test_connection 預存程序，可以協助你做故障排除

EXECUTE sys.sp_rda_test_connection  
   @database_name = N'db_name',   
   @server_address = N'azure_server_fully_qualified_address',  
   @azure_username = N'azure_username',   
   @azure_password = N'azure_password',  
   @credential_name = N'credential_name'

李秉錡 Christian Lee
Once worked at Microsoft Taiwan

回首頁