Using a Microsoft SQL Server Database as a
Target for AWS Database Migration Service

You can migrate data to Microsoft SQL Server databases using AWS DMS. With an SQL
Server database as a target, you can migrate data from either another SQL Server
database or one of the other supported databases.

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

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

Limitations on Using SQL Server
as a Target for AWS Database Migration Service

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

When you manually create a SQL Server target table with a computed column,
full load replication is not supported when using the BCP bulk-copy utility.
To use full load replication, disable the Use BCP for loading
tables option on the Advanced
tab on the AWS Management Console. For more information on working with BCP, see the
Microsoft SQL Server documentation.

When replicating tables with SQL Server spatial data types (GEOMETRY and
GEOGRAPHY), AWS DMS replaces any spatial reference identifier (SRID) that
you might have inserted with the default SRID. The default SRID is 0 for
GEOMETRY and 4326 for GEOGRAPHY.

Temporal tables are not supported. Migrating temporal tables may work with
a replication-only task in transactional apply mode if those tables are
manually created on the target.

Currently, boolean datatypes in a PostgreSQL source are migrated to a
SQLServer target as bit datatype with inconsistent values. As a workaround,
pre-create the table with a VARCHAR(1) datatype for the column (or let
AWS DMS create the table), and then have downstream processing treat
an "F" as False and a "T" as True.

Security Requirements When Using
SQL Server as a Target for AWS Database Migration Service

The following describes the security requirements for using AWS DMS with a
Microsoft SQL Server target:

The AWS DMS user account must have at least the db_owner
user role on the SQL Server database that you are connecting to.

A SQL Server system administrator must provide this permission to all
AWS DMS user accounts.

Extra Connection Attributes
When Using SQL Server as a Target for AWS DMS

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

The following table shows the extra connection attributes that you can use when
SQL Server is the target.

Name

Description

useBCPFullLoad

Use this to attribute to transfer data for
full-load operations using BCP. When the target table contains
an identity column that does not exist in the source table, you
must disable the use BCP for loading table
option.

Default value: Y

Valid values: Y/N

Example: useBCPFullLoad=Y

BCPPacketSize

The maximum size of the packets (in bytes)
used to transfer data using BCP.

Default value: 16384

Valid values: 1–100000

Example: BCPPacketSize=16384

controlTablesFileGroup

Specify a filegroup for the AWS DMS
internal tables. When the replication task starts, all the
internal AWS DMS control tables (awsdms_ apply_exception,
awsdms_apply, awsdms_changes) are created on the specified
filegroup.

Target Data Types for Microsoft
SQL Server

The following table shows the Microsoft SQL Server target data types that are
supported when using AWS DMS and the default mapping from AWS DMS data types.
For additional information about AWS DMS data types, see Data Types for AWS Database Migration Service.

AWS DMS Data Type

SQL Server Data Type

BOOLEAN

TINYINT

BYTES

VARBINARY(length)

DATE

For SQL Server 2008 and later, use DATE.

For earlier versions, if the scale is 3 or less use DATETIME.
In all other cases, use VARCHAR (37).

TIME

For SQL Server 2008 and later, use DATETIME2 (%d).

For earlier versions, if the scale is 3 or less use DATETIME.
In all other cases, use VARCHAR (37).

DATETIME

For SQL Server 2008 and later, use DATETIME2 (scale).

For earlier versions, if the scale is 3 or less use DATETIME.
In all other cases, use 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:

For SQL Server 2008 and later, use DATETIME2.

For earlier versions, if the scale is 3 or less use
DATETIME. 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

To use this data type with AWS DMS, you must enable the use
of BLOBs for a specific task. AWS DMS supports BLOB data
types only in tables that include a primary key.

CLOB

VARCHAR(max)

To use this data type with AWS DMS, you must enable the use
of CLOBs for a specific task. During change data capture (CDC),
AWS DMS supports CLOB data types only in tables that include
a primary key.

NCLOB

NVARCHAR(max)

To use this data type with AWS DMS, you must enable the use
of NCLOBs for a specific task. During CDC, AWS DMS supports
NCLOB data types only in tables that include a primary
key.

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.