Using Babelfish as a target for AWS Database Migration Service
You can migrate data from a Microsoft SQL Server source database to a Babelfish target using AWS Database Migration Service.
Babelfish for Aurora PostgreSQL extends your Amazon Aurora PostgreSQL-Compatible Edition database with the ability to accept database connections from Microsoft SQL Server clients. Doing this allows applications originally built for SQL Server to work directly with Aurora PostgreSQL with few code changes compared to a traditional migration, and without changing database drivers.
The AWS DMS Babelfish endpoint supports Aurora PostgreSQL with Babelfish compatibility version 13.6 (Babelfish version 1.2.0) or later. Earlier versions of Babelfish on Aurora PostgreSQL version 13.4 and 13.5 require a minor version upgrade before using the Babelfish endpoint.
For information about using Babelfish as a database endpoint, see Babelfish for Aurora PostgreSQL in the Amazon Aurora User Guide for Aurora
Prerequisites to using Babelfish as a target for AWS DMS
Perform the following preparatory steps prior to migrating data from your source to your target Babelfish database.
-
Prepare and create your tables. To ensure the use of correct data types, you must create your tables before migrating data. If the tables aren't pre–created before running AWS DMS, DMS will create the tables with incorrect data types. For example, a timestamp column will be created by DMS as binary(8) instead, and will not provide the expected timestamp/rowversion functionality.
-
Manage tables that have an IDENTITY column.
-
Set minimum Aurora capacity units (ACUs) for your Aurora PostgreSQL Serverless V2 target database.
Procedures for performing those preparatory steps follow.
To prepare and create your tables prior to migration
-
Run your create table DDL statements that include any unique constraints, primary keys, or default constraints.
Do not include foreign key constraints, or any DDL statements for objects like views, stored procedures, functions, or triggers. You can apply them after migrating your source database.
-
Identify any identity columns, computed columns, or columns containing rowversion or timestamp data types for your tables. Then, create the necessary transformation rules to handle known issues when running the migration task. For more information see, Transformation rules and actions.
-
Identify columns with data types that Babelfish doesn't support. Then, change the affected columns in the target table to use supported data types, or create a transformation rule that removes them during the migration task. For more information see, Transformation rules and actions.
The following table lists source data types not supported by Babelfish, and the corresponding recommended target data type to use.
Source data type
Recommended Babelfish data type
IMAGE
VARBINARY(MAX)
DATETIMEOFFSET(7)
DATETIMEOFFSET(6)
Babelfish supports values less than 7.
XML
When using AWS DMS to migrate XML data from a SQL Server source to a Babelfish target, the size of XML data can nearly double. So, we recommend you double the size typically set for the
LobMaxSize
option in the target metadata task settings. For more information, see Target metadata task settings.HEIRARCHYID
NVARCHAR(250)
GEOMETRY
VARCHAR(MAX)
GEOGRAPHY
VARCHAR(MAX)
To set Aurora capacity units (ACUs) level for your Aurora PostgreSQL Serverless V2 source database
You can improve performance of your AWS DMS migration task prior to running it by setting the minimum ACU value.
-
From the Severless v2 capacity settings window, set Minimum ACUs to
2
, or a reasonable level for your Aurora DB cluster.For additional information about setting Aurora capacity units, see Choosing the Aurora Serverless v2 capacity range for an Aurora cluster in the Amazon Aurora User Guide
After running your AWS DMS migration task, you can reset the minimum value of your ACUs to a reasonable level for your Aurora PostgreSQL Serverless V2 source database.
Security requirements when using Babelfish as a target for AWS Database Migration Service
The following describes the security requirements for using AWS DMS with a Babelfish target:
-
The administrator user name (the Admin user) used to create the database.
-
T-SQL login and user with the sufficient SELECT, INSERT, UPDATE, DELETE, and REFERENCES permissions.
User permissions for using Babelfish as a target for AWS DMS
For security purposes, the user account used for the data migration must be a registered user in any Babelfish database that you use as a target.
Your Babelfish target endpoint requires minimum user permissions to run an AWS DMS migration.
To create a login and a low-privileged Transact-SQL (T-SQL) user
-
Create a login and password to use when connecting to the server.
CREATE LOGIN dms_user WITH PASSWORD =
'password'
; GO -
Create the virtual database for your Babelfish cluster.
CREATE DATABASE my_database; GO
-
Create the T-SQL user for your target database.
USE my_database GO CREATE USER dms_user FOR LOGIN dms_user; GO
-
For each table in your Babelfish database, GRANT permissions to the tables.
GRANT SELECT, DELETE, INSERT, REFERENCES, UPDATE ON [dbo].[Categories] TO dms_user;
Limitations on using Babelfish as a target for AWS Database Migration Service
The following limitations apply when using a Babelfish database as a target for AWS DMS:
-
Enter TDS port number 1433 (Default) to use Babelfish as a target.
-
Only Full Load Replication is supported. AWS DMS doesn't support CDC with Babelfish as a target.
-
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 isn't supported.
-
Replication task settings have the following limitations:
{ "FullLoadSettings": { "TargetTablePrepMode": "DO_NOTHING", "CreatePkAfterFullLoad": false, }. "ValidationSettings": { "EnableValidation": 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 later, 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 characterscol
from theActor
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%" } }] }
-
Babelfish doesn't copy IMAGE data type columns.
-
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 limits the number of columns that you can replicate to a Babelfish target endpoint to twenty (20) columns. With Babelfish version 2.2.0 and later, the limit increases to 100 columns. For tables with more than 100 columns, you can use the PostgreSQL endpoint as a workaround.
Target data types for Babelfish
The following table shows the Babelfish target data types that are supported when using AWS DMS and the default mapping from AWS DMS data types.
For additional information about AWS DMS data types, see Data types for AWS Database Migration Service.
AWS DMS data type |
Babelfish data type |
---|---|
BOOLEAN |
TINYINT |
BYTES |
VARBINARY(length) |
DATE |
DATE |
TIME |
TIME |
INT1 |
SMALLINT |
INT2 |
SMALLINT |
INT4 |
INT |
INT8 |
BIGINT |
NUMERIC |
NUMERIC(p,s) |
REAL4 |
REAL |
REAL8 |
FLOAT |
STRING |
If the column is a date or time column, then do the following:
If the column is not a date or time column, use VARCHAR (length). |
UINT1 |
TINYINT |
UINT2 |
SMALLINT |
UINT4 |
INT |
UINT8 |
BIGINT |
WSTRING |
NVARCHAR(length) |
BLOB |
VARBINARY(max) To use this data type with DMS, you must enable the use of BLOBs for a specific task. DMS supports BLOB data types only in tables that include a primary key. |
CLOB |
VARCHAR(max) To use this data type with DMS, you must enable the use of CLOBs for a specific task. |
NCLOB |
NVARCHAR(max) To use this data type with DMS, you must enable the use of NCLOBs for a specific task. During CDC, DMS supports NCLOB data types only in tables that include a primary key. |