AWS DMS CDC SQL Server: Configure, Consider, Limitations, Alternatives

We’ve all been there: your business is growing, and your data is expanding across various systems. You’re trying to keep everything in sync, but manual updates and batch processing don’t cut it anymore. 

You need a reliable way to keep your data up-to-date across all platforms. This is where AWS Database Migration Service (DMS) and Change Data Capture (CDC) come into play. This blog will guide you in setting up AWS DMS CDC SQL Server, configuring it as both a source and destination, and understanding its limitations and alternatives.

Before diving into the heavy stuff, let us understand what AWS DMS actually is

What is AWS DMS?

AWS Database Migration Service (DMS) is a cloud-based service that simplifies and accelerates database migration to AWS. Whether you’re moving your data to Amazon RDS, Amazon Redshift, Amazon DynamoDB, or other AWS storage services, DMS can help you achieve this with minimal downtime. DMS supports continuous data replication, ensuring that your source and target databases remain synchronized during and after migration.

Now that we understand DMS let’s understand how CDC comes into play when talking about real-time data ingestion.

What is CDC?

In simple terms, Change Data Capture (CDC) is a method that captures changes (like inserts, updates, and deletes) in your source database and replicates them to a target database. Whenever an operation in your source database occurs, the same change is reflected in your destination, making it available for further processing. 

By leveraging CDC, you can ensure your data is always current and consistent across your systems without performing full data reloads. This is especially useful for real-time analytics, data warehousing, and synchronizing databases.

How Does DMS Support CDC?

AWS DMS supports CDC by continuously monitoring your source database for changes and applying those changes to the target database. This is handy for real-time data synchronization, like analytics, reporting, or operational data stores. So, how does AWS DMS make CDC work for you? Here’s the scoop:

1. Change Data Capture Mechanisms: AWS DMS utilizes the built-in CDC features of your source database to catch changes. This involves using redo logs and tools like Binary Reader to track updates.
2. Replication Instances: These workhorses grab changes from the source and apply them to the target database. They handle all the data transfer and transformation magic.
3. Endpoints: You’ll need to set up source and target endpoints to tell AWS DMS how to connect to your databases and move data around.

What is Microsoft SQL Server?

MS SQL Server is a relational database management system (RDBMS) developed by Microsoft. It supports a wide range of applications, from small, single-machine applications to large, internet-facing applications with many concurrent users. SQL Server includes tools to manage databases, analyze data, and perform other essential tasks.

How to configure SQL Server using AWS DMS

Setting up AWS DMS CDC for SQL Server involves several steps, including preparing your SQL Server environment, configuring AWS DMS, and setting up ongoing replication. Let’s dive into each step.

Step 1: Setup SQL Server as a Source

Prerequisites

Before you begin, ensure you meet the following prerequisites:

1. Microsoft SQL Server versions 2005, 2008, 2008R2, 2012, 2014, 2016, 2017, 2019, and 2022.
   • The Enterprise, Standard, Workgroup, Developer, and Web editions support full-load replication.
   • The Enterprise, Standard (version 2016 and higher), and Developer editions support CDC (ongoing) replication and full load.

Note: The Express edition isn’t supported.

2. Enable CDC: CDC must be enabled on the SQL Server database. You can enable CDC by using the following SQL command: EXEC sys.sp_cdc_enable_db;
3. User Permissions: The user account used for DMS must have permission to read from the source database and write to the target database. For CDC, ensure the account has the `db_owner` role.
4. Network Connectivity: Ensure network connectivity between your SQL Server instance and the AWS DMS replication instance. If your SQL Server is on-premises, this might involve setting up a VPN or Direct Connect connection.

Step 2: Setting Up AWS DMS

Now that your SQL Server environment is prepared, it’s time to configure AWS DMS.

Step 2.1: Create a Replication Instance

The replication instance is the compute resource AWS DMS uses to perform the migration tasks.

1. Navigate to the AWS DMS Console
2. Create Replication Instance: Click on “Replication instances“.

3. Configure Instance: Provide a name for the instance, choose the instance class based on your performance needs, configure the VPC and security groups to allow connectivity to your SQL Server, and then “Create replication instance.”

Step 2.2: Create Source and Target Endpoints

Endpoints define the connection information for your source and target databases.

1. Create Source Endpoint:
   • Navigate to the Endpoints section in the DMS console.
   • Click Create Endpoint → Source Endpoint.
   • Provide the connection details for your SQL Server instance, including the server name, port, database name, and credentials. Then, click Create Endpoint.

2. Create Target Endpoint:
   1. Follow the same steps as creating a source endpoint, but choose Target and provide the connection details for your target database.

Step 2.3: Configure and Start the Migration Task

1. Create a Migration Task:
   • Navigate to the Database Migration Tasks section and click Create database migration task.
   • Provide a name for the task and select the replication instance and endpoints you created earlier.

2. Configure Task Settings:
   • Choose CDC to enable Change Data Capture.
   • Optionally, you can also choose Full load to perform an initial full data load before starting CDC.
   • Configure the table mappings to specify which tables and schemas to replicate.
3. Start the Task:
   • Review your settings and click Create Task to start the migration. AWS DMS will now begin replicating changes from your SQL Server to the target database.

Source Data Types for SQL Server

When you’re using AWS DMS with SQL Server as a source, it supports most SQL Server data types. Here’s a quick look at the supported data types and their default mappings to AWS DMS data types:

SQL Server data types	AWS DMS data types
BIGINT	INT8
BIT	BOOLEAN
DECIMAL	NUMERIC
INT	INT4
MONEY	NUMERIC
NUMERIC (p,s)	NUMERIC
SMALLINT	INT2
SMALLMONEY	NUMERIC
TINYINT	UINT1
REAL	REAL4
FLOAT	REAL8
DATETIME DATETIME2 (SQL Server 2008 and higher) SMALLDATETIME	DATETIME
DATETIMEOFFSET	WSTRING
CHAR VARCHAR	STRING
BINARY VARBINARY	BYTES
TIMESTAMP	BYTES
UNIQUEIDENTIFIER	STRING
XML	NCLOB

Note: AWS DMS doesn’t support tables with fields with the following data types.

• CURSOR
• SQL_VARIANT
• TABLE

Step 3: Setup SQL Server as a Destination

Step 3.1: Endpoint Settings

When using SQL Server as a target for AWS DMS, ensure the following endpoint settings are configured correctly:

1. Server Name: The name of your SQL Server instance.
2. Port: The port number for your SQL Server instance (default is 1433).
3. Database Name: The name of the target database where you want to store your data.
4. Credentials: The username and password with the necessary permissions to write to the target database.

Target data types for Microsoft SQL Server

Ensure that the target database supports the data types used in your source SQL Server database. AWS DMS provides a list of supported data types in its documentation.

AWS DMS data type	SQL Server data type
BOOLEAN	TINYINT
BYTES	VARBINARY(length)
DATE	DATE (SQL Server 2008 and higher) DATETIME (For earlier versions, if the scale is 3 or less) VARCHAR (37).
TIME	DATETIME2 (%d) (SQL Server 2008 and higher) DATETIME (earlier versions, if the scale is 3 or less) VARCHAR (37.
DATETIME	DATETIME2 (scale) (SQL Server 2008 and higher) DATETIME (For earlier versions, if the scale is 3 or less) VARCHAR (37).
INT1	SMALLINT
INT2	SMALLINT
INT4	INT
INT8	BIGINT
NUMERIC	NUMERIC (p,s)
REAL4	REAL
REAL8	FLOAT
STRING	If the column is a date or time column, then do the following: 1. For SQL Server 2008 and higher, use DATETIME2. 2. For earlier versions, if the scale is 3 or less, use DATETIME.  3. In all other cases, use VARCHAR (37). If the column is not a date or time column, use VARCHAR (length).
UINT1	TINYINT
UINT2	SMALLINT
UINT4	INT
UINT8	BIGINT
WSTRING	NVARCHAR (length)
BLOB	VARBINARY(max)IMAGE
CLOB	VARCHAR(max)
NCLOB	NVARCHAR(max)

Limitations of AWS DMS

While AWS DMS offers powerful features for database migration and replication, there are some limitations to keep in mind:

• At least One Database in AWS: AWS DMS requires that your source or target database be within AWS. This is crucial to consider when planning your migration strategy.
• No Complex Transformations: AWS DMS supports basic data transformations but has limited capabilities for complex transformations during migration. For advanced data manipulation, you may need additional tools or custom solutions.
• CDC Overhead: Capturing and applying changes can add extra load to the source database. This overhead could impact performance, especially in high-transaction environments.
• Compatibility Issues: Some features and data types in SQL Server might not be fully supported by AWS DMS, potentially leading to data replication issues or requiring extra configuration to work around these limitations.
• Data Type Support: AWS DMS might not fully support some complex data types and features.
• Cost: AWS DMS incurs costs based on the replication instance class, data transfer, and additional storage used. Monitor your usage and optimize your configurations to manage costs effectively.

Configuring SQL Server using Hevo Data

Hevo Data offers an alternative data integration and replication approach, focusing on ease of use and automation. With Hevo, you can set up automated pipelines for data replication from SQL Server without requiring extensive manual configuration.

Hevo Data supports SQL Server both as a Source and Destination. 

Configure SQL Server as Source

Perform the following steps to configure SQL Server as a Source in Hevo:

• Click PIPELINES in the Navigation Bar.
• Click + CREATE in the Pipelines List View.
• In the Select Source Type page, select SQL Server.
• In the Configure your SQL Server Source page, specify the following:

Configure SQL Server as Destination

After configuring the Source, in the Configure Destination page, select SQL Server and add the details to your server:

Why choose Hevo over AWS DMS for SQL Server Migration?

Hevo offers several advantages over AWS Database Migration Service for SQL Server migration:

1. Easy Setup: Hevo simplifies the setup process with minimal configuration, allowing quick migration without getting bogged down in technical details.
2. No-Code Platform: Hevo’s no-code approach enables users, even those with non-technical backgrounds, to perform migrations without writing code.
3. Real-Time Data Ingestion: Hevo supports continuous, real-time data synchronization, reducing downtime and keeping your data up-to-date during migration.
4. Auto Schema Mapping: Hevo automatically maps source schemas to target schemas, ensuring accurate data transfer with minimal manual effort.
5. Flexible Transformations: Hevo offers in-built and post-load transformations, allowing users to easily cleanse, prepare, and analyze data.

Conclusion

Setting up AWS DMS CDC for MS SQL Server can be a big task, but following these steps can streamline data migration and replication processes. We’ve covered how to configure your AWS DMS, set up MS SQL Server as both a source and destination, and understand the key data types and their mappings. We’ve also discussed the limitations. 

Remember, you can skip the manual loading and complex configurations. By choosing Hevo, you get a user-friendly, no-code platform that facilitates real-time data ingestion and automatic schema mapping, making your SQL Server migration process straightforward and efficient.

FAQ on AWS DMS CDC SQL Server