Questions tagged with AWS Database Migration Service

Please let know on the console how to provided S3 name while configuring Amazon redshift as target endpoint, by default it creates intermediary S3 bucket for data transit but there is option for user provided S3 bucket, just not able to find where to fill the details in the Target endpoint form.

IHAC who is doing scoping with an Architecture using DMS and SCT. I had a few questions I was hoping you can get answered for me. 1. Does AWS DMS support data validation with Cassandra as a source? I don’t see it here - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html#CHAP_BestPractices.DataValidation but I do see Cassandra as a valid source target here https://aws.amazon.com/about-aws/whats-new/2018/09/aws-dms-aws-sct-now-support-the-migration-of-apache-cassandra-databases/ 2. Does AWS DMS support ongoing replication with Cassandra as a source? Reading the docs it looks like if I wanted to extract data from Cassandra and write to s3 (Using DMS) then post process that data into a different format (Like json) and write to a different S3 bucket, I could so by attaching a Lamba to the original S3 event from the DMS extract and drop. Can you confirm my understanding? 3. How is incremental data loaded ongoing after initial load from Cassandra (with DMS)? In the docs it looks like its stored in s3 in csv form. Does it write 1 csv per source table and keep appending or updating the existing csv? does it create 1 csv per row, per batch...etc? I’m wondering how the event in step 3 would be triggered if I did want to continuously post process updates as they come in in real time and covert source data from Cassandra into Json data I store on s3.

I am selling my site and need to transfer the AWS account to the buyer's business (the buyers do not use AWS for their other sites - but they want my site to continue with AWS). I cannot figure out how to do it. Do I need to pay for support and what level? This is Amazon's advice on transfering ownership of a site: https://aws.amazon.com/premiumsupport/knowledge-center/transfer-aws-account/ "To assign ownership of an AWS account and its resources to another party or business, contact AWS Support for help: Sign in to the AWS Management Console as the root user. Open the AWS Support Center. Choose Create case. Enter the details of your case: Choose Account and billing support. For Type, choose Account. For Category, choose Ownership Transfer. For all other fields, enter the details for your case. For Preferred contact language, choose your preferred language. For Contact methods, choose your preferred contact method. Choose Submit. AWS Support will contact you with next steps and help you transfer your account ownership." I have done all this but have not yet been contacted (24 hours). The text seems to suggest that advice on transfering ownership is a necessary aspect of transfering an AWS root account to a company, and that such advice is provided free by Amazon, since nothing is said about pricing. If on the other hand AWS clients must pay for a support package to transfer ownership, which package? The $29 Developer package or the $100 Business package or some other package? How quickly does Amazon AWS respond? How quick is the transfer process? I am finding this very frustrating.

I have AWS DB of Production and AWS DB of UAT environment. I want whenever needed I can get Production data of few tables to my UAT Database by clicking button from GUI. So GUI means we will design one GUI and we put one button there and whenever user need just click that button and using some DMS API's (not sure) selected tables data from Production get synchronized in UAT database.

How to migrate DB on premise Oracle to Microsoft SQL Server DB in AWS. We see SCT will not allow MS SQL Server as Target when source is Oracle. Looking for tools like SCT to move Schema from Oracle. We see SCT is useful to move Source Schema (Eg: Oracle ) to Target Schema DB (Eg: Aurora Postgres) and other limited DB combinations. Now requirement is to migrate from On-premise Oracle to MS SQL Server in AWS Cloud. Please let me know if anyone has worked on this task.

Hi, Chr(0) is present in the source data and dms is not migrating this character to target. DMS transfers all non-printable characters except chr(0). Due to this DMS reports these records as mismatched records. Is there any workaround for this in DMS to retain chr(0) in target. Source DB:rds oracle Target :AUrora postgresql 13.X Sample insert query: insert into non_ascii_test values (1,'This string doesnt contain non-printable ascii characters'); insert into non_ascii_test values (2,'This string contains non-printable ascii characters'||chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||chr(9)); insert into non_ascii_test values (3,'This string contains non-printable ascii characters'||chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||chr(9)); insert into non_ascii_test values (6,chr(0)||chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||chr(9)||chr(10)||chr(11)||chr(12)||chr(13)||chr(14)||chr(15)||chr(16)||chr(17)||chr(18)||chr(19)||chr(20)||chr(21)||chr(22)||chr(23)||chr(24)||chr(25)||chr(26)||chr(27)||chr(28)||chr(29)||chr(30)||chr(31)||'test non-ascii print'||chr(0)||chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||chr(9)); Thanks Bharath

Hi Currently we have two dbs in AWS RDS (Postgres). One for Demo (Internal Testing) and another for beta environment. Both dbs have a schema called "strapi" which contains common data to display to user. Our use case is that whenever the admin updates the strapi data in demo db, it should reflect in beta as well. So I was searching for AWS services that supports our use case. Searched for similar questions and I found people recommending AWS DMS. So went ahead and created endpoints and initially ran the task with "Do Nothing" option for Target Table Preparation Mode. Later ran the full load task with "Truncate" option for Target Table Preparation Mode . The task failed with following error "RetCode: SQL_ERROR SqlState: 0A000 NativeError: 1 Message: ERROR: cannot truncate a table referenced in a foreign key constraint; Error while executing the query [1022502] (ar_odbc_stmt.c:4828). Failed to truncate table strapi.programs [1022502] (odbc_endpoint_imp.c:4372)" Tried setting session_replication_role parameter to replica on target database in RDS and still getting the same error. Any help would be appreciated. Note: I still am not sure whether AWS DMS is a good service for our use case. Thanks in Advance.

Trying to migrate existing data from AWS RDS Postgres to AWS managed OpenSearch, but it is not working, no rows were migrated to opensearch, When checking the Cloudwatch log getting below error Bulk request failed. no retry. TotalRecordCount 4080, FailedRecordCount 4080 [1026400] (elasticsearch_bulk_utils.c:181) DMS has the following configuration: { "TargetMetadata": { "TargetSchema": "", "SupportLobs": false, "FullLobMode": false, "LobChunkSize": 0, "LimitedSizeLobMode": false, "LobMaxSize": 0, "InlineLobMaxSize": 0, "LoadMaxFileSize": 0, "ParallelLoadThreads": 5, "ParallelLoadBufferSize": 100, "BatchApplyEnabled": false, "TaskRecoveryTableEnabled": false, "ParallelLoadQueuesPerThread": 0, "ParallelApplyThreads": 0, "ParallelApplyBufferSize": 100, "ParallelApplyQueuesPerThread": 0 }, "FullLoadSettings": { "TargetTablePrepMode": "DO_NOTHING", "CreatePkAfterFullLoad": false, "StopTaskCachedChangesApplied": false, "StopTaskCachedChangesNotApplied": false, "MaxFullLoadSubTasks": 8, "TransactionConsistencyTimeout": 600, "CommitRate": 50000 }, "Logging": { "EnableLogging": true, "LogComponents": [ { "Id": "TRANSFORMATION", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "SOURCE_UNLOAD", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "IO", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "TARGET_LOAD", "Severity": "LOGGER_SEVERITY_DETAILED_DEBUG" }, { "Id": "PERFORMANCE", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "SOURCE_CAPTURE", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "SORTER", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "REST_SERVER", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "VALIDATOR_EXT", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "TARGET_APPLY", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "TASK_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "TABLES_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "METADATA_MANAGER", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "FILE_FACTORY", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "COMMON", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "ADDONS", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "DATA_STRUCTURE", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "COMMUNICATION", "Severity": "LOGGER_SEVERITY_DEFAULT" }, { "Id": "FILE_TRANSFER", "Severity": "LOGGER_SEVERITY_DEFAULT" } ], "CloudWatchLogGroup": null, "CloudWatchLogStream": null }, "ControlTablesSettings": { "historyTimeslotInMinutes": 5, "ControlSchema": "", "HistoryTimeslotInMinutes": 5, "HistoryTableEnabled": true, "SuspendedTablesTableEnabled": false, "StatusTableEnabled": true, "FullLoadExceptionTableEnabled": false }, "StreamBufferSettings": { "StreamBufferCount": 3, "StreamBufferSizeInMB": 8, "CtrlStreamBufferSizeInMB": 5 }, "ChangeProcessingDdlHandlingPolicy": { "HandleSourceTableDropped": true, "HandleSourceTableTruncated": true, "HandleSourceTableAltered": true }, "ErrorBehavior": { "DataErrorPolicy": "LOG_ERROR", "EventErrorPolicy": null, "DataTruncationErrorPolicy": "LOG_ERROR", "DataErrorEscalationPolicy": "SUSPEND_TABLE", "DataErrorEscalationCount": 0, "TableErrorPolicy": "SUSPEND_TABLE", "TableErrorEscalationPolicy": "STOP_TASK", "TableErrorEscalationCount": 0, "RecoverableErrorCount": -1, "RecoverableErrorInterval": 5, "RecoverableErrorThrottling": true, "RecoverableErrorThrottlingMax": 1800, "RecoverableErrorStopRetryAfterThrottlingMax": true, "ApplyErrorDeletePolicy": "IGNORE_RECORD", "ApplyErrorInsertPolicy": "LOG_ERROR", "ApplyErrorUpdatePolicy": "LOG_ERROR", "ApplyErrorEscalationPolicy": "LOG_ERROR", "ApplyErrorEscalationCount": 0, "ApplyErrorFailOnTruncationDdl": false, "FullLoadIgnoreConflicts": true, "FailOnTransactionConsistencyBreached": false, "FailOnNoTablesCaptured": true }, "ChangeProcessingTuning": { "BatchApplyPreserveTransaction": true, "BatchApplyTimeoutMin": 1, "BatchApplyTimeoutMax": 30, "BatchApplyMemoryLimit": 500, "BatchSplitSize": 0, "MinTransactionSize": 1000, "CommitTimeout": 1, "MemoryLimitTotal": 1024, "MemoryKeepTime": 60, "StatementCacheSize": 50 }, "PostProcessingRules": null, "CharacterSetSettings": null, "LoopbackPreventionSettings": null, "BeforeImageSettings": null, "FailTaskWhenCleanTaskResourceFailed": false, "TTSettings": null } Opensearch have index with following settings { "settings": { "index.max_ngram_diff" :8, "analysis": { "analyzer": { "my_ngram_analyzer": { "type": "custom", "tokenizer": "standard", "filter": [ "lowercase", "mynGram" ] } }, "filter": { "mynGram": { "type": "nGram", "min_gram": 6, "max_gram": 14, "token_chars": [ "letter", "digit", "whitespace", "symbol" ] } } }, "number_of_shards": 6, "number_of_replicas": 1 }, "mappings" : { "properties" : { "created_at" : { "type" : "date" }, "id" : { "type" : "long" }, "name" : { "type" : "text", "analyzer":"my_ngram_analyzer" , "search_analyzer": "my_ngram_analyzer" }, "phone" : { "type" : "text", "analyzer":"my_ngram_analyzer" , "search_analyzer": "my_ngram_analyzer" }, "updated_at" : { "type" : "date" } } } } I have tried to insert a sample document using _bulk API on opensearch console and it worked, below is the thing I had tried over opensearch, which worked POST _bulk {"index":{"_index":"contacts"}} {"name": "name","phone" : "11111111","created_at" : "2021-12-21T12:12:59","updated_at" : "2021-12-21T12:12:59","id": 101}

