Using a Microsoft SQL Server database as a source for AWS DMS - AWS Database Migration Service
Limitations on SQL Server as a sourceSQL Server permissionsPrerequisites for using ongoing CDCCapturing changes for self-managed SQL Server on-premises or on EC2Setting up ongoing replication on a cloud SQL Server DB instanceOptional settings for RDS for SQL Server as a sourceSupported compression methodsWorking with self-managed SQL Server AlwaysOn availability groupsSecurity requirementsEndpoint settingsSource 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.

For information about versions of SQL Server that AWS DMS supports as a source, see Sources for AWS DMS.

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. This account must have the view definition and view server state permissions. You add this permission using the following command:

grant view definition to [user]
grant view server state to [user]

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. But dynamic ports that change each time SQL Server is started, and specific static port numbers used to connect to SQL Server through a firewall are also often used. So, you want to 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.

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

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:

  • The identity property for a column isn't migrated to a target database column.

  • The SQL Server endpoint doesn't support the use of tables with sparse columns.

  • 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 aren't 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.

  • When using a Microsoft SQL Server source database in a replication task, the SQL Server Replication Publisher definitions aren't removed if you remove the task. A Microsoft SQL Server system administrator must delete those definitions from Microsoft SQL Server.

  • Migrating data from schema-bound and non-schema-bound views is supported for full-load only tasks.

  • 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';)

  • AWS DMS doesn't support change processing to set and unset column default values (using the ALTER COLUMN SET DEFAULT clause with ALTER TABLE statements).

  • AWS DMS doesn't support change processing to set column nullability (using the ALTER COLUMN [SET|DROP] NOT NULL clause with ALTER TABLE statements).

  • With SQL Server 2012 and SQL Server 2014, when using DMS replication with Availability Groups, the distribution database can't be placed in an availability group. SQL 2016 supports placing the distribution database into an availability group, except for distribution databases used in merge, bidirectional, or peer-to-peer replication topologies.

  • For partitioned tables, AWS DMS doesn't support different data compression settings for each partition.

  • 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 (extra connection attribute) doesn't work on RDS for 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 run the SQL Server stored procedure, sp_repldone.

  • AWS DMS doesn't capture truncate commands.

  • AWS DMS doesn't support replication from databases with accelerated database recovery (ADR) turned on.

  • AWS DMS doesn't support capturing data definition language (DDL) and data manipulation language (DML) statements within a single transaction.

  • AWS DMS doesn't support the replication of data-tier application packages (DACPAC).

  • UPDATE statements that involve primary keys or unique indexes and update multiple data rows, can cause conflicts when you apply changes to the target database. This might happen, for example, when the target database applies updates as INSERT and DELETE statements instead of a single UPDATE statement. With the batch optimized apply mode, the table might be ignored. With the transactional apply mode, the UPDATE operation might result in constraint violations. To avoid this issue, reload the relevant table. Alternatively, locate the problematic records in the Apply Exceptions control table (dmslogs.awsdms_apply_exceptions) and edit them manually in the target database. For more information, see Change processing tuning settings.

  • AWS DMS doesn't support the replication of tables and schemas, where the name includes a special character from the following set.

    \\ -- \n \" \b \r ' \t ;

  • Data masking isn't supported. AWS DMS migrates masked data without masking.

  • AWS DMS replicates up to 32,767 tables with primary keys and up to 1,000 columns for each table. This is because AWS DMS creates a SQL Server replication article for each replicated table, and SQL Server replication articles have these limitations.

  • When using Change Data Capture (CDC), you must define all columns that make up a unique index as NOT NULL. If this requirement is not met, SQL Server system error 22838 will result.

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.

  • AWS DMS doesn't support direct processing of transaction log backups at the file level from alternative shared folders.

Permissions for full load only tasks

The following permissions are required to perform full load only tasks. Note that AWS DMS does not create the dms_user login. For information about creating a login for SQL Server, see Creating a database user with Microsoft SQL Server.

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;

