SQL Server 2016ストレッチデータベース

6月1日はSQL Server 2016のリリースでした。これにより、「ホット」および「コールド」データをSQL ServerからAzureに動的に転送できる、長く発表されたStretch Databaseテクノロジーなど、おなじみの開発に多数のイノベーションが導入されました。



マーケティングの観点から、 Stretch Databaseは非常に広く公開されています。 合理的な議論が行われ、履歴データベースに蓄積されると、その操作の複雑さとコストが増加します。 そして、彼らは合理的な解決策を提案しました-「クラウド」で陳腐化するアーカイブデータの自動転送。 正直、私はこのアイデアが好きでした。



SQL Server 2016 RC0以降、開発を支援している2つのプロジェクトでStretch Databaseテクノロジのテストを開始しました。 1つ目はOLTP負荷を特徴とする稼働時間追跡ツールで、2つ目はDW負荷を伴う内部プロジェクトです。



簡単な例として、その仕組みを検討してください。 最初に行う必要があるのは、サーバーでこの機能がデフォルトで無効になっているため、 Stretch Databaseの使用を有効にすることです。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

サーバーを再起動する必要はありません。 次に、テストベースを作成します。

 USE [master] GO IF DB_ID('StretchDB') IS NOT NULL BEGIN ALTER DATABASE StretchDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE StretchDB END GO CREATE DATABASE StretchDB /* COLLATE Cyrillic_General_CI_AS */ GO USE StretchDB GO CREATE TABLE dbo.ErrorLog ( LogID INT IDENTITY PRIMARY KEY , PostTime DATETIME NOT NULL DEFAULT GETDATE() , UserName NVARCHAR(100) NOT NULL , ErrorMessage NVARCHAR(MAX) COLLATE Ukrainian_BIN NOT NULL ) GO INSERT INTO dbo.ErrorLog (UserName, ErrorMessage) VALUES (N'sergeys', N'Azure row')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、 SSMS 2016からStretch Databaseウィザードを呼び出します。







必要なStretch Databaseを正常に構成するために、事前に警告が表示されます。

• サーバーの管理者権限（すべてがsysadmin権限で行われました）
• アクティブなAzureサブスクリプション
• SQL Serverインターネットアクセスを許可する

ウィザードの次のステップでは、最初の失望に直面します。







Stretch Databaseテクノロジーの「一部」の制限により、テーブルからAzureにデータを移動できません。 主なものをリストします（ 太字で、証明書で考慮されていない制限を強調しました）：



Azureデータホスティングは、次のテーブルではサポートされていません。

• 1,023を超える列または998を超えるインデックスを含む
• FILESTREAMデータを含む
• レプリケーションに参加するか、 変更追跡または変更データキャプチャを使用する
• メモリ内の配置に最適化（ メモリ内テーブル）
• 常に列を暗号化しています

さらに、テーブルは以下を使用できません。

• すべてのユーザーデータ型（ CLR 、 ユーザー定義型 ）
• TEXT 、 NTEXT 、 IMAGE
• タイムスタンプ
• SQL_VARIANT
• XML
• 幾何学 、 地理学 、 ヒエラルキード
• SYSNAME
• COLUMN_SET
• COMPUTED列
• 列のシーケンス
• デフォルトおよびチェックの制約
• 全文検索 、 XMLおよび空間インデックス
• テーブルごとにインデックス表現がないはずです
• テーブルを参照する外部キー（たとえば、子のOrderDetailテーブルではStretchを有効にできますが、親ではOrderができなくなります）

判明したように、問題はDEFAULT定数にありました。 テーブルを再作成して再試行します。

 DROP TABLE IF EXISTS dbo.ErrorLog CREATE TABLE dbo.ErrorLog ( LogID INT IDENTITY PRIMARY KEY , PostTime DATETIME NOT NULL , UserName NVARCHAR(100) NOT NULL , ErrorMessage NVARCHAR(MAX) COLLATE Ukrainian_BIN NOT NULL ) GO INSERT INTO dbo.ErrorLog (PostTime, UserName, ErrorMessage) VALUES (GETDATE(), N'sergeys', N'Azure row')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これで、ウィザードでテーブルを選択できるようになりました。







ただし、次のように警告します 。 移行されたデータを含むAzureテーブルのUNIQUE制約とPRIMARY KEY制約には一意性が適用されません 。 このため、 StretchテーブルではIDENTITYフィールドでプライマリキーを作成するか、最悪の場合はUNIQUEIDENTIFIERを使用することをお勧めします。



次に、ログインしてAzureに SQL Serverを自動的に作成するよう招待されます。







Azure Portalで SQL Serverを手動で作成しましょう。







ウィザードで作成したサーバーを選択します。







マスターパスワードを設定しましょう。これは今後も必要です。







ファイアウォールに追加する必要がある例外を示します。







そして、ここで我々はフィニッシュラインにいます：







しかし、設定するとき、残念ながら、誰もヘルプに書いていないという別の制限に直面しています：







ログに次のエラーが表示されました。

 Configure Stretch on the Database StretchDB Status : 'Failed' Details : Task failed due to following error: Alter failed for Database 'StretchDB': 'Cyrillic_General_CI_AS' is not a supported collation. ALTER DATABASE statement failed.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

データベースを作成するときに、衝突を明示的に指定しなかったため、他の多くの製品と同様に、 Cyrillic_General_CI_ASはサポートされていませんでした。 テストの結果、データベースプロパティで照合が使用されている場合、 Stretch Databaseは機能しないことが判明しました。

• Windows照合 （ Latin1_General_100_CI_AS 、...）
• AIおよびBIN照合
• CS照合には選択的な問題があります

すべてが安定して機能するためには、 SQL Server照合のみを使用することをお勧めします 。 私はそれらをすべてチェックしませんでしたが、 SQL_Latin1_General_CP1_CI_ASを使用する場合 、問題はありませんでした。

 ALTER DATABASE StretchDB COLLATE SQL_Latin1_General_CP1_CI_AS
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

データベースのCOLLATEを変更した後、ウィザードを再起動して、次のように見ます：







次に、モニターを介してデータがAzureに移行する方法を追跡してみましょう。







または別のsys.dm_db_rda_migration_statusシステムビュー：

 SELECT * FROM sys.dm_db_rda_migration_status WHERE table_id = OBJECT_ID('dbo.ErrorLog') AND database_id = DB_ID()
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

RC3にバグがあり、列のCOLLATEがデータベースのCOLLATEと一致しない場合、データはAzureに転送されず、 sys.dm_db_rda_migration_statusは絶えず成長し、クリアされませんでした。



この例では、列の1つにCOLLATEがありますが、これはデータベースプロパティで設定されているものと一致しません。 このため、データ転送は定期的にエラーになります。

 migrated_rows start_time_utc end_time_utc error_number error_severity error_state -------------- -------------------- -------------------- ------------ -------------- ----------- 0 2016-06-15 15:44:41 2016-06-15 15:45:09 NULL NULL NULL 0 2016-06-15 15:45:16 2016-06-15 15:45:16 NULL NULL NULL 0 2016-06-15 15:45:16 2016-06-15 15:45:58 1205 13 55 0 2016-06-15 15:45:59 NULL NULL NULL NULL
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

しかし、そのようないくつかの失敗した試行の後、成功します：

 migrated_rows start_time_utc end_time_utc error_number error_severity error_state -------------- -------------------- -------------------- ------------ -------------- ----------- 0 2016-06-15 15:46:21 2016-06-15 15:46:21 NULL NULL NULL 1 2016-06-15 15:46:21 2016-06-15 15:46:27 NULL NULL NULL 0 2016-06-15 15:47:56 2016-06-15 15:47:56 NULL NULL NULL 0 2016-06-15 15:47:56 NULL NULL NULL NULL
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このことから、 RTMのバグは最後まで修正されていないと結論付けることができるため、すべての列にデータベースと同じCOLLATEを設定することが非常に望ましいです。



この時点で、 ストレッチテーブルを作成する方法の1つを試しました。 ところで、スクリプトはすべて非常に簡単かつ高速に実行されます。

 USE StretchDB GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'VasyaPupkin12' GO CREATE DATABASE SCOPED CREDENTIAL azure WITH IDENTITY = N'server_name', SECRET = N'VasyaPupkin12' GO ALTER DATABASE StretchDB SET REMOTE_DATA_ARCHIVE = ON ( SERVER = 'server_name.database.windows.net', CREDENTIAL = azure ) GO ALTER TABLE dbo.ErrorLog SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

主なことは、事前にAzureサーバーのアクセス許可を設定することです。







次に、テーブルでどのような変更が発生したかを見てみましょう...



データがテーブルに分類されるたびに、しばらくの間ローカルサーバーに物理的に配置され、その後自動的にAzureに転送されます。 これは、 SSMS 2016に実装されているLive Query Statistics機能を使用して簡単に確認できます。





5〜10秒後：







実行計画を見ると、 Stretch Databaseのすべての機能はもはや秘密ではありません。データをAzureに注ぐリンクサーバーと個別のコンポーネントが作成されます。



どのような制限がありますか：

• Stretch対応テーブルまたはStretch対応テーブルを含むビューで、移行された行、または移行に適格な行を更新または削除することはできません。
• リンクサーバー上のStretch対応テーブルに行を挿入することはできません。

テーブルにデータを挿入できるだけであることがわかります。



INSERT INTO dbo.ErrorLog（PostTime、UserName、ErrorMessage）

VALUES（GETDATE（）、N'sergeys '、N'Local row'）



DELETE、UPDATE、TRUNCATE操作はエラーで失敗するためです。



メッセージ14893、レベル16、状態1、行6

テーブル '...'は、REMOTE_DATA_ARCHIVEオプションが移行述語なしで有効になっているため、更新または削除ステートメントのターゲットにすることはできません。



ローカルマシン上にあるデータとAzureに移動できるデータを決定するフィルターを使用して、 ストレッチテーブルを作成してみましょう。



これを行うために、Azureからすべてのデータを返します。

 ALTER TABLE dbo.ErrorLog SET (REMOTE_DATA_ARCHIVE (MIGRATION_STATE = INBOUND))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このデータをクラウドに移行できるという兆候を保存する関数と列を作成します。

 CREATE FUNCTION dbo.fn_stretchpredicate(@IsOld BIT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS is_eligible WHERE @IsOld = 1 GO ALTER TABLE dbo.ErrorLog ADD IsOld BIT GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クラウド内のテーブルを再配置するときに、フィルターを指定します。

 ALTER TABLE dbo.ErrorLog SET ( REMOTE_DATA_ARCHIVE = ON ( FILTER_PREDICATE = dbo.fn_stretchpredicate(IsOld), MIGRATION_STATE = OUTBOUND ) )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

チームが働いた後、何が変わったのかを確認しようとします。 第一に、移行条件はまだ定められていないため、ローカルデータで何でもできます。 削除または更新：

 UPDATE dbo.ErrorLog SET IsOld = 0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Azureに選択的に移行する必要がある場合：

 UPDATE TOP(1) dbo.ErrorLog SET IsOld = 1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

覚えておくべき主なこと：すでにクラウドにあるものは、単純なUPDATEによって戻されなくなります：

 Msg 14875, Level 16, State 1, Line 14 DML operation failed because it would have affected one or more migrated (or migration-eligible) rows.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

データがどれだけのスペースを取るか見てみましょう：

 EXEC sys.sp_spaceused 'dbo.ErrorLog', @mode = 'LOCAL_ONLY' EXEC sys.sp_spaceused 'dbo.ErrorLog', @mode = 'REMOTE_ONLY'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 name rows reserved data index_size unused -------------- ----- ---------- ------ ------------- -------- dbo.ErrorLog 1 72 KB 8 KB 8 KB 56 KB name rows reserved data index_size unused -------------- ----- ---------- ------ ------------- -------- dbo.ErrorLog 1 144 KB 8 KB 24 KB 112 KB
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

それでは、 ストレッチテーブルの呼び出しをより効率的にするために、クエリでフィルターを使用してみましょう。

 SELECT * FROM dbo.ErrorLog WHERE IsOld = 0 SELECT * FROM dbo.ErrorLog WHERE IsOld = 1
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

実行プランを見ると、最初のケースでは、リンクサーバーへの接続を行う必要はありません。これは、桁違いに速く動作するはずです。







Stretch Databaseの使用が正当化されるオプションを本当に見つけましたか？ 実際には、すべてがそれほど良いわけではありません。バグにより両方のリクエストが同じようにゆっくりと動作するためです（ 2016年7月26 日更新 ：KB3174812を修正するには、 アップデート＃1をインストールする必要があります）。

 Table 'ErrorLog'. Scan count 1, logical reads 2, physical reads 0, .... SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1225 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. Table 'ErrorLog'. Scan count 1, logical reads 2, physical reads 0, .... SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1104 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Stretchテーブルへのアピールでは、 Azureへの接続があります。











この動作により、 Stretchテーブルを含むクエリはすべてスローダウンまたはタイムアウトします。 私が予備として残した制限は、上記のパフォーマンス問題の背景に対してとんでもないように思われます。

• Stretch対応テーブルを含むビューのインデックスを作成することはできません
• SQL Serverインデックスのフィルターはリモートテーブルに伝達されません

小さい結果



上で言ったように、その前に2つのプロジェクトでStretch Databaseを使用しようとしました。 トラッカーには、作業時間のセグメントを持つ大きなテーブルが住んでいました。 これへのアクセスは非常にアクティブであるため、 Stretch Databaseはパフォーマンスが低いため、単にルートになりませんでした。 彼らはこのテーブルのサイズと非常に単純に苦労しました：すべての外部キーを削除し、クラスターCOLUMNSTOREインデックスでテーブルを再作成しました（結果として、テーブルを12回圧縮しました）。



内部プロジェクトでは、セクション切り替えのトリックが役に立ちました。 ポイントは... 2つのテーブルが作成されます（ AおよびB ）。 テーブルAをアクティブに操作してから、セクションをBに切り替えます。これは、 ストレッチテーブルです。

 DROP TABLE IF EXISTS A GO CREATE TABLE A (val INT PRIMARY KEY) GO INSERT INTO A SELECT 1 GO DROP TABLE IF EXISTS B GO CREATE TABLE B (val INT PRIMARY KEY) GO ALTER TABLE B SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND)) GO ALTER TABLE A SWITCH TO B GO SELECT * FROM A SELECT * FROM B
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

実際、履歴データと運用データを区別しているため、パフォーマンスのバグをうまく回避できます（このような決定にはまだ欠点があります-セクションを空でないテーブルに切り替えると失敗します）。



大きな記事が出てきて読みにくいですが、 Stretch Databaseを使用して2、3か月で遭遇した問題はすべてここにあります。 このため、これらの作品が将来誰かに役立つことを願っています。 彼が何かでそれを補いたいという願望を持っているならば、私はコメントすることをうれしく思います。



すべてがMicrosoft SQL Server 2016（RTM）-13.0.1601.5（X64）でテストされました。



この記事を英語圏の聴衆と共有したい場合：

SQL Server 2016ストレッチデータベース