SQL Server permissions SQL Server limitations SQL Server CDC Supported compression methods Working with SQL Server AlwaysOn availability groups Configuring a SQL Server database as a replication source for AWS DMS Extra connection attributes when using SQL Server as a source Source data types for SQL Server

Using a Microsoft SQL Server database as a source for AWS DMS

Migrate data from one or many Microsoft SQL Server databases using AWS DMS. With a SQL Server database as a source, you can migrate data to another SQL Server database, or to one of the other AWS DMS supported databases.

AWS DMS supports, as a source, Microsoft SQL Server versions 2005, 2008, 2008R2, 2012, 2014, 2016, 2017, and 2019 on-premise databases and Amazon EC2 instance databases. The Enterprise, Standard, Workgroup, Developer, and Web editions are supported. Ongoing replication (CDC) is supported for all versions of Enterprise Edition, and Standard Edition version 2016 SP1 and later.

AWS DMS supports, as a source, Amazon RDS DB instance databases for SQL Server versions 2008R2, 2012, 2014, 2016, 2017, and 2019. The Enterprise and Standard editions are supported. Ongoing replication (CDC) is supported for all versions of Enterprise Edition, and Standard Edition version 2016 SP1 and later.

Note

Support for Microsoft SQL Server version 2019 as a source is available in AWS DMS versions 3.3.2 and later.

The source SQL Server database can be installed on any computer in your network. A SQL Server account with appropriate access privileges to the source database for the type of task you chose is required for use with AWS DMS.

AWS DMS supports migrating data from named instances of SQL Server. You can use the following notation in the server name when you create the source endpoint.


IPAddress\InstanceName

For example, the following is a correct source endpoint server name. Here, the first part of the name is the IP address of the server, and the second part is the SQL Server instance name (in this example, SQLTest).


10.0.0.25\SQLTest

Also, obtain the port number that your named instance of SQL Server listens on, and use it to configure your AWS DMS source endpoint.

Note

Port 1433 is the default for Microsoft SQL Server. However, dynamic ports that change each time SQL Server is started. Specific static port numbers are also often used to connect to SQL Server through a firewall. So, make sure that you know the actual port number of your named instance of SQL Server when you create the AWS DMS source endpoint.

You can use SSL to encrypt connections between your SQL Server endpoint and the replication instance. For more information on using SSL with a SQL Server endpoint, see Using SSL with AWS Database Migration Service.

To capture changes from a source SQL Server database, the database must be configured for full backups and must be either the Enterprise, Developer, or Standard Edition.

For additional details on working with SQL Server source databases and AWS DMS, see the following.

Topics

Permissions for full load only tasks
Limitations on using SQL Server as a source for AWS DMS
Using ongoing replication (CDC) from a SQL Server source
Supported compression methods
Working with SQL Server AlwaysOn availability groups
Configuring a SQL Server database as a replication source for AWS DMS
Extra connection attributes when using SQL Server as a source for AWS DMS
Source data types for SQL Server

Permissions for full load only tasks

The permissions following are required to perform full load only tasks.



                USE db_name;
                
                CREATE USER dms_user FOR LOGIN dms_user; 
                ALTER ROLE [db_datareader] ADD MEMBER dms_user; 
                GRANT VIEW DATABASE STATE to dms_user ;



                USE master;
                
                GRANT VIEW SERVER STATE TO dms_user;

Limitations on using SQL Server as a source for AWS DMS

The following limitations apply when using a SQL Server database as a source for AWS DMS:

You can't use an SQL Server database as an AWS DMS source if SQL Server Change Tracking (CT) is enabled on the database.

Note

