234 ) AWS MIGRATION PROJECT STEPS (Amazon RDS / Amazon Aurora)

 234  ) Migrating On-Premises SQL Server Databases to AWS (Amazon RDS / Amazon Aurora)

Here is the complete, detailed migration guide formatted with clear explanations, specific AWS tools, and their exact purposes:

Phase 1: Governance Discovery & Secret Setup

Why we are doing this step: In order to protect sensitive database details, ensure compliance, and keep track of our credentials safely, we need to set up enterprise security and data governance. For data governance, we use AWS Glue, and for storing secret connection strings safely, we use AWS Secrets Manager.

Tools used :

 data governance, we use AWS Glue, and 

for storing secret connection strings safely, we use AWS Secrets Manager

Steps:

1. Discover and Catalog Data with AWS Glue:
   
   
   • Open the AWS Management Console -> Search for and open AWS Glue.
     
     
   • Navigate to Crawlers -> Click Add crawler.
     
     
   • Configure the crawler to connect to your on-premises SQL Server database, scan your tables, and populate the AWS Glue Data Catalog with metadata and schema definitions.
     
     
2. Secure Credentials in AWS Secrets Manager:
   
   
   • Search for Secrets Manager in the AWS Console -> Click Store a new secret.
     
     
   • Select Credentials for database and input your source SQL Server and target AWS database connection details. This ensures no hardcoded passwords are exposed in plain text.
     
     

Phase 2: Staging Raw Data Landing Zone

Why we are doing this step: In order to safely export massive volumes of raw records (such as CSV/Parquet files of policies, customer lists, and claims) from on-premises systems without slowing down live operations, we need a secure cloud storage staging area. For this temporary raw storage, we use Amazon Simple Storage Service (Amazon S3).

Steps:

1. Provision an S3 Bucket:
   
   
   • Open the AWS Console -> Search for S3 -> Click Create bucket (e.g., st-insurance-raw-data).
     
     
   • Configure standard security settings (enable bucket versioning and block public access).
     
     
2. Create Staging Folders and Upload Data:
   
   
   • Inside your S3 bucket, create logical folders: raw-landing-zone/ and processed-data/.
     
     
   • Export tables (customer, policy, claims, agents) from SQL Server as data files and upload them into the raw-landing-zone/ prefix.
     
     

Phase 3: Heavy Data Cleansing and Transformation

Why we are doing this step: In order to clean, filter, and process large amounts of complex dataset records before moving them into the final database, we need a powerful serverless analytics engine. For heavy data transformations, we use AWS Glue (Spark-based ETL) or Amazon EMR.

Steps:

1. Create an AWS Glue ETL Job:
   
   
   • Open AWS Glue -> Navigate to ETL jobs -> Create a new script.
     
     
   • Use a Spark or Python script to read the raw files from your S3 bucket, clean missing values, normalize claim statuses, and save the refined datasets back to the processed-data/ folder in S3.
     
     

Phase 4: Target Database Setup

Why we are doing this step: In order to host our core application data in a fully managed, high-performance cloud database with automated backups and auto-scaling, we need a relational cloud database service. For this primary destination, we use Amazon RDS for SQL Server or Amazon Aurora.

Steps:

1. Provision Amazon RDS / Aurora:
   
   
   • Open the AWS Console -> Search for RDS -> Click Create database.
     
     
   • Select Microsoft SQL Server (for Amazon RDS) or your preferred engine, configure instance specifications, and securely link the master password via AWS Secrets Manager.
     
     
2. Create Target Schema:
   
   
   • Connect to your new Amazon RDS database endpoint using SQL Server Management Studio (SSMS) or a database client to run your core table creation scripts.
     
     

Phase 5: Orchestration and Data Loading

Why we are doing this step: In order to automate the workflow of moving cleaned data from cloud storage into our final database tables on a scheduled or trigger-based pipeline, we need a cloud ETL orchestration service. For this data pipeline orchestration, we use AWS Glue Workflows or Amazon Managed Workflows for Apache Airflow (MWAA).

Steps:

1. Build Data Pipelines:
   
   
   • Open AWS Glue Workflows -> Define a workflow that chains your crawler, S3 transformation jobs, and bulk loading tasks together.
     
     
   • Schedule the workflow to run automatically or trigger it based on file arrivals in Amazon S3.
     
     

Phase 6: Migration, Synchronization, and Go-Live

Why we are doing this step: In order to handle live database migrations with minimal downtime, perform schema conversions, and switch production applications from the old on-premises environment to AWS with zero data loss, we use AWS Database Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT).

Steps:

1. Convert Schema with AWS SCT:
   
   
   • Download and run the AWS Schema Conversion Tool locally to analyze your SQL Server schema, convert T-SQL stored procedures/views to match the target engine (if migrating engines), and apply them directly to your Amazon RDS instance.
     
     
2. Execute Migration with AWS DMS:
   
   
   • Open the AWS DMS Console -> Create a Replication Instance.
     
     
   • Define your Source Endpoint (on-premises SQL Server) and Target Endpoint (Amazon RDS / Aurora).
     
     
   • Create a Database Migration Task set to Migrate existing data and replicate ongoing changes to handle initial full-load and real-time CDC (Change Data Capture) synchronization.
     
     
3. Cutover:
   
   
   • Stop application writes to the on-premises database, wait for DMS to sync final delta changes, update your application connection strings to point to the new AWS RDS endpoint, and complete the go-live.