SQL Azure

SQL Azure Database では、データ圧縮(行やPAGE圧縮)をすることができます。
この辺りは、ムッシュの「SQL Database で行/ページ圧縮が利用可能になったようです」で紹介されています。
今回紹介するのは地味につらいけど、あまりちゃんと説明されていない部分の注意点になります。

MSDNで「Data Compression」でツラツラ書かれている中で次のような注意事項があります。

行またはページの圧縮を有効または無効にするために必要なディスク空き容量は、インデックスを作成または再構築するために必要なディスク空き容量と同じです。

とてもしれっと、さりげなく書いていますが、とても重要なのです。
つまり、ストレージに十分な空き容量がないとデータ圧縮を有効にできません。

では十分な空き容量とはなんでしょうか?
対象となるのは、「デーベースファイル」と「トランザクションログファイル」の2つです。
それぞれで十分な空き容量が必要となります。

クラスタ化インデックスを作成するときに必要なディスク領域については、「インデックスのディスク領域の例」と「インデックス操作用のトランザクション ログのディスク領域」で解説されています。

オンラインでのクラスタ化インデックス作成(SORT_IN_TEMPDB=ONに設定されたオンラインインデックスの操作)だと、
元のテーブルが約363MBだとすると
作業中に必要な領域の合計サイズは1058MBで、
作成されたインデックスサイズは453MBになります。
作業途中では約2倍の空き領域が必要に(3倍じゃないのは1058MBにはもともとの容量が含まれているので)なりますね。
ただしTEMPDBが含まれているのでAzure SQL Databaseの場合には、
242MBが含まれるので810MBです。オリジナルの1.3倍ぐらいの追加空き容量があればOK。

さて、トランザクションログはというと「インデックス操作を確実にロールバックできるようにするには、インデックス操作が完了するまでトランザクション ログを切り捨てることができません。」や「SORT_IN_TEMPDB オプションを ON に設定することを検討します。この設定により、インデックス トランザクションが同時実行のユーザー トランザクションから分離されます。インデックス トランザクションは、tempdb データベースのトランザクション ログに格納され、」と書かれています。
SORT_IN_TEMPDBオプションは既定ではOFFなのでクエリで明示的に指定しなければOFFです。

ここがあまり理解できていないのですが、対象のデータベースでページ圧縮以外のトランザクションがなくてもトランザクションログは必要で、必要な容量はおそらくディスクデータ領域の数倍かと思われます。

どうやるのがベスト?

データ圧縮を有効にするには、データ容量とトランザクション容量の両方が必要になることを確認しました。

データ容量については、まぁ仕方ないので増やして対応しましょう。
しかし、トランザクションログだけは制限が入ってるので、対策をする必要があります。
Azure SQL Database の P15 でトランザクションログが1TB、P2でトランザクションログが340GBとなっています。そのため、100GBを超えるテーブルを圧縮するには確実に足りません。

そこで利用するのが、「再開可能なオンラインインデックス再構築」です。

