Using Babelfish as a target for AWS Database Migration Service

The administrator user name (the Admin user) used to create the database.

PSQL login and user with the sufficient SELECT, INSERT, UPDATE, DELETE, and REFERENCES permissions.

Only table preparation mode “Do Nothing“ is supported.

The ROWVERSION data type requires a table mapping rule that removes the column name from the table during the migration task.

The sql_variant data type isn't supported.

Full LOB mode is supported. Using SQL Server as a source endpoint requires the SQL Server Endpoint Connection Attribute setting ForceFullLob=True to be set in order for LOBs to be migrated to the target endpoint.

Replication task settings have the following limitations:

{
   "FullLoadSettings": {
      "TargetTablePrepMode": "DO_NOTHING",
      "CreatePkAfterFullLoad": false,
      }.
    
}
                        

TIME(7), DATETIME2(7), and DATETIMEOFFSET(7) data types in Babelfish limit the precision value for the seconds portion of the time to 6 digits. Consider using a precision value of 6 for your target table when using these data types. For Babelfish versions 2.2.0 and higher, when using TIME(7) and DATETIME2(7), the seventh digit of precision is always zero.

In DO_NOTHING mode, DMS checks to see if the table already exists. If the table doesn't exist in the target schema, DMS creates the table based on the source table definition, and maps any user defined data types to their base data type.

An AWS DMS migration task to a Babelfish target doesn't support tables that have columns using ROWVERSION or TIMESTAMP data types. You can use a table mapping rule that removes the column name from the table during the transfer process. In the following transformation rule example, a table named Actor in your source is transformed to remove all columns starting with the characters col from the Actor table in your target.

{
 	"rules": [{
		"rule-type": "selection",is 
		"rule-id": "1",
		"rule-name": "1",
		"object-locator": {
			"schema-name": "test",
			"table-name": "%"
		},
		"rule-action": "include"
	}, {
		"rule-type": "transformation",
		"rule-id": "2",
		"rule-name": "2",
		"rule-action": "remove-column",
		"rule-target": "column",
		"object-locator": {
			"schema-name": "test",
			"table-name": "Actor",
			"column-name": "col%"
		}
	}]
 }

For tables with identity or computed columns, where the target tables use mixed case names like Categories, you must create a transformation rule action that converts the table names to lowercase for your DMS task. The following example shows how to create the transformation rule action, Make lowercase using the AWS DMS console. For more information, see Transformation rules and actions.

Prior to Babelfish version 2.2.0, DMS limited the number of columns that you could replicate to a Babelfish target endpoint to twenty (20) columns. With Babelfish 2.2.0 the limit increased to 100 columns. But with Babelfish versions 2.4.0 and higher, the number of columns that you can replicate increases again. You can run the following code sample against your SQL Server database to determine which tables are too long.

USE myDB;
GO
DECLARE @Babelfish_version_string_limit INT = 8000; -- Use 380 for Babelfish versions before 2.2.0
WITH bfendpoint
AS (
SELECT 
	[TABLE_SCHEMA]
      ,[TABLE_NAME]
	  , COUNT( [COLUMN_NAME] ) AS NumberColumns
	  , ( SUM( LEN( [COLUMN_NAME] ) + 3)  
		+ SUM( LEN( FORMAT(ORDINAL_POSITION, 'N0') ) + 3 )  
	    + LEN( TABLE_SCHEMA ) + 3
		+ 12 -- INSERT INTO string
		+ 12)  AS InsertIntoCommandLength -- values string
      , CASE WHEN ( SUM( LEN( [COLUMN_NAME] ) + 3)  
		+ SUM( LEN( FORMAT(ORDINAL_POSITION, 'N0') ) + 3 )  
	    + LEN( TABLE_SCHEMA ) + 3
		+ 12 -- INSERT INTO string
		+ 12)  -- values string
			>= @Babelfish_version_string_limit
			THEN 1
			ELSE 0
		END AS IsTooLong
FROM [INFORMATION_SCHEMA].[COLUMNS]
GROUP BY [TABLE_SCHEMA], [TABLE_NAME]
)
SELECT * 
FROM bfendpoint
WHERE IsTooLong = 1
ORDER BY TABLE_SCHEMA, InsertIntoCommandLength DESC, TABLE_NAME
;