CI/CD for RDS SQL Server

Question

Hi AWS, I have to build the CI/CD pipeline for the database schema running on Amazon Hosted RDS SQL Server 2022. I have a couple of questions for the same:
1. The cluster is not publicly accessible, so how I am going to connect with it using pipeline using GitHub Hosted self-hosted runner for deployment.
2. How the database code on source control tool (GitHub) will be synchronized with SQL Server on EC2 machine?
3. State-based deployment or Migrations deployment, which deployment strategy should I prefer and how to do it in CI/CD context.
4. The database code is stateful so will it affect the existing databases on the target server.
5. Moving forward if the server is showing out of storage issues how to sort that out?

Please assist.

Answer

Hello,

1) Along with Github you will be using a CI/CD tool such Jenkins, AWS Codepipeline. Organizations configure the CI/CD pipelines to connect to resources/servers in private subnets.

2) In the CI/CD configuration, define the steps necessary to deploy your database schema changes. This might include tasks such as building scripts, executing tests against the database in lower environments, and promoting changes through higher environments (e.g., staging, production). Utilize database migration tools like Flyway, Liquibase to manage schema changes in a version-controlled and automated manner.

3) Its a customer's choice. State-based deployments are difficult to manage overtime with respect to databases.

4) Not clear

5) AWS RDS Storage capacity increase require infrastructure changes. If the database server was deployed via Infrastructure as Code (examples AWS Cloudformation, Terraform), the template would need to modified and redeployed.

CI/CD for RDS SQL Server

Relevant content