This limitation no longer applies in AWS DMS versions 3.3.1 and later.
The identity property for a column isn't migrated to a target database column.
The SQL Server endpoint doesn't support the use of sparse tables.
Windows Authentication isn't supported.
Changes to computed fields in a SQL Server aren't replicated.
Temporal tables aren't supported.
SQL Server partition switching isn't supported.
When using the WRITETEXT and UPDATETEXT utilities, AWS DMS doesn't capture events applied on the source database.
The following data manipulation language (DML) pattern isn't supported:
```
SELECT * INTO new_table FROM existing_table
                    
```
When using SQL Server as a source, column-level encryption isn't supported.
AWS DMS doesn't support server level audits on SQL Server 2008 or SQL Server 2008 R2 as sources. This is because of a known issue with SQL Server 2008 and 2008 R2. For example, running the following command causes AWS DMS to fail.
```
USE [master]
GO 
ALTER SERVER AUDIT [my_audit_test-20140710] WITH (STATE=on)
GO
                        
```
Geometry columns are not supported in full lob mode when using SQL Server as a source. Instead, use limited lob mode or set the InlineLobMaxSize task setting to use inline lob mode.
A secondary SQL Server database isn't supported as a source database.
When using a Microsoft SQL Server source database in a replication task, the SQL Server Replication Publisher definitions are not removed if you remove the task. A Microsoft SQL Server system administrator must delete those definitions from Microsoft SQL Server.
Replicating data from indexed views isn't supported.
Renaming tables using sp_rename isn't supported (for example, sp_rename 'Sales.SalesRegion', 'SalesReg;)
Renaming columns using sp_rename isn't supported (for example, sp_rename 'Sales.Sales.Region', 'RegID', 'COLUMN';)
TRUNCATE events aren't captured.

The following limitations apply when accessing the backup transaction logs:

Encrypted backups aren't supported.
Backups stored at a URL or on Windows Azure aren't supported.

The following limitations apply when accessing the backup transaction logs at file level:

The backup transaction logs must reside in a shared folder with the appropriate permissions and access rights.
Active transaction logs are accessed through the Microsoft SQL Server API (and not at file-level).
AWS DMS and Microsoft SQL Server machines must reside in the same domain.
Compressed backup transaction logs aren't supported.
Transparent Data Encryption (TDE) isn't supported. When accessing the backup transaction logs using SQL Server's native functionality (not using file-level access), TDE encryption is supported
UNIX platforms aren't supported.
Reading the backup logs from multiple stripes isn't supported.
Microsoft SQL Server backup to multiple disks isn't supported.
When inserting a value into SQL Server spatial data types (GEOGRAPHY and GEOMETRY), you can either ignore the spatial reference system identifier (SRID) property or specify a different number. When replicating tables with spatial data types, AWS DMS replaces the SRID with the default SRID (0 for GEOMETRY and 4326 for GEOGRAPHY).
If your database isn't configured for MS-REPLICATION or MS-CDC, you can still capture tables that do not have a Primary Key, but only INSERT/DELETE DML events are captured. UPDATE and TRUNCATE TABLE events are ignored.
Columnstore indexes aren't supported.
Memory-optimized tables (using In-Memory OLTP) aren't supported.
When replicating a table with a primary key that consists of multiple columns, updating the primary key columns during full load isn't supported.
Delayed durability isn't supported.
The readBackupOnly=Y endpoint setting (ECA) doesn't work on Amazon RDS SQL Server source instances because of the way RDS performs backups.
EXCLUSIVE_AUTOMATIC_TRUNCATION doesn’t work on Amazon RDS SQL Server source instances because RDS users don't have access to execute the SQL Server stored procedure, sp_repldone.

Using ongoing replication (CDC) from a SQL Server source

You can use ongoing replication (change data capture, or CDC) for a self-managed SQL Server database on-premises or on Amazon EC2, or an AWS-managed database on Amazon RDS.

AWS DMS supports ongoing replication for these SQL Server configurations:

For source SQL Server instances that are on-premises or on Amazon EC2, AWS DMS supports ongoing replication for SQL Server Enterprise Edition, Standard Edition version 2016 SP1 and later, and Developer Edition.
For source SQL Server instances running on Amazon RDS, AWS DMS supports ongoing replication for SQL Server Enterprise through SQL Server 2016 SP1. Beyond this version, AWS DMS supports CDC for both SQL Server Enterprise and Standard editions.

To have AWS DMS automatically set up ongoing replication, the DMS user account that connects to the source needs the sysadmin fixed server role. If you don't want to assign the sysadmin role to the user account you use, you can still use ongoing replication. You do so by taking the series of manual steps discussed following.

The following requirements apply specifically when using ongoing replication with a SQL Server database as a source for AWS DMS:

SQL Server must be configured for full backups, and you must perform a backup before beginning to replicate data.
The recovery model must be set to Bulk logged or Full.
SQL Server backup to multiple disks isn't supported. If the backup is defined to write the database backup to multiple files over different disks, AWS DMS can't read the data and the AWS DMS task fails.
For self-managed SQL Server sources, SQL Server Replication Publisher definitions for the source used in a DMS CDC task aren't removed when you remove the task. A SQL Server system administrator must delete these definitions from SQL Server for self-managed sources.
During CDC, AWS DMS needs to look up SQL Server transaction log backups to read changes. AWS DMS doesn't support SQL Server transaction log backups created using third-party backup software that aren't in native format. To support transaction log backups that are in native format and created using third-party backup software, add the use3rdPartyBackupDevice=Y connection attribute to the source endpoint.
For self-managed SQL Server sources, be aware that SQL Server doesn't capture changes on newly created tables until they've been published. When tables are added to a SQL Server source, AWS DMS manages creating the publication. However, this process might take several minutes. Operations made to newly created tables during this delay aren't captured or replicated to the target.
AWS DMS change data capture requires full logging to be turned on in SQL Server. To turn on full logging in SQL Server, either enable MS-REPLICATION or CHANGE DATA CAPTURE (CDC).
You can't reuse the SQL Server tlog until the changes have been processed.
CDC operations aren't supported on memory-optimized tables. This limitation applies to SQL Server 2014 (when the feature was first introduced) and later.

Capturing data changes for SQL Server

For a self-managed SQL Server source, AWS DMS uses the following:

MS-Replication, to capture changes for tables with primary keys. You can configure this automatically by giving the AWS DMS endpoint user sysadmin privileges on the source SQL Server instance. Or you can follow the steps provided in this section to prepare the source and use a non-sysadmin user for the AWS DMS endpoint.
MS-CDC, to capture changes for tables without primary keys. MS-CDC must be enabled at the database level, and for all of the tables individually.

For a SQL Server source running on Amazon RDS, AWS DMS uses MS-CDC to capture changes for tables, with or without primary keys. MS-CDC must be enabled at the database level, and for all of the tables individually, using the Amazon RDS-specific stored procedures described in this section.

There are several ways you can use a SQL Server database for ongoing replication (CDC):

Set up ongoing replication using the sysadmin role. (This applies only to self-managed SQL Server sources.)
Set up ongoing replication to not use the sysadmin role. (This applies only to self-managed SQL Server sources.)
Set up ongoing replication for an Amazon RDS for SQL Server DB instance.

Setting up ongoing replication using the sysadmin role

AWS DMS on-going replication for SQL Server uses native SQL Server replication for tables with primary keys, and change data capture (CDC) for tables without primary keys.

For tables with primary keys, AWS DMS can configure the required artifacts on the source. But for self-managed SQL Server source database instances, the SQL Server Distribution must first be configured manually. Then, AWS DMS source endpoint users with sysadmin permission can automatically create the Publication for tables with primary keys.

To check if distribution has already been configured, run the following command.


sp_get_distributor

If the result is NULL for column distribution, distribution is not configured. To enable distribution, follow these steps:

Connect to the SQL Server source database using the SQL Server Management Studio (SSMS) tool.
Open the Replication folder, and choose Configure Distribution. The Configure Distribution Wizard appears.
Follow the wizard to enter the default values and create the Distribution.

For tables without primary keys, you need to set up MS-CDC.

First, enable MS-CDC for the database by running the following command. Use an account that has the sysadmin role assigned to it.


use [DBname]
EXEC sys.sp_cdc_enable_db

Next, enable MS-CDC for each of the source tables by running the following command.


EXECUTE sys.sp_cdc_enable_table @source_schema = N'MySchema', @source_name =
N'MyTable', @role_name = NULL;

For more information on setting up MS-CDC for specific tables, see the SQL Server documentation.

Setting up ongoing replication without assigning the sysadmin role

You can set up ongoing replication for a SQL Server database source that doesn't require the user account to have sysadmin privileges.

Note

You can perform this procedure while the DMS task is running. If the DMS task is stopped, you can perform this procedure only if there are no transaction log or database backups in progress. This is because SQL Server requires the SYSADMIN privilege in order to query the backups for the LSN position.

To set up a SQL Server database source for ongoing replication without using the sysadmin role

Create a new SQL Server account with password authentication using SQL Server Management Studio (SSMS). In this example, we use an account called dmstest.
In the User Mappings section of SSMS, choose the MSDB and MASTER databases (which gives public permission) and assign the DB_OWNER role for the database you want to use ongoing replication.
Open the context (right-click) menu for the new account, choose Security and explicitly grant the Connect SQL privilege.

Run the following grant commands.


