0

I'm trying to setup AWS DMS migration from SQL Server 2019 on a Windows 2022 EC2 instance to an AWS RDS Oracle 19 instance, for a select number (12) of tables.

I created a DMS source endpoint for the SQL Server with the appropriate database schema name, and that seems to test ok.

I tried both Serverless and Migration Task with a Replication instance, but get this error:

No tables were found at task initialization. Either the selected table(s) or schemas(s) no longer exist or no match was found for the table selection pattern(s).

I verified in SSMS that the schema name and table name are correct.

In the Selection Rules, I tried to use the actual schema name in the Source Schema name field and also tried "dbo". I put 1 table name in the Table Name field (AP_Vendor). I tried %AP_Vendor, incase it was looking for something else at the beginning. But no combination seems to work.

The task seems to start ok, but it doesn't return any tables to migrate, based on my input.

Can someone tell me what to put in the schema and table name fields? Actual schema name? Does Upper or lower case matter? dbo.AP_Vendor in the table name field?

I have not found an example in the AWS rabbit hole specifically for SQL Server as the source database.

Improve this question
3
  • Does AWS Database Migration Service User Guide > Endpoints > Sources for data migration > Using SQL Server as a source provide any insight? If it's getting "no longer exist" errors when looking for the schema/tables have you confirmed that the SQL Login configured in the source endpoint details has its Default Database setting configured to use the expected database? Commented Mar 21, 2024 at 0:48
  • If you can connect then it could be something in your aws dms replication task table mapping file. Are you using "rule-type": "selection" to include your tables, if so, can you post a sample of that? Commented Mar 21, 2024 at 12:28
  • @AlwaysLearning - yes, I followed that AWS doc. Commented Mar 22, 2024 at 18:04

1 Answer 1

Reset to default
0

I should mention that I was doing this because AWS RDS Oracle does not allow you to setup ODBC on the oracle server instance, which prevents you from creating database links to SQL Server databases. Instead, using AWS DMS, I replicated the few necessary tables from the SQL Server database to a separate user schema on the RDS oracle database, and modified the stored procedures and functions to use those replicated tables instead.

Eventually, I got the DMS replication to work.

In the DMS setup, I configured the debugging Cloudwatch logs and found issues in the logs that were not mentioned in the AWS documentation. (Don't forget to turn those off later...)

I finally went with setting up my own Replication instance (dms.t2.micro), and created my own Migration task. This makes the turn around time when you make changes, much faster (2 minutes). The Serverless setup took 20 minutes for any small change. It also allows you to test if your source/target endpoints are setup correctly. I also had to configure the parallel table option to just 1 table, because it was running out of memory in my small replication instance.

  • It required the source user to be in the sysadmin role. In SSMS SQL Query:

    ALTER SERVER ROLE [sysadmin] ADD MEMBER [DMS_USER]

  • The doc obsurely mentioned the "distributor" needs to be setup in the source SQL Server. Turns out that it is a requirement. They should call attention to that step. Though it is easy to setup:

    In SSMS, right mouse click "Replication", choose "Distributor properties...", and create one. I setup a separate folder share on D: drive, D:\Snapshots, hoping to NOT fill up the C: drive. I've never seen anything in this folder, so perhaps that's nothing to worry about.

In the Migration Task, the soruce schema is just "dbo" (acutal schema is set in the source endpoint), and make sure you use the correct capitalization of table names as they appear in SSMS.

After I got 1 table replicating to my RDS oracle database, I entered several "Selection rules" into the Migration Task, for the 12 tables that I need to keep synchronized between the 2 databases.

It works like a charm! I validated that changes to the replicated tables in the source database (accounting), were made to my target database.

Eventually I may move to the Serverless option, because it will automatically "right-size" the behind-the-scenes replication instance. But for now, this will be the most cost effective approach.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.