Prerequisites for 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 a cloud database such as Amazon RDS or a Microsoft Azure SQL managed instance.

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 transaction logging to be turned on in SQL Server. To turn on full transaction logging in SQL Server, either enable MS-REPLICATION or CHANGE DATA CAPTURE (CDC).

  • SQL Server tlog entries won't be marked for re-use until the MS CDC capture job processes those changes.

  • CDC operations aren't supported on memory-optimized tables. This limitation applies to SQL Server 2014 (when the feature was first introduced) and higher.

  • AWS DMS change data capture requires a distribution database by default on Amazon EC2 or On-Prem SQL server as source. So, ensure that you have activated the distributor while configuring MS replication for tables with primary keys.

Capturing data changes for self-managed SQL Server on-premises or on Amazon EC2

To capture changes from a source Microsoft SQL Server database, make sure that the database is configured for full backups. Configure the database either in full recovery mode or bulk-logged mode.

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 sysadmin privileges to the AWS DMS endpoint user on the source SQL Server instance. Or you can follow the steps in this section to prepare the source and use a user that doesn't have sysadmin privileges for the AWS DMS endpoint.

MS-CDC

To capture changes for tables without primary keys. Enable MS-CDC at the database level and for all of the tables individually.

When setting up a SQL Server database for ongoing replication (CDC), you can do one of the following:

  • Set up ongoing replication using the sysadmin role.

  • Set up ongoing replication to not use the sysadmin role.

Setting up ongoing replication on a self-managed SQL Server

This section contains information about setting up ongoing replication on a self-managed SQL server with or without using the sysadmin role.

Setting up ongoing replication on a self-managed SQL Server: Using sysadmin role

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

Before setting up ongoing replication, see Prerequisites for using ongoing replication (CDC) from a SQL Server source.

For tables with primary keys, AWS DMS can generally configure the required artifacts on the source. However, for SQL Server source instances that are self-managed, make sure to first configure the SQL Server distribution manually. After you do so, AWS DMS source 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 isn't configured. You can use the following procedure to set up distribution.

To set up distribution

  1. Connect to your SQL Server source database using the SQL Server Management Studio (SSMS) tool.

  2. Open the context (right-click) menu for the Replication folder, and choose Configure Distribution. The Configure Distribution wizard appears.

  3. Follow the wizard to enter the default values and create the distribution.

To set up CDC

AWS DMS version 3.4.7 and greater can set up MS CDC for your database and all of your tables automatically if you aren't using a read-only replica. To use this feature, set the SetUpMsCdcForTables ECA to true. For information about ECAs, see Endpoint settings.

For versions of AWS DMS earlier than 3.4.7, or for a read-only replica as a source, perform the following steps:

  1. For tables without primary keys, set up MS-CDC for the database. To do so, use an account that has the sysadmin role assigned to it, and run the following command.

    use [DBname]
EXEC sys.sp_cdc_enable_db

  2. Next, set up MS-CDC for each of the source tables. For each table with unique keys but no primary key, run the following query to set up 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

  3. For each table with no primary key or no unique keys, run the following query to set up MS-CDC.

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

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

Setting up ongoing replication on a standalone SQL Server: Without sysadmin role

For information about setting up up ongoing replication on a standalone SQL Server without the sysadmin role, see Setting up ongoing replication on a standalone SQL Server: Without sysadmin role.

Setting up ongoing replication on a cloud SQL Server DB instance

This section describes how to set up CDC on a cloud-hosted SQL Server database instance. A cloud-hosted SQL server instance is an instance running on Amazon RDS for SQL Server, an Azure SQL Manged Instance, or any other managed cloud SQL Server instance. For information about limitations for ongoing replication for each database type, see Limitations on using SQL Server as a source for AWS DMS.

Before setting up ongoing replication, see Prerequisites for using ongoing replication (CDC) from a SQL Server source.

