Sharded migration schema changes
When a sharded migration is selected by the user in the connect to database page, SMT automatically makes some changes to the converted Spanner schema for performing sharded migrations.
Sharded migrations are only supported for MySQL currently.
Table of contents
Addition of migration_shard_id
SMT adds a new column - migration_shard_id to each table of the source database. Each row getting copied to Spanner can be mapped back to its source. This has several advantages:
- Enables reverse replication since each row in Spanner can be mapped back to its own source shard.
- Enabling restarts and failure handling. For example, adding a
migration_shard_idenables usage of partitioned DML to delete all the data migrated for a shard and restart a migration.
The migration_shard_id column is populated via the database to shardId map that is configured during connection profile configuration in the prepare migration page. For details on this configuration, refer here.
migration_shard_id as Primary Key
It is a common practice to use auto-incremented primary keys in many source databases. However during a sharded migration, where multiple source shards are merged into a single Spanner Database, this may lead to conflicts during the data migration. To avoid these conflicts, the migration_shard_id is added to the primary key of each table by default.
To disable this behaviour, please click on “View Rule” and delete the shard_id rule as shown below.
