Docs Menu
Docs Home
/
Relational Migrator
/
Sync Jobs
/
Configure Prerequisites

Configure Migration Prerequisites for SQL Server

On this page

  • About this Task
  • Steps
  • Learn More

To run sync jobs from a SQL Server source database, the database may require some configuration changes. Before you start a sync 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. SQL Server configuration depends on the type of sync job:

  • Snapshot sync jobs migrate all the data and then stops.

  • Continuous sync job run a snapshot and then enter a CDC stage to continuously replicate data changes.

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 sync jobs. The create index creation permission is not required.

Steps

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 sync job. To understand the permissions or run the SQL manually, read the procedures below.

Configure your SQL Server instance based on the sync job type. Refer to the tab below for details on snapshot and continuous sync job configurations.

For snapshot jobs against SQL Server, you must enable CDC at the database level.

1

Configure CDC at the Database Level

Enabling CDC at the database-level CDC generates a small number of system tables in the database, leaving user tables unchanged, and does not add any performance overhead. Enabling CDC alone does not result in changes being captured.

For continuous jobs against SQL Server, you must enable CDC at both the database level and at the table level for each table.

1

Configure CDC at the database level

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
2

Enable the SQL Server agent and check database permissions

To enable the CDC option at the table level:

  1. You must have the server level sysadmin role.

  2. You must have the database level db_owner role.

  3. The SQL Server agent must be running.

  4. The service account used to connect to SQL Server must have Select permission against all required tables.

3

Configure CDC at the table level

To enable CDC at the table level use the sys.sp_cdc_enable_table stored procedure.

You can check the SQL Server CDC settings when viewing the is_tracked_by_cdc column in the sys.tables catalog view. A value of 1 for is_tracked_by_cdc indicates the table is enabled for change data capture.

The code block below is a sample of the automatically generated code.You can run the code manually to enable table CDC:

USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@role_name     = N'MyRole',
@filegroup_name = N'MyDB_CT',
@supports_net_changes = 1
GO

Learn More

Relational Migrator relies on the open-source Debezium connector to capture row-level changes. For more details, see Debezium SQL Server.

Back

PostgreSQL

Next

Sybase