Which solution will meet these requirements?
Create Amazon DynamoDB global tables with auto scaling enabled. Use the AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS) to move the data from on premises to DynamoDB. Create an AWS Lambda function to move the spatial data to Amazon S3. Query the data by using Amazon Athena. Use Amazon EventBridge to schedule jobs in DynamoDB for maintenance. Use Amazon API Gateway for foreign table support.
Create an Amazon RDS for Microsoft SQL Server DB instance. Use native replication to move the data from on premises to the DB instance. Use the AWS Schema Conversion Tool (AWS SCT) to modify the SQL Server schema as needed after replication. Move the spatial data to Amazon Redshift. Use stored procedures for system maintenance. Create AWS Glue crawlers to connect to the on-premises Oracle databases for foreign table support.
Launch Amazon EC2 instances to host the Oracle databases. Place the EC2 instances in an Auto Scaling group. Use AWS Application Migration Service to move the data from on premises to the EC2 instances and for real-time bidirectional change data capture (CDC) synchronization. Use Oracle native spatial data support. Create an AWS Lambda function to run maintenance jobs as part of an AWS Step Functions workflow. Create an internet gateway for foreign table support.
Create an Amazon RDS for PostgreSQL DB instance. Use the AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS) to move the data from on premises to the DB instance. Use PostgreSQL native spatial data support. Run cron jobs on the DB instance for maintenance. Use AWS Direct Connect to connect the DB instance to the on-premises environment for foreign table support.
Explanations:
Amazon DynamoDB does not support spatial data natively, and the solution involves several unnecessary steps such as moving data to S3 and querying with Athena, which is not optimal for foreign table access to the on-premises databases. Additionally, DynamoDB is not designed for handling relational database functions like cron jobs.
While Amazon RDS for Microsoft SQL Server can be used, the solution introduces unnecessary complexity with native replication and moving spatial data to Amazon Redshift, which would complicate access and maintenance. Moreover, AWS Glue is not the best fit for direct foreign table support for on-premises Oracle databases.
Running Oracle databases on EC2 instances could work, but it requires managing the infrastructure and scaling. The solution also introduces complexity with an internet gateway for foreign table support and does not leverage managed database services effectively. Additionally, real-time CDC synchronization is not explicitly required by the scenario.
Amazon RDS for PostgreSQL supports spatial data through PostGIS, making it a suitable choice for handling spatial queries. The AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS) are appropriate for migrating data. Cron jobs can be run on the PostgreSQL instance for maintenance. Furthermore, AWS Direct Connect provides a reliable connection to the on-premises environment, facilitating foreign table access.
I infer that the answer is:
Create an Amazon RDS for PostgreSQL DB instance. Use the AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS) to move the data from on premises to the DB instance. Use PostgreSQL native spatial data support. Run cron jobs on the DB instance for maintenance. Use AWS Direct Connect to connect the DB instance to the on-premises environment for foreign table support.