To run migration jobs from a SQL Server source database, the database may require some configuration changes. Before you start a migration job, Relational Migrator checks if the database is configured correctly. If Relational Migrator determines the database needs configuration changes, it automatically generates a SQL script with the required changes. It is recommended to have a Database Administrator (DBA) review the commands in this script and perform their execution on the database server. This topic provides more details on the required configuration steps.
About this Task
This page covers the details of the SQL scripts automatically generated by Relational Migrator.
Relational Migrator automatically detects configuration settings when connecting to your database and generates the appropriate SQL statements to enable CDC if required.
Relational Migrator does not create any indexes on SQL Server to facilitate migration jobs. The create index creation permission is not required.
Steps
To configure your SQL Server instance for snapshot jobs, you must enable CDC at the database level.
The easiest way to set up your database is to run the automatically generated script that Relational Migrator prompts you to download when you create a migration job. To understand the permissions or run the SQL manually, read the following procedure.
To enable CDC at the database level
use the sys.sp_cdc_enable_db stored procedure.
The code blocks below are a sample of the code
automatically generated by Relational Migrator.
You can run the code manually by replacing the
database name for MyDB:
USE MyDB GO EXEC sys.sp_cdc_enable_db GO
For SQL Server instances hosted on AWS RDS:
USE MyDB GO EXEC msdb.dbo.rds_cdc_enable_db 'MyDB'; GO
Note
Enabling CDC at the database-level CDC generates a small number of system tables in the database, leaves user tables unchanged, and does not add any performance overhead. Enabling CDC alone does not result in changes being captured.
Learn More
Relational Migrator relies on the open-source Debezium connector to capture row-level changes. For more details, see Debezium SQL Server.