Unlike self-managed Microsoft 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 ongoing changes in a source SQL Server instance. Make sure to turn on MS-CDC for the Amazon RDS instance (using master user privileges) as in the following procedure.

To turn on MS-CDC for a cloud SQL Server DB instance

  1. Run one of the following queries at the database level.

    For an RDS for SQL Server DB instance, use this query.

    exec msdb.dbo.rds_cdc_enable_db 'DB_name'

    For an Azure SQL managed DB instance, use this query.

    USE DB_name 
GO 
EXEC sys.sp_cdc_enable_db 
GO

  2. For each table with a primary key, run the following query to turn on 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 turn on 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 turn on MS-CDC.

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

  3. 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 = 86399
exec sp_cdc_stop_job 'capture'
exec sp_cdc_start_job 'capture'

    The parameter @pollinginterval is measured in seconds with a recommended value set to 86399. This means that the transaction log retains changes for 86,399 seconds (one day) when @pollinginterval = 86399. The procedure exec sp_cdc_start_job 'capture' initiates the settings.

    Note

    With some versions of SQL Server, if the value of pollinginterval is set to more than 3599 seconds, the value resets to the default five seconds. When this happens, T-Log entries are purged before AWS DMS can read them. To determine which SQL Server versions are affected by this known issue, see this Microsoft KB article.

    If you are using Amazon RDS with Multi-AZ, make sure that you also set your secondary to have the right values in case of failover.

    exec rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , 86399

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

  1. Stop the job truncating the transaction logs by using the following command. 

    exec sp_cdc_stop_job 'capture'

  2. Find your task on the AWS DMS console and resume the task.

  3. Choose the Monitoring tab, and check the CDCLatencySource metric.

  4. 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'

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

Limitations for ongoing replication on a cloud SQL Server DB instance

  • AWS DMS supports ongoing replication (CDC) with the active transaction log only. You can't use the backup log with CDC.

  • You may lose events if you move them from the active transaction log to the backup log, or truncate them from the active transaction log.

Recommended settings when using Amazon RDS for SQL Server as a source for AWS DMS

When you work with Amazon RDS for SQL Server as a source, the capture job relies on the parameters maxscans and maxtrans. These parameters govern the maximum number of scans that the capture does on the transaction log and the number of transactions that are processed for each scan.

For databases, where a number of transactions is greater than maxtrans*maxscans, increasing the polling_interval value can cause an accumulation of active transaction log records. In turn, this accumulation can lead to an increase in the size of the transaction log.

Note that AWS DMS does not rely on the MS-CDC capture job. The MS-CDC capture job marks the transaction log entries as having been processed. This allows the transaction log backup job to remove the entries from the transaction log.

We recommend that you monitor the size of the transaction log and the success of the MS-CDC jobs. If the MS-CDC jobs fail, the transaction log could grow excessively and cause AWS DMS replication failures. You can monitor MS-CDC capture job errors using the sys.dm_cdc_errors dynamic management view in the source database. You can monitor the transaction log size using the DBCC SQLPERF(LOGSPACE) management command.

To address the transaction log increase that is caused by MS-CDC

  1. Check the Log Space Used % for the database AWS DMS is replicating from and validate that it increases continuously.

    DBCC SQLPERF(LOGSPACE)

  2. Identify what is blocking the transaction log backup process.

    Select log_reuse_wait, log_reuse_wait_desc, name from sys.databases where name = db_name();

    If the log_reuse_wait_desc value equals REPLICATION, the log backup retention is caused by the latency in MS-CDC.

  3. Increase the number of events processed by the capture job by increasing the maxtrans and maxscans parameter values.

    EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@maxtrans = 5000, @maxscans = 20 
exec sp_cdc_stop_job 'capture'
exec sp_cdc_start_job 'capture'

To address this issue, set the values of maxscans and maxtrans so that maxtrans*maxscans is equal to the average number of events generated for tables that AWS DMS replicates from the source database for each day.

