Set up a CI/CD pipeline for database migration by using Terraform

Created by Dr. Rahul Sharad Gaikwad (AWS), Aarti Rajput (AWS), Ashish Bhatt (AWS), Aniket Dekate (AWS), Naveen Suthar (AWS), Nadeem Rahaman (AWS), Ruchika Modi (AWS), and Tamilselvan P (AWS)

Summary

This pattern is about establishing a continuous integration and continuous deployment (CI/CD) pipeline for managing database migrations in a reliable and automated manner. It covers the process of provisioning the necessary infrastructure, migrating data, and customizing schema changes by using Terraform, which is an infrastructure as code (IaC) tool.

Specifically, the pattern sets up a CI/CD pipeline to migrate an on-premises Microsoft SQL Server database to Amazon Relational Database Service (Amazon RDS) on AWS. You can also use this pattern to migrate a SQL Server database that's on a virtual machine (VM) or in another cloud environment to Amazon RDS.

This pattern addresses the following challenges associated with database management and deployment:

• Manual database deployments are time-consuming, error-prone, and lack consistency across environments.

• Coordinating infrastructure provisioning, data migrations, and schema changes can be complex and difficult to manage.

• Ensuring data integrity and minimizing downtime during database updates is crucial for production systems.

This pattern provides the following benefits:

• Streamlines the process of updating and deploying database changes by implementing a CI/CD pipeline for database migrations. This reduces the risk of errors, ensures consistency across environments, and minimizes downtime.

• Helps improve reliability, efficiency, and collaboration. Enables faster time to market and reduced downtime during database updates.

• Helps you adopt modern DevOps practices for database management, which leads to increased agility, reliability, and efficiency in your software delivery processes.

Prerequisites and limitations

Prerequisites

• An active AWS account

• Terraform 0.12 or later installed on your local machine (for instructions, see the Terraform documentation)

• Terraform AWS Provider version 3.0.0 or later from HashiCorp (see the GitHub repository for this provider)

• Least privilege AWS Identity and Access Management (IAM) policy (see the blog post Techniques for writing least privilege IAM policies)

Architecture

This pattern implements the following architecture, which provides the complete infrastructure for the database migration process.

In this architecture:

• The source database is a SQL Server database that is on premises, on a virtual machine (VM), or hosted by another cloud provider. The diagram assumes that the source database is in an on-premises data center.

• The on-premises data center and AWS are connected through a VPN or AWS Direct Connect connection. This provides secure communications between the source database and the AWS infrastructure.

• The target database is an Amazon RDS database that is hosted inside the virtual private cloud (VPC) on AWS with the help of a database provisioning pipeline.

• AWS Database Migration Service (AWS DMS) replicates your on-premises database to AWS. It is used to configure the replication of the source database to the target database.

The following diagram shows the infrastructure set up with different levels of the database migration process, which involves provisioning, AWS DMS setup, and validation.

In this process:

• The validation pipeline validates all checks. The integrated pipeline moves to the next step when all necessary validations are complete.

• The DB provisioning pipeline consists of various AWS CodeBuild stages that perform Terraform actions on the provided Terraform code for the database. When these steps are complete, it deploys resources in the target AWS account.

• The AWS DMS pipeline consists of various CodeBuild stages that perform tests and then provision the AWS DMS infrastructure for performing the migration by using IaC.

Tools

AWS services and tools

• AWS CodeBuild is a fully managed continuous integration service that compiles source code, runs tests, and produces ready-to-deploy software packages.

• AWS CodePipeline is a fully managed continuous delivery service that helps you automate your release pipelines for fast and reliable application and infrastructure updates.

• Amazon Relational Database Service (Amazon RDS) helps you set up, operate, and scale a relational database in the AWS Cloud.

• Amazon Simple Storage Service (Amazon S3) is an object storage service that offers scalability, data availability, security, and performance.

• AWS Database Migration Service (AWS DMS) helps you migrate data stores into the AWS Cloud or between combinations of cloud and on-premises setups.

Other services

• Terraform is an IaC tool from HashiCorp that helps you create and manage cloud and on-premises resources.

Code repository

The code for this pattern is available in the GitHub Database Migration DevOps Framework using Terraform samples repository.

Best practices

• Implement automated tests for your database migration to verify the correctness of schema changes and data integrity. This includes unit tests, integration tests, and end-to-end tests.

• Implement a robust backup and restore strategy for your databases, especially before migration. This ensures data integrity and provides a fallback option in case of failures.

• Implement a robust rollback strategy to revert database changes in case of failures or issues during migration. This could involve rolling back to a previous database state or reverting individual migration scripts.

• Set up monitoring and logging mechanisms to track the progress and status of database migrations. This helps you identify and resolve issues quickly.

Epics

Set up your local workstation

Task	Description	Skills required
Set up and configure Git on your local workstation.	Install and configure Git on your local workstation by following the instructions in the Git documentation.	DevOps engineer
Create a project folder and add the files from the GitHub repository.	Open the GitHub repository for this pattern. Choose Code to see cloning options, and copy the URL provided in the HTTPS tab. Create a folder for your project on your workstation. Open a terminal and navigate to this folder. Clone the GitHub repository: git clone <github-repository-url> where <github-repository-url> is the URL you copied in step 2. When cloning is complete, go to the cloned repository in your project folder: cd <folder-name>/aws-terraform-db-migration-framework-samples Open this project in an integrated development environment (IDE) of your choice.	DevOps engineer

Provision the target architecture

Task	Description	Skills required
Update required parameters.	The ssm-parameters.sh file stores all required AWS Systems Manager parameters. You can configure these parameters with the custom values for your project. In the setup/db-ssm-params folder on your local workstation, open the ssm-parameters.sh file and set these parameters before you run the CI/CD pipeline.	DevOps engineer
Initialize the Terraform configuration.	In the db-cicd-integration folder, enter the following command to initialize your working directory that contains the Terraform configuration files: terraform init	DevOps engineer
Preview the Terraform plan.	To create a Terraform plan, enter the following command: terraform plan -var-file="terraform.sample" Terraform evaluates the configuration files to determine the target state for the declared resources. It then compares the target state against the current state and creates a plan.	DevOps engineer
Verify the plan.	Review the plan and confirm that it configures the required architecture in your target AWS account.	DevOps engineer
Deploy the solution.	Enter the following command to apply the plan: terraform apply -var-file="terraform.sample" Enter yes to confirm. Terraform creates, updates, or destroys infrastructure to achieve the target state declared in the configuration files. For more information about the sequence, see the Architecture section of this pattern.	DevOps engineer

Verify the deployment

Task	Description	Skills required
Validate the deployment.	Verify the status of the db-cicd-integration pipeline to confirm that the database migration is complete. 1. Sign in to the AWS Management Console, and then open the AWS CodePipeline console. 2. In the navigation pane, choose Pipelines. 3. Choose the db-cicd-integration pipeline. 4. Validate that the pipeline execution has completed successfully.	DevOps engineer

Clean up infrastructure after use

Task	Description	Skills required
Clean up the infrastructure.	After your project is complete, clean up the infrastructure you created by using the command: terraform destroy --var-file=terraform.sample Enter yes to confirm.	DevOps engineer

Related resources

AWS documentation

• Getting started with a Terraform product

Terraform documentation

• Terraform installation

• Terraform backend configuration

• Terraform AWS Provider documentation