sqldevops demos

Pre-req

• install Docker

• create a docker hub account and send an email to erickang at microsoft dot com to get the access permission for WideWorldImporters unittest docker image

  • otherwise, you can just build your own docker image using docker_cluster/mssql_unittest/docker_build_push.sh

• install Minikube

Ignite Demo source codes

1. Boot-strapping a dev env in Kubernetes cluster. https://github.com/erickangMSFT/sqldevops/tree/master/docker_cluster/kubernetes/dev_env

2. Database version change project https://github.com/erickangMSFT/wwi-db

3. Database Test and Version change Kubernetes Jobs https://github.com/erickangMSFT/sqldevops/tree/master/docker_cluster/kubernetes/dev_env/jobs

4. Database Unittest project https://github.com/erickangMSFT/wwi-test

5. Persistent Storage and Production DB Kubernetes specs (localhostversion) https://github.com/erickangMSFT/sqldevops/tree/master/docker_cluster/kubernetes/prod_db

6. Scheduled Backup Jobs https://github.com/erickangMSFT/sqldevops/tree/master/docker_cluster/kubernetes/prod_db/mssql/jobs/business-continuity

7. Azure Container Service Provisioning https://github.com/erickangMSFT/sqldevops/tree/master/docker_cluster/kubernetes_azure

Docker Demo

Bootstrap dev and test enviroment as a developer

As a developer, you can build the entire dev and test environment for database, application and test in a few seconds.

1. simply run the following commands:

cd ./docker_cluster/docker-compose
docker-compose up -d
docker ps

That's it. check docker-compose file [./docker_cluster/docker-compose/docker-compose.yml](./docker_cluster/docker-compose/docker-compose.yml)

sqltools cotainer has pre-installed sqlcmd, bcp and mssql-scripter.

docker exec -ti sqltools bash

2. Open web browser and check following:

   • demo webapp http://localhost

     • check people information page for the result of data sanitization with dynamic data masking http://localhost/Directory

   • demo wideworldimporters unittest http://localhost:8000

     • click each link on the index page

ACS Kubernetes cluster provisioning demo

Enable parallel testing with kunbernetes cluster: note that parallel testing implementation is a demo purpose only. URL to Azure Container Registry for this demo:

0. install kubectl

1. create Azure container service. Or you can use minikube to play locally. miniKube

• open the source folder
• it can be fully automated but you will need to provide values for the settings first
• or just run the script line-by-line for demo

code ./docker_cluster/kubernetes_unittest_cluster/acs_provisioning.sh
vim ./docker_cluster/kubernetes_unittest_cluster/acs_provisioning.sh

I assume you already have visual studio code installed. use vim otherwise. To install azure-cli, go to https://github.com/Azure/azure-cli

2. open kubernetes dashboard

kubectl proxy

Open dashboard [http://127.0.0.1/ui] and check the settings

3. run all tests in single pod

cd ./test_controller

# modify node_runner_url='http://localhost:8000' 
# to extenal IP assigned by Kubernetes for slacker_runner service
./run_tests.sh

Total execution time would be ~10 seconds

4. increase the number of pods to 2 and run the same test again use kubernetes dashboard / deployment / slacker pods property or run the following command:

# spining up / down

kubectl scale deployment slacker-runner --replicas=2 -n sqldevops
watch kubectl get pods -n sqldevops-dev

kubectl scale deployment slacker-runner --replicas=1 -n sqldevops
watch kubectl get pods -n sqldevops-dev

Data sanitization details

• source folder: data_sanitization
• automation script: ./data_sanitization/create_unittest_db.ps1
• key take aways
  • the solution is defined in powershell script and automated. automation sample: ./docker_cluster/mssql_unittest/docker_build_push.sh
    • intentionally used powershell for linux for demo. it can be defined on bash or python.
  • show the use of sqlcmd and bcp
  • show the use of mssql-scripter
  • data sanitization flow
    • restore pre-prod using prod backup or clone
    • enable dynamic data masking and create a low-previleged database user w.o UNMASK permission
    • mssql-scripter to dump schema
    • regex to modify the database name and reduce the database size from 4GB total to a few MB.
    • bcp out reference data only with the low-previleged user to convert dynamic data masking to static data masking
    • create database using mssql-scripter dump script and bcp in reference data only.

Docker image build process details

• source folder: docker_cluster

  • Unittest database creation Dockerfile Shell script

  • demo webapp: Dockerfile

  • slacker-node-runner (web based test runner): Dockerfile

  • slacker (framework only): Dockerfile

• Docker hub repositories

  • https://hub.docker.com/r/ericskang/unittestdb/tags/ (private)

  • https://hub.docker.com/r/ericskang/webapp/tags/ (public)

  • https://hub.docker.com/r/ericskang/slacker-node-runner/tags/ (public)

  • https://hub.docker.com/r/ericskang/mssql-slacker/tags/ (public)

Other demos

• Crete UnitTest Database: sanitize data with Dynamic Data Masking and including schema and reference table data only

  • mssql-scipter to create a database with schema only.
  • bcp out and bcp in only reference table data
  • demo

• UnitTest for WideWorldImporters

  • RSpecs for WideWorldImporters WebSite SPs.
  • Slacker framework with tiny_tds driver support.
  • demo folder

• Parallel Testing with Slacker framework, containers, ACR and ACS with Kubernetes

  • docker container for unit test datbase (mssql)
  • docker container for application (wwiapp)
  • docker container for slacker test runner (slacker)
  • ACR for sqldevops
  • Kubernetes cluster
    • mssql service and deployment with StatefullSet - ClusterIP and PersistentVolume mounting to Azure File disk
    • wwiapp service and deployment with Stateless Loadbalancer - External IP
    • slackerrunner service and deployment with Stateless Loadbalancer - ClusterIP
    • slackercontrol service and deployment with Stateless - External IP
  • Spin up and spin down for slacker_runner pods to loadbalance.
  • demo folder
  • test runner
  • [test controller] (./test_controller)

• Docker containerization

  • demo folder

• Demo web application - WidWorldImporters (node.js)

  • demo folder

• CI and CD automation for all above scenarios

Advanced

StatefulSet demo

https://github.com/erickangMSFT/sqldevops/tree/master/docker_cluster/kubernetes/statefulset/testenv