GRANT SELECT ON FN_DBLOG TO dmstest;
GRANT VIEW SERVER STATE TO dmstest;
use msdb;
GRANT EXECUTE ON MSDB.DBO.SP_STOP_JOB TO dmstest;
GRANT EXECUTE ON MSDB.DBO.SP_START_JOB TO dmstest;
GRANT SELECT ON MSDB.DBO.BACKUPSET TO dmstest;
GRANT SELECT ON MSDB.DBO.BACKUPMEDIAFAMILY TO dmstest;
GRANT SELECT ON MSDB.DBO.BACKUPFILE TO dmstest;

In SSMS, open the context (right-click) menu for the Replication folder, and then choose Configure Distribution. Follow all default steps and configure this SQL Server instance for distribution. A distribution database is created under databases.
Create a publication using the procedure following in Creating a SQL Server publication for ongoing replication.
Create a new AWS DMS task with SQL Server as the source endpoint using the user account you created.

Note

The steps in this procedure apply only for tables with primary keys. You still need to enable MS-CDC for tables without primary keys.

Creating a SQL Server publication for ongoing replication

To use CDC with SQL Server, create a publication for each table that is participating in ongoing replication.

To create a publication for SQL Server ongoing replication

Log in to SSMS using the SYSADMIN user account.
Expand Replication.
Open the context (right-click) menu for Local Publications.
In the New Publication Wizard, choose Next.
Choose the database where you want to create the publication.
Choose Transactional publication, and then choose Next.
Expand Tables and choose the tables with PK (also the tables you want to publish). Choose Next.
Choose Next, because you don't need to create a filter.
In the Snapshot Agent screen, choose the first option to Create a snapshot immediately and keep the snapshot available to initialize subscriptions. Choose Next.
Choose Security Settings and choose Run under the SQL Server Agent service account. Make sure to choose By impersonating the process account for publisher connection. Choose OK.
Choose Next.
Choose Create the publication.
Provide a name of the publication in the format AR_PUBLICATION_000DBID.

For example, if your DBID is less than 10, you need to name the publication AR_PUBLICATION_0000DBID> (4 zeros). If your DBID is greater than or equal to 10, you need to name the publicationAR_PUBLICATION_000DBID (3 zeros). You can also use the DB_ID function in SQL Server. For more information on the DB_ID function, see the SQL Server documentation.

Setting up ongoing replication on an Amazon RDS for SQL Server DB instance

Amazon RDS for SQL Server supports MS-CDC for all versions of Amazon RDS for SQL Server Enterprise editions up to SQL Server 2016 SP1. Standard editions of SQL Server 2016 SP1 and later versions support MS-CDC for Amazon RDS for SQL Server.

Unlike self-managed SQL Server sources, Amazon RDS for SQL Server doesn't support MS-Replication. Therefore, AWS DMS needs to use MS-CDC for tables with or without primary keys.

Amazon RDS doesn't grant sysadmin privileges for setting replication artifacts that AWS DMS uses for on-going changes in a source SQL Server instance. You must enable MS-CDC on the Amazon RDS instance using master user privileges in the following procedure.

To enable MS-CDC on an RDS for SQL Server DB instance

Run the following query at the database level.



exec msdb.dbo.rds_cdc_enable_db 'DB_name'

For each table with a primary key, run the following query to enable MS-CDC.


exec sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@role_name = NULL,
@supports_net_changes = 1
GO

For each table with unique keys but no primary key, run the following query to enable MS-CDC.


exec sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@index_name = N'unique_index_name'
@role_name = NULL,
@supports_net_changes = 1
GO

For each table with no primary key nor unique keys, run the following query to enable MS-CDC.


exec sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@role_name = NULL
GO

Set the retention period for changes to be available on the source using the following commands.
```
use dbname
EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@pollinginterval = 3599
exec sp_cdc_stop_job 'capture'
exec sp_cdc_start_job 'capture'
```
The parameter @pollinginterval is measured in seconds with a maximum length of 3599. This means that the transaction log retains changes for 3599 seconds (nearly one hour) when @pollinginterval = 3599. The procedure exec sp_cdc_start_job 'capture' initiates the settings.

If you are using Amazon RDS with Multi-AZ, make sure that also you set secondary following to have the right values in case of failover.
```
exec rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , 3599
```

If an AWS DMS replication task that captures ongoing changes to your SQL Server source stops for more than one hour, use the following procedure.

To maintain the retention period during an AWS DMS replication task

