AWS DMS endpoint test connection error

amazon-dms

I am trying to migrate Microsoft SQL Server databases from on-premise to an RDS instance. I have created a SQL Server 2014 Express Edition instance in RDS and I have SQL Server 2014 Express Edition on premise in in my corporate network. I am able to connect to the RDS instance from my on premise SSMS. While using the DMS service to migrate and creating endpoints, I am getting below error.

Error Details:
[errType=ERROR_RESPONSE, status=1022506, errMessage=Failed to connect Network error has occurred, errDetails= RetCode: SQL_ERROR SqlState: HYT00 NativeError: 0 Message: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired ODBC general error.]]

I am getting the same error even when I am testing the target endpoint which is an RDS instance. I have kept RDS and replication instance on the same VPC. Port 1433 is open in my system and as I am able to connect to the RDS instance via SSMS so there shouldn't be a port issue.

Please help.

Best Answer

I had a similar issue, though I was using DMS to migrate data from S3 to an (RDS) SQL Server 2008. I figured out what my issue was, I didn't require SSL on the SQL Server endpoint. I additionally had to make sure my IAM policy had the right level of access to the services. Hope this helps.

Additionaly:

AWS DMS currently does not support SQL Server Express as a source or target.

^{Reference: Troubleshooting Migration Tasks in AWS Database Migration Service (AWS Database Migration Service | User Guide)}

Related Solutions

AWS DMS – Tables Created with No Auto_Increment

If DMS is told to create the table on the destination (the default option) it only creates the most basic structure needed to move the raw data over. So you shouldn't trust that other metadata like default values are moved over either.

You can either migrate all your changes after moving the data, or create the schema first on your destination and tell DMS not to create tables.

Just keep in mind that copying data with indexes at the same time is much slower, so if you have a lot of data you can create them afterwards or simply just wait longer for the sync.

SQL Server RDS Replication Failure – Troubleshooting with AWS DMS

Need to disable SQL Server Change tracking and enable RDS Change Tracking.

Find any tables which are enabled for CDC with:

SELECT s.name as Schema_name, t.name AS Table_name, tr.* FROM sys.change_tracking_tables tr INNER JOIN sys.tables t on t.object_id = tr.object_id INNER JOIN sys.schemas s on s.schema_id = t.schema_id;

Then for each of those tables table run:

ALTER TABLE dbo.Products DISABLE CHANGE_TRACKING;

Disable CDC on the database:

alter database AdventureWorks set change_tracking = off

Enable RDS CDC on the database:

exec msdb.dbo.rds_cdc_enable_db 'AdventureWorks'

Enable RDS CDC for each table:

exec sys.sp_cdc_enable_table   
   @source_schema           = N'dbo'
,  @source_name             = N'Products'
,  @role_name               = NULL;

Finally rerun your DMS tasks and they should work.

Best Answer

Related Solutions

AWS DMS – Tables Created with No Auto_Increment

SQL Server RDS Replication Failure – Troubleshooting with AWS DMS

Related Question