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
-
Production databases running in AWS RDS
-
Windocks installed on an EC2 VM which already has SQL Server, PostgreSQL, or MySQL.
You may use SQL Server Developer Edition on the EC2 instance. See this link for Windocks install details
1. Set up an S3 bucket
This S3 bucket 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. Make the backup accessible to Windocks
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. Prepare release migration SQL scripts
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. Create dev/test/staging environments
You can create these non-production environments with cloned production data using 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, you can also use the REST API with curl to create and start the container with clone as documented here