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 件格納されていることが確認できますね。
定義を変更したい場合には、以下のようなクエリを実行します。
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