DMS, if used with SQL Server in a self-managed context (EC2 or on-prem), supports one-time and ongoing replication in two modes: via MS-Replication & MS-CDC. However if DMS is used in the context of RDS as a source, only MS-CDC is supported. DMS offers a range of Source/Targets and supports heterogeneous database engines and offers a fine-grained control on the replication process. The Schema Conversion Tool (SCT) can be utilized along with DMS for heterogeneous configuration setup, that helps with schema level changes with an in-built code editor and also produces various reports for migration readiness & planning.
Source/Targets need to be added as end points in DMS and it uses logical replication process under the hood using either MS-Replication or MS-CDC. In a Hybrid setup or within AWS, DMS can be configured between on-prem and AWS with an on-going replication configuration using CDC and during the cutover, the DMS migration task can be stopped and the application will be able to connect to the database that is already in sync with the on-prem database avoiding typical delays of restoring Differential backups to bring the target database online etc.
Below are some of the associated limitations when using SQL Server databases as the source for DMS:
- How large are our databases?
- How many tables do we have in each database?
- Do we have primary keys on all the existing tables? (This is highly recommended for DMS)
- Do we have any identity columns?
- Do we use truncate tables? How frequently?
- Do we have temporal tables?
- Do we have any column level encryption today?
- How often are DDL's executed?
- SELECT * INTO new_table FROM existing_table is not supported. Do we do that frequently?