TECHNICAL DOCS

Dev, test, staging environments with database clones, C# Entity Framework EF core migrations, containers and Git

The EF core migration workflow

Build the database image - An administrator builds the Windocks image specifying the database backups needed to deliver database clones, the path to the git repo containing the EF core migrations. This image is used to deliver containers and database clones with specified migrations applied. You may also add data masking scripts that automatically mask sensitive production data.

Develop code using production data clones in containers - Developers write code and auto generate EF migrations using production database clones in containers. They get these environments from the Windocks image on demand using a web app, a command line or a REST API. Using production data clones and containers makes collaboration easy since developers can easily reproduce each other's environments in seconds. Using production data improves release quality since the entire release cycle uses production data so the final upgrade works smoothly

Git for source control - Developers push code and auto generated EF core migrations to a Git repo feature branch. Use one branch for each feature and/or each developer. Also use one or two Git branches for each release (such as release301ReadyForTest, release301Final).

Unit test - Developers get a database clone and container from Windocks with the EF migrations automatically pulled from the repo into the container and automatically run on the production database clone. The developer can specify the FROM and TO EF migration names while getting the clone and container. Different developers can get production data clones with different EF migrations applied. This provides flexibility to developers to work on different features using production database clones from the same image.

Merge code including EF migrations to release branch - When the developer is ready to release, they merge their branch into the Git release branch. All future production clones and containers from the Windocks image automatically have the EF migrations automatically applied.

Test and stage with production clones, containers and EF migrations automatically applied Devops pipelines or test teams get the production database clones on demand with the EF migrations applied automatically. The on demand production clones with EF migrations applied are used as test and staging environments. Because the testing and staging is done with production data, the final production deployment is smooth and less error-prone.

Do both release upgrade and rollback testing with containers As a best practice, the EF migrations should be both for release upgrade and release rollback. Containers make it easy for many developers, testers, and pipelines to work with various combinations of EF migrations applied (for features, release upgrade, and release rollback) because containers are created and torn down in seconds. Upgrade testing is done on one container with upgrade migrations applied and rollback testing is done on another container with upgrade and rollback migrations applied. Multiple developers, testers, pipeline runs can each develop, test and stage with various combinations of migrations using containers

 

 

Detailed Steps

There is a working sample in the Windocks installation at windocks\samples\EFMigration with a pre-existing public repo at https://github.com/windocks-github/efmigration
 

 

 

 

Step 1. Setup the git repo

Instructions to set up a git repo are here

The sample shows an example of a public repo. If the repo is private, use git over SSH and follow these steps (Email support@windocks.com if you need help)

  • cd C:\Windows\System32\config\systemprofile
  • Copy the private key privatekey.pem to C:\Windows\System32\config\systemprofile
  • Create a file named config (no extensions) and enter the following with your ip address and user name for the git repo machine:
Host securedvmhostname
  Hostname 142.4.299.87
  IdentityFile C:\Windows\System32\config\systemprofile\privatekey.pem
  User yourSshUserName

 

Create a git branch for each feature and release. Also create a branch for the current release. When you are finished with a feature branch, merge the feature branch to the release Git branch so all the code and migrations from the feature branch are available in the release branch.

 

 

Step 2. Setup the Windocks machine

Install Windocks on a Windows server as described here

Install a git client on the Windocks machine from here

Install .Net Core SDK from here

Install sqlcmd utility from here

 

 

Step 3. Create the dockerfile for the image

The dockerfile used to build the image is available in windocks\samples\efmigration and is also below. You may add data masking scripts to mask sensitive production data (see the sample windocks\samples\cloneFromFullBackup for this)

dockerfile

FROM mssql-2017

SETUPCLONING FULL customers C:\windocks\dbbackups\customerdatafull.bak

ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1

# When container is created, the RUN command below clones the git repo to a directory in the container named homedirforrepo
RUN "C:\Program Files\Git\cmd\git.exe" clone https://github.com/windocks-github/efmigration.git homedirforrepo

# This is the format for SSH secure git repo (commented)
# RUN "C:\Program Files\Git\cmd\git.exe" clone securedvmhostname:/path/to/git/repo.git homedirforrepo

# STARTENV_GITBRANCH_ENDENV is a variable name for the git branch you want - the variable value is specified at container create time
# The convention of STARTENV_something_ENDENV is used for these variables (Functionality available in enterprise editions, for other editions, you may just use a hardcoded git branch name)
# When container is created, changes the container's git repo code to the branch specified at container create time
RUN git -C homedirforrepo checkout STARTENV_GIT_BRANCH_ENDENV

# These two RUN commands add the EF packages to the C# project in the container
RUN dotnet add homedirforrepo\EntityFrameworkMigrationScript.csproj package Microsoft.EntityFrameworkCore.SqlServer
RUN dotnet add homedirforrepo\EntityFrameworkMigrationScript.csproj package Microsoft.EntityFrameworkCore.Design

# This RUN command builds the C# project
RUN dotnet publish homedirforrepo\EntityFrameworkMigrationScript.csproj -c Release -o out

