SQL ServerからTiDBへの移行で試行錯誤した話

SQL Ser ver からTiDBへの移行 で試行錯誤した話 株式会社アイスタイル 岩﨑

自己紹介 岩﨑航大（@k_dai0915） # 所属 株式会社アイスタイル T&C開発センター 第1開発本部クラウドソリューション部 サービスインフラグループ DBREチーム # 主な業務内容 データベース構築・運用、クエリレビュー、クラウド移行など # 好きなPingCapロゴ入りノベルティ サーモステンレスマグ ©︎ istyle Inc. No.1

今日お話しすること 1. アイスタイルにおけるTiDBの活用について 2. SQL ServerからTiDBへデータ移行する際に苦労したこと 3. 異機種間のデータ移行を通して学んだこと ©︎ istyle Inc. No.2

1. アイスタイルにおける TiDBの活用について ©︎ istyle Inc. No.3

アイスタイルでのTiDBの活用 現在、アイスタイルでは大きく2つの利用用途でTiDBを活用しています その①：レガシーSQL Serverの移行先としての活用 古くからオンプレミスで運用している Microsoft SQL ServerをTiDBへ移行する取 り組み その②：MySQL系のDBの統合 リソース効率・管理効率の向上を目的として 社内で運用している複数のMySQLサーバーを TiDBクラスターに統合する取り組み ©︎ istyle Inc. No.4

アイスタイルでのTiDBの活用 現在、アイスタイルでは大きく2つの利用用途でTiDBを活用しています その①：レガシーSQL Serverの移行先としての活用 古くからオンプレミスで運用している Microsoft SQL ServerをTiDBへ移行する取 り組み その②：MySQL系のDBの統合 リソース効率・管理効率の向上を目的として社内で運用 している複数のMySQLサーバーをTiDBクラスターに統 合する取り組み ©︎ istyle Inc. No.5

アイスタイルではSQL Server→TiDB Cloudへの移行にチャレンジ中です 6

SQL ServerをTiDB Cloudに載せ替える理由 • SQL Serverの便利機能を活用し続けた結果、長い時間をかけて複雑に依存し合った巨大な DBが出来上がっていた • 全社的にクラウド化の流れが来ていた • TiDB Cloudであれば複雑な依存関係を保ったままクラウド移行できる 詳しくは下記をご覧ください @cosmeがレガシーシステムをクラウドネイティブDBに載せ替える理由 - TiDB User Day 2022 /istyle session https://speakerdeck.com/pingcap0315/istyle-session ©︎ istyle Inc. No.7

• https://speakerdeck.com/pingcap0315/istyle-session

データ 今回はSQL ServerからTiDB Cloudへのデータ移行で苦労した点を 紹介させていただきます 8

2. SQL ServerからTiDB へデータ移行する際に 苦労したこと ©︎ istyle Inc. No.9

SQL Server → TiDB Cloudデータ移行概要 データ移行を実現するため、下記リソースを利用しました。 データ移行に加えて、切り戻しのためのデータの書き戻しも実施しました。 • AWS DMS(Data Migration Service) • TiCDC • Aurora MySQL AWS Database Migration Service (AWS DMS) AWS Database Migration Service (AWS DMS) Amazon Aurora ©︎ istyle Inc. No.10

SQL Server → TiDB Cloudデータ移行時の課題（一部） データ移行を実施した際、様々な課題が発生しました。 課題 詳細 DMSデータ移行において計算列があるテー ブルの移行に失敗する DMSのデータ移行で移行元のSQL Serverのテーブルに計算列が含まれる場合、タスク実行時にTable errorとなり対象テーブルのデータ移行が失敗する TiDB(MySQL)にはフィルター選択されたイン デックスが実装されていないため、未削除 の場合のみのユニークインデックスが作れ ない。 次のようなインデックスは作成出来ない。 CREATE UNIQUE INDEX IDX_XXXXXX ON table_name ( `id` ASC, `name` ASC, `kana` ASC ) WHERE (`deleted_flag`=(0)) DMSのデータ移行で、ターゲットの方が列 が多い場合、データ移行が失敗する 論理削除されたレコードをユニーク制約から外す対応でTiDB側のみ計算列を作っている。 そのテー ブルがソースとの列数ミスマッチを起こして、データ移行が失敗している。 MySQLとTiDBとで1行のサイズ上限が異なり、 MySQLの1行は65KB、TiDBは6MB。このためTiDBでCreate Tableが成功したDDLを、書き戻し用のMySQL 書き戻しテーブル作成時にエラーになる で実行すると、サイズ上限エラーが発生する 書き戻しレプリケーションの実行防止 本番環境の書き戻しレプリケーションを本番環境移行時以外で実行すると障害になってしまうため、 防止策が必要 参照系DBに存在するデータベースのため、 DMSでデータ移行ができない場合がある DMSは更新系DBからしかレプリケーションできない仕様であるため。 このDBのみ構築時の事情によ り参照系DBに更新系DBを置いておりレプリケーションも行っていない。 主キーがないテーブルは書き戻しレプリ ケーションできない 書き戻しレプリケーションが必要なテーブルは主キーを付けてもらう必要がある ©︎ istyle Inc. No.11