If you set these parameters higher than the recommended value, the capture jobs process all events in the transaction logs. If you set these parameters below the recommended value, MS-CDC latency increases and your transaction log grows.

Identifying appropriate values for maxscans and maxtrans can be difficult because changes in workload produce varying number of events. In this case, we recommend that you set up monitoring on MS-CDC latency. For more information, see Monitor the process in SQL Server documentation. Then configure maxtrans and maxscans dynamically based on the monitoring results.

If the AWS DMS task is unable to find the log sequence numbers (LSNs) needed to resume or continue the task, the task may fail and require a complete reload.

Note

When using AWS DMS to replicate data from an RDS for SQL Server source, you may encounter errors when trying to resume replication after a stop-start event of the Amazon RDS instance. This is due to the SQL Server Agent process restarting the capture job process when it restarts after the stop-start event. This bypasses the MS-CDC polling interval.

Because of this, on databases with transaction volumes lower than the MS-CDC capture job processing, this can cause data to be processed or marked as replicated and backed up before AWS DMS can resume from where it stopped, resulting in the following error:

[SOURCE_CAPTURE ]E: Failed to access LSN '0000dbd9:0006f9ad:0003' in the backup log sets since BACKUP/LOG-s are not available. [1020465] (sqlserver_endpoint_capture.c:764)

To mitigate this issue, set the maxtrans and maxscans values as recommended prior.

Supported compression methods for SQL Server

Note the following about support for SQL Server compression methods in AWS DMS:

  • AWS DMS supports Row/Page compression in SQL Server version 2008 and later.

  • AWS DMS doesn't support the Vardecimal storage format.

  • AWS DMS doesn't support sparse columns and columnar structure compression.

Working with self-managed SQL Server AlwaysOn availability groups

SQL Server Always On availability groups provide high availability and disaster recovery as an enterprise-level alternative to database mirroring.

In AWS DMS, you can migrate changes from a single primary or secondary availability group replica.

Working with the primary availability group replica

To use the primary availability group as a source in AWS DMS, do the following:

  1. Turn on the distribution option for all SQL Server instances in your availability replicas. For more information, see Setting up ongoing replication on a self-managed SQL Server.

  2. 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 your availability group listener.

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

Working with a secondary availability group replica

To use a secondary availability group as a source in AWS DMS, do the following:

  1. Use the same credentials for connecting to individual replicas as those used by the AWS DMS source endpoint user.

  2. Ensure that your AWS DMS replication instance can resolve DNS names for all existing replicas, and connect to them. You can use the following SQL query to get DNS names for all of your replicas.

    select ar.replica_server_name, ar.endpoint_url from sys.availability_replicas ar
JOIN sys.availability_databases_cluster adc
ON adc.group_id = ar.group_id AND adc.database_name = '<source_database_name>';

  3. When you create the source endpoint, specify the DNS name of the availability group listener for the endpoint's Server name or for the endpoint secret's Server address. For more information about availability group listeners, see What is an availability group listener? in the SQL Server documentation.

    You can use either a public DNS server or an on-premises DNS server to resolve the availability group listener, the primary replica, and the secondary replicas. To use an on-premises DNS server, configure the Amazon Route 53 Resolver. For more information, see Using your own on-premises name server.

  4. Add the following extra connection attributes to your source endpoint.

    Extra connection attribute Value Notes
    applicationIntent ReadOnly Without this ODBC setting, the replication task is routed to the primary availability group replica. For more information, see SQL Server Native Client Support for High Availability, Disaster Recovery in the SQL Server documentation.
    multiSubnetFailover yes For more information, see SQL Server Native Client Support for High Availability, Disaster Recovery in the SQL Server documentation.
    alwaysOnSharedSynchedBackupIsEnabled false For more information, see Endpoint settings when using SQL Server as a source for AWS DMS.
    activateSafeguard false For more information, see Limitations following.
    setUpMsCdcForTables false For more information, see Limitations following.

  5. Enable the distribution option on all replicas in your availability group. Add all nodes to the distributors list. For more information, see To set up distribution.

  6. Run the following query on the primary read-write replica to enable publication of your database. You run this query only once for your database. 

    sp_replicationdboption @dbname = N'<source DB name>', @optname = N'publish', @value = N'true';