# The RUN commands below run on container start
# This RUN uses EF migration to generate the migration SQL scipt
# For the sample when you create the container use MyFirstMigration for the FROM_MIGRATION and NewColMig for the TO_MIGRATION
RUN RUN_ON_CONTAINER_START dotnet ef migrations script STARTENV_FROM_MIGRATION_NAME_ENDENV STARTENV_TO_MIGRATION_NAME_ENDENV --project homedirforrepo\EntityFrameworkMigrationScript.csproj --output migrate.sql

# This RUN command adds the USE database name to the migration sql generated above
RUN RUN_ON_CONTAINER_START powershell.exe -command " @(\"USE customers;\") + (Get-Content migrate.sql) | Set-Content migratefinal.sql"

# This RUN command applies the migration script to the container database
# Windocks will automatically replace $ContainerPort with the correct container's port
RUN RUN_ON_CONTAINER_START sqlcmd -S .\INSTANCE$ContainerPort -i migratefinal.sql

 

 

Step 4. Build the image

In a command line on the Windocks server:
docker build -t yourimagename path\to\directory\containing\dockerfile
This builds the image from which clones and containers are delivered. Alternatively, use the web application described here and click on Build in the top menu.

For SQL Server, once the image is built, in the web application, select Refresh and put in the schedule for refreshing the image with transaction log backups (SQL Server) and the directory in which the log backups are available periodically. Now Windocks automatically refreshes the image to keep it current with production. Upon a release, you may also manually refresh the image with production log backups with
docker exec imagename path\to\log\backups
or in the UI under Refresh

 

 

Step 5. Create the database environment

When the developer or the devops pipeline needs a refreshed environment, they can use the web application specifying the variable values for FROM and TO migration names in the text boxes (Put the Windocks server IP in the text box, click on Images in the top menu) or use the command line below (Command line only works on the Windocks machine) or use the REST API (works from any machine):

Command line is as below:

docker create -e STARTENV_GIT_BRANCH_ENDENV=master -e STARTENV_FROM_MIGRATION_NAME_ENDENV=MyFirstMigration -e STARTENV_TO_MIGRATION_NAME_ENDENV=NewColMig --name=yourcontainername yourimagename
Then,
docker start yourcontainerName
Or
docker start firstThreeLettersOfContainerIdReturnedByCreate

Web app is the easiest way to create these containers. In enterprise editions, you may specify the variable values for git branch and migrations in the UI.

REST API with curl is the best option for devops pipelines and it works as below. (Download curl from here:

First, get your access token - create a file named getauthtoken.json and put the following inside it:
{ "Data": "action=bizexec&bizexectype=authlogin&bizparameters=administrator~youradministratorpassword" }
Then run the curl command below:
curl -H "Content-Type: application/json" -d@getauthtoken.json -X POST http://localhost:3000/windocks

This will return your access token to be used for the REST API call below. The token is the string after the last ~ (don't copy the ~)

Create a file named create.json and put the following inside it (Note the \\ to escape the directory \ in manifest path):

{ "AttachStdout": false, "AttachStderr": false, "ExposedPorts": {"10032/tcp":{}}, "Env": ["STARTENV_FROM_MIGRATION_NAME_ENDENV=MyFirstMigration", "STARTENV_TO_MIGRATION_NAME_ENDENV=NewColMig", "STARTENV_GIT_BRANCH_ENDENV=master","SA_PASSWORD=secret123@"],"Image": "yourimagename", "HostConfig": {"NetworkMode": "bridge", "RestartPolicy": {"Name": "no"}}}

Then run the curl command below using a container name you want and the access token from above:
curl -H "Content-Type: application/json" -d@create.json -X POST "http://localhost:3000/containers/create?name=yourcontainername&token=youraccesstoken"

This creates a container from the image named yourimagename (must already exist) at the port 10032 with admin password of secret123@. If you want an auto generated port, set ExposedPorts to {}. The scripts in the specified git repo branch in the manifest will be applied


Parse the return string from the curl command to get the container Id (or use yourcontainername to avoid parsing) and then start the container with the following curl command:
curl -H "Content-Type: application/json" -X POST http://localhost:3000/containers/yourcontainername/start?token=youraccesstoken

 

 

Step 6. Logs

View logs of the scripts in the web application by clicking log next to your container / clone in the web application. You may also get image build time logs in Images

You may also use the REST API to get the logs as below:

Create a file named getcontainerlog.json and put the following inside it:

{ "Data": "{\"action\": \"containerlog\", \"containerlog\": {\"containerid\": \"yourFullContainerId\"}} " }

Then run the curl command below and the access token from above:
curl -H "Content-Type: application/json" -d@getcontainerlog.json -X POST http://localhost:3000/windockswrapper?token=youraccesstoken

For image logs, create a file named getimagelog.json and put the following inside it:

{ "Data": "{\"action\": \"imagelog\", \"imagelog\": {\"imagename\": \"yourImageName\"}} " }

Then run the curl command below and the access token from above:
curl -H "Content-Type: application/json" -d@getimagelog.json -X POST http://localhost:3000/windockswrapper?token=youraccesstoken

Extend CI/CD to your data layer

GET A DEMO TRY FREE