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