Limitations

Following are limitations for working with a secondary availability group replica:

  • AWS DMS doesn't support Safeguard when using a read-only availability group replica as a source. For more information, see Endpoint settings when using SQL Server as a source for AWS DMS.

  • AWS DMS doesn't support the setUpMsCdcForTables extra connection attribute when using a read-only availability group replica as a source. For more information, see Endpoint settings when using SQL Server as a source for AWS DMS.

  • AWS DMS can use a self-managed secondary availability group replica as a source database for ongoing replication (change data capture, or CDC) starting from version 3.4.7. Cloud SQL Server Multi-AZ read replicas are not supported. If you use previous versions of AWS DMS, make sure that you use the primary availability group replica as a source database for CDC.

Failover to other nodes

If you set the ApplicationIntent extra connection attribute for your endpoint to ReadOnly, your AWS DMS task connects to the read-only node with the highest read-only routing priority. It then fails over to other read-only nodes in your availability group when the highest priority read-only node is unavailable. If you don't set ApplicationIntent, your AWS DMS task only connects to the primary (read/write) node in your availability group.

Security requirements when using SQL Server as a source for AWS Database Migration Service

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

Endpoint settings when using SQL Server as a source for AWS DMS

You can use endpoint settings to configure your SQL Server source database similar to using extra connection attributes. You specify the settings when you create the source endpoint using the AWS DMS console, or by using the create-endpoint command in the AWS CLI, with the --microsoft-sql-server-settings '{"EndpointSetting": "value", ...}' JSON syntax.

The following table shows the endpoint settings that you can use with SQL Server as a source.

Name Description

ActivateSafeguard

This attribute turns Safeguard on or off. For information about Safeguard, see SafeguardPolicy following.

Default value: true

Valid values: {false, true}

Example: '{"ActivateSafeguard": true}'
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.

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 start-up, AWS DMS tries to connect to each node in the cluster, but fails if it can't connect to any one of the nodes.

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}'

"ApplicationIntent": "readonly"

This ODBC driver attribute setting causes SQL Server to route your replication task to the highest priority read-only node. Without this setting, SQL Server routes your replication task to the primary read-write node.

EnableNonSysadminWrapper

Use this endpoint setting when you are setting up ongoing replication on a standalone SQL server without a sysadmin user. This parameter is supported on AWS DMS version 3.4.7 and higher. For information about setting up ongoing replication on a standalone SQL server, see Setting up ongoing replication on a standalone SQL Server: Without sysadmin role.

Default value: false

Valid values: true, false

Example: '{"EnableNonSysadminWrapper": true}'

ExecuteTimeout

Use this extra connection attribute (ECA) to set the client statement timeout for the SQL Server instance, in seconds. The default value is 60 seconds.

Example: '{"ExecuteTimeout": 100}'

FatalOnSimpleModel

When set to true, this setting generates a fatal error when SQL Server database recovery model is set to simple.

Default value: false

Valid values: true or false

Example: '{"FatalOnSimpleModel": true}'

ForceLobLookup

Forces LOB lookup on inline LOB.

Default value: false

Valid values: true, false

Example: '{"ForceLobLookup": false}'

"MultiSubnetFailover": "Yes"

This ODBC driver attribute helps DMS to connect to the new primary in case of an Availability Group failover. This attribute is designed for situations when the connection is broken or the listener IP address is incorrect. In these situations, AWS DMS attempts to connect to all IP addresses associated with the Availability Group listener.

ReadBackupOnly