SQL Server → TiDB Cloudデータ移行時の課題（一部） SQL Serverのフィルター選択されたインデックスが TiDBへ移行できない問題について ©︎ istyle Inc. No.12

論理削除時のユニーク制約の修正対応 • 論理削除を採用しているテーブルで、未削除のレコードのみにユニー ク制約を設定したい場合があります。 userテーブル id name birthday email delete_flag 1 aaa 1990-01-01 00:00:00 [email protected] 0 2 bbb 2000-01-01 00:00:00 [email protected] 1 3 bbb 2000-01-01 00:00:00 [email protected] 0 4 bbb 2000-01-01 00:00:00 [email protected] 0 論理削除されていないemail だけにユニーク制約を 設定したい 登録できない →SQL Serverの場合、下記のようにフィルター選択されたインデックス を作成する事で実現可能です CREATE UNIQUE INDEX UK_email ON person ( `email` ASC ) WHERE (`deleted_flag`=(0)) ©︎ istyle Inc. No.13

論理削除時のユニーク制約の修正対応 TiDB（MySQL）ではフィルター選択されたインデックスが実装されていない為、 Generated Columnを作成し、削除フラグが0の時はユニーク制約をかけたい値を 入れ、1の場合はNULLを入れる。 CREATE TABLE person ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, birthday datetime, email VARCHAR(255) NOT NULL,ｄ active_email VARCHAR(255) GENERATED ALWAYS AS (CASE WHEN `delete_flag` = 0 THEN `email` ELSE NULL END) VIRTUAL, delete_flag tinyint NOT NULL DEFAULT 0 ); SQL Serverのフィルター選 択されたインデックスと同 じ挙動になる CREATE UNIQUE INDEX uk_active_email ON person(active_email); id name birthday email active_email delete_flag 1 aaa 1990-01-01 00:00:00 [email protected] [email protected] 0 2 bbb 2000-01-01 00:00:00 [email protected] NULL 1 3 bbb 2000-01-01 00:00:00 [email protected] [email protected] 0 4 aaa 1990-01-01 00:00:00 [email protected] NULL 1 ©︎ istyle Inc. No.14

SQL Server → TiDB Cloudデータ移行時の課題（一部） 解決！ SQL Serverのフィルター選択されたインデックスが TiDBへ移行できない問題について ©︎ istyle Inc. No.15

SQL Server → TiDB Cloudデータ移行時の課題（一部） SQL Serverのフィルター選択されたインデックスを TiDBへ移行したことによる副作用 ©︎ istyle Inc. No.16

論理削除時のユニーク制約の修正対応をしたことによる副作用 id name birthday email delete_flag ターゲットをMySQLとした場合では エラーとならない Generated Column が含まれるテーブ ルをTarget へ同期する場合のLoad Dataの仕様がMySQLとTiDBで異なる AWS DMS id name birthday email active_email delete_flag Generated Column が含まれるテーブ ルをTiDBへ同期するには別の方法を とる必要があった DMSでデータ移行時にターゲットのみ列が多いことでエラーになりま した ©︎ istyle Inc. No.17

論理削除時のユニーク制約の修正対応をしたことによる副作用 id name birthday email delete_flag ③データ同期再開 AWS DMS ①フルロード後、一時停止 id name birthday email delete_flag id name birthday email active_email delete_flag ②Generated Column追加 同じテーブル定義でデータ移行後にGenerated Columnを追加する事 で対応しました ©︎ istyle Inc. No.18

3. 異機種間のデータ移行 を通して学んだこと ©︎ istyle Inc. No.19

異機種間のデータ移行を通して学んだこと • 様々な課題があったが工夫すれば乗り切れる • 異機種間のデータ移行なのですんなりいかない事は前もって覚悟しておく • うまくいかない事があっても結果なんとかなった • 何かあったらPingCapさんが親身になって解決に導いてくれる • うまくいかない事があってもPingCapさんに相談すれば何とかなる • テキストコミュニケーションで足りない時はオンラインで会話してくれる ©︎ istyle Inc. No.20

ご清聴 ありがとうございました 21