SQL Server on Azure VM で実行している SQL Server では、SQL Server IaaS Agent 拡張機能 を使用することができます。
この拡張機能を使用することで、Azure VM (仮想マシン) 上で実行している SQL Server の運用の利便性を向上させることができ、今回、話題にする「自動バックアップ機能」につても、利便性を向上させることができる機能の一つとなっています。
この自動バックアップ機能について、少し調べる機会がありましたので、自動バックアップ機能の構成についてまとめておきたいと思います。
Contents
自動バックアップ機能を使用するためには
自動バックアップ機能については、次の情報が公式ドキュメントとなります。
- SQL Server 2014 仮想マシンの自動バックアップ (Resource Manager)
- Azure Virtual Machines の自動バックアップ v2 (Resource Manager)
機能自体は、SQL Server 2014 Standard Edition / Enterprise Edition 以降で使用することができます。
内部では SQL Server エージェントの機能が使用されていますため、エージェントをサポートしていない Express Edition では、残念ながら利用することができません。
SQL Server 2014 と SQL Server 2016 以降では、自動バックアップで使用される SQL Server の機能が異なっているため、SQL Server 2016 以降は、Azure VM 自動バックアップ v2 というように、2014 とは異なるバージョンで実装が行われています。
今回対象としているのはこの v2 の方です。
自動バックアップの機能は、SQL Server IaaS Agent 拡張機能により実現されており、ポータルのブレードとしては、「SQL 仮想マシン」のリソースの「バックアップ」から設定を行うことができます。
自動バックアップを実行するためには「SQL Server IaaS Agent 拡張機能」が必要であり、現在、この拡張機能は「SQL VM リソースプロバイダー」に登録することで、VM に対してインストールが行われます。
登録の方法ついては、次のドキュメント公開されています。
Marketplace の SQL Server インストール済みイメージを展開したのであれば、自動的に登録が行われるため、明示的な作業は不要ですが、SQL Server をセルフインストールした環境や、Marketplace の SQL Server インストール済みイメージの SQL Server を再インストールした環境では、再登録する必要があります。
SQL Server を再インストールする理由で多いものは「SQL Server を日本語バージョンで利用する」ケースではないでしょうか。
この時の再登録方法については、本ブログでまとめたものがありますので、その投稿をご確認ください。
ポイントは次の内容です。
- 自動バックアップを使用するためには、SQL Server IaaS Agent 拡張機能のフル管理モードが必要となる
- 現在、SQL Server IaaS Agent 拡張機能を利用するためには、SQL VM リソースプロバイダーに仮想マシンを登録する必要がある
自動バックアップの構成を把握する
自動バックアップの機能は、SQL Server IaaS Agent 拡張機能経由で設定を行うことができます。
自動バックアップの機能ですが、Azure 固有の機能のみで実装されているのではありません。
最終的にバックアップを取得する機能は、SQL Server の機能を使用しており、自動バックアップの設定については、SQL Server の機能の設定を行うためのインタフェースとして使用されています。
基本的な構成としては次のようになります。
Azure VM には、基本的に Azure 仮想マシンエージェント が導入されて (セルフインストールしたイメージであれば手動でインストール) います。
IaaS Agent 拡張機能は、仮想マシンエージェントのプラグインとして機能が追加されています。
このプラグインがインストールされると、「C:\Program Files\Microsoft SQL Server IaaS Agent」に IaaS Agent についてのプログラムがインストールされ、これらのファイルで SQL Serer IaaS エージェントが構成されます。
IaaS Agent をフル管理モードでインストールした場合、このプラグインは SQL Server に対してクエリを実行できる設定となっています。
具体的には、「Microsoft SQL Server IaaS Query Service (SqlIaaSExtensionQuery)」というサービスが、インストールされます。
このサービスの説明は次のようになっています。
Helper service for Microsoft SQL Server IaaS Agent that runs SQL queries against SQL Server on IaaS Virtual Machine.
サービスは、SQL Server IaaS エージェントが SQL Server に対してクエリを実行するための、ヘルパーサービスとして動作します。
どのようにクエリを実行しているかというと、フル管理モードでインストールされた IaaS Agent 拡張機能では、上記のサービスのサービスアカウントが sysadmin の権限を持つログインとして作成された状態となります。 
これにより、IaaS Agent 拡張機能が SQL Server に対して管理者権限で操作ができるようになっています。
IaaS Agent 拡張機能をフル管理モードでインストールする際のドキュメントには、次の記載があります。
SQL Server VM をフル モードで登録すると、SQL Server サービスが再起動されます。 注意して進めてください。
SqlIaaSExtensionQuery が sysadmin の権限を持つログインとして作成するために、裏では、SQL Server のインスタンスをシングルユーザーモードで再起動して、ログインを作るというような方法がとられているためです。
(SQL Server のインスタンスをシングルユーザーモードで起動して、Local System 権限でログインしてログイン作成のスクリプトを実行するというような仕組みだったはずです)
このような構成が取られることで、Azure は、VM Agent 経由で SQL Server の状態 / 設定変更が可能となります。
SQL Server を再インストールした環境では、master データベースの内容も初期化されており、SqlIaaSExtensionQuery サービスが SQL Server に接続できない状態となっています。
SQL Server IaaS Agent 拡張機能をフル管理モードでインストールすることで、サービスが SQL Server に接続できる状態になります。
この状態にするために SQL VM リソースプロバイダーへの再登録を行い、SQL Server IaaS Agent 拡張機能の再インストールを行う必要があるということですね。
IaaS Agent 拡張機能で、SQL Server 寄りの設定がうまく反映されていない場合などは、SqlIaaSExtensionQuery サービスから実行されるクエリがどのような実行状況となっているかを取得 / トレースすることで、問題解決につながるケースがあります。
これで SQL Server に対して各種ステートメントを実行することができる環境となり、実際の自動バックアップによるバックアップの取得は、SQL Server の機能により取得が行われます。
この際に使用される機能が Microsoft Azure への SQL Server マネージド バックアップ です。
Azure の自動バックアップは、SQL Server マネージドバックアップ (Managed Backup / 管理対象バックアップ) の設定を行うためのインタフェースであり、実際のバックアップの取得は、マネージドバックアップの機能により行われます。
そのため、自動バックアップのトラブルシューティングには、SQL Server マネージドバックアップの機能も抑えておく必要があります。
(余談ですが、SQL Server 2014 では、Managed Backup ではなく、Smart Admin Backup と呼ばれていたため、今でも内部的には一部 SmartAdmin/SmartBackupという記載があるかと)
SQL Server マネージドバックアップ (管理対象バックアップ)
自動バックアップは、Azure ポータル / API で設定した自動バックアップの設定内容を、SQL Server マネージドバックアップの設定として投入し、自動的なバックアップを設定する機能です。
そのため、自動バックアップの機能を使用する場合には、SQL Server マネージドバックアップの機能を把握しておく必要があります。
公式のドキュメントは Microsoft Azure への SQL Server マネージド バックアップ になり、バックアップの基本動作については、このドキュメントから確認することができます。
本投稿ではトラブルシューティング等で、現在、どのような設定が行われているかを確認する必要がある際のポイントをまとめておきたいと思います。
マネージドバックアップの公式ドキュメントとしては、次のドキュメント (ならびに、ドキュメントツリー) を確認してください。
- Microsoft Azure への SQL Server マネージド バックアップ
- Azure への SQL Server マネージド バックアップを有効にする
- Microsoft Azure への SQL Server マネージド バックアップの詳細設定オプションの構成
BLOB の接続情報
マネージドバックアップは Azure BLOB ストレージに対して、バックアップを取得する機能となります。
BLOB の接続方法については、SAS (Shared Access Signature) が使用されており、SAS を使用した資格情報を SQL Server に登録する必要があります。
Azure から自動バックアップを設定する際には、取得先のストレージアカウントを設定しますが、設定したストレージアカウント向けの資格情報が自動的に SQL Server に登録されるようになっています。
資格情報については、次のクエリで確認できます。
select * from sys.credentials
バックアップの取得先のコンテナーですが「backupcontainer」となり、このコンテナー向けの資格情報が登録されます。
コンテナーについても自動で作成されますので、明示的に backupcontainer を作成する必要ないはずです。
資格情報で設定された SAS の詳細については、登録後に確認することはできませんが、設定時のクエリをトレースすることでどのような設定で登録を行っているかは確認することができます。
実際には、次にように資格情報を作成するためのストアドプロシージャが一時的に作成され、自動的に生成された SAS Token による資格情報が登録される仕組みとなっています。
CREATE PROCEDURE sp_autobackup_v2_create_credential_new @url nvarchar(max), @sasKey nvarchar(max)
AS BEGIN
DECLARE @Query NVARCHAR(MAX)
SET @Query = 'IF EXISTS (SELECT * FROM sys.credentials WHERE NAME = ' + QUOTENAME(@url, '''') + ') DROP CREDENTIAL ' + QUOTENAME(@url) +
'CREATE CREDENTIAL ['+ @url +'] WITH IDENTITY = ''Shared Access Signature'', secret = ' + QUOTENAME(@sasKey, '''')
EXEC (@Query)
END
GO
exec sp_autobackup_v2_create_credential_new
@url=N'https://xxxxxxxxxxxxxx.blob.core.windows.net/backupcontainer',
@sasKey=N'sv=2018-03-28&sr=c&si=backuppolicy_RWL&sig=xxxxxxxxxxxxx'
マネージドバックアップの BLOB ストレージへの接続は上記で登録された資格情報によって実施されます。
マネージバックアップのバックアップ設定
マネージドバックアップを操作するためには、Components に記載されているストアドプロシージャを使用することになります。
マネージドバックアップで使用される情報 / ストアドプロシージャは msdb に格納されています。
マネージドバックアップは、いくつかの処理では、SQL Server エージェントのジョブの機能が使用されており、動作させるためには SQL Server エージェントが動作している必要があります。
msdb には、ストアドプロシージャ以外に「autoadmin」で始まるテーブルが提供されており、バックアップの設定については次のようなクエリで確認ができます。
use msdb select am.autoadmin_id, am.task_agent_guid, am.last_modified, md.db_id, md.db_guid, md.db_name, bc.ManagedBackupVersion, bc.IsEnabled, bc.IsAlwaysOn, bc.IsDropped, bc.RetentionPeriod, bc.SchedulingOption, bc.DayOfWeek, am.task_agent_data from dbo.autoadmin_task_agent_metadata as am LEFT JOIN dbo.autoadmin_managed_databases AS md ON am.autoadmin_id = md.autoadmin_id LEFT JOIN dbo.autoadmin_backup_configurations AS bc ON bc.db_id = md.db_id order by md.autoadmin_id ASC, md.db_id ASC
主要な設定は「dbo.autoadmin_task_agent_metadata」というテーブルに格納されています。
autoadmin_id =0 のレコードがインスタンスレベルの設定となります。
マネージドバックアップは DB 単位でも設定することができますので、各 DB 単位でどのような設定を行っているかもこの情報から確認できます。
IaaS Agent 拡張機能で設定を行った場合、インスタンスレベルでマネージドバックアップを有効にするのではなく、IaaS Agent が定期的に DB の状態をチェックしながら、新規に作成された DB を DB レベルで設定を有効化するような動きをしているように見えますね。
トラブルシューティング時にポイントとなるのが 「autoadmin_id =0 」のレコードです。
Managed backup error: Auto-admin data table is possibly corrupt でも解説されているのですが、マネージドバックアップの設定情報が想定しない状態になっていると、次のようなエラーが発生するケースがあります。
The operation failed because of an internal error. Auto-admin data table is possibly corrupt. Could not locate task agent global metadata in database Command: smartbackup configure_backup_basic 0 Please retry later.
このようなエラーが発生した場合、SQL Server エージェントのサービスを再起動してみてください。
autoadmin_task_agent_metadata の autoadmin_id =0 のレコードですが、SQL Server エージェントの起動時に処理が行われているようで、データの生成や読み取りがエージェントサービスの起動時に実行されているようです。
そのため、上記のエラーが発生した場合は、サービスを再起動することで、正常なレコードを生成できるようです。
他にも SAS のエラーが出たときにもこのサービスの再起動を有効に使用することができるようです。
何度か、有効化 / 無効化を実施していたときに、SAS でコンテナーにアクセスできないというようなエラーが出たことがありました。
その時は、
truncate table msdb.dbo.autoadmin_task_agent_metadata
で、一度、メタデータをクリーンアップし、SQL Server エージェントの再起動を行うことで、メタデータを初期状態で再生成させることで、エラーを解消することができました。
「dbo.autoadmin_task_agent_metadata」からコードを削除すると、マネージドバックアップの設定は初期化され、Azure の自動バックアップの設定も無効状態になりますが、状態を初期化することができます。
予期せぬエラーが発生し、回避ができない状態になった場合は、
- SQL Server エージェントサービスの再起動
- 「dbo.autoadmin_task_agent_metadata」 のレコードを削除し、SQL Server エージェントサービスを再起動
の 2 パターンは覚えておくと良いのではないでしょうか。
SQL Server IaaS Agent 拡張機能については、自動バックアップ以外にもいくつかの機能がありますが、基本的には OS / SQL Server の機能を活用しながら、自動化を実施しているものとなります。
どのような機能を使用しているかを把握することは、機能を活用する際のポイントになるのではないでしょうか。
自動バックアップについては、マネージドバックアップ (管理対象バックアップ) をベースとした実装となっていますので、自動バックアップの機能を活用する際には、この機能の情報を確認すると良いかと。