How can I see objects within babelfish_db using ssms?

Question

I have created two database instances of RDS on Amazon Web Services (AWS).

1. SQL Server on RDS
2. Aurora for Postgres (babelfish enabled)

Data Inserted

Now I have manually inserted database objects like tables, views, and stored procedures inside the SQL Server RDS instance. NOTE: Tables in this instance have data inserted manually.

Data Migrated

Now I want to migrate these database objects to an Aurora Postgres instance. Since it is babelfish enabled, I am using the SSMS client for inserting these objects, and then using aws dms, I am transferring this data from the SQL server to Aurora Postgres. Now, when you create Aurora Postgres using Babelfish, an additional database gets created in the list of databases within Postgres called babelfish_db. When I try running the dms migration task, new tables get created in addition to the old ones. This is something I can check via pgAdmin, but when I try checking the same via SSMS, I don't see anything.

Question

What am I missing here? How can I view migrated data via DMS in SSMS?

Answer 1

Most likely you haven't configured the schema rename transformations, e.g. from dbo to mydb_dbo. This causes DMS to think these are new tables, instead of using the existing ones which you correctly pre-created through Babelfish. Possibly a transformation rule to lowercase the table names is also needed.

Quoting from the docs:

When you define a migration task for a Babelfish target, you need to include transformation rules that ensure DMS uses the pre-created T-SQL Babelfish tables in the target database.

First, add a transformation rule to your migration task that makes all table names lowercase. Babelfish stores as lowercase in the PostgreSQL pg_class catalog the names of tables that you create using T-SQL. However, when you have SQL Server tables with mixed-case names, DMS creates the tables using PostgreSQL native data types instead of the T-SQL compatible data types. For that reason, be sure to add a transformation rule that makes all table names lowercase.

Next, if you used the multidatabase migration mode when you defined your cluster, add a transformation rule that renames the original SQL Server schema. Make sure to rename the SQL Server schema name to include the name of the T-SQL database. For example, if the original SQL Server schema name is dbo, and your T-SQL database name is mydb, rename the schema to mydb_dbo using a transformation rule.

There are also some additional configurations or workarounds that could be needed depending on the data types you're using, etc.

Documentation link: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html#CHAP_Target.PostgreSQL.Babelfish