I am doing a proof of concept to migrate an existing SQL Server database to Amazon RDS. I set up the Amazon RDS database (I tried with SQL Server and Aurora – the result was the same in both cases).
To perform the migration, Amazon sets up the replication task. This task can connect to the source database (which is on the public internet), but always fails to connect to the Amazon database instance I have set up (SQL server, Aurora). However, I can connect to the Amazon database instance via a client tool (SQL Server Management Studio / MySQL client) without a problem!
So it seems that Amazon doesn't allow its own replication task to connect to the database, but allows external clients ??! That seems totally weird ?!
I used all default settings and haven't modified any network/security parameters.
If anyone can shed some light on this it'd be highly appreciated.
Here's the error message I got from the replication task:
Test Endpoint failed: Application-Status: 1020912, Application-Message: Cannot connect to ODBC provider ODBC general error., Application-Detailed-Message: RetCode: SQL_ERROR SqlState: HY000 NativeError: 2003 Message: [unixODBC][MySQL][ODBC 5.3(w) Driver]Can't connect to MySQL server on 'foobar.ap-southeast-2.rds.amazonaws.com' (110) ODBC general error.
Best Answer
Couple of possible reasons come to mind...
You talk about SQL Server (= Microsoft SQL Server) but the error message is about MySQL - are you sure you've got the correct engine configured?
I assume you use DMS (Database Migration Service) - if your DMS instance is in a VPC does the VPC have connectivity to the RDS? Is the DMS in the same VPC as the RDS?
Does the RDS security group permit access for the DMS instance?
I bet it's got something to do either with network configuration or some mismatch in the DMS vs RDS configuration. It's hard to tell with the information provided.
It may be beneficial if you post more details on how is the RDS configured (e.g. screenshot from the console) as well as how is the DMS instance configured (again a screenshot), showing the subnets, VPCs, etc. Otherwise it's hard to tell.
Another thing you can try is to spin up a small instance in the same subnet where DMS is, using the same security group as DMS has, login to it and try to access the RDS instance from there.
Hope that helps :)