Can SCT be used to auto generate a data migration when both source and destination schemas exist yet they are different engine types?

Question

I have a SQL Server 2016 database with several tables and 30+ million rows . Currently the Microsoft instance is on premises but it could be copied onto an EC2 instance. We are still investigating the feasibility of this. The target database is an Aurora PostgreSQL database. We already have a working target schema for PostgreSQL.

What i wish to understand is whether SCT can automate the process of comparing the two (similar but not identical schemas) and generate a migration process.

Even if there is a fair bit of hand holding and decision making involved - I will consider it a win.

Let me know if I need to provide more details to answer the question.

Regards

Accepted Answer

SCT as the name implies is schema conversion tool, What you trying to do is schema diff, SCT does not do schema comparison.  What you can do is,  use SCT to covert your Sql Server into a new schema in Aurora and then use Schema diff option of pgadmin
https://www.pgadmin.org/docs/pgadmin4/development/schema_diff.html

Answer

SCT was initially built to create the new "target schema" - via an easy push-button UI.

Even so, .. .. it can ALSO help build the TRANSFORMATION-Rules of Source-database to an ALREADY-existing TARGET-database schema.

To me (at least) SCT was smart enough - enabling me to define a VERY SIMPLE transformation-rule - from Oracle to Postgres (to copy over 3 tables even tho the DDL data-types were different).

Even so .. Yes! You can use Schema Conversion Tool to also do MANUAL ad-hoc data transfers!  If you'd like to automate this transfer (say, midnight daily), you will need to "deploy" (from SCT of course) to AWS DMS service.

NOTE: COST!! AWS DMS is a "traditional EC2-style" service (unlike the serverless Glue ElasticViews), as in .. it MUST run 24x7x365 and there is NO automated way to "shut it down" (via EC2-console or EC2-cli) when it's not in use (this is, as of Dec 2021).

WARNING: It does NOT work with AWS SSO, or any other  M O D E R N  security-model that requires SESSION-KEY in addition to ACCESS-KEY and SECRET-KEY.   So, it was a failure at our Enterprise environment, where traditional IAM-Users are banned.  Clearly, everyone at the SCT-team didn't feel it important to support enterprises in their ROBUST-security efforts.  It's really sad that no one at SCT team even thought of leveraging the ~/.aws/credentials [default] credentials either.

Can SCT be used to auto generate a data migration when both source and destination schemas exist yet they are different engine types?

相关内容