End-to-end migration pipeline · SQL Server & beyond · Bidirectional · On-Prem to Cloud
* MySQL CHECK: enforced only on MySQL 8.0.16+, stored on earlier versions · ** Clustered indexes become B-Tree on PostgreSQL/MySQL (no clustered index concept) · † Full-Text requires SQLSERVER_FULLTEXT option enabled in the RDS option group (one-time setup)
| Constraint / Index | Status | What happens during migration | Implication / manual steps required |
|---|---|---|---|
| PRIMARY KEY | ✔ Auto | Reflected from source metadata and recreated on target exactly. | None. IDENTITY / SERIAL / AUTO_INCREMENT semantics are translated per-engine. |
| UNIQUE Constraint | ✔ Auto | Reflected and recreated as a UNIQUE constraint on the target table. | None. |
| FOREIGN KEY | ✔ Auto | Created with use_alter=True so circular FKs are added in a second pass after all tables exist. |
None. Cross-schema FKs require both schemas to be migrated in the same run. |
| CHECK Constraint | ✔ Auto * | SQL expression reflected and recreated. On MySQL < 8.0.16 the constraint is stored but silently not enforced by the engine. | Verify MySQL target version. On 8.0.16+ enforcement is identical to SQL Server. |
| DEFAULT Values | ✔ Auto | Server-side DEFAULT expressions reflected and applied to target DDL. | Functions like GETDATE() are translated to NOW() / CURRENT_TIMESTAMP. Complex expressions may need manual review. |
| B-Tree Indexes | ✔ Auto | All standard non-clustered indexes reflected and created on target. | None. Fill factor and PAD_INDEX hints are stripped (target-engine specific). |
| UNIQUE Indexes | ✔ Auto | Reflected as UniqueConstraint in SQLAlchemy metadata; enforced on target. | None. |
| INCLUDE Columns (PG) | ✔ Auto | PostgreSQL covering index INCLUDE columns are preserved when migrating PostgreSQL → PostgreSQL. | INCLUDE columns are silently dropped when the target is not PostgreSQL (MySQL/SQL Server do not support the INCLUDE syntax in the same way). |
| Pre-migration inventory log | ✔ Auto | Before copy begins, every constraint and index on every source table is written to the migration log. | Use this log as your reference checklist to verify ✗ items after migration. Search the log for [inventory] lines. |
| Post-migration count diff | ✔ Auto | After all tables are copied, source and target row counts are compared and the diff is logged. | A non-zero diff means rows were lost or duplicated — investigate before cutover. Count mismatch does NOT block the migration; it is a warning only. |
| CLUSTERED → B-Tree | ~ Partial | SQL Server clustered indexes (rows physically sorted by PK on disk) are migrated as standard B-Tree indexes on PostgreSQL / MySQL — which have no native clustered concept. |
Performance implication: Range scans on the PK (e.g. WHERE ClaimId BETWEEN 1 AND 1000000) will be slower on PostgreSQL because rows are heap-stored rather than clustered.Fix (PostgreSQL): Run CLUSTER <table> USING <pk_index>; post-migration — one-time cost, significant read gain for large tables. Requires an exclusive lock — schedule during low-traffic window.MySQL InnoDB: Unaffected — InnoDB always clusters on the PK automatically. |
| Filtered → Partial Index | ✔ Auto | SQL Server filtered indexes (CREATE INDEX … WHERE IsActive = 1) are scraped from sys.indexes.filter_definition and recreated on the target with the original WHERE clause. Runs after data copy as part of Phase 5 index migration. |
Filters using SQL Server-only functions (CONVERT, ISNULL) will fail DDL on PostgreSQL/MySQL targets — check the log for Filtered indexes: N failed and rewrite those manually.MySQL: Does not support partial indexes — filtered index DDL will error; the error is logged and skipped. |
| COLUMNSTORE Index | ✔ Auto | Clustered and non-clustered columnstore indexes are scraped from sys.indexes WHERE type IN (5,6) and recreated on the target after data copy as part of Phase 5 index migration. |
Same-engine (SQL Server → SQL Server / Azure SQL / RDS) only. No equivalent on PostgreSQL or MySQL targets — Phase 5 will log an error for each and continue; verify the log for Columnstore indexes: N failed.PostgreSQL target: Use materialized views with pre-aggregation for reporting workloads. MySQL target: No equivalent — consider a dedicated analytics replica. |
| Full-Text Index † | ✔ Auto † | Full-text catalogs are created first (sys.fulltext_catalogs), then full-text indexes (sys.fulltext_indexes) are applied automatically after data copy. Queries using CONTAINS() and FREETEXT() will work on the target. |
† One-time AWS prerequisite: RDS option group must include the SQLSERVER_FULLTEXT option before migration runs (RDS Console → Option groups → Add option). Without it, catalog creation will fail — logged and skipped, migration still completes.PostgreSQL target: Not applicable — add a tsvector column + GIN index manually and rewrite queries.MySQL target: Not applicable — use ALTER TABLE t ADD FULLTEXT INDEX ft_idx(col); manually.
|
| Spatial Index | ✔ Auto | Spatial indexes are scraped from sys.spatial_indexes — tessellation scheme and bounding box are preserved — and recreated on the target after data copy as part of Phase 5 index migration. |
Same-engine (SQL Server → SQL Server / Azure SQL / RDS) only. ⚠ Warning: If you used [type_overrides] geography = NVARCHAR(4000), the column is plain text on the target — spatial index creation will fail (logged, not fatal).PostgreSQL target: Install PostGIS → convert column to geometry → CREATE INDEX … USING GIST(col);MySQL target (InnoDB 5.7.5+): Column must be NOT NULL → ALTER TABLE t ADD SPATIAL INDEX(col);
|
WHERE pk > last_pk chunking for all INT/BIGINT PK tables — checkpoint saved after every batchsslmode=require enforced on all PostgreSQL family connections — required by Azure DB for PostgreSQL Flexible Server and AWS RDS for PostgreSQLSequence executed on first boot by the Endrias Bridge Windows Service