Prerequisites to using Babelfish as a target Security requirements when using Babelfish as a target for AWS Database Migration Service Permissions needed to use Babelfish as a target Limitations on using Babelfish as a target for AWS Database Migration Service Target data types for Babelfish

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

Important

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 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%"
		}
	}]
 }
```
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: For SQL Server 2008 and later, use DATETIME2. For earlier versions, if the scale is 3 or less use DATETIME. In all other cases, use VARCHAR (37). 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.

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Using Redis as a target

VPC endpoints for data migration