Stop the job truncating the transaction logs by using the following command.
```
exec sp_cdc_stop_job 'capture'
```
Find your task on the AWS DMS console and resume the task.
Open the Monitoring tab, and check the CDCLatencySource metric.
After the CDCLatencySource metric equals 0 (zero) and stays there, restart the job truncating the transaction logs using the following command,
```
exec sp_cdc_start_job 'capture'
```

Note

Remember to start the job that truncates SQL Server transaction logs. Otherwise, storage on your SQL Server instance might fill up.

Supported compression methods

The following table shows the compression methods that AWS DMS supports for each SQL Server version.

SQL Server version	Row/Page compression (at partition level)	Vardecimal storage format
2005	No	No
2008	Yes	No
2012	Yes	No
2014	Yes	No

Note

Sparse columns and columnar structure compression aren't supported.

Working with SQL Server AlwaysOn availability groups

The SQL Server AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring.

To use AlwaysOn Availability Groups as a source in AWS DMS, do the following:

Enable the Distribution option on all SQL Server instances in your Availability Replicas.
In the AWS DMS console, open the SQL Server source database settings. For Server Name, specify the Domain Name Service (DNS) name or IP address that was configured for the Availability Group Listener.

When you start an AWS DMS task for the first time, it might take longer than usual to start. This slowness is because the creation of the table articles is being duplicated by the Availability Groups Server.

Note

In AWS DMS versions 3.3.1 and later, you can migrate changes from a single AlwaysOn replica.

Configuring a SQL Server database as a replication source for AWS DMS

You can configure a SQL Server database as a replication source for AWS DMS. For the most complete replication of changes, we recommend that you use the Enterprise, Standard, or Developer edition of SQL Server. One of these versions is required because these are the only versions that include MS-Replication (EE,SE) and MS-CDC (EE,DEV). The source SQL Server must also be configured for full backups. In addition, AWS DMS must connect with a user (a SQL Server instance login) that has the sysadmin fixed server role on the SQL Server database you are connecting to.

Extra connection attributes when using SQL Server as a source for AWS DMS

You can use extra connection attributes to configure your SQL Server source. You specify these settings when you create the source endpoint. If you have multiple connection attribute settings, separate them from each other by semicolons with no additional white space (for example, oneSetting;thenAnother).

The following table shows the extra connection attributes that you can use with SQL Server as a source:

Name	Description
`alwaysOnSharedSynchedBackupIsEnabled`	This attribute adjusts the behavior of AWS DMS when migrating from an SQL Server source database that is hosted as part of an Always On availability group cluster. Starting with AWS DMS version 3.3.x, AWS DMS has enhanced support for SQL Server source databases that are configured to run in an Always On cluster. In this case, AWS DMS attempts to track if transaction backups are happening from nodes in the Always On cluster other than the node where the source database instance is hosted. At migration task startup, AWS DMS tries to connect to each node in the cluster, but fails if it can't connect to any one of the nodes. In version 3.1.x, this doesn't happen because AWS DMS connects only to the node hosting the SQL Server instance configured as the source endpoint. Essentially, version 3.1.x treats an Always On cluster node as a standalone node. If this attribute is `true`, AWS DMS behaves in an Always On cluster like the 3.1.x version. If you need AWS DMS to poll all the nodes in the Always On cluster for transaction backups, set this attribute to `false`. Default value: `true` Valid values: `true` or `false` Example: `alwaysOnSharedSynchedBackupIsEnabled=false;`
`safeguardPolicy`	For optimal performance, AWS DMS tries to capture all unread changes from the active transaction log (TLOG). However, sometimes due to truncation, the active TLOG might not contain all of the unread changes. When this occurs, AWS DMS accesses the backup log to capture the missing changes. To minimize the need to access the backup log, AWS DMS prevents truncation using one of the following methods: 1. Start transactions in the database: This is the default method. When this method is used, AWS DMS prevents TLOG truncation by mimicking a transaction in the database. As long as such a transaction is open, changes that appear after the transaction started aren't truncated. If you need Microsoft Replication to be enabled in your database, then you must choose this method. 2. Exclusively use sp_repldone within a single task: When this method is used, AWS DMS reads the changes and then uses sp_repldone to mark the TLOG transactions as ready for truncation. Although this method doesn't involve any transactional activities, it can only be used when Microsoft Replication isn't running. Also, when using this method, only one AWS DMS task can access the database at any given time. Therefore, if you need to run parallel AWS DMS tasks against the same database, use the default method. Default value: `RELY_ON_SQL_SERVER_REPLICATION_AGENT` Valid values: {`EXCLUSIVE_AUTOMATIC_TRUNCATION`, `RELY_ON_SQL_SERVER_REPLICATION_AGENT`} Example: `safeguardPolicy=RELY_ON_SQL_SERVER_REPLICATION_AGENT;` Note: `EXCLUSIVE_AUTOMATIC_TRUNCATION` doesn’t work on Amazon RDS SQL Server source instances because RDS users don't have access to execute the SQL Server stored procedure, `sp_repldone`.
`readBackupOnly`	When this attribute is set to `Y`, during ongoing replication AWS DMS reads changes only from transaction log backups and doesn't read from the active transaction log file. Setting this parameter to `Y` enables you to control active transaction log file growth during full load and ongoing replication tasks. However, it can add some source latency to ongoing replication. Valid values: `N` or `Y`. The default is `N`. Example: `readBackupOnly=Y;` Note: This parameter doesn't work on Amazon RDS SQL Server source instances because of the way RDS performs backups.
`use3rdPartyBackupDevice`	When this attribute is set to `Y`, AWS DMS processes third-party transaction log backups if they are created in native format.

