Database development and test for AWS RDS and EC2

Dev, test, staging environments with production data clones, containers, and release migrations for SQL Server, PostgreSQL, MySQL



Prerequisites

1. Production databases running in AWS RDS

2. Windocks installed on an EC2 VM which already has SQL Server, Postgres, MySQL. You may use SQL Server Developer Edition on the EC2 instance. See this link for Windocks install details

Steps

1. Set up an S3 bucket that will hold the backups / dumps of your databases from RDS. See this link for IAM roles and EC2 group options to provide access to S3 buckets

2. Install the AWS CLI on the machine running Windocks. See this link for the AWS CLI. Add the path to the install into your PATH. Confirm you can see the backups in S3 with the command
aws s3 ls s3://youraccountname.s3.amazonaws.com

3. From RDS, create a native backup. For SQL Server the instructions are here. For MySQL use the command
mysqldump -h rdsHostnameInRds -u user_name -p dbName > dump.sql
. For PostgreSQL, use the command
pg_dump -h rdsHostname -U username -f dump.sql dbName

4. If your backup is in an S3 bucket, copy it to the Windocks machine or a volume accessible to the Windocks machine using the command
aws s3 cp s3://youraccountname.s3.amazonaws.com/test.bak c:\windocks\dbbackups

5. For release upgrade and rollback scripts development and testing, see this detailed tutorial or see the sample in windocks\samples\DbScriptsSourceControlGitForSqlServerImages

6. Build the Windocks image - See the sample in windocks\samples\\cloneFromFullBackup. Build an image with the dockerfile below using the command
docker build -t yourimagename path\to\directory\containing\dockerfile
or by using the web application and selecting Build. An administrator builds an image once using full backups (SQL Server) or dumps (PostgreSQL, MySQL). The image is configured to run database scripts from the git repo.

The dockerfile used to build the image is available in windocks\samples\DbScriptsSourceControlGitForSqlServerImages and is also below. Samples for MySql and PostgreSql are in the directories windocks\samples\mysql and windocks\samples\postgres. If you want to run a masking script on the data to protect sensitive data, then put the script in the directory containing the dockerfile before building the dockerfile.

dockerfile
FROM mssql-2017
# FROM postgres-10.13
# FROM mysql-5.7

ENV FULL_BACKUPS_ONLY=1

SETUPCLONING FULL customers E:\windocks\dbbackups\customerdatafull.bak
#SETUPCLONING FULL customers E:\windocks\dbbackups\postgresdump1
#SETUPCLONING FULL customers E:\windocks\dbbackups\mysqldump1

COPY maskingScript.sql .

# This script is run at image build time
RUN maskingScript.sql

COPY scriptToRunAtContainerCreateTime.sql

ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1

# This script is run at container create time
RUN scriptToRunAtContainerCreateTime.sql

7. You may create dev/test/staging environments with cloned production data with Windocks. These environments (containers) are available on ports on the EC2 instance. You may specify the ports as well.

Users can use the Windocks web application to create the dev/test/staging environments with cloned production data from the image built above. More details on the web app are here

Alternatively, they can also use the REST API with curl to create and start the container with clone as documented here

8. Logs

View logs of the scripts in the web application by clicking Log next to your container / clone.

Alternatively, they can also use the REST API with curl to create and start the container with clone as documented here