Use of this attribute requires sysadmin privileges. 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.

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 the unread changes. When this occurs, AWS DMS accesses the log backup to capture the missing changes. To minimize the need to access the log backup, AWS DMS prevents truncation using one of the following methods:

  1. RELY_ON_SQL_SERVER_REPLICATION_AGENT (Start transactions in the database): This is the default for AWS DMS.

    When you use this setting, AWS DMS requires that the SQL Server Log Reader Agent be running, so that AWS DMS can move transactions that are marked for replication from the active TLOG. Note that if the Log Reader Agent is not running, the active TLOG can become full, causing the source database to switch to read-only mode until you can resolve the issue. If you need to enable Microsoft Replication in your database for a purpose other than AWS DMS, then you must choose this setting.

    When you use this setting, AWS DMS minimizes log backup reads by creating a table called awsdms_truncation_safeguard and prevents TLOG truncation by mimicking an open transaction in the database. This keeps the database from truncating events and moving them to the backup log for five minutes (by default). Make sure that the table is not included in any maintenance plan, as it may cause the maintenance job to fail. You can safely delete the table if there are no tasks configured with the Start Transactions database option.

  2. EXCLUSIVE_AUTOMATIC_TRUNCATION (Exclusively use sp_repldone with a single task): When you use this setting, AWS DMS has full control of the replication agent process that marks log entries as ready for truncation using sp_repldone. With this setting, AWS DMS doesn't use a dummy transaction as with the RELY_ON_SQL_SERVER_REPLICATION_AGENT (default) setting. You can only use this setting when MS Replication is not used for any other purpose other than AWS DMS on the source database. Also, when using this setting, only one AWS DMS task can access the database. If you need to run parallel AWS DMS tasks against the same database, use RELY_ON_SQL_SERVER_REPLICATION_AGENT.

    • This setting requires that the Log Reader Agent be stopped in the database. If the Log Reader Agent is running when the task starts, the AWS DMS task will force it to stop. Alternatively, you can stop the Log Reader Agent manually before starting the task.

    • When using this method with MS-CDC, you should stop and disable the MS-CDC capture and MS-CDC cleanup jobs.

    • You can't use this setting when the Microsoft SQL Server Migration job runs on a remote Distributor machine, because AWS DMS doesn't have access to the remote machine.

    • EXCLUSIVE_AUTOMATIC_TRUNCATION doesn't work on Amazon RDS for SQL Server source instances because Amazon RDS users don't have access to run the sp_repldone stored procedure.

    • If you set SafeguardPolicy to EXCLUSIVE_AUTOMATIC_TRUNCATION without using the sysadmin role, you must grant permissions on the dbo.syscategories and dbo.sysjobs objects to the dmsuser user.

Default value: RELY_ON_SQL_SERVER_REPLICATION_AGENT

Valid values: {EXCLUSIVE_AUTOMATIC_TRUNCATION, RELY_ON_SQL_SERVER_REPLICATION_AGENT}

Example: '{"SafeguardPolicy": "EXCLUSIVE_AUTOMATIC_TRUNCATION"}'

SetUpMsCdcForTables

This attribute turns on MS-CDC for the source database and for tables in the task mapping that don't have MS-Replication enabled. Setting this value to true runs the sp_cdc_enable_db stored procedure on the source database, and runs the sp_cdc_enable_table stored procedure on each table in the task that doesn't have MS-Replication enabled in the source database. For more information about turning on distribution, see Setting up ongoing replication on a self-managed SQL Server.

Valid values: {true, false}

Example: '{"SetUpMsCdcForTables": true}'

TlogAccessMode

Indicates the mode used to fetch CDC data.

Default value: PreferTlog

Valid values: BackupOnly, PreferBackup, PreferTlog, TlogOnly

Example: '{"TlogAccessMode": "PreferTlog"}'

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 higher)

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 SupportLobs for a specific task. For more information about enabling Lob support, see Setting LOB support for source databases in an AWS DMS 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.

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.