Troubleshoot SQL Server varbinary(max) to PostgreSQL bytea using AWS DMS

Question:

I'm trying to migrate some SQL Server tables with varbinary(MAX)  data type defined columns using the AWS Database Migration Service (DMS).   The target is AWS RDS PostgreSQL

I've noticed the LOB sizes are different when migrated to the targtet PostgreSQL environment , can you direct me in the right direction?

The source SQL Server data type for the column is :  varbinary(max) 

The target PostgreSQL server data type is  : bytea

Answer: 

FIRST STEP -  as a basic initial investigation compare the  byte length of the LOB columns across SQL Server & PostgreSQL. Use these queries to compare the source & target tables.

On SQL Server :

select datalength(BLOBData) as bytes from myTable;

On PostgreSQL:

---You can use the length() function to get the length in bytes:
select length(blobdata) as bytes from myTable ;

bytea is a sensible choice as an equivalent for varbinary(max)

Using "large objects" (aka "oid") only make sense if you have values > 1GB or if you need the streaming capabilities that large objects offer.

These are the recommended source and target data types 

SECOND STEP- you need to review the DMS LOB configuration settings . If these configurations are not set correctly , you may experience unexpedted outcomes.

In the official AWS documentation for these are basic descriptions

. Limited LOB mode migrates all LOB values up to a user-specified size limit (default is 32 KB). LOB
values larger than the size limit must be manually migrated. Limited LOB mode, the default for all
migration tasks, typically provides the best performance. However, ensure that the Max LOB size
parameter setting is correct. Set this parameter to the largest LOB size for all your tables.

. Full LOB mode migrates all LOB data in your tables, regardless of size. Full LOB mode provides the
convenience of moving all LOB data in your tables, but the process can have a significant impact on
performance.

The Full LOB mode will not convert\migrate the source column onto the target  RDS PostgreSQL if the source Table does not have a primary key created. 

There are a number of other associated memory problems related to migrating LOBs using AWS DMS. 

• AWS DMS processes LOBs in memory. This operation requires a fair amount of memory.

A  Common error when migrating LOBs through AWS DMS is :  

Last Error Replication task out of memory. Stop Reason FATAL_ERROR Error Level FATAL

Review your  DMS task setting - and read through Troubleshooting AWS DMS error "Last Error Replication task out of memory. Stop Reason FATAL_ERROR Error Level FATAL"