There are two important concepts you need to understand to know how data flows from your source to the destination cluster: migration types and migration phases. Database Migration Service supports two types of migration: continuous and one-time. Only continuous migrations are available for heterogeneous SQL Server to Cloud SQL for PostgreSQL migrations.
With continuous migrations, your data is migrated in two phases: full dump, and Change Data Capture (CDC).
Full dump phase
The full dump phase is the first part of the migration process. During full dump, Database Migration Service connects to your source instance, reads the content of the tables you selected for migration, and then loads the data to the destination instance. In this phase, Database Migration Service captures actual contents of your database.
The full dump phase depends on your source database snapshot consistency. To ensure snapshot consistency, Database Migration Service requires that you enable snapshot isolation when you configure your source database for migration. Enabling snapshot isolation creates a temporary view of your database at the start of the full dump process. This ensures that the data being copied remains consistent, even if other users are making changes to the live tables at the same time. Enabling snapshot isolation might have a slight performance impact, but it's essential for reliable data extraction.
Change Data Capture (CDC)
When the full dump phase is over, Database Migration Service switches to the CDC phase where it keeps monitoring your source database for changes, and then continuously replicates them on the destination cluster.
In this phase, Database Migration Service doesn't copy actual data from your source database: instead, it uses the SQL Server change data capture mechanism to read information from dedicated change tables. As DML events are applied to the tables in your source database, the changes are recorded in the corresponding change tables. These change tables have the same structure as the source tables, but with extra columns to include the metadata for the changes. Only committed transactions are added to the change tables, along with the log sequence number (LSN) of the commit operation.
Database Migration Service requires that you enable the change data capture mechanism on your source database for the migration. When this feature is active, your source database is subject to certain operational limitations. For example, you can't rename columns in your schema, or you might need to adjust maximum size limits for write operations that involve Large Objects (LOBs). For more information about how the change data capture feature in SQL Server can impact your source database, see Known limitations.
What's next
To learn more about data and feature support for heterogeneous SQL Server migrations with Database Migration Service, see Scenario overview.
To get a complete, step-by-step migration walkthrough, see SQL Server to Cloud SQL for PostgreSQL migration guide.