Azure SQL Database では次のような操作を行うとプライマリからデータ同期を行い複製環境が作成されます。
上記の機能の中ではリソースのスケーリングが一般的に使用される機会が多いものかもしれませんね。
Azure SQL Database の性能を変更する場合には次の動作が行われます。
データベース用に新しいコンピューティング インスタンスを作成する
要求されたサービス レベルとコンピューティング サイズを持つ新しいコンピューティング インスタンスが作成されます。 サービス レベルとコンピューティング サイズの変更の組み合わせの中には、新しいコンピューティング インスタンスにデータベースのレプリカを作成する必要があるものがあります。これにはデータのコピーも含まれ、全体の待機時間に大きく影響する場合があります。 いずれにしても、この手順の間、データベースはオンラインのままで、接続は元のコンピューティング インスタンスのデータベースに引き続き向けられます。
変更後のサイズによっては、データベースのコピー機能が使用され新しいインスタンスにデータの同期が行われ、コピー完了後に切り替えを行うというような動作により、新しい環境の提供が行われます。
データの同期が行われる場合、データベースのサイズによって変更が完了するまでに必要となる時間が変動するのですが、この時間の予測を標準で提供されている機能より精度を高くして把握する方法について考えてみました。
標準で提供されている機能の問題
データ同期の進捗については、標準で進捗を確認するための機能が提供されています。
- sys.dm_operation_status
- Database Operations – List By Database
- Azure ポータルから取得されている情報はこの API 経由のものとなります。
これらの機能では、「percent_complete」という項目が提供されており、冒頭で記載したデータベースコピーの操作の完了状況 (進捗) を確認することができます。
しかし percent_complete ですがリアルタイムの進捗は返しておらず、次のような情報が返されています。
- 65% から数時間 % が更新されない
- 70% から急に 100% になる
- 65% から 41% に減少する
正直、この情報の精度だと、実際の進捗を把握することは難しいかと思います…。
精度の高い進捗の把握方法
どの情報を使用して進捗を把握するか？
実際に自分で使用してみるとわかるのですが、標準で提供されている機能では精度が低いというのは現時点では事実としてあるかと思います。
そこで、「どの程度データの同期が行われているのか」を「percent_complete」を使用せずに、精度を高くして算出することができないかを考えてみました。
冒頭に記載したデータ同期については、私が調べた範囲では、Always On 可用性グループの自動シード処理に近い処理が行われているように見えました。
自動シード処理もデータベースのコピーを保持するノードを追加する際に使用される機能なのですが、この機能による初期シードについては、データベースのデータファイルを読み取り、追加したノードにコピーするというような処理が行われています。
そこで、SQL Database でも同じような傾向の処理が行われていないのかを、データ同期中に情報を取得して確認してみたところ、次の情報を活用することができそうでした。
- sys.dm_io_virtual_file_stats (Transact-SQL)
- sys.dm_resource_governor_workload_groups_history_ex (Azure SQL Database)
「1.」はデータベースの各ファイルの I/O を調査する際に使用する DMF となっており、この情報を使用するとデータファイルの読み取り数を確認することができます。このデータファイルの読み取りがデータベースサイズになるとデータ同期が完了に近づいていると判断できます。しかし、データ同期中も他のセッションからデータファイルの読み取りが発生した場合は、この DMF でも集計されるため、ユーザーアクセスが停止していない状態だとかなり誤差が出る可能性があります。
ユーザーアクセスが停止している状態であれば「1.」を活用することができるのですが、ユーザーアクセスが発生している状態でも精度の高いデータファイルの読み取りサイズを把握することができないかを考えてみたところ「2.」の活用を思いつきました。
「2.」については、SQL Dataabse のワークグループ統計の定期的なスナップショットを取得することができる DMV となります。データ同期中のこの DMV の情報を分析してみたところ、データ同期については「SeedingGroup」というワークロードグループが使用されているようでした。
DMV では「delta_read_bytes」という情報が提供されており、この情報を確認することで該当のワークロードグループの処理で読み取られたバイト数を把握することができます。
ユーザーワークロードとデータ同期のワークロードはワークロードグループは異なるグループが使用されていますので、この情報をベースとした場合には「1.」で懸念となっていたユーザーアクセスによるデータファイルへのアクセスは除いた形で情報を把握することができます。
どのようにして進捗を把握するか？
私が調べた範囲では「データ同期はデータベースサイズ分のデータファイルの読み取りが発生している」というような挙動が行われていました。
「2.」の情報を活用する場合は「delta_read_bytes」の合計がデータベースファイルの使用サイズの合計に近づいたタイミングでデータ同期の終了も近くなっていると判断することができるのではないでしょうか？
そこで次のようなクエリを作成してみました。
set nocount on drop table if exists #progress select snapshot_time, duration_ms, delta_read_bytes into #progress from sys.dm_resource_governor_workload_groups_history_ex where name = 'SeedingGroup' and 1=0 order by snapshot_time ASC create clustered index cix_temp_progress on #progress(snapshot_time) insert into #progress select snapshot_time, duration_ms,delta_read_bytes from sys.dm_resource_governor_workload_groups_history_ex where name = 'SeedingGroup' declare @total_read_size bigint declare @total_used_size bigint = (select SUM(FILEPROPERTY(name, 'SpaceUsed')) * 8.0 / 1024 from sys.database_files where type = 0) declare @total_file_size bigint = (select SUM(size * 8.0 / 1024) from sys.database_files where type = 0) declare @read_rate_sec bigint declare @msg varchar(1000) while(0=0) begin insert into #progress select snapshot_time, duration_ms, delta_read_bytes from sys.dm_resource_governor_workload_groups_history_ex where name = 'SeedingGroup' and snapshot_time > (select max(snapshot_time) from #progress) SELECT @total_read_size = SUM(delta_read_bytes) FROM #progress select top 1 @read_rate_sec = delta_read_bytes / (duration_ms / 1000) from #progress order by snapshot_time desc SET @msg = FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') + ': ' + FORMAT(@read_rate_sec / POWER(1024.0,2), '#,##0.00') + ' MB/sec' + ' / total_read_size (MB): ' + FORMAT(@total_read_size / POWER(1024,2), '#,##0') + ' / total used size (MB): ' + FORMAT(@total_used_size, '#,##0') + ' / total file size (MB): ' + FORMAT(@total_file_size, '#,##0') raiserror (@msg, 0, 0) WITH NOWAIT waitfor delay '00:00:20' end
このクエリをデータ同期が発生する操作を実施したデータベースに対して SSMS で実行すると、「メッセージ」のタブで、次のような情報を取得できます。
2023-09-17 08:29:42: 0.00 MB/sec / total_read_size (MB): 0 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:30:02: 0.00 MB/sec / total_read_size (MB): 0 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:30:22: 0.00 MB/sec / total_read_size (MB): 0 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:30:42: 0.00 MB/sec / total_read_size (MB): 0 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:31:02: 0.00 MB/sec / total_read_size (MB): 0 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:31:22: 0.00 MB/sec / total_read_size (MB): 0 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:31:42: 36.50 MB/sec / total_read_size (MB): 730 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:32:02: 52.60 MB/sec / total_read_size (MB): 1,782 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:32:22: 53.50 MB/sec / total_read_size (MB): 2,852 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:32:42: 57.10 MB/sec / total_read_size (MB): 3,994 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:33:02: 54.20 MB/sec / total_read_size (MB): 5,078 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:33:22: 61.40 MB/sec / total_read_size (MB): 6,306 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:33:42: 66.10 MB/sec / total_read_size (MB): 7,628 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:34:02: 63.10 MB/sec / total_read_size (MB): 8,890 / total used size (MB): 166,453 / total file size (MB): 166,608 2023-09-17 08:34:22: 67.30 MB/sec / total_read_size (MB): 10,236 / total used size (MB): 166,453 / total file size (MB): 166,608
「sys.dm_resource_governor_workload_groups_history_ex」には次のような特徴があります。
- 基本的な間隔として 20 秒間隔でデータのスナップショットが生成される
- 最大で 128 スナップショット (約 42 分程度) の情報が格納される
- 一部の情報としては delta として前回のスナップショットからの差分が計算済みの状態で算出が行われている
データベースのサイズによっては操作が完了するまで数時間かかりますので、情報の蓄積を行いながら次のような観点で情報の取得を行っています。
- 「SeedingGroup」の最新のスナップショットを使用したデータの転送速度の算出
- 「SeedingGroup」で読み取られたデータサイズ
この情報を定期的にメッセージとして取得を行うことで、データ同期情報をニアリアルタイムに算出しています。私が検証していた限りでは、total_read_size (MB) が total used_size (MB) 近くになると、データの同期が完了に近づいていると判断することができていました。
本投稿を作成するためにサイズ変更を実施した際には、標準機能の percent_complete で情報を取得すると次のようになりました。
2023/09/17 10:18:58 2023/09/17 9:45:11 ALTER DATABASE 69 IN_PROGRESS 2023/09/17 10:19:03 2023/09/17 9:45:11 ALTER DATABASE 69 IN_PROGRESS 2023/09/17 10:19:08 2023/09/17 9:45:11 ALTER DATABASE 69 IN_PROGRESS 2023/09/17 10:19:13 2023/09/17 9:45:11 ALTER DATABASE 69 IN_PROGRESS 2023/09/17 10:19:18 2023/09/17 9:45:11 ALTER DATABASE 70 IN_PROGRESS 2023/09/17 10:19:48 2023/09/17 9:45:11 ALTER DATABASE 70 IN_PROGRESS 2023/09/17 10:19:54 2023/09/17 9:45:11 ALTER DATABASE 70 IN_PROGRESS 2023/09/17 10:20:19 2023/09/17 9:45:11 ALTER DATABASE 71 IN_PROGRESS 2023/09/17 10:20:24 2023/09/17 9:45:11 ALTER DATABASE 71 IN_PROGRESS 2023/09/17 10:20:59 2023/09/17 9:45:11 ALTER DATABASE 71 IN_PROGRESS 2023/09/17 10:21:04 2023/09/17 9:45:11 ALTER DATABASE 71 IN_PROGRESS 2023/09/17 10:21:09 2023/09/17 9:45:11 ALTER DATABASE 100 COMPLETED 2023/09/17 10:21:14 2023/09/17 9:45:11 ALTER DATABASE 100 COMPLETED
71% の次が 100% になっており、取得されている進捗の正確性が微妙です。
上述のクエリで同一の時間を取得したものが次になります。
(サイズ変更の最後のタイミングでプライマリの切り替えが発生するため、既存接続の切断の影響を受けています)
2023-09-17 10:18:58: 75.70 MB/sec / total_read_size (MB): 159,262 / total used size (MB): 166,454 / total file size (MB): 166,608 2023-09-17 10:19:18: 81.40 MB/sec / total_read_size (MB): 160,890 / total used size (MB): 166,454 / total file size (MB): 166,608 2023-09-17 10:19:38: 74.40 MB/sec / total_read_size (MB): 162,378 / total used size (MB): 166,454 / total file size (MB): 166,608 2023-09-17 10:19:58: 65.00 MB/sec / total_read_size (MB): 163,678 / total used size (MB): 166,454 / total file size (MB): 166,608 2023-09-17 10:20:18: 67.80 MB/sec / total_read_size (MB): 165,034 / total used size (MB): 166,454 / total file size (MB): 166,608 2023-09-17 10:20:38: 67.80 MB/sec / total_read_size (MB): 165,034 / total used size (MB): 166,454 / total file size (MB): 166,608 メッセージ 0、レベル 11、状態 0、行 0 現在のコマンドで重大なエラーが発生しました。結果があれば、破棄する必要があります。 メッセージ 0、レベル 20、状態 0、行 0 現在のコマンドで重大なエラーが発生しました。結果があれば、破棄する必要があります。
クエリで取得したほうでは同一の時間帯で 95% 以上のデータ同期が完了していることを把握することができており、標準機能で取得した場合と比較すると、精度が高く同期状態をを取得することができているのではないでしょうか。