SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Azure の DMS で SQL Server の主キー無しテーブルのオンライン データ マイグレーションを実行する際の注意点

leave a comment

Azure の Data Migration Service (DMS) では、オンライン データ マイグレーションが実行できるようになり、投稿を書いている時点では、SQL Server から SQL Database への移行と、MySQL から Azure Database for MySQL への移行に対応しています。

SQL Server のデータ移行については、「主キーが設定されていないテーブルを CDC (変更データキャプチャ) で移行することができる」という特徴があるのですが、この機能を利用する際の動作について、注意点がありますのでまとめておきたいと思います。

この内容については、SR で確認をしたのですが、DMS の内部的な動作の制限のようで、現状記載がされている箇所がないので、ドキュメントへの反映を検討してくださるとのことでした。

注意点の内容ですが「主キーが設定されていないテーブルを移行する際に、初期データの移行と増分データの移行のタイミングによっては、データが重複されてしまう」という動作についてです。

初期のデータ移行をオフラインで実行できる / 初期同期が高速に行えるデータ量, 処理性能であればたぶん発生しないですが、大量のデータの初期同期や、SQL Database の性能の設定によっては発生する確率は高いかと。

CDC が設定されているテーブルについては、主キーが設定されていなくても DMS で移行対象として選択することができます。

今回は次のようなテーブルを移行元 (SQL Server 2017) として作成しています。

USE DemoDB
GO

DROP TABLE IF EXISTS CDCTable
CREATE TABLE CDCTable(
	C1 int IDENTITY,
	C2 nvarchar(100),
	C3 uniqueidentifier,
	C4 uniqueidentifier DEFAULT(NEWID()),
	C5 datetime2 DEFAULT(GETDATE())
)
GO
EXEC sys.sp_cdc_enable_db 
GO 
EXEC sys.sp_cdc_enable_table 
	@source_schema = N'dbo', 
	@source_name = N'CDCTable', 
	@role_name = NULL, 
	@supports_net_changes = 0
GO 
SET NOCOUNT ON
DECLARE @cnt int = 1
BEGIN TRAN
	WHILE (@cnt <= 10000)
	BEGIN
		INSERT INTO CDCTable(C2, C3) VALUES(NEWID(), NEWID())
		SET @cnt += 1
	END
COMMIT TRAN
GO 10
SELECT FORMAT(COUNT(*), '#,0') FROM CDCTable -- Count : 100,000

移行先 (SQL Database) は次のようなテーブルを作成します。

ちなみに移行先の SQL Database は Basic で作成しています。

高い DTU の SQL Database ですと、この現象は発生しない可能性があります。

DROP TABLE IF EXISTS CDCTable
CREATE TABLE CDCTable(
	C1 int IDENTITY,
	C2 nvarchar(100),
	C3 uniqueidentifier,
	C4 uniqueidentifier DEFAULT(NEWID()),
	C5 datetime2 DEFAULT(GETDATE())
)
GO

移行元 (SQL Server 2017) のテーブルについては、主キーは設定していませんが、CDC を設定しているため DMS の移行対象として選択することができます。

image

今回の事象を確認したい場合、DMS で移行を実行する前に、移行元の SQL Server で次のクエリを実行した状態にしてください。

USE DemoDB
GO
SELECT GETDATE()
WHILE(0=0)
BEGIN
	IF EXISTS(
		SELECT 
			s.session_id,
			s.last_request_start_time,
			s.last_request_end_time,
			at.name,
			s.host_name,
			s.program_name,
			st.transaction_id,
			st.open_transaction_count
		FROM 
			sys.dm_exec_sessions AS s 
			LEFT JOIN sys.dm_tran_session_transactions AS st
			ON st.session_id = s.session_id
			LEFT JOIN sys.dm_tran_active_transactions AS at
			ON at.transaction_id = st.transaction_id
		WHERE s.open_transaction_count > 0
		AND program_name = 'Azure Database Migration Service [Sync]'
	)
	BEGIN
		WAITFOR DELAY '00:02:00'
		SELECT GETDATE()
		SET NOCOUNT ON
		DECLARE @cnt int = 1
		BEGIN TRAN
			WHILE (@cnt <= 10000)
			BEGIN
				INSERT INTO CDCTable(C2, C3) VALUES(NEWID(), NEWID())
				SET @cnt += 1
			END
		COMMIT TRAN
		SELECT GETDATE()
		BREAK
	END
END
GO

 

このクエリを実行したら、DMS による移行を開始します。

しばらくすると、SQL Database に対して、初期のデータ移行と、上記のクエリで実施された「10,000 件」の増分データ移行が行われます。

最初に「100,000 件」のデータが登録された状態で、そのあとに「10,000 件」のデータを投入しましたので、「SQL Database」では「110,000 件」のデータが登録された状態になってほしいのですが、結果はどうなるでしょう。

SQL Database で移行完了後のデータを次のクエリで確認してみます。

SELECT FORMAT(COUNT(*), '#,0') FROM CDCTable WITH (NOLOCK)

image

「110,000 件」ではなく、「120,000」件、登録されていますね…。

これが本投稿の主題になるのですが、CDC によって移行を実行した場合、移行先で主キーが設定されていないテーブルに対して移行を実施すると、初期の全体の移行と増分データ同期のタイミングによっては、上述の結果のようにデータが重複して登録される可能性があります。

DMS の結果を見てみましょう。

image

image

全体で「110,000 件」のデータが移行され、増分データとして「10,000 件」のデータが移行されていますね。

移行元の SQL Database で重複データの状況を確認してみます

SELECT C1 ,COUNT(*) FROM CDCTable
GROUP BY C1 HAVING COUNT(*) > 1
ORDER BY C1

imageimage

「100,001 ~ 110,000」までのデータが重複して登録されていることが確認できますね。

DMS で移行を開始する前に SQL Server で実行していたクエリですが、

  • DMS のプロセスが SQL Server に接続された後に、SQL Database にデータの初期同期が完了する前に、新規のデータを投入する

というような動作を実行するためのクエリとなっており、絶妙なタイミングでデータの投入を行うためのものとなっています。

移行先に主キーが設定されている場合は、キー重複でエラーになってデータ重複は発生しないのですが、主キーが設定されていない場合は、データ重複して登録が行われてしまいます。

これについては、現時点のDMS の内部的な動作の制限らしく、現状のワークアラウンドとしては、

  • 主キーまたは、ユニークインデックスを設定する
  • 移行完了後に重複データを手動で削除する

というようなステップを踏まざるを得ないようです。

主キーが設定されているテーブルの移行であれば、データ重複は発生しないはずですが、CDC を使用している場合、データ重複の可能性を意識せざるを得ないのかと。

この辺の情報についてはドキュメントのアップデートを検討してくださるとのことでしたので、そのうち docs のドキュメントか、ブログあたりに情報が追加されるのではないでしょうか。

DMS については、2018/10/31 までは、無償で利用することができるはずですので、データマイグレーションを検討している方は、この機会に検証をされておいた方がよいのではないでしょうか。

Written by masayuki.ozawa

9月 9th, 2018 at 7:36 pm

Posted in SQL Database

Tagged with ,

Leave a Reply

*