[dms cost]( https://aws.amazon.com/dms/pricing/) According to the above web page on DMS costs, the listed fees are added on an hourly basis depending on the instance type. Question. Is there a charge for just having a replication instance (even if the database migration task is not running)? Or is the fee charged only when the database migration task is running? For example, if I create a replication instance and run the database migration task for 2 hours a day with an api like boto3 below, do I have to pay for only 2 hours of usage? [boto3 api]( https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/dms.html#DatabaseMigrationService.Client.start_replication_task)

I wanted to migrate Database from On-Prem which is My SQL Server to AWS Managed RDS using DMS which can support database partitioning. I migrated My SQL server to AWS Aurora(MY SQL) using DMS but this is not supporting database partitioning. So is there any new version of AWS MY SQL supports data partitioning? or how can we migrate using DMS from on prem MY SQL Server to AWS Managed RDS (Any suggestion of RDS) which support Database partitioning along with performance.

We are trying to migrate data from AWS Aurora Postgres (v13.4) to another AWS Aurora Postgres database (Serverless v2) but the data migration fails for tables wherein we add a column filter. Our table names and column names are in the upper camel case. I checked the logs it shows the following error: ``` 2022-04-27T09:31:12 [TRANSFORMATION ]E: Failed to init filter for column CompanyId [20014] (manipulator.c:605) 2022-04-27T09:31:12 [TRANSFORMATION ]W: The metadata transformations defined for table 'public.Tenants' were not performed as at least one of the transformation expressions contains an error (manipulator.c:147) ``` Here is the mapping rule: ``` { "rules": [ { "rule-type": "selection", "rule-id": "046273916", "rule-name": "046273916", "object-locator": { "schema-name": "public", "table-name": "Tenants" }, "rule-action": "include", "filters": [ { "filter-type": "source", "column-name": "CompanyId", "filter-conditions": [ { "filter-operator": "eq", "value": "xxxxxx" } ] } ] }, { "rule-type": "selection", "rule-id": "042087387", "rule-name": "042087387", "object-locator": { "schema-name": "public", "table-name": "Plans" }, "rule-action": "include", "filters": [] } ] } ```

We are running DMS task which is ingesting from the on-prem mssql table with over 200mil records and has LOBs in it. Ingestion takes ~ 33 hours On-prem DB has a backup that happens after midnight and the task is failing right after that. Below are the logs: ``` [SOURCE_UNLOAD ]D: Network error encountered [SOURCE_UNLOAD ]D: Is connection error? TRUE [SOURCE_UNLOAD ]D: Endpoint is disconnected [1020414] (endpointshell.c:3807) [SOURCE_UNLOAD ]W: Error was encountered while FETCH-ing data from table ... [SOURCE_UNLOAD ]D: Error executing source loop [1020414] ``` I haven't tried the executeTimeout in the source endpoint connection attributes yet and wanted to ask what would be the suggested option to get around the timeout on the source issue

I am migrating data from a MySQL DB to DynamoDB. I am using object-mapping rule to transform the data to match my needs. However I have a EPOCH timestamp stored in MySQL DB that I would like to transform to Datetime before loading into my DynamoDB. I see there is a transformation rule I could use to by leveraging SQLite expressions. However I couldnt use transformation rules in conjunction with object-mapping. Is there any other way I could transform my date field by still using 'object-mapping' rule ?

Hi, I've started this journey with AWS and hope someone can shed some light regarding proper configuration. I'd like to use DMS to sync db from On-premise to newly setup AWS RDS db (running within VPC#1) To connect to the on-premise db I originally created NAT gateway (running within VPC#2) as the db is firewalled and needs static IP to gain access. I've quickly realized the DMS needs single instance that works with both endpoints - that's where my issue is. What are my options for this? -Do I create new NAT gateway within VPC#1? -Do I use Peering connection between VPCs? What are the best practices for setting something like that.

Hi! I am a data engineer. Assumption: Our in-house application DB is Aurora MySQL. (Version 5.6 or higher) The reservation table in the application DB always contains the latest values and the history cannot be tracked only by the information in the DB. We learned that Change Data Capture (CDC) is an effective method to obtain DB update logs, and we learned that it can be achieved with AWS's DMS service. However, the cost of DMS is high. So we are looking for a more reasonable solution. If you have any good ideas, I would appreciate it if you could let me know. Thank you in advance.

According to the following documentation for creating a target endpoint using AWS DMS https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Neptune.html The setup looks like as follows: 1. A Neptune DB cluster(without Notebook) 2. A DMS replication instance 3. A source endpoint (S3) 4. A role that has full access to S3, Neptune and RDS 5. A target endpoint (AWS Neptune) <-- This is the point of failure After configuring the target endpoint when I click on "Run test" I see following error: `arn:aws:iam::12xxxxxx2:role/xxxx-dms-role role should be associated with xxx-cluster-xxx-neptune cluster.` Is there something missing? The role is configured with policies that provide full access to all resources(especially Neptune and S3) ```JSON { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:*" ], "Resource": "*" }, { "Effect": "Allow", "Action": "neptune-db:*", "Resource": "*" }, { "Effect": "Allow", "Action": "rds:*", "Resource": "*" } ] } ```

For MongoDB collections that have documents with multiple nested-level, if use table mode will create a lot of columns, and if use document mode _doc field with a maximum varchar length is very limited. Is it possible to use Super type for the top-level fields in MongoDB or _doc field?

I used keytool to import the client cert and the key as one certificate and the server CA as its own certificate. And created a jks keystore and added that as a trust store in the SCT tool. But I am unable to establish connection to my MySQL database. I can do that using MySQL Workbench so I know my certs/password etc. work. Please let me know what I could be missing.

Hi, I am using DMS to move data (5.200.000 records) between an RDS SQL Server Express database and an RDS MySQL database. The number of records transferred is perfect : every record count per table matches exactly. However when running the validation step I get a lot of mismatched records that I believe is an issue in the DMS validation process at the driver level (charset configuration probably): RECORD_DIFF [[{'Title': 'سلام ژوند څنګه دی (1) 😀🥳'}, {'Title': 'سلام ژوند څنګه دی (1) ??'}],] If I do the select query directly in the MySQL database with MySQL Workbench I get exactly the same emojis as I the DMS validation tool gets from the SQL Server. Besides this I am also getting a lot of RECORD_DIFF [[{'Content': '<VALUE_PRINT_NOT_SUPPORTED>'}, {'Content': '<VALUE_PRINT_NOT_SUPPORTED>'}],] which I am not sure what they mean querying both database I see the values perfectly for those rows. Any help will be greatly appreciated. Thanks, Claudio.

Hi, When running a DMS task from MySQL to Redshift (CDC), I'm seeing an error like the below. "Failed to get blob column values for table..." I don't see any specific reasons for this error in the cloud watch logs. Anyone know why BLOB fields are having this problem migrating to Redshift during DMS? For reference, below are the LOB-related task settings I have set: "InlineLobMaxSize": 0, "SupportLobs": true, "LobChunkSize": 0, "LobMaxSize": 63, "FullLobMode": false, "LimitedSizeLobMode": true, Thanks!

What is the best DB service in AWS to store XML data? I do not want to convert to JSON. Need to retrieve XML and display on UI when needed. Size may not be more than 200 KB max. Today it is stored in oracle as BLOB/CLOB which we want to redesign.

Hello, We're facing an issue where when running a DMS task with a MySQL source to a Redshift target, columns containing non-ASCII values are being converted/replaced with UTF-8 hex values. Is there a way to disable this? We would like to keep these values as-is when migrated over to Redshift. ***For example, the below source on MySQL is automatically converted to the below target value when DMS'd to Redshift:** Source value(MySQL): ' feW®ê EªAÆð sWÊ' zÒ*%ÿ 5 ß ' ' Target value(Redshift): '92666557AEEA1E45AA41C6F0160A7357CA27027AD22A25FF1135020ADF122706' Edit: Issue is actually with BLOB datatypes in general. BLOB data types from MySQL are automatically converted to hex values when DMSed to Redshift. Thanks!

Hello, We use DMS to replicate data from Sql Server to Aurora Postgresql. One scenario we've found recently is that the inserts and updates on sql server are slow to get applied to postgres. The odd thing is that we can stop the task, resume it, and it will catch up immediately. It doesn't happen in particularly busy times, its often coped very well with large loads, however, in quieter periods it can seem to replicate very few inserts/updates/etc over the course of a few hours, fall further behind, but essentially stopping the task and resuming it seems to fix it. Appreciate anyone taking the time to read and think about this question, any additional information I will try to provide. Thanks

When will AWS enable creating Database Migration Service (DMS) endpoint for PostgreSQL version 14.0 and 14.1?

Good evening everyone. I'm trying to migrate some tables from RDS MySQL to OpenSearch through DMS. However, I have a problem with decimal columns. When configuring the task and sending it to OpenSearch, the DMS is treating it as a Float. But Float doesn't have the precision I need (decimal(10,2)) and it distorts the data. OpenSearch supports both double and scaled_float, but if in my index I map the column with these types I get DMS error like: > "cannot be changed from type [scaled_float] to [float]" From the documentation it seems that DMS only supports int and float for numbers. (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Elasticsearch.html) I would like to know if there is any alternative to this.

I'm using DMS to move a MySQL 5.7 database from one AWS account to another. I'm not changing versions or schemas or anything, just migrating it to a new RDS instance. Everything was going pretty well until I ran into the "VALUE_PRINT_NOT_SUPPORTED" error. This error only seems to happen on MySQL "TEXT" fields, and only on some of them. Looking at my table statistics I can see that there are 10 tables that have a Validation State of "Mismatched records". When I query the "awsdms_validation_failures_v1" table in the TARGET database I can see these errors are listed as "VALUE_PRINT_NOT_SUPPORTED". {'contact_notes': '<VALUE_PRINT_NOT_SUPPORTED>'}, {'contact_notes': '<VALUE_PRINT_NOT_SUPPORTED>'} What is this error trying to tell me? Are there characters in those records that can't be migrated? Is the content on the fields too large? Is there some other error?

When we pause our mongo connections on DMS for some hours and the resume point is no longer in the oplog we are unable to restart or resume our task. Is there any way we can ignore these errors and continue replication or setup a hearbeat similar to Postgres RDS endpoint to update the resume point? The only fix we found was deleting the task and recreating it. ``` Resume of change stream was not possible, as the resume point may no longer be in the oplog ```

I have an uncrypted rds mysql, inside there are several databases so I can't afford a stop of all my applications. I have thought to create a snapshot of my actual rds mysql, create a new encrypted mysql db and then import this snapshot into new rds instance. If this isn't possible maybe can I use DMS to sync my actual uncrypted db with the new one encrypted ?

Hello All, As of now, SCT doesn't support SAP HANA as one of the source databases. Any other way to migrate the SAP HANA database (schemas, views, procedures, function....) to Redshift? I understand that for data migration, there are many available options (SAP Data Services, Snow family, Glue etc....). Thank You! Rajendran

I have data in mongodb and I want to use some of the properties saved in that database to start and maintain a graph database using amazon neptune. I can use aws dms to get the initial batch of data, but how about maintaining and updating the data as it changes in mongodb? Essentially I need to keep the neptune database synced up with the mongodb database because mongodb remains my primary database. As a simple example, consider that mongodb contains, among other things, users' lists of subscribers. I want this info available for graph queries in neptune, but changes to that info are saved in mongodb primiarily so they need to be forwarded to neptune to keep neptune current. Is there a best-practice for doing something like this? The bulk loader and dms options implicitly assume that I'd be inserting large amounts of data at a time, but these may be simple small changes like one user subscribing to another user. What's the best/correct/recommended way to go about this?

Hello all, just wondering if anyone had any solutions for extracting data from DB2 zOS into AWS RDS or redshift using DMS. Struggling to find an effective solution.

Hello, What is the recommended way to create a snapshot (for backup) of an RDS Postgres instance to apply schema changes? We want to maintain both replicas in sync, minimize downtime and keep the new replica up to date. We want to create and keep a snapshot in case we need to rollback these schema changes. I have made research and found https://aws.amazon.com/getting-started/hands-on/move-to-managed/migrate-postgresql-to-amazon-rds/ not sure if this feature can be used for this purpose. Will live schema changes break the migration? Our schema changes are simple, we are adding nullable new columns to existing tables and we are also creating new tables. Thank you in advance.

Hi We are creating a DMS task that we were expecting to use a view in a Aurora Postgres service as data source, for a single use table migration. But it seems that views arent actually supported in this specific source case .... any reason for this? Theres a column which serves as primary key for that view Below is the message when creating the DMS task Error in mapping rules. Rule with ruleId = 042389161 failed validation. view selection is not available for aurora-postgresql source type In the meantime, im looking towards AWS Glue to do the procedure, and in the "worst" case, create a procedure to do the task / load process Any tips would be great for the ones that may fall for the same case best regards

I have used DMS with plain redshift. however, I created a redshift server less instance, it does not seem to work.

Want to know which AWS service will help me to transfer data from RDS to Keyspaces Cassandra on real time basis. #Case I am using RDS MYSQL as a production database and i have also created a read replica for the same. Now i want that as and when new data updated on my database then it automatically transferred to keyspace cassandra for further use.

We need to replicate data between two collections in AWS documentDB to get rid of duplicate documents. Source and Target is AWS documentDB instances version 4.0.0. I've created a unique index in target table to only allow non-duplicate values. I needed to create index before migrating the data to new target, because our data size in ~1TB and index creation on source collection is impossible. Full load fails after the following error. Task status becomes table error and no data is migrated further to that collection. ``` 2022-03-23T03:13:57 [TARGET_LOAD ]E: Execute bulk failed with errors: 'Multiple write errors: "E11000 duplicate key error collection: reward_users_v4 index: lockId", "E11000 duplicate key error collection: reward_users_v4 index: lockId"' [1020403] (mongodb_apply.c:153) 2022-03-23T03:13:57 [TARGET_LOAD ]E: Failed to handle execute bulk when maximum events per bulk '1000' was reached [1020403] (mongodb_apply.c:433) ``` ``` "ErrorBehavior": { "FailOnNoTablesCaptured": false, "ApplyErrorUpdatePolicy": "LOG_ERROR", "FailOnTransactionConsistencyBreached": false, "RecoverableErrorThrottlingMax": 1800, "DataErrorEscalationPolicy": "SUSPEND_TABLE", "ApplyErrorEscalationCount": 1000000000, "RecoverableErrorStopRetryAfterThrottlingMax": true, "RecoverableErrorThrottling": true, "ApplyErrorFailOnTruncationDdl": false, "DataTruncationErrorPolicy": "LOG_ERROR", "ApplyErrorInsertPolicy": "LOG_ERROR", "ApplyErrorEscalationPolicy": "LOG_ERROR", "RecoverableErrorCount": 1000000000, "DataErrorEscalationCount": 1000000000, "TableErrorEscalationPolicy": "SUSPEND_TABLE", "RecoverableErrorInterval": 10, "ApplyErrorDeletePolicy": "IGNORE_RECORD", "TableErrorEscalationCount": 1000000000, "FullLoadIgnoreConflicts": true, "DataErrorPolicy": "LOG_ERROR", "TableErrorPolicy": "SUSPEND_TABLE" }, ``` How can I configure AWS DMS to continue even if such duplicate key errors keep on happening. I tried modifying the TableErrorEscalation count and many other error counts but loading always stops at first duplicate key error. I have 580k Documents in test workload for this task.

We have a Table A with a DMS task setup to do continuous replication on this from the source database (SQL Server Instance on EC2) to target database (Aurora Postgres). This table has 10 columns, one of which is of type text (I know this bad), we will call this column, 'INSTRUCTIONS'. We also monitor blocking on this table and on occasions we can see blocking issues arise where our DMS user is being blocked trying to run the following query: SELECT Instructions FROM dbo.TableA Where ID = @p1 The blocking is not the issue, what I'm wondering is why is DMS querying the table directly, should it not be using transactional replication and reading from the T-Log. Any help on this would be appreciated as I cannot see any mention of DMS reading from the table in replication mode in the docs. Thanks.

We are having trouble migrating on-premise MySQL 8.0.26 to AWS Aurora MySQL. For this project we're using AWS Database Migration Service (DMS) but it seems we are reaching the limit of the amount of databases to migrate. When we try to migrate over 500+ databases with one task, the task fails with the error "No tables were found at task initialization". When we edit the filter to migrate < 500 databases, the task starts successfully. Can anyone confirm that 500 databases is the limit for DMS? We have to do over 7k databases, but filtering those will not be easy. Is it possible to increase the current limit of 500? Kind regards, Lennard.

We have an EC2 instance with SQL Server installed and AWS DMS Replication set up to sync changes from Database A - Table A to an Aurora Postgres Database A Table A. This has been working fine for the last year for inserts/updates/deletes. We recently added a new field (LastUpdateDate) into Table A in SQL Server of type datetimeoffset, this was automatically added to the corresponding Table A in Aurora Postgres of type varchar(34). This works fine for insert and updates. However if the column LastUpdateDate is NULL and we delete this row on the source (SQL Server), an error is throw in DMS where it is attempting to do an update of the corresponding row in Aurora Postgres but trying to set the LastUpdateDate to an invalid date (10000-01-01 04:17:59.0000000 +04:18), this is 35 characters long and so fails to insert into the LastUpdateField which is on 34 characters. My question is we are only doing a delete, where is this update coming from? Where is this strange date coming from? It is not occurring on the SQL Server side as we have no triggers on the table, have cdc enabled and can see that only a Delete operation was done. The delete operation does succeed but we still have these update errors showing up in our 'public.awsdms_apply_exceptions table.' Any help would be appreciated. Thanks

Hi Guy I need some help / advise. Is it possibe to install really old service portal on AWS? Apache 2.0 Handler PHP Version 5.3.3-7+squeeze9 PGSQL v. 8.4 I have to somehow take over it from the last owner and install somewhere. Just to ask you if you think it is doable? Thanks Marta

Hi, I have a DMS task to move data from Oracle CDC to Redshift. (Full Load and ongoing changes). During the full load, the task just keeps on running and is not reaching a 100%. In the target, I can see the row count is already 4 times the row count in the source. There are 4x full row duplicates in the target. Can anyone please guide what could be the issue here and how to debug? thnx.

Does AWS DMS support using Oracle Application Container Database ( Application root and application PDB ) as a source ? If so, are there differences in the way replication setup is configured to replicate data from application root ( common data) and data from application container PDB ?

Hi, When deploying DMS in a multi-az configuration, I understand the standby replication instance will be in a different AZ and sat in a different subnet. In the event the standby replication instance is called into action as per the documentation; *"If the primary replication instance fails or becomes unresponsive, the standby resumes any running tasks with minimal interruption"* I am correct in thinking you can specify the standby replication instance IP address from the subnet of the AZ? - Reason being is I use DMS within private subnets that connect to a client via a site-to-site vpn, so I often have to specify the subnets in use and also the IP addresses of the replication instance. There is no such thing as a floating or shared private IP for the primary and standby replication instances? Thanks, David

we are using Terraform as CICD, I want to migrate a PostgressSQL Database to S3 bucket, I need CDC and Data validation, when I run the terraform, I have this error: ValidationSettings are not supported for 's3' endpoint I don't see anywhere in the AWS documentation that saying S3 endpoint can't do data validation. How to solve this issue? Can I do or not the data validation with S3 as target?

Hi, I started a DMS task last week, and after letting it run for three days, I stopped adding new tables to the process, and when I tried to activate it again, I've received the message: ``` Last Error Task 'PMZPTPTYYJZGJQLBZCBYC7GSUQMWC2RICPEY6DA' was suspended due to 6 successive unexpected failures Stop Reason FATAL_ERROR Error Level FATAL ``` Looking at the logs, I found this error message 6x consecutively: ``` 2022-02-14T13:57:39 [DATA_STRUCTURE ]E: SQLite general error. Code <19>, Message <UNIQUE constraint failed: events.identifier, events.eventType, events.detailMessage>. [1000505] (at_sqlite.c:525) ``` What does it mean? Which kind of actions should I take?

Hello: --- We have 18 DMS replicate tasks with origin endpoints from SQL server databases on premises to target ec2 SQL server endpoints. Same database versions. All tasks are replicating with this red status: "Running with errors". we have 2 x r5.2xlarge replication instances. cloudwatch logs are reporting always the same error. [DATA_STRUCTURE]E:SQLite general error. Code <19>, Message<UNIQUE constraint failed: events.identifier, events.eventType, events.detailMessage> What is causing this error? We dont know if the replication is going well

Hello! I'm reading through the docs that talk about AWS DMS selection rules and wildcards: 1. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Selections.html 2. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Wildcards.html I have a use-case where we're currently pulling in a few dozen unnecessary tables in a snapshot load from source (Postgres) to destination (Athena DataLake). I want to exclude MOST tables matching a certain pattern (`%_aud`, to be specific), but then there's one table matching that same pattern I want to explicitly keep (let's say its called `foo_aud`). I'd like to not have to write explicit exclusion rules for the ~20 or so other tables, so I'm hoping I can do something like this: (rules are specified in JSON in a cloudformation file) ```json { "rules": [ // ...our other selection rules... { "rule-type": "selection", "rule-id": "21", "rule-name": "excludeaudit", "object-locator": { "schema-name": "myschema", "table-name": "%_aud" }, "rule-action": "exclude" }, { "rule-type": "selection", "rule-id": "22", "rule-name": "includefooaudit", "object-locator": { "schema-name": "myschema", "table-name": "foo_aud" }, "rule-action": "include" } ] } ``` What will happen in the above example? Will DMS correctly exclude all `%_aud` tables, but then include `foo_aud`? Does the rule ordering matter - e.g. if I swap rules 22 and 21? (How is rule priority managed?) Or is there some other way I can achieve this? Thank you!

I am exploring DMS for migrating RDS to Aurora database. Can someone please explain how this migration type works: "Migrate existing data and replicate on-going changes" Time T1 - So, my application is talking Source database Time T2 - I use DMS to migrate data from source to target database. Time T3 - Data migration completes.. Time T4 - I change my application to use target database by changing the endpoint of the database My question is - Is the above correct ? What happens to data writes between Time T3 and T4 ? Does. "replicate on-going changes" , means, it will keep replicating the data from Source to Target till it is not stopped , and will make sure all writes get replicated to Target database ?

I have my Oracle instance setup on-premise. The version is 21c express edition. Does DMS work with this version of Oracle as the source ?

I have a database on my company's AWS server. We have hundreds but I am only concerned with two. Both are hosted on AWS. One is a MS SQL server and the other a MySQL. I need to periodically move an entire database from MSSQL *to* MySQL. They are large databases so MySQL workbench and SSIS often fail due to size. ~1-2 million records. Less than 10GB data. I have seen mentions of an Amazon service called DMS. Is there a service on Amazon that can do this? I do *not* need a permanent connection or sync between the two. Just 3-4 times a month to move the database. I do not want to mention this to my Workgroup until I know it can be done. Is Data Migration Service the tool I need? Are there any other AWS solutions ?

I am testing offline migration from MongoDB to DocumentDB using mongodump/mongorestore and got the following error: `mongorestore --username <username> --password <password> \ --uri="mongodb://my-documentdb-cluster.cluster-abcd1234.us-east-2.docdb.amazonaws.com:27017" \ –-sslCAFile rds-combined-ca-bundle.pem --db <dbname> /var/lib/mongodb/dumps/dump 2022-02-03T09:54:15.042-0500 error parsing command line options: error parsing positional arguments: provide only one polling interval in seconds and only one MongoDB connection string. Connection strings must begin with mongodb:// or mongodb+srv:// schemes 2022-02-03T09:54:15.042-0500 try 'mongorestore --help' for more information` Where did I do wrong? Thanks in advance!

DMS load fails from Oracle NUMBER(38) source to Aurora Postgres BIGINT (INT8) target. Passes premigration assessment. All values being migrated are below 2,000,000,000 (~BIGINT limit). The load succeeds if the target datatype is changed to Postgres NUMERIC. Inserting "102675852.0000000000" manually (using SQL) to the same column defined as BIGINT also works without error. The full error is "ERROR: 22P02: invalid input syntax for type bigint: "102675852.0000000000" ("LOCATION: scanint8, int8.c:127", "STATEMENT: COPY "(table name)"."(column name)" FROM STDIN WITH DELIMITER ',' CSV NULL 'attNULL' ESCAPE '\'"). Could work around by changing datatype after load, but the table is quite large so this takes much too long for our purposes.

Hello. via terraform I've created a stack with EKS. On EKS I did setup aws-load-balancer-ingress-conotroller and traefik. Before to destroy everything via terraform I didn't delete the 2 pods for my alb and traefik service. Doing so I'm not able to delete the eni created by these process. I'm root in my account and I run also the following command ``` aws ec2 detach-network-interface --attachment-id eni-attach-xxxxxxxx --force An error occurred (AuthFailure) when calling the DetachNetworkInterface operation: You do not have permission to access the specified resource. ``` Also I've a basic account, so I cannot contact the support in order to ask them to delete for me. Can please someone shed some light ? Thanks

i wnat to migrate my DB , so i use DMS service using client VPN but data is very scarce. I am trying to use DMS using client VPN. 2003 error keeps popping up. Could the client vpn not be able to use the DMS service?? If yes, how can I do it?

If you go into DMS service there is a tab for Event Subscription. if you register a topic, then you will get notification of every event that happens in the event tab. For example, then you reboot a DMS instance, there will be two events (replication application shutdown followed by replication application restarted.) if on the other hand, you use EventBridge, then you will only get a single event from CloudTrail called "RebootReplicationInstance". The same holds true with events surrounding tasks. We intentionally caused an issue with a task by deleting the source database. within the DMS application, we can see the following: * Replication task has started * Replication task has failed. Reason: Last Error ODBC general error. Stop Reason RECOVERABLE_ERROR Error Level RECOVERABLE. But if you look at the events emitted by EventBridge, you only see the StartReplicationTask coming from CloudTrails. Sorry for the long question, but why does EventBridge not provide the same sequence of events that you can see within the Application.

I created an event bridge rule which should trigger on any DMS event. Currently the event bridge rule sends the message to SNS which sends an email to my personal email address. within DMS I reboot a DMS instance which produces two events: *dms-instance replication-instance January 24, 2022, 11:45:33 (UTC-05:00) Replication application restarted dms-instance replication-instance January 24, 2022, 11:45:20 (UTC-05:00) Replication application shutdown * however, I don't see these two events triggering my Event Bridge Rule. Not sure if i am doing something wrong. In Event Bridge rule, the event pattern is: { "source": ["aws.dms"] } and the single target is: Name: Default_CloudWatch_Alarms_Topic Type: SNS topic (sends email)

I am trying to migrate MySQL ( version 8) which is hosted in Azure Redhat VM to the RDS instance using Database Migration Services. 1. I have created both source and target endpoints - connections are successful 2. Replication Instance has been created 3. Migration Task has been created. 4. However the progress of the task is 0 % from last 4 hours and not sure what is happening. Please help me with the same.

Details: **Source**: Postgres **Target**: S3 **Format**: parquet Hi Guys! Currently, in default mode, the DMS task is reading a boolean column in the source and automatically converting to a char format in the target. Theoretically Amazon DMS provides a step to transfor data in the mapping rules task. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Selections.html However after some attempts the data was not correctly converted in the target. The follow mapping transformations was tried. ```json { "rules": [ { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "test_schema", "table-name": "%" }, "rule-action": "include" }, { "rule-type": "transformation", "rule-id": "2", "rule-name": "2", "rule-action": "change-data-type", "rule-target": "column", "object-locator": { "schema-name": "test_schema", "table-name": "table_test", "column-name": "column1" }, "data-type": { "type": "int8" } }, { "rule-type": "transformation", "rule-id": "3", "rule-name": "3", "rule-action": "change-data-type", "rule-target": "column", "object-locator": { "schema-name": "test_schema", "table-name": "table_test", "column-name": "doc" }, "data-type": { "type": "boolean" } } ] } ``` I undertood that DMS automatically convert the from Boolean type to the char type, following the doc below. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html Ok, great...so the "doc" column is currently a "char" type. But, what's the reason the "doc" column has not converted to boolean type in the target when used the transformation above? Would it be for that reason? https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Transformations.html "*AWS DMS supports column data type transformations for the following DMS data types: "bytes", "date", "time", "datetime", "int1", "int2", "int4", "int8", "numeric", "real4", "real8", "string", "uint1", "uint2", "uint4", "uint8", "wstring", "blob", "nclob", "clob", "boolean", "set", "list" "map", "tuple*""

Could you please tell me whether there is any AWS service that can mask the content of S3 files? (Data masking/Anonymization). For Example, 1. Masking Name from John to 'abcd' 2. Masking Phone Number from 770012 to 11111 3. Masking Date of Birth from 2020-01-05 to 2000-11-11 Even after masking, the column's format should be preserved. Example, Date of birth should be in date format even after the masking Thanks & Regards, Aflah Kanhayi

I'm migrating some data from postgres to OpenSearch, but i'm struggling with migrating a set of coordinates. In postgres i have `latitude` and `longitude`, and i know DMS does not support geo types when using OpenSearch as a target. I wanted to work around this by using an ingest pipeline and an index template: ``` PUT _ingest/pipeline/my-pipeline-id { "description": "My optional pipeline description", "processors": [ { "set": { "field": "location.lon", "value": "{{{longitude}}}" } }, { "set": { "field": "location.lat", "value": "{{{latitude}}}" } } ] } ``` ``` PUT /_index_template/jobs_template { "index_patterns": [ "jobs*" ], "template": { "settings": { "index.default_pipeline": "my-pipeline-id" }, "mappings":{ "properties": { "location": { "type": "geo_point" } } } } } ``` I first tested having only the pipeline without the mappings and that part works. However, when i add a mapping to the template, and re run the migration task i get the following error ``` 2022-01-20T15:23:01 [TARGET_LOAD ]E: Elasticsearch:FAILED SourceTable:jobs TargetIndex:jobs Operation:INSERT_ENTRY RecordPKKey:93904e3c-5565-4469-94d6-e58fbecdc5a3 RecordPKID:217D5CE32D4EC983FE2C3CFD6048821EA2A95F3658122A80A7EEB3A6088EA89CES HttpCode:400 ESErrorResponse: { "error": { "root_cause": [ { "type": "illegal_argument_exception", "reason": "Rejecting mapping update to [jobs] as the final mapping would have more than 1 type: [_doc, doc]" } ], "type": "illegal_argument_exception", "reason": "Rejecting mapping update to [jobs] as the final mapping would have more than 1 type: [_doc, doc]" }, "status": 400 } ``` Is there any way to make this work? Any way to create a geo point so I can perform geo queries in OpenSearch?

Source: postgres Target: OpenSearch I have a `text` column called `description` in one of my postgres tables. Per the documentation, this data type is mapped to a `NCLOB`. Since OpenSearch does not not offer LOB support, my `description` is missing in my OpenSearch documents. I tried using the mapping rule bellow, but does not seem to be doing anything ``` { "rule-type": "transformation", "rule-id": "3", "rule-name": "3", "rule-target": "column", "object-locator": { "schema-name": "public", "table-name": "jobs", "column-name": "description" }, "rule-action": "change-data-type", "data-type": { "type": "string", "length": 500 } } ``` When i check the logs i see the following ``` Column 'description' is unsupported in table def 'public.jobs' since the LOB support is disabled ``` However, i do have LOB enabled under task settings: ``` "TargetMetadata": { "ParallelApplyBufferSize": 0, "ParallelApplyQueuesPerThread": 0, "ParallelApplyThreads": 0, "TargetSchema": "", "InlineLobMaxSize": 0, "ParallelLoadQueuesPerThread": 0, "SupportLobs": true, "LobChunkSize": 10, "TaskRecoveryTableEnabled": false, "ParallelLoadThreads": 0, "BatchApplyEnabled": false, "FullLobMode": true, "LimitedSizeLobMode": false, "LoadMaxFileSize": 0, "ParallelLoadBufferSize": 0 }, ``` Is that transformation rule supposed to work? Or will any LOB column be skipped because OpenSearch does not have LOB support? Any way to make this work? Thanks!

Hi I'm try migrating my website wordpress to aws, I try 3 plugins, didnt work, because size limite, other empty, Could you please help me migrate my wordpress to lightsail using wordpress too, all wp migation, updraflus these plugins not working. I will appreciate your help thanks

We have a database on a SQL Server Standard Edition version 2016 on an EC2 Instance and we have continuous replication for a small portion of the data over to an Aurora Postgres Database using DMS. Sometimes out of the blue, we notice that our data on Aurora has fallen behind, by as much as 90 mins, i.e. data was added in SQL Server but 90 mins later still has not been migrated over by DMS. This can happen times when there is not much a load on either database, i.e. there be only a few transactions to replicate over but nothing is happening. When looking at the logs of the DMS instance we do see this: "Reading from source is paused. Total storage used by swap files exceeded the limit 1048576000 bytes" but as I mentioned there is not much data to replicate and the DMS instance does not seem to be under much stress. Any ideas of what might be causing this would be most welcome. Thanks

I have setup DMS to connect to MySQL database as the source and Kafka as target. I tested the source and target flow without TLS authentication works fine.Now I want to setup DMS with Kafka endpoint using TLS authentication. I tried out this [link](https://docs.aws.amazon.com/msk/latest/developerguide/msk-authentication.html) to check if I am able to connect using TLS authentication to Kafka cluster using the certificates generated, which worked. I used this with Glue as well and the connectivity works. Now for DMS, I followed the steps provided [here](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Kafka.html) ( Section: Using SSL authentication ). But when I test the endpoint I get the error: `Test failed due to KMS key Exception : No permission to access Key 'arn:aws:kms:ap-southeast-2:xxxxxxxxxxxx:key/xxxxxxxxxxxxx'`. The KMS key here is the default key that DMS creates when we launch a replication instance. I tried using a CMK for DMS and created the endpoint using this KMS key. But it did not work. In this case, the endpoint errored out saying it could not connect to Kafka. Not really sure what the issue is. Has anyone come across this issue?

Test Endpoint failed: Application-Status: 1020912, Application-Message: Failed to connect Network error has occurred, Application-Detailed-Message: RetCode: SQL_ERROR SqlState: 08001 NativeError: 101 Message: [unixODBC]timeout expired i have tried setting ALL Traffic On my RDS Default security group and have public accessible rds set

Hi All, I have a storage gateway with file gateway as an instance on AWS , and im trying to migrate my gateway following the doc under this url : https://docs.aws.amazon.com/fr_fr/filegateway/latest/files3/migrate-data.html . but i found not Clear : 1/ For the instance that i should create ( if it's a new instance with an AMI of the old ) ? 2/ which instance type i should choose if i have : t3a.xlarge ? Thank you

We're running DMS on an on-prem Oracle database, with a destination to S3 (which we then load to Snowflake outside of DMS). We're finding the replication task will, seemingly at random times after working fine for a few hours, simply stop processing Oracle log files and so will report no source CDC changes, and will report zero latency (whereas the source latency generally fluctuates between 1 and 5s). Cloudwatch logs continue to show the heartbeat message (but few other messages do): ```[SORTER ]I: Task is running (sorter.c:736)``` After turning on DEBUG logging, we're seeing the following messages that appear to be related to this problem: ``` 2021-12-27T16:39:50:430405 [TASK_MANAGER ]D: There are 284 swap files of total size 1 Mb. Left to process 5 of size 1 Mb (replicationtask_cmd.c:1639) ``` And this error, which seemingly pops up much more frequently, so including it here, but not sure if it's related: ``` 2021-12-27T16:43:01:094411 [DATA_STRUCTURE ]E: SQLite general error. Code <19>, Message <UNIQUE constraint failed: events.identifier, events.eventType, events.detailMessage>. [1000506] (at_sqlite.c:475) ``` We're running a dms.r5.large, and can't otherwise find any patterns about when/why the issue appears (again, without any warnings, other errors, etc.). Restarting the task (stopping the task takes an unusually long time) fixes the problem and causes the task to "catch up" to where the updates stalled. Our current workaround is to set an alert looking for too-long a time of zero latency, and then having a lambda function stop and restart the task.

upgrading MySQL RDS instance 5.7.36 to 8.0 but it didn't get upgraded. We checked the prechecked compatibility log and found error "mysql - schema's default character set: utf8" how to fix this.

I am trying to use the AWS Schema Conversion Tool to convert a SQL Server instance running on RDS to Aurora MySQL. I have a large number of databases in the same SQL Server instance, and was able to successfully convert the schema for 5 of them. Then I attempted to use the "Refresh from Database" menu option for one of the databases and received an error stating > Metadata loading was interrupted because of data fetching issues. For details, see the log. On checking the logs, I see the following error that is causing the issue: > Error executing 'load-script-object-ext-properties' query: com.microsoft.sqlserver.jdbc.SQLServerException: Collection specified does not exist in metadata : 'sys' Now I am unable to see the schema of any database on that server, and they ALL fail with the same issue. I have tried creating a new SCT project, and using a different SQL login. I am completely blocked at the moment, and not sure how to progress my migration.

We have a PostgreSQL DB with single-tenant structure that include around 15k schemas. The data size is around 3 TB. We want to replicate the data of those schemas into one big MySQL multi-tenant DB. Note: all schemas have the same tables' structure, and the same for the target DB. Can DMS help us achieving that?

Hello, I have a nightly DMS pipeline setup to move data from an AWS Aurora RDS MySql instance to S3. The DMS process has gotten longer and longer. I only use a fraction of the rows that are migrated, and I'm wondering if using a source filter would speed up the DMS process tremendously. Is the source filter applied on the MySql database (hence speed depend on MySql indexing), or is it applied after the data is already migrated? Thanks for your help!

Hello, I have the Oracle 19c that running on my laptop, and I can connect to it from other laptops. Then I setup a endpoint to access to this Oracle, and provided my public ip address that I got it from [https://whatismyipaddress.com/]() , port, user and password. Then I tried to test this endpoint with created replication instance to check connection but received error **"Application-Status: 1020912"** and** "Login timeout"** Note: I disabled firewall on my laptop. And add rule to outbound with my public IP address in security group to allow connection access to my laptop. I'm going to use DMS for data migration from on-premise to Oracle RDS, so want to do some steps with my laptop in advance. Are there more steps I need to do at AWS then can access to my Oracle on my laptop? Please advise. Thank you.

I am running some tests to understand how a pipeline can be made from MongoDB Atlas, (MongoDB v 4.4) to AWS Redshift cluster for data analytics and BI, I have loaded sample data to a MongoDB cluster and I am trying to load the sample data to Redshift cluster using a Database migration tasks, I have a connection to both my source endpoint and my target endpoint tested, I have created the DMS replication instance and the Redshift cluster both in the same region, I have also set a security group for DMS instance and allowed inbound access on Redshift port 5439 from the DMS security group, I have also allowed inbound access on port 27017 on the DMS security group as that is the MongoDB port I am trying to connect to, after setting up the task finishes normally but not even a single row appears in the Redshift tables. I am really not able to understand what wrong am I doing as there I am not even able to see any obvious errors. Link with some screenshots to the question: https://stackoverflow.com/questions/70392492/no-data-appearing-in-redshift-from-mongodb-using-aws-dms

IHAC who is facing a couple of sync issues: Say we have a table T on mysql src db with a column C that is nullable. The table is fully loaded on the target and the DMS task is operating in CDC mode. If this table T gets altered so column C is made not nullable with a default, this change does not propagate , nor does the DMS task indicate an error. In addition, any new columns added to table T on the source, also do not propagate. When the DMS task is operating in CDC mode, new tables that are created without a Primary Key do not propagate to the target. It can break DMS replication (error shows in logs). Thank you

Hi there, I am planning to migrate our on-prem SQL Server to RDS SQL Server. Unfortunately, this db was managed by several people and we are not sure that all of the tables have a primary key. I wish to keep the on-prem db and RDS in sync for a couple of days, so I have to be 100% sure that all tables have a primary key. Do you know an automatic way, where I can check if all the tables (1000+) have a primary key? Cheers, Mark

Is this possible? I have to stagger my ETL processes out because DMS opens up ~34(+7regular) connections on our Postgres source database to complete one transfer task. The table count is 224 for this task/database (one example, there are many databases). If I don't stagger them the total connection count goes way above 100 and then our source server essentially crashes. It simply won't handle that many open connections at once. Is there any way to control the total connections AWS DMS uses per task job? Thanks Edited by: ted1111 on Sep 30, 2021 8:06 AM

Hi all, Has anyone come across the DMS migration task returning "Failed to prepare the table for validation"? I've been finding it very hard to find out what the problem is. The migration task doesn't provide detailed errors, even with the log level set to "debug". <https://ibb.co/ZGRmLtk> Regards, Vinny Osiro

Hi, I have a replication instance in AWS DMS which is using DocumentDB as the source and Elasticsearch as the target. Test connection is working with the DocumentDB, but not with ElasticSearch. I get the following error: `Test Endpoint failed: Application-Status: 1020912, Application-Message: Failed to connect to database.` Here is what I have done: Created a role which I have used in the Endpoint for ES, arn:aws:iam::XXX:role/DMS-ES-Access The above role has the following trust relationship: ``` { "Version": "2012-10-17", "Statement": \[ { "Sid": "1", "Effect": "Allow", "Principal": { "Service": "dms.amazonaws.com" }, "Action": "sts:AssumeRole" } ] } ``` And the following policy: ``` { "Version": "2012-10-17", "Statement": \[ { "Effect": "Allow", "Action": \[ "es:ESHttpDelete", "es:ESHttpGet", "es:ESHttpHead", "es:ESHttpPost", "es:ESHttpPut" ], "Resource": "*" } ] } ``` In the elasticsearch cluster, the access policy was modified to include the above role: ``` { "Version": "2012-10-17", "Statement": \[ .... { "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::XXX:role/DMS-ES-Access" }, "Action": "es:*", "Resource": "arn:aws:es:us-east-1:XXX:domain/YYY/*" }, ... ] } ``` The replication instance is in a public subnet with internet access and a public ip. Please help.

I'm trying for first time to use DMS to migrate and keep in sync a database while I migrate our site to aws but I keep getting : Last Error Failed in resolving configuration. Task error notification received from subtask 0, thread 0 \[reptask/replicationtask.c:2859] \[1020418] Error Code \[10002] : MySQL binary Logging must use ROW format; Errors in MySQL server binary logging configuration. Follow all prerequisites for 'MySQL as a source in DMS' from https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.MySQL.html or'MySQL as a target in DMS' from https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.MySQL.html ; Failed while preparing stream component 'st_0_ZJFFAMOOBGXIX26POQR3LG6TVKDJFXFVACA4QCQ'.; Cannot initialize subtask; Stream component 'st_0_ZJFFAMOOBGXIX26POQR3LG6TVKDJFXFVACA4QCQ' terminated \[reptask/replicationtask.c:2866] \[1020418] Stop Reason FATAL_ERROR Error Level FATAL I did follow all steps and source database it's already in ROW format task: arn:aws:dms:us-east-1:254218654658:task:RNPUCUQVEMS4Q46SV6FX2GCTW36VFJ6ZELIUTJI

Have been trying to figure out for couple days. tried various combinations. -Full load works. -Destination PG table has primary key. -Very small table. 10 rows. -Enabled detailed logging. -When the ongoing replication runs, cloud watch says "Reading file returned no data (2) (csv_util.c:282)" - CDC files do have Op, table, schema columns Anyone has any suggestions? please suggest. Thanks

I attempted to configure my RDS postgres instance for CDC using [Setting up an Amazon RDS PostgreSQL DB instance as a source][1] I configured the DMS source endpoint to use a specific slotName. First using a slot that already existed in the DB and then using a slot I created using pg_create_logical_replication_slot. After successfully testing the endpoint to ensure connectivity, I started the replication task with was to load existing data followed by on-going changes. Both times, the replication failed with a fatal error such as the following and no data (even the existing data that should have loaded) gets replicated. Last Error Stream Component Fatal error. Task error notification received from subtask 0, thread 0 [reptask/replicationtask.c:2822] [1020101] Error executing source loop; Stream component failed at subtask 0, component st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y ; Stream component 'st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y' terminated [reptask/replicationtask.c:2829] [1020101] Stop Reason FATAL_ERROR Error Level FATAL What is the configuration I'm missing to enable CDC from RDS Postgres v11.8? I'm configuring DMS to use the RDS master user. A migration existing data only DMS task succeeds on this instance (if the slotName configuration is removed). The relevant CloudWatch log entries appear to be these 2021-02-03T22:03:05 [SOURCE_CAPTURE ]I: Slot has plugin 'test_decoding' (postgres_test_decoding.c:233) 2021-02-03T22:03:05 [SOURCE_CAPTURE ]I: Initial positioning requested is 'now' (postgres_endpoint_capture.c:511) 2021-02-03T22:03:05 [SOURCE_CAPTURE ]E: When working with Configured Slotname, user must specify LSN [1020101] (postgres_endpoint_capture.c:517) 2021-02-03T22:03:05 [TASK_MANAGER ]I: Task - W6AUC5OI3DNFMFUQ6ZDEYYNZ3NBSABQK3HFD2WQ is in ERROR state, updating starting status to AR_NOT_APPLICABLE (repository.c:5101) 2021-02-03T22:03:05 [TASK_MANAGER ]E: Task error notification received from subtask 0, thread 0 [1020101] (replicationtask.c:2822) 2021-02-03T22:03:05 [TASK_MANAGER ]E: Error executing source loop; Stream component failed at subtask 0, component st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y ; Stream component 'st_0_KVPUHIZBICJJRNGCC32HP5EFGGLIZLRF2YSRJ6Y' terminated [1020101] (replicationtask.c:2829) 2021-02-03T22:03:05 [TASK_MANAGER ]E: Task 'W6AUC5OI3DNFMFUQ6ZDEYYNZ3NBSABQK3HFD2WQ' encountered a fatal error (repository.c:5194) 2021-02-03T22:03:05 [SORTER ]I: Final saved task state. Stream position , Source id 0, next Target id 1, confirmed Target id 0, last source timestamp 0 (sorter.c:803) Other items in the log appear to be informational to track progress. [1]: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.RDSPostgreSQL.CDC

I seem to be missing something, I ran a test run of DMS and all my tables were properly migrated. However none of my views came along for the ride. Is there a way to include the views in the migration. Are there are other things that are not migrated by default? Triggers, stored procedures, functions? Thanks!

2021-01-14T23:00:59.000+08:00 2021-01-14T15:00:59 \[AT_GLOBAL ]I: Task Server Log - 3DD5OUFBJIVNLDZTIWAKBVENSW3SDHDKLOXO7YA (V3.4.3.R1 localhost.localdomain Linux 4.14.59-64.43.amzn1.x86_64 #1 SMP Thu Aug 2 21:29:33 UTC 2018 x86_64 64-bit, PID: 26697) started at Thu Jan 14 15:00:59 2021 (at_logger.c:2700) 2021-01-14T23:00:59.000+08:00 2021-01-14T15:00:59 \[DATA_STRUCTURE ]I: SQLite version is 3.31.1 (at_sqlite.c:174) 2021-01-14T23:00:59.000+08:00 2021-01-14T15:00:59 \[VALIDATOR ]I: validation_util_class_initialize (validation_util.c:70) 2021-01-14T23:00:59.000+08:00 2021-01-14T15:00:59 \[VALIDATOR ]I: Creating Table Def Mutex (validation_util.c:74) 2021-01-14T23:00:59.000+08:00 2021-01-14T15:00:59 \[VALIDATOR ]I: ==> Success Creating Table Def Mutex (validation_util.c:82) 2021-01-14T23:00:59.000+08:00 2021-01-14T15:00:59 \[COMMON ]D: at_common_is_supported: load_common_wrapper( common_load_locker ) failed (at_common.c:69) 2021-01-14T23:01:00.000+08:00 2021-01-14T15:01:00 \[TASK_MANAGER ]I: Execute Request Task '3DD5OUFBJIVNLDZTIWAKBVENSW3SDHDKLOXO7YA' running full load and CDC with flags fresh start with cdcPosition null and stop_at null (replicationtask.c:753) 2021-01-14T23:01:00.000+08:00 2021-01-14T15:01:00 \[TASK_MANAGER ]I: BatchApplyPreserveTransaction is set to false, because BatchApplyEnabled is false (replicationtask.c:1100) 2021-01-14T23:01:00.000+08:00 2021-01-14T15:01:00 \[TASK_MANAGER ]I: Task '3DD5OUFBJIVNLDZTIWAKBVENSW3SDHDKLOXO7YA' starting full load and CDC in fresh start mode (replicationtask.c:1312) 2021-01-14T23:01:00.000+08:00 2021-01-14T15:01:00 \[TASK_MANAGER ]W: The "Transactional apply" option is not available when MongoDB is the target endpoint. The "Batch optimized apply" option will be used instead. (replicationtask.c:1541) 2021-01-14T23:01:00.000+08:00 2021-01-14T15:01:00 \[TASK_MANAGER ]I: Task Id: 1df77829-2a03-4d26-ba83-941e13de6edb (replicationtask.c:3279) 2021-01-14T23:01:00.000+08:00 2021-01-14T15:01:00 \[TASK_MANAGER ]I: LOB support on the task has been enabled (endpointshell.c:1792) 2021-01-14T23:01:00.000+08:00 2021-01-14T15:01:00 \[TASK_MANAGER ]I: Task is running in Limited LOB Mode. MaxLobSize is set to '32768' Bytes (endpointshell.c:1795) 2021-01-14T23:01:00.000+08:00 2021-01-14T15:01:00 \[METADATA_MANAGE ]I: Source endpoint 'mongoDB' is using provider syntax 'MongoDB' (provider_syntax_manager.c:622) 2021-01-14T23:01:00.000+08:00 2021-01-14T15:01:00 \[METADATA_MANAGE ]I: Connection string: 'mongodb://172.16.3.147:27017/?retryWrites=false' (mongodb_imp.c:408) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[METADATA_MANAGE ]I: MongoDB version: 4.0.1 (mongodb_imp.c:210) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[METADATA_MANAGE ]I: Target endpoint 'mongoDB' is using provider syntax 'MongoDB' (provider_syntax_manager.c:628) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[METADATA_MANAGE ]I: Connection string: 'mongodb://mongo:****@inventorydb.cluster-c3jaqp3qyz8v.us-east-2.docdb.amazonaws.com:27017/?retryWrites=false&authSource=admin&authMechanism=SCRAM-SHA-1' (mongodb_imp.c:408) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[METADATA_MANAGE ]I: MongoDB version: 4.0.0 (mongodb_imp.c:210) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[TASK_MANAGER ]I: Preparing all components (replicationtask.c:1935) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[TASK_MANAGER ]I: Task - 3DD5OUFBJIVNLDZTIWAKBVENSW3SDHDKLOXO7YA is in STARTING state, updating starting status to AR_PREPARING_COMPONENTS (repository.c:5111) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[TASK_MANAGER ]I: Creating threads for all components (replicationtask.c:1968) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[TASK_MANAGER ]I: Task - 3DD5OUFBJIVNLDZTIWAKBVENSW3SDHDKLOXO7YA is in STARTING state, updating starting status to AR_CREATING_TREADS (repository.c:5111) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[TASK_MANAGER ]I: Task - 3DD5OUFBJIVNLDZTIWAKBVENSW3SDHDKLOXO7YA is in STARTING state, updating starting status to AR_CREATING_TABLES_LIST (repository.c:5111) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[TABLES_MANAGER ]I: Calling for get capture table list from the Metadata Manager started. (tasktablesmanager.c:928) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[TABLES_MANAGER ]I: Calling for get capture table list from the Metadata Manager ended. (tasktablesmanager.c:935) 2021-01-14T23:01:01.000+08:00 复制 2021-01-14T15:01:01 \[TASK_MANAGER ]E: No tables were found at task initialization. Either the selected table(s) no longer exist or no match was found for the table selection pattern(s). \[1021707] (replicationtask.c:2107) 2021-01-14T15:01:01 \[TASK_MANAGER ]E: No tables were found at task initialization. Either the selected table(s) no longer exist or no match was found for the table selection pattern(s). \[1021707] (replicationtask.c:2107) 2021-01-14T23:01:01.000+08:00 2021-01-14T15:01:01 \[TASK_MANAGER ]E: Task '3DD5OUFBJIVNLDZTIWAKBVENSW3SDHDKLOXO7YA' failed \[1021707] (replicationtask.c:3316) 2021-01-14T23:01:30.000+08:00 2021-01-14T15:01:30 \[TASK_MANAGER ]I: Task - 3DD5OUFBJIVNLDZTIWAKBVENSW3SDHDKLOXO7YA is in ERROR state, updating starting status to AR_NOT_APPLICABLE (repository.c:5103) 2021-01-14T23:01:30.000+08:00 2021-01-14T15:01:30 \[TASK_MANAGER ]E: Task '3DD5OUFBJIVNLDZTIWAKBVENSW3SDHDKLOXO7YA' encountered a fatal error (repository.c:5196) 2021-01-14T23:01:33.000+08:00 2021-01-14T15:01:33 \[METADATA_MANAGE ]I: Destroying mongoc client: '23271072968192' (mongodb_imp.c:1153) 2021-01-14T23:01:33.000+08:00 2021-01-14T15:01:33 \[METADATA_MANAGE ]I: Destroying mongoc client: '23271073330768' (mongodb_imp.c:1153) 2021-01-14T23:01:40.000+08:00 2021-01-14T15:01:40 \[TASK_MANAGER ]I: Task Management thread terminated abnormally (replicationtask.c:3969) 2021-01-14T23:01:40.000+08:00 2021-01-14T15:01:40 \[AT_GLOBAL ]I: Closing log file at Thu Jan 14 15:01:40 2021 (at_logger.c:2548)

Issue- Can not create a new replication task from either the console or aws cli. Details Source - Aws RDS postgres version 10 Target - Aws Redshift cluster with 4 nodes Both the source and target are in same VPC, there are 5-6 tasks running already which seem to be working fine. I have tested the endpoint connection for the new task beforehand, it shows successful. Errors: Console error : SYSTEM ERROR MESSAGE:An internal error has occurred. Please try your query again at a later time. aws cli --debug error: 2021-01-13 00:26:00,174 - MainThread - botocore.parsers - DEBUG - Response headers: {'x-amzn-RequestId': '0975d842-34b5-455f-be83-f2aed1f301a0', 'Content-Type': 'application/x-amz-json-1.1', 'Content-Length': '117', 'Date': 'Tue, 12 Jan 2021 18:55:59 GMT', 'Connection': 'close'} 2021-01-13 00:26:00,174 - MainThread - botocore.parsers - DEBUG - Response body: b'{"__type":"InternalFailure","message":"An internal error has occurred. Please try your query again at a later time."}' This error message is completely opaque can not figure out where to look. Please help. Thanks

Good morning, I have a DMS task which failed last night. I tried to restart the task and now its stuck in a "starting" state. I am not sure how to proceed as I can not stop it and I can not shutdown or modify the migration instance. Any advice on what I can do would be appreciated. -Andy

I have 2 RDS instances (one mysql and one postgres) and I need to enable encryption after they were already created. The DBs are large, and I am concerned about potential downtime required to create a snapshot, restore the DB, and then complete the warming process. I estimate it would take ~5 hours to create the snapshot (Postgres ~1.1TB) and ~24 hours for the mysql (~4.5 TB). Then there is time to restore from snapshot and warm the DBs, and then they'd need to (I'm assuming) catch up transactions that may be happening during that gap of time. I'm thinking I would need to make sure they have a multi-AZ DB. Then create a snapshot for the DBs, encrypt the snapshot and restore to a new RDS instance. Then, could I use AWS DMS do on-going replication for the DB to catch up the transactions and then flip over to the new DB when there is an available small window for downtime? Is there a better approach or recommendation?

Full Load, ongoing replication data migration from postgres to postgres RDS using Data Migration Service. Migration task is failing and throwing the below error: Last Error: Unable to use plugins to establish logical replication on source PostgreSQL instance. Follow all prerequisites for 'PostgreSQL as a source in DMS' from https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html Task error notification received from subtask 0, thread 0 \[reptask/replicationtask.c:XXXX] Remaining 1 error messages are truncated. Check the task log to find details Stop Reason FATAL_ERROR Error Level FATAL Any suggestion or ideas are welcome. Thanks in advance!

We had a DMS source endpoint that was connecting to an Aurora Postgres 10.7 database and it was working fine. We upgraded the database to Aurora Postgres 11.8 and now the endpoint connection test is failing. A DMS target to the same database is working fine, so it appears the replication instance is setup correctly. The error message is: Test Endpoint failed: Application-Status: 1020912, Application-Message: Failure in resolving database version. ODBC general error., Application-Detailed-Message: Failed retrieve database backend version RetCode: SQL_ERROR SqlState: XX000 NativeError: 1 Message: ERROR: rds_activity_stream stack item 9 not found on top - cannot pop; Error while executing the query Failed (retcode -1) to execute statement Is Aurora Postgres 11.8 supported as a source by DMS? Edited by: mnpete on Nov 19, 2020 7:16 AM

Hi, I have a mysql -> redshift replication that is frequently pausing CDC with the following message: "Reading from source endpoint temporary paused as total storage used by swap files exceeded the limit for task" I've tried increasing the values for BatchApplyMemoryLimit & MemoryLimitTotal, but it hasn't seemed to change the behavior, any suggestion on how I should troubleshoot this issue?

I am using *AWS DMS* to perform migration from on premise **SQL Server to Aurora PostgreSQL**. 1/ After performing the data migration task step, I am seeing same table name twice in PostgreSQL database. One table was created during schema conversion tool and 2nd table was created during data migration task. Is there any option in data migration task to prevent creation of new table,if it exists. 2/ I am also facing issues when converting the schema using AWS Schema Conversion Tool.It seems SCT is not supporting auto conversion of computed fields, temp tables and trigger objects to targeted Aurora PostgreSQL database. Does SCT automate part of the process, and not all of the process.