Can SCT be used to auto generate a data migration when both source and destination schemas exist yet they are different engine types?
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
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
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.
Relevant questions
Can SCT be used to auto generate a data migration when both source and destination schemas exist yet they are different engine types?
Accepted Answerasked 5 months agoHow to migrate DB on premise Oracle to Microsoft SQL Server DB in AWS. We see SCT will not allow MS SQL Server as Target when source is Oracle. Looking for tools like SCT to move Schema from Oracle.
asked 13 days agoCan AWS Glue read data from different SQL Server table, generate csv files and zipping it to S3?
Accepted Answerasked 3 months agomigration tool for DB2 zOS to AWS RDS
asked 2 months agoDMS vs CloudEndure for DB Migration
Accepted Answerasked 2 years agoError loading metadata using AWS SCT against an RDS SQL Server Instance
asked 5 months agoAWS DMS Issues | Duplicate Tables and SCT Conversion Issue
Accepted Answerasked 2 years agoTEXT columns from Source SQL Server database are not being replicated to target
asked a day agoIs there a service to move a database from MSSQL to MySQL (both hosted on AWS)
asked 3 months agoError while connecting to SQL Server Express Edition from SCT
asked 10 months ago
Ok, I've reread your answer. If I understand you correctly, the outcome would be a list of differences for the two databases but only between the two postgreSQL schema's. Is there a process for taking this list of schema differences and then taking advantage of the data migration service to bulk migrate?