再開可能なオンラインインデックス再構築のポイント

  • 実行中は、更新系クエリに一桁パーセントのオバーヘッドがかかるかも。(参考
  • インデックス再構築の障害からの回復 (データベースのフェールオーバーやディスク領域の不足など)。参考
  • インデックス再構築操作の間はトランザクション ログの切り捨てを有効にします (通常のオンライン インデックス操作に対してこの操作を実行することはできません)。
    • つまり、途中で止めてトランザクションログバックアップがされればトランザクションログが切り捨てされて幸せに!
    • 再開可能な再構築では実行時間の長いトランザクションを開いたままにする必要はなく、この操作の間のログの切り捨てと、より優れたログ領域管理が可能です。
    •  新しい設計では、必要なデータを、再開可能な操作を再開するために必要なすべての参照と共に、データベースに保持しています。
  • 再構築中にReconfigurationが発生してもフェールオーバー後に途中から再開可能。
  • 再構築中の進捗状況がクエリで取得可能。

テーブル圧縮とインデックス圧縮について

ALTER TABLEとALTER INDEXそれぞれでデータ圧縮ができますが、それぞれにどんな意味があって、挙動が違うのでしょうか。
それを知る手掛かりが、MSDNの「圧縮されたテーブルおよびインデックスの作成」で説明されています。

圧縮できる対象のデータベースオブジェクトが次のものです。

  • ヒープとして格納されているテーブル全体。
  • クラスター化インデックスとして格納されているテーブル全体。
  • 非クラスター化インデックス全体。
  • インデックス付きビュー全体。
  • パーティション分割されているテーブルおよびインデックスの場合、パーティションごとに圧縮オプションを構成することができ、オブジェクトの各パーティションを同じ圧縮設定にする必要がありません。

つまり、ヒープテーブルへのALTER TABLEと、非ヒープテーブルのクラスター化インデックスへのALTER INDEXが同じ意味を持っていそうです。

Let’s Try

ムッシュがすでに「SQL Database で再開可能なオンラインインデックス再構築が Public Preview で利用可能となりました」で紹介されています。

そこから学ぶと以下のクエリでよさそうですね。

— 再開可能なオンラインインデックス再構築
ALTER INDEX [PK_T1] ON [dbo].[T1] REBUILD PARTITION = ALL
WITH (RESUMABLE = ON, ONLINE = ON, MAX_DURATION = 1, DATA_COMPRESSION = PAGE)

azure

Azure VM 上で、Active Directory を構成して、ドメインコントローラーなどを立てた時に、うっかりするとデフォルトのセキュリティ設定では、ユーザーパスワードが期限切れをしてしまいます。
しかも悲しいことに、ローカルアドミン、ドメインアドミン両方で発生します。
そんな時の対処方法について説明します。

「Your password has expired and must be changed.」てエラーが表示されるのですが変更する方法がわからず辛い。
Azure Portalでパスワードをリセットしようとしても、「VMAccess Extension does not support Domain Controller」とエラーになります。

仕方ないので、スクリプトを実行する方法で対応します。
Azure Portalで、VM Extensionを使って、スクリプトをアップロードして実行します。
スクリプトで、パスワードをリセットします。

スクリプトは次のような感じ。
“` reset.ps1
net user adminユーザー名 新しいパスワード(複雑性に注意)
“`

Azureポータルで、VMメニューからextensionを選択し、AddからCustom Script extensionを選びます。
reset.ps1を選びます。
しばらくすると、CustomScriptExtensionに「Provisioning succeeded」と表示されて完了します。
めでたしめでたし。

噂によると、スクリプト作成に失敗することがあり、その時にはblobアカウントが必要で求められているアカウントを作成してからスクリプトを作ったらうまくいくとか。

SQL Azure

特徴

SQL Serverと100%に近い互換性があり、ネイティブでVNETに対応しています。
オンプレからクラウドへの移行を容易にします。
PaaSと同じインフラで、全てのPaaSの機能(自動バッチ、バージョンアップ、バックアップ、高可用性)を提供します。

・オンプレミスのSQL Server 2005から最新バージョンまで互換性があリます。
(バックポートして互換性を持たせたので、SQL Server 2005以降全てのバックアップファイルをリストアできます)
・通常のSQL Serverのリストアに対応
・SQL Agent
・SQL Profiler
・ログシッピング
・トランザクションレプリケーション
・データベースをまたぐクエリ
・Database Mail
・Service Brocker
・SQL CLR
・SSIS(もうまもなく)
・99.99% SLA
・自動バックアップ
・ポイントタイムリストア

細かい情報

プロパティ 備考
@@VERSION Microsoft SQL Azure (RTM) – 12.0.2000.8 2018-03-07 Copyright (C) 2018 Microsoft Corporation. SQL Databaseと同じ
SERVERPROPERTY (‘Edition’) SQL Azure SQL Databaseと同じ
SERVERPROPERTY(‘EngineEdition’) Managed Instance専用
@@SERVERNAME, SERVERPROPERTY (‘ServerName’) フォーマット:..database.windows.net 例:my-managed-instance.wcus17662feb9ce98.database.windows.net

Azure Database Migration Service(preview)

SQL Server からAzure SQL Database Managed Instanceに簡単に移行できるように設計されています。
DocuSignもこれを使って移行しました。

サービス帯

Public Preview中は、General Purposeが提供されます。
基本的な可用性、共通的なIOレイテンシーようです。

・基本的な性能要求と可用性
・Azure Premium Storage(8TB)
・1インスタンス100DB
・8/16/24 vCore
・ストレージは32GB〜8TB
・500-7500IOPS/1データファイル
・1データベースのデータファイル数は複数
・ログファイルのデータファイルは1つ
・自動バックアップ
・可用性は、リモートストレージとAzure Service Fabric
・インスタンスとデータベースのモニタリングと計測
・自動パッチ
・監査ログ
・ポータル対応

メトリックス画面

監査ログと脅威分析

vCoreについて

vCore(Virtual Core)は、論理CPUで、2世代のハードウェアで提供されます。
・4世代:Intel E5-2673 v3 (Haswell) 2.4 GHz プロセッサー
・5世代:Intel E5-2673 v4 (Broadwell) 2.3 GHz プロセッサー

vCore、ストレージサイズのスケールアップダウンが必要に応じてできるようになっています。
(ポータルから設定できますが、ダウンタイムなどがあるのかは確認が必要ですねぇ)
データベースファルは全て分離されたPremiumストレージ上に配置されます。

オンプレミスとの違い

詳細な機能比較は、「こちら

・可用性は組み込みで予め設定されています。Always On 可用性機能はSQL IaaS実装と同じ方法では公開されません。
・自動バックアップ、ポイントタイムリストア。バックアップチェインに影響与えない、コピー専用バックアップを出力できます。
・物理パスの指定ができません。Bulk insertはAzure Blobのみの対応です。
・Windows認証の代わりにAzure AD認証を提供します
・インメモリOLTPオブジェクトが含まれるXTPファイルグループとファイルは自動で管理します。

バックアップ

・Azure Blob認証

“`
CREATE CREDENTIAL [https://myacc.blob.core.windows.net/testcontainer]
WITH IDENTITY=’SHARED ACCESS SIGNATURE’
, SECRET = ‘sv=2014-02-14&sr=c&sig=GV0T9y%2B9%2F9%2FLEIipmuVee3jvYj4WpvBblo%3D&se=2019-06-01T00%2A%3AZ&sp=rwdl’;
“`

・コピー専用バックアップ

“`
BACKUP DATABASE tpcc2501
TO URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501.bak’
WITH COPY_ONLY
“`

・ストライプバックアップ
Blobは200GBのサイズ制限があります。次のようにしてファイルを分割してバックアップします。

“`
BACKUP DATABASE tpcc2501
TO URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-1.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-2.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-3.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-4.bak’
WITH COPY_ONLY
“`

・MAXTRANSFERSIZE
大きなデータベースの時には、MAXTRANSFERSIZE=4194304を指定するといいでしょう。
1ファイルあたり、48GBを超えないようにする。
COMPRESSIONで圧縮して帯域を節約します。
CHECKSUM or STATS = で正しくバックアップできたか確認してもいいでしょう。

“`
BACKUP DATABASE tpcc2501
TO URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-1.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-2.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-3.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-4.bak’
WITH COPY_ONLY, MAXTRANSFERSIZE = 4194304, COMPRESSION
“`

価格

Preview版のお値段については、「ここ」に記載があります。
目的別に2種類のサービス帯が提供さえる予定なのですが、現時点では一般的なワークロード用のGeneral Purposeのみが提供されています。

「BASE RATE PRICE WITH AZURE HYBRID BENEFIT (% SAVINGS)」は、SQL Serverのライセンスを持っていて有効なSA権を持っている場合に、ライセンスをクラウドに持ち込んだ場合の価格です。
ですので、通常は「LICENSE INCLUDED PREVIEW PRICE」を選ぶことになります。

24vCoreを選択すると、東日本リージョンで375.44円/時間かかり、1日9,010円、1ヶ月279,327円かかります。
ストレージが最初の32GBがvCore代金に含まれていて、以後月1GB毎に7.73円かかり、最大8TBまでいけます。1TBで月7915円。
バックアップデータは月1GB毎に5.6円かかるので、月3000円。
IO100万毎に13.44円なので、50,000IOPSで、月58060円。

月間、高くても35万円あたりでしょうか。
プレビュー中はNW転送量に課金されません。

バックアップ費用の見積もり方

利用しているサーバーストレージサイズと同じ容量までは追加費用がかかりません。
それを超えると費用が加算されます。
100GBのデータベースの場合、100GBのバックアップまでは無料、110GBの場合は10GBが費用発生します。
2018/6/30まではバックアップ費用は発生しません。

提供されているリージョン

20リージョンで提供されています。
Canada Central
Canada East
Central US
East Asia
East US
East US 2
Japan East
Korea Central
Korea South
North Central US
North Europe
South Central US
South India
Southeast Asia
UK South
West Central US
West Europe
West India
West US
West US 2

azure

2018年2月に開催されたTokyo Jazug Nightで登壇しました。
テーマは、http://github.com/azure で公開されているリポジトリについてでした。

AzureVM

書きかけ

AzureのIaaS を使用して、 SQL Serverを運用したい時に見るべき資料が散逸しているようなので整理してみたいと思います。
AzureのIaaS、つまり Azure Virtual Machine (Azure VM)を使用して、SQL Serverを構築し、可用性担保のためにAlwaysON可用性グループを構築、チューニングをするための資料となります。

資料としては、次の内容が把握できると、構築して、運用設計して、性能チューニングして、運用・監視して、障害対応とIaaSでのSQL Server運用に関して必要なことが網羅できていることになります。

  1. ライセンスのお話
  2. 構築し終わると完成するインフラ環境と登場するコンポーネントのお話
  3. 構築手順のお話
  4. 性能チューニングのお話
  5. 運用(バックアップ設計、ログ管理)のお話
  6. 性能監視、障害監視のお話
  7. AlwaysONのお話
  8. 障害対応(フェイルオーバー)のお話
  9. 障害対応(そのほか)のお話

1. ライセンスのお話

AzureでSQL Serverを運用するには、「ライセンス付きのAzureVMのイメージを使う」か「ライセンスをクラウドに持ち込む」かのどちらかになります。

  • 「ライセンス付きのAzureVMのイメージを使う」
    仮想マシンのイメージ費用にSQL Serverのライセンス費が付加されていて、追加費用が不要となっています。SQL Server付きのイメージでない他のイメージでは、その付加がされていないので、「ライセンスをクラウドに持ち込む」が必要になります。
    SQL Server ライセンス付きのAzure VMイメージ一覧にあるイメージを使うとOKです。
    ちなみに、そのイメージを使って起動した仮想マシン上であれば、SQL Serverをインストールし直してもライセンスはそのまま有効になっています。つまり、イメージでは英語版のSQL Serverがインストールされていますが、インストールし直して日本語版を利用することができます。利用方法は、ムッシュBlog(ライセンスの話 / Agentの話)を参照してください。
  • 「ライセンスをクラウドに持ち込む」
    すでに持っている(もしくはこれから買う)SQL ServerのライセンスをAzure上で使用するには、SA契約のライセンスモビリティ権を使用することになるのでSA契約が別途必要になります。
    そのあたりの説明は、SQL Server公式ブログ「既存の SQL Server ライセンスを使用し、Azure 仮想マシン上に SQL Server をインストールするためには。」でも説明されています。

4. 性能チューニングのお話

構築が完了したら次に実施する必要があるのが性能チューニングです。

  • ストレージの設定を見直す必要があります。検証中
  • メモリ:最大値と最小値を固定にします
  • tempdbにデータファイルを追加し10個にし、それぞれ2GBにします
  • トランザクションログファイルは1個で、ログファイルサイズを必要な十分なサイズ(30GB?)にします。
  • データベースファイルを10個にし、それぞれの容量を1000GBにします。(将来的な自動拡張の防止)
    バックアップなどから復元した後、データファイルを追加した時は、データを平準化させるために再構築が必要です。
  • 起動パラメターを設定し、起動時にメモリを確保するように設定します。Lock Page in Memory
  • トランザクションログとtempdbをデータベースファイル以外に配置することを検討します。