Creating a Database Dev Environment using SQL Server container images with Docker

Here in the example I have used SQL Server 2019 preview container image to create a database development environment on macOS. The image consists of SQL Server running on Linux based on Ubuntu 16.04.

The complete list of prerequisites have been documented here:

First I used docker pull command to pull the container image from Docker Hub for SQL Server 2019.

Then I ran the container image with Docker.

I could see the container using docker ps command.

Microsoft recommends to change the SA password for security reasons as it is easily discoverable in the container after creating the container from the MSSQL_SA_PASSWORD environment variable.

Then I started the interactive bash shell inside container.

Now I could use the Sqlcmd to connect to the SQL instance locally and preform a quick check.

Now that a SQL Instance was up and working I wanted data from an existing database which was in AWS RDS for SQL Server. I could use SSMS or other tools to generate DDL scripts for the database but I was looking into a more scripting like tool which could also help in automating the process at a later stage. I used mssql-scripter which is the multiplatform command line equivalent of the widely used Generate Scripts Wizard experience in SSMS. It is build on Python and easy to use.

I had already configured the tool and now just had to extract DDL for an existing AWS RDS.

Using Sqlcmd I execute the script generated to the docker container.

Using Azure Data Studio I can see the list of tables from the AWD RDS database and for my project I only need data from one table.

I used bcp command-line utility to extract the table records from the AWS RDS and imported them into the docker container for the project.

Finally I preformed a quick check using Sqlcmd and now I had the data needed to work on the project.