DEV Community: Kevin George

Containers | Building a local TSQL development environment

Kevin George — Mon, 10 Feb 2020 00:00:00 +0000

Docker-Compose, Flyway and a T-SQL runner to help you get up and running with a local development environment.

In my introductory posts on docker and docker-compose. We managed to set up an empty SQL Server container, but in the real world, that is not very useful.

Okay great - we can create SQL Server containers now let us build on that to create a ready-to-go development environment with docker-compose up.

Let us take a moment to talk about the objective. We need a SQL Server, a database and something to deploy code to it (build). We will define a service (an instance of a container) in our compose file for each of these tasks.

Containers used:

SQL Server using image mcr.microsoft.com/mssql/serverA simple T-SQL Script runner aletasystems/tsqlrunnerFlyway for Build/Deployment boxfuse/flyway

Flyway cannot create a database it expects the database to exist before deploying the code. This is why we use aletasystems/tsqlrunner which is a custom-image based on mcr.microsoft.com/mssql-tools.

SQL Server

We have configured our SQL-Server as follows.

Running on port 14333
Username sa
Password is set in a .env file

Initialise

We now need to initialise SQL server using the container aletasystems/tsqlrunner.Our initialisation process is really simple, execute a create database script against the master database.

First a quick tour on how aletasystems/tsqlrunner works.

In your compose file, you map a local directory containing TSQL files to the containers /tsqlscripts
The local directory follows the convention of databasename\tsql-filename.sql
The runner will first run the scripts in master\filename (in alphabetical order)
Then process each database and script (again in alphabetical order)

With this information, we place a create database TSQL script file (create-db.sql) in ./path2initsqlfiles/master

We now add inittools service to our docker-compose file, we configure it as below.

The SQL Server it connects to is the db service we defined earlier
It users the username sa and password (again it is taken from our .env file).
We give it the path to the SQL Scripts to run.

Deployment

We will use flyway to deploy our code, but you can plugin an alternative that suits your need. I found flyway the easiest to work with, it is a fantastic tool and I hope to write/talk about it in the future.

I won’t get into too much detail about how flyway works as that deserves it own post, but if you are interested you can read How Flyway works

The basics:

volumes shows flyway where the SQL & conf folder are
command tells it what we want to do along with the various parameters

Some useful commands:
To re-run flyway with the latest changes to source code docker-compose restart flyway
To view the flyway log docker-compose logs -f flyway

How did we control order?

We used the depends_on property to tell each service what it depends on. This is not exactly true, as it is (and from what I understand) the moment a service comes online, docker believes it is ready - which can be problematic as SQL Server takes a few seconds to become accessible.

It is therefore important that your services have a retry/delay mechanism built-in. Flyway has an additional parameter -connectRetries=60 and aletasystems/tsqlrunner has a modest retry mechanism

Tools | Data Tools and Software

Kevin George — Wed, 22 Jan 2020 11:28:14 +0000

"To the man who only has a hammer, everything he encounters begins to look like a nail." - Abraham Maslow

Today's Data Engineer requires a variety of tools for their day to day tasks, which vary from working with source control to containers. The objective of this post is to share with you the various tools that I use.

Integrated Development Environment (IDE)

Code IDE's

Visual Studio 2019 community (latest) here
Visual Studio Code here
SQL Server Data Tools (latest) here

Database IDE's

SQL Server Management Studio (SSMS) (latest) here
Azure Data Studio (latest) here
MySQL WorkBench (latest) here
pgAdmin-4 (for Postgresql) here
Oracle Sql Developer here
MongoDB Compass (appropriate version) here
PAID License Multi-platform | DataGrip here

RealTime Data analytics's

Azure Service Bus Explorer ServiceBusExplorer-*.*.*zip here

Data Science Tools

R-Studio here
Anaconda here - This is a collection of DS tools

SDK's & Plugins

.Net core here
Python 2.7.x here
Python 3.x (latest) here
NodeJS (latest) here

Infrastructure

Docker here
minikube here
Azure Storage Emulator here
Azure Cosmos DB Emulator here

DevOps

AzurePowershell RM here
Az CLI module here
Chocolatey here
flywaydb Community Edition here

Utilities

Notepad++ (latest) here
DiffTool Winmerge here
cmder here
git here

Powershell Modules

dbatools here
posh-git here
MicrosoftPowerBIMgmt here
~AzureRM Powershell Module (see above under DevOps)~
Azure AZ Powershell Module here

Useful Websites

https://statisticsparser.com/ - SQL Server SET STATISTICS * parser
https://www.brentozar.com/pastetheplan/ - Share SQL Server Execution Plans
https://jsoneditoronline.org/ - Edit JSON Files easily without worrying about escapes.
http://www.kloth.net/services/nslookup.php - DNS Lookup Site
https://stackedit.io/ - Online Markdown Editor.
https://colab.research.google.com/ - Google Colab (Jupyter notebook like Google Docs)

Slack Communities

SQL Server Slack Community https://dbatools.io/slack
Powershell Slack Community http://slack.poshcode.org/
Octopus Deploy Slack Community https://octopus.com/slack

Personal Notes

Azure Data Studio

Currently, I have a love-hate relationship with Azure Data Studio (Previously SqlOps Studio). While it has a long way to go, the features such as inbuilt graphs, exports, temperamental auto-complete and plugins - do differentiate it slightly, however, I do have to push myself to use it and when I do, I usually end up back in SSMS to do some serious work.

JetBrains DataGrip

Whilst it is an excellent tool with good support for connecting to multiple systems, I have not been able to justify the need for it - this is probably attributed to the fact that I work mostly on SQL Server and Microsoft Tech.

Visual Studio Code

This is one of the IDE's I turn to on a regular basis, whether it is to blog (Jekyll), script (Powershell, Shell, Batch), develop (Python, C#), document (Markdown), Infrastructure (Azure ARM, Docker) - VSCode has a plugin for it. It even has extensions for Data but I always go to SSMS - 'old habits die hard'

My Plugin List:

Requires code to be part of your PATH

code --install-extension Compulim.vscode-azure-storage-utility
code --install-extension DavidAnson.vscode-markdownlint
code --install-extension eamodio.gitlens
code --install-extension eriklynd.json-tools
code --install-extension fcrespo82.markdown-table-formatter
code --install-extension formulahendry.azure-storage-explorer
code --install-extension msazurermtools.azurerm-vscode-tools
code --install-extension ms-azuretools.vscode-azureappservice
code --install-extension ms-azuretools.vscode-azurefunctions
code --install-extension ms-azuretools.vscode-azurestorage
code --install-extension ms-azuretools.vscode-azureterraform
code --install-extension ms-azuretools.vscode-cosmosdb
code --install-extension mshdinsight.azure-hdinsight
code --install-extension ms-kubernetes-tools.vscode-kubernetes-tools
code --install-extension ms-mssql.mssql
code --install-extension ms-python.python
code --install-extension ms-toolsai.vscode-ai
code --install-extension ms-vscode.azure-account
code --install-extension ms-vscode.azurecli
code --install-extension ms-vscode.csharp
code --install-extension ms-vscode.PowerShell
code --install-extension ms-vscode.Theme-MarkdownKit
code --install-extension ms-vscode.vscode-azureextensionpack
code --install-extension ms-vsts.team
code --install-extension PeterJausovec.vscode-docker
code --install-extension redhat.vscode-yaml
code --install-extension Summer.azure-event-hub-explorer
code --install-extension usqlextpublisher.usql-vscode-ext
code --install-extension VisualStudioOnlineApplicationInsights.application-insights
code --install-extension vsciot-vscode.azure-iot-edge
code --install-extension vsciot-vscode.azure-iot-toolkit
code --install-extension wmontalvo.vsc-jsonsnippets
code --install-extension yzane.markdown-pdf

Containers | QuickStart Docker Compose for SQL Server

Kevin George — Tue, 21 Jan 2020 00:00:00 +0000

‘Visual Studio Code + Compose File + Docker Plugin = Amazing’

In my previous post, introduction to docker we looked at how to get, start, stop and remove containers by running the various docker commands. Today, we will look at how to use docker-compose, followed by how easy it is to use with Visual Studio Code + vscode-docker extension.

Introduction to `docker-compose`

Dockers documentation on docker-compose.

Compose is a tool for defining and running multi-container Docker applications.

Note: docker-compose will only work with Linux containers. (At the moment).

Let us now break down the docker run command into a compose file.

docker run -d -p 14333:1433 --name myDocker --hostname myDocker -e sa_password='$3cureP@ssw0rd' -e ACCEPT_EULA=Y microsoft/mssql-server-linux

Recapping, we have a Microsoft/mssql-server-linux container named myDocker, running SQL Server on port 14333 with the SA password as $3cureP@ssw0rd.

Let me explain what each line means, in a simplistic way.

Code	Notes
`version: '3'`	Version of Docker-Compose
`services:`	A list Services in the container
`____ db:`	A container named `db`
`________ image: microsoft/mssql-server-linux`	The image that this services will use
`________ environment:`	A list of environment variables
`____________ SA_PASSWORD: $3cureP@ssw0rd`	The environment variable `SA_PASSWORD` with its value as `$3cureP@ssw0rd`
`____________ ACCEPT_EULA: Y`	The environment variable `ACCEPT_EULA` with its value as `Y`
`________ ports:`	A list of ports exposed by this container
`____________ - '14333:1433'`	The mapping of container-port:1433 to 14333

_Spaces are important in YML files which is why I have used _ to represent them here.

Now that we have an understanding of the basics, how do we run a compose file?

Running a `docker-compose` file

To run a docker-compose file

To run docker-compose -f "path\to\docker-compose.yml" up -d --build
To tear-down docker-compose -f "path\to\docker-compose.yml" down
to restart, well it is down and up :)

And now a screencast of how to run a docker-compose file in VS Code

mp4 version(Ps: I used screentogif to make these)

Environment Variables in `docker-compose` file

If you recall, in the above screencast there was an error Invalid interpolation format for "environment" option in service "db": "$3cureP@ssw0rd". With a compose file you can use variables in the format of ${variable_name}.

Scenario: You don’t want to hard-code the password into your Docker-Compose file but would like it to be taken from a .env file (ideally it is not part of source-control).

In your docker-compose file you replace the hard-code password with a variable ${SQL_SERVER_PASSWORD}

Then you create a file name .env in the same folder as the docker-compose file.

Now when you run docker-compose up it will automatically substitute the value of $3cureP@ssw0rd into ${SQL_SERVER_PASSWORD} and as a bonus docker will automatically escape the $ and change to $$

A snippet to run this code-example.

Containers | Quick Start SQL Server Containers

Kevin George — Tue, 14 Jan 2020 00:00:00 +0000

‘It works on my machine’, ‘What version are you using’, ‘check your config’ - another developer

Components

Let me try and explain the graphic above

Container Registry: A place where container-images are hosted and can be downloaded onto your local docker instance. A common public repository is docker-hub. You can also have private container repositories, lookup azure container registry
Docker on your machine, for illustrative purposes I have broken it down into two parts
1. a local repository of container images
2. a space that represents running containers, an instance of a container. You can have multiple instances of a container running

The docker engine can run in either Windows or Linux mode but not both simultaneously (at the moment), this is important as you cannot run a Linux SQL Server side by side with a windows SQL server or more realistically, you cannot use a windows-SQL-server with a Linux Web server. This is one of the reasons you will see that I tend to use Linux containers over windows.

Container WorkFlow

We Get the container image
1. for windows microsoft/mssql-server-windows-developer by running docker pull microsoft/mssql-server-windows-developer:latest
2. for linux microsoft/mssql-server-linux by running docker pull microsoft/mssql-server-linux:latest
Run (start) the container docker run -d -p 14333:1433 --name myDocker --hostname myDocker -e SA_PASSWORD='$3cureP@ssw0rd' -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer
1. for windows docker run -d -p 14333:1433 --name myDocker --hostname myDocker -e SA_PASSWORD='$3cureP@ssw0rd' -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer
2. for linux docker run -d -p 14333:1433 --name myDocker --hostname myDocker -e SA_PASSWORD='$3cureP@ssw0rd' -e ACCEPT_EULA=Y microsoft/mssql-server-linux
Connect
1. using SSMS Server localhost,14333 , sa , $3cureP@ssw0rd
2. using Powershell Invoke-Sqlcmd -ServerInstance "localhost,14333" -Database master -Username sa -Password '$3cureP@ssw0rd' -Query 'SELECT DB_NAME() as DatabaseName'
Stop the container docker stop myDocker
Start (Resume/Restart) the container docker start myDocker
Destroy the container docker rm myDocker --force

Note: Linux or Windows is not based on your OS but on the runtime version of docker-desktop as illustrated below, you can see switch to windows containers indicates I am currently running in Linux container mode.

Parameters:

Docker Reference docker run

`-p 14333:1433 | --expose`

-p defines the port mapping between the host machine and the container. For example, an httpd image exposes port 80, and similarly, the mssql-server-windows-developer exposes 1433.

The reason I mapped port 14333 to 1433 is that on my machine I have a SQL Server Developer Edition installed and running. If I bind to 1433:1433 then there would have been a port conflict between the docker instance and the local SQL Server instance.

`--name myDocker`

A friendly name for your container that allows you to run commands such as docker inspect myDocker vs docker inspect <ContainerGuidID>

`--hostname myDocker`

This is the value of the hostname which I needed specifically for JDBC URL. Please note that you can use localhost / 127.0.0.1 too

`--env | -e`

-e is a docker parameter for environment variables, the two variables exposed by the mssql-server-windows-developer image are

SA_PASSWORD: it sets the sa password. (the password matches complexity requirements).
ACCEPT_EULA: a flag to indicate you accept the EULA.

`--volume`

Simplistically, using --volume allows you to share a folder between host and container.

A few reasons you would do this are:

I want to persist my .mdf files to my local disk.
I have a .bak / .mdf file that is shared between developers
I need to use BULK INSERT
Any other reason that requires me to share files between the host and container instance.

Host: C:\DockerPersistedStorage\Containers\myDocker

Example: --volume 'C:\DockerPersistedStorage':'C:\DockerPersistedStorage' would map 'C:\DockerPersistedStorage' on your host machine to 'C:\DockerPersistedStorage' on the Docker instance.

Example: --volume 'C:\DockerPersistedStorage':'/path/on/linux/file/system/DockerPersistedStorage' would map 'C:\DockerPersistedStorage' on your host machine to '/path/on/linux/file/system/DockerPersistedStorage' on the Docker instance.