SQL Server から SQL Server ベースの環境へのマイグレーションに使用することができるツールとして、Database Migration Assistant (DMA) という無償のツールが提供されています。
このツールでは、SQL Server から SQL Database へのスキーマ移行 / データ移行をサポートしており、SQL Server の環境を SQL Database に移行する際に利用することができます。
このツールの中で、SQL Database にデータを移行する際には、どのような方法を使用しているのかが理解できると、SQL Server ベースの環境の移行に活用できるのではと思い、データ移行方法を調べてみました。
SQL Server から SQL Database へのデータ移行
ツールの利用方法自体は、ドキュメントに記載されていますので割愛しますが、SQL Server から SQL Database にデータ移行を行う際には、どのテーブルを移行するかを選択して移行を行うことができます。
「Start data migration」をクリックするとデータ移行が開始されます。
この裏では、どのような処理が行われ、データの移行が実施されているのかが本投稿の主題となります。
移行元からデータの取得
データの移行を開始すると、移行元となるSQL Server に対して、次のようなクエリが実行されます。
(@UpperBound int) SELECT "L_ORDERKEY","L_PARTKEY","L_SUPPKEY","L_LINENUMBER","L_QUANTITY", "L_EXTENDEDPRICE","L_DISCOUNT","L_TAX","L_RETURNFLAG","L_LINESTATUS", "L_SHIPDATE","L_COMMITDATE","L_RECEIPTDATE","L_SHIPINSTRUCT","L_SHIPMODE","L_COMMENT" FROM "tpch"."dbo"."LINEITEM" WHERE"L_ORDERKEY" <= @UpperBound ORDER BY "L_ORDERKEY" ASC, "L_LINENUMBER" ASC (@LowerBound int,@UpperBound int) SELECT "L_ORDERKEY","L_PARTKEY","L_SUPPKEY","L_LINENUMBER", "L_QUANTITY","L_EXTENDEDPRICE","L_DISCOUNT","L_TAX","L_RETURNFLAG","L_LINESTATUS", "L_SHIPDATE","L_COMMITDATE","L_RECEIPTDATE","L_SHIPINSTRUCT","L_SHIPMODE","L_COMMENT" FROM "tpch"."dbo"."LINEITEM" WHERE"L_ORDERKEY" > @LowerBound AND "L_ORDERKEY" <= @UpperBound ORDER BY "L_ORDERKEY" ASC, "L_LINENUMBER" ASC
上記のクエリを生成する前には、
- テーブルのスキーマ情報の取得
- キー項目の取得
- データ件数の取得
などが実施されているようです。
上記の情報を取得することで、エクスポート項目の取得 / データエクスポート時のソート順の指定 / 抽出データの分割を行うことができます。
データ移行を実施している最中の DMA から実行されているデータ取得のクエリは、次のようなクエリを実行することで確認ができます。
WITH XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select
*,
query_plan.value('(//ParameterList/ColumnReference[@Column="@LowerBound"]/@ParameterRuntimeValue)[1]', 'nvarchar(100)') AS lowerbound,
query_plan.value('(//ParameterList/ColumnReference[@Column="@UpperBound"]/@ParameterRuntimeValue)[1]', 'nvarchar(100)') AS upperbound
from(
select er.session_id, er.status, es.program_name, st.text, x.query_plan
from sys.dm_exec_requests AS er
inner join sys.dm_exec_sessions as es on es.session_id = er.session_id
outer apply sys.dm_exec_sql_text(sql_handle) AS st
outer apply sys.dm_exec_query_statistics_xml(er.session_id) AS x
where command = 'SELECT' and es.program_name = '.Net SqlClient Data Provider' and er.session_id <> @@spid
) AS T
order by lowerbound
「LowerBound」「UpperBound」を指定して、一定のサイズでデータ範囲を区切った SELECT が 4 並列で実行されていることが確認できます。
これが、「移行用データの取得部分」となります。
移行用データについては DataTable にデータの取得が行われています。
テーブル全体のデータではなく、ある程度分割して DataTable にデータの取得を行っていますので、テーブル全体のデータサイズのメモリは必要としませんが、数 GB のメモリは使用しますので大量のデータを移行する場合、DMA を実行する環境のメモリサイズについても注意する必要があります。
移行先へのデータ投入
移行元のデータ取得については、テーブルのデータをある程度の件数で分割して取得していることが確認できました。
それでは、データ投入についてはどのようになっているでしょうか。
データの投入については Bulk Insert で実行されています。
これについては、移行先で次のようなクエリを実行することで情報を取得することができます。
select * from sys.dm_exec_requests outer apply sys.dm_exec_sql_text(sql_handle) where command = 'BULK INSERT' and session_id <> @@spid
実際には次のようなクエリで Bulk Insert が実行されています。
insert bulk "MigrationTest"."dbo"."LINEITEM" ([L_ORDERKEY] Int, [L_PARTKEY] Int, [L_SUPPKEY] Int, [L_LINENUMBER] Int, [L_QUANTITY] Decimal(15,2), [L_EXTENDEDPRICE] Decimal(15,2), [L_DISCOUNT] Decimal(15,2), [L_TAX] Decimal(15,2), [L_RETURNFLAG] NVarChar(1) COLLATE Japanese_XJIS_140_CI_AS_UTF8, [L_LINESTATUS] NVarChar(1) COLLATE Japanese_XJIS_140_CI_AS_UTF8, [L_SHIPDATE] Date, [L_COMMITDATE] Date, [L_RECEIPTDATE] Date, [L_SHIPINSTRUCT] NVarChar(25) COLLATE Japanese_XJIS_140_CI_AS_UTF8, [L_SHIPMODE] NVarChar(10) COLLATE Japanese_XJIS_140_CI_AS_UTF8, [L_COMMENT] NVarChar(44) COLLATE Japanese_XJIS_140_CI_AS_UTF8) with (KEEP_NULLS)
Bulk Insert については、SqlBulkCopy.WriteToServerAsync メソッド が使用されています。
この時に投入されるデータについては、先ほどの DataTable で取得された内容となります。
移行先のデータ投入のための Bulk Insert については、SqlBulkCopy の WriteToServer によって実施されますので、4 並列でデータの取得が行われていた場合は、4 並列で Bulk Insert が実行された状態となります。
まとめ
DMA を使用した SQL Database へのデータ移行は、一定のサイズで分割したデータを取得した DataTable を SqlBulkCopy を使用して Bulk Insert することによって実現されています。
一つの DataTable に格納されるデータは絞られていますが、並列で実行されるため、数 GB のメモリは必要となります。
- 主キーの項目を判断して、データを自動的に分割して取得する
- 分割したデータを Bulk Insert で移行する
という考え方は、データ移行をする際の基本的なアプローチとして有効なものです。
データ移行ツールがどのようにしてデータの移行を行っているのかを理解するのは、効率的なデータ移行方法を検討する際に重要ではないでしょうか。