NOTE: If you want me to make a translation of this video to english, please help me with a little of support!
This project will create a docker image with all the sample databases restored. You can deploy by either a stateless deployment or a stateful deployment.
Databases included:
- Pubs
- Northwind
- WideWorldImporters
- AdventureWorks2017
- AdventureWorks2016*
- AdventureWorks2014*
- AdventureWorks2012*
- AdventureWorksDW2017*
- StackOverflow2010*
- WideWorldImportersDW*
NOTE: Databases marked with * must be switched on during build with INCLUDE_ALL_DATABASES=1
Only common databases are deployed by default. To deploy ALL databases in your container, please enable the build flag called "INCLUDE_ALL_DATABASES=1"
docker-compose build --build-arg INCLUDE_ALL_DATABASES=1IMPORTANT: StackOverflow2010 database is huge and it will require a couple of minutes to initialize. Please be patient. You can work and play within the other databases while the StackOverflow database is being prepared
Edit the docker-compose.yml file and comment the following lines:
#volumes:
# - ${LOCAL_MOUNTPOINT}:/var/opt/mssql/dataNOTE: Doing that, will disable mounting the local folder specified in the .env file
Then, you can create and run the image with the following command:
docker-compose up --buildIMPORTANT: StackOverflow2010 database is huge and it will require a couple of minutes to initialize. Please be patient. You can work and play within the other databases while the StackOverflow database is being prepared
With the docker-compose.yml file you will deploy all databases in a persistent folder in the host (remind to configure the .env file with a valid local folder):
-
LOCAL_MOUNTPOINT
The folder must exist ( for example: /home/enrique/your/path/to/volume/)
-
SHARED_FOLDER
The folder must exists. This shared folder can be used for example, to deploy backups or easily copy-paste between container and host
IMPORTANT: There is some kind of bug with WSL2 and if you want to use stateful deployment, you need to start your container inside the wsl2 image. You cant execute docker-compose up from windows
Permissions are very important, since you are mounting local volumes to your container. To create a local folder and mount that folder to your container:
# log into your linux (host or wsl2 image)
mkdir -p /your/path/
chown 10001:0 /your/path/
chmod +rwx /your/path/And now, in the docker-compose, you can reference that path, for example
volumes:
- ${LOCAL_MOUNTPOINT}:/var/opt/mssql/dataNow, when you start the container, you will see how the files are deployed locally
mssql-server-samplesdb | 2020-05-25 16:23:11.74 Server Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2020-05-25 16:23:12.05 Server Did not find an existing master data file /var/opt/mssql/data/master.mdf, copying the missing default master and other system database files. If you have moved the database location, but not moved the database files, startup may fail. To repair: shutdown SQL Server, move the master database to configured location, and restart.
2020-05-25 16:23:12.11 Server Setup step is copying system data file 'C:\templatedata\mastlog.ldf' to '/var/opt/mssql/data/mastlog.ldf'.
2020-05-25 16:23:12.15 Server Setup step is copying system data file 'C:\templatedata\model.mdf' to '/var/opt/mssql/data/model.mdf'.
....-
FORCE_ATTACH_IF_MDF_EXISTS
1 -> if you don´t want to "restore" and the files exists, you can attach those databases 0 -> if you did´nt executed docker-compose down, you can still "up" your container with previously restored databases
You can create and run the image with the following command:
docker-compose up --buildThe Dockerfile specifies which base SQL Server Instance you want to use for your image.
In case you want to change the version of the SQL Server used, please go edit the first line of the Dockerfile and select your prefered version. For example
Change
FROM mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
To
FROM mcr.microsoft.com/mssql/server:2017-latest-ubuntu
To get the latest SQL Server 2017 version with applied CU
NOTE: To see which SQL Server versions, please go here and select your "tag"
It´s as easy as modifying the Dockerfile, and adding the new backups you want to restore, and modifying the setup.sql file with the RESTORE command.
The password for the "sa" account is specified at the docker-compose.yml file.
Well, its a little tricky but when you find how it works, its very simple and stable:
Dockerfile makes 3 mayor steps
This is the first thing we need to do, since we are going to download directly to the image, the databases we want
RUN apt-get update && apt-get install -y \
curl \
apt-transport-https \
p7zip-full
IMPORTANT: Please have in mind that starting with SQL Server 2019, mssql server containers are non-root. We need to change to root for executing specific tasks like this one
Once we have the curl installed, we are now ready to download the databases, and that´s what you found here:
##############################################################
# DATABASES SECTION
# 1) Add here the databases you want to have in your image
# 2) Edit setup.sql and include the RESTORE commands
#
# Adventureworks databases
#
RUN curl -L -o AdventureWorks2017.bak https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak
RUN curl -L -o AdventureWorks2016.bak https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak
...
NOTE: Here you can add-remove the databases you want
This is the tricky part since involves 2 scripts and the final command to keep alive the image
COPY setup.* ./
COPY entrypoint.sh ./
RUN chmod +x setup.sh
RUN chmod +x entrypoint.sh
# This entrypoint start sql server, restores data and waits infinitely
ENTRYPOINT ["./entrypoint.sh"]
To avoid the container to stop after first run, you need to ensure that is waiting for something. the best solution is to add a sleep infinity...as simple as it sounds :)
CMD ["sleep infinity"]