Source data types for SQL Server

Data migration that uses SQL Server as a source for AWS DMS supports most SQL Server data types. The following table shows the SQL Server source data types that are supported when using AWS DMS and the default mapping from AWS DMS data types.

For information on how to view the data type that is mapped in the target, see the section for the target endpoint you are using.

For additional information about AWS DMS data types, see Data types for AWS Database Migration Service.

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	DATETIME
DATETIME2 (SQL Server 2008 and later)	DATETIME
SMALLDATETIME	DATETIME
DATE	DATE
TIME	TIME
DATETIMEOFFSET	WSTRING
CHAR	STRING
VARCHAR	STRING
VARCHAR (max)	CLOB TEXT To use this data type with AWS DMS, you must enable the use of CLOB data types for a specific task. For SQL Server tables, AWS DMS updates LOB columns in the target even for UPDATE statements that don't change the value of the LOB column in SQL Server. During CDC, AWS DMS supports CLOB data types only in tables that include a primary key.
NCHAR	WSTRING
NVARCHAR (length)	WSTRING
NVARCHAR (max)	NCLOB NTEXT To use this data type with AWS DMS, you must enable the use of NCLOB data types for a specific task. For more information about enabling data types for a specific task, see Transformation rules and actions. For SQL Server tables, AWS DMS updates LOB columns in the target even for UPDATE statements that don't change the value of the LOB column in SQL Server. During CDC, AWS DMS supports CLOB data types only in tables that include a primary key.
BINARY	BYTES
VARBINARY	BYTES
VARBINARY (max)	BLOB IMAGE For SQL Server tables, AWS DMS updates LOB columns in the target even for UPDATE statements that don't change the value of the LOB column in SQL Server. To use this data type with AWS DMS, you must enable the use of BLOB data types for a specific task. AWS DMS supports BLOB data types only in tables that include a primary key.
TIMESTAMP	BYTES
UNIQUEIDENTIFIER	STRING
HIERARCHYID	Use HIERARCHYID when replicating to a SQL Server target endpoint. Use WSTRING (250) when replicating to all other target endpoints.
XML	NCLOB For SQL Server tables, AWS DMS updates LOB columns in the target even for UPDATE statements that don't change the value of the LOB column in SQL Server. To use this data type with AWS DMS, you must enable the use of NCLOB data types for a specific task. During CDC, AWS DMS supports NCLOB data types only in tables that include a primary key.
GEOMETRY	Use GEOMETRY when replicating to target endpoints that support this data type. Use CLOB when replicating to target endpoints that don't support this data type.
GEOGRAPHY	Use GEOGRAPHY when replicating to target endpoints that support this data type. Use CLOB when replicating to target endpoints that don't support this data type.

AWS DMS doesn't support tables that include fields with the following data types:

CURSOR
SQL_VARIANT
TABLE

Note

User-defined data types are supported according to their base type. For example, a user-defined data type based on DATETIME is handled as a DATETIME data type.

Warning Javascript is disabled or is unavailable in your browser.

To use the AWS Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Using Oracle as a source

Using Azure SQL database as a source