SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2016 RC0 の Stretch Database を使用して Hot データと Cold データに分割する

leave a comment

SQL Server 2016 RC0 以前にもできたのかもしれませんが、先週気づきました。
Stretch Database が発表された際に、

  • ローカルに Hot データを格納
  • Azure に Cold データを格納

というような、説明がされることがありました。
そのようなデータを分割する方法をまとめてみたいと思います。

詳細については、以下を参照してください。

Stretch Database
Use a Filter Predicate to Select Rows to Migrate (Stretch Database)
Enable Stretch Database for a database

Hot データと Cold データの分割にはフィルター述語を指定して、テーブルのストレッチの設定を行います。

CREATE TABLE StretchTable (Col1 int, Col2 nvarchar(100))
GO

CREATE FUNCTION fn_stretchpredicate(@Col1 int)
RETURNS TABLE
WITH SCHEMABINDING 
AS 
RETURN	SELECT 1 AS is_eligible
		WHERE @Col1 <= 100
GO

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

「FILTER_PREDICATE」として、テーブル値関数を指定することで、どのデータを Azure 側にアーカイブするかを指定することができます。

上記のクエリであれば、Col1 が 100 以下のものが Azure 上に保存されることになります。

実際にデータを挿入して確認をしてみます。


SET NOCOUNT ON
GO
DECLARE @cnt int = 1
BEGIN TRAN
WHILE (@cnt <= 500)
BEGIN
	INSERT INTO StretchTable VALUES(@cnt, NEWID())
	SET @cnt += 1
END
COMMIT TRAN

EXEC sp_spaceused 'StretchTable', @mode='ALL'
EXEC sp_spaceused 'StretchTable', @mode='LOCAL_ONLY'
EXEC sp_spaceused 'StretchTable', @mode='REMOTE_ONLY'

こちらが実行結果となるのですが、リモートに 100 件格納されていることが確認できますね。

image

 

定義を変更したい場合には、以下のようなクエリを実行します。

CREATE FUNCTION fn_stretchpredicate2(@Col1 int)
RETURNS TABLE
WITH SCHEMABINDING 
AS 
RETURN	SELECT 1 AS is_eligible
		WHERE @Col1 <= 300
GO

ALTER TABLE StretchTable SET ( REMOTE_DATA_ARCHIVE = ON (
	FILTER_PREDICATE = dbo.fn_stretchpredicate2(Col1),
	MIGRATION_STATE = OUTBOUND
) )
GO

定義を変更する場合は、新しい関数を作成して、その関数をフィルター述語として再設定します。

条件は既に設定されている関数より、前のデータには変更することができませんので、先程の条件を変更するのであれば、100未満を条件として設定することはできません。

# 現状、すでに Azure へ移行済みのデータをフィルター述語を変更してローカルに戻すということはできません。

フィルター述語の実装により、Stretch Table の削除ができないという制限も一部緩和されています。

制限については、Surface area limitations and blocking issues for Stretch Database に記載されており、DELETE/UPDATE の制約としては以下のように記載されています。

You can’t run UPDATE or DELETE operations on a Stretch-enabled table.

フィルター述語を使用して、ローカルと Azure にデータを分散させている場合は、ローカルのデータについては削除/変更をすることができます。

-- ローカルに格納されているデータのため、削除/変更が可能
UPDATE StretchTable SET Col2 = NEWID() WHERE Col1 = 101
DELETE FROM  StretchTable  WHERE Col1 = 102

-- 移行済みデータのため、削除/変更ができない
UPDATE StretchTable SET Col2 = NEWID() WHERE Col1 = 100
DELETE FROM  StretchTable  WHERE Col1 = 100

CTP 3.3 までで触っていた機能のついても見直したほうがよさそうですね。

以下の動画が参考になります。

SQL Server Data Driven: Technical Deep Dive

Microsoft SQL Server 2016

Written by masayuki.ozawa

3月 15th, 2016 at 11:35 pm

Posted in SQL Server

Tagged with ,

Leave a Reply

*