What is Docker container https://www.docker.com/resources/what-container
In simple container is an image or package that will have binaries, configurations etc of the application to run on. Example – In a single OS you can run multiple containers, like CentOS SQL 2017 and 2019 version etc. It is very easy to install, once the image pulled and stored locally, creating SQL server is in a seconds, the advantage is you can create your own images based on the organization standards and store it in Docker HUB. Docker is a container engine developed by Docker to run a containers.
Being SQL server database administrator, mostly we will not worked on Linux platform, I have supported Oracle couple of years and have knowledge of Linux platform.
Following is the 6 steps we are going to test and do ourselves in laptop.
- Install VMware workstation and CentOS
- Install SQL server on Linux CentOS
- Create and install multiple Docker SQL server
- Create volumes with persist data for SQL server database files
- Customize base SQL server image with Docker file
Creating customized SQL container based the organization standard and push to Docker hub.
Create multiple container from Docker file template by using custom SQL server images.
Create multiple SQL server containers creation by using Docker compose with YML script.
- Backup and restore SQL server databases from Linux to windows
- Customize base SQL server image with Docker file
Create our own customized images by using various technique and push it into Docker HUB.
Creating customized container based the organization standard and push to Docker hub.
Create multiple container from Docker file template by using custom images.
Create multiple container creation by using Docker compose with YML script.
Workout:
- Create your customized image and container (create Db, agent enabled) push it to Docker hub and pull back.
- Test your production changes on Docker before on prod like deployments.
- Create container from Docker file template by using custom images.
- We can automate and create multiple container by using Docker compose file using YML script.
This is very useful to build quickly to test of all, otherwise we have to install SQL and create database (build OS) etc.
Most if the time, we have to build custom container images according to your organization standards, like database recovery mode and number of data files and list of tables etc. available whenever we create a Docker SQL.
####You have to build custom container images according to your standards.
#Test: 1) Create a container 2) Enable the agent on 3) Create a new image of agent on 4) create a new container from the new image
# create a container without agent enabled, this is default setting
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed' -p 1410:1433 --name sql-linux_CE_agent -d -h computer-3.broadband mcr.microsoft.com/mssql/server:2019-latest
#Start the SQL agent when we restart the SQL server docker
/opt/mssql-tools/bin/sqlcmd -S ip,port -U user name
/opt/mssql-tools/bin/sqlcmd -S 192.168.1.27,1410 -U SA
exec sp_configure 'show advanced options',1
exec sp_configure 'Agent XPs',1
exec sp_configure 'show advanced options',0
# create database and tables , objects
docker stop sql-linux_CE_agent
docker commit sql-linux_CE_agent
#Sign up the docker and create a repository called databaseimages.
#https://hub.docker.com/repository/docker/muthukumark1986/databaseimages #docker pull muthukumark1986/databaseimages
docker tag <image id> docker user name / repository:tag
docker tag 41e00dad1e8d muthukumark1986/databaseimages:sqlagenton
# remove the old container
docker rm sql-linux_CE_agent
# create a new container by pulling your images from local SQLagenton and create a sql-linux_CE_agenton
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed' -p 1420:1433 --name sql-linux_CE_agenton -d -h computer-3.broadband muthukumark1986/databaseimages:sqlagenton
############=============Push images into docker hub
docker login # enter user name & password
docker push username/imagename:tag
docker push muthukumark1986/databaseimages:sqlagenton
# check your docker hub account repository
#Pull it your own image from docker hub and create a new container
docker pull muthukumark1986/databaseimages:sqlagenton
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed' -p 1430:1433 --name sql-linux_CE_agentON_pull -d -h computer-3.broadband muthukumark1986/databaseimages:sqlagenton
####You have to build custom container images according to your standards.
#Test: 1) Create a container 2) Enable the agent on 3) Create a new image of agent on 4) create a new container from the new image
============
# create a container without agent enabled, this is default setting
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed' -p 1410:1433 --name sql-linux_CE_agent -d -h computer-3.broadband mcr.microsoft.com/mssql/server:2019-latest
#Start the SQL agent when we restart the SQL server docker
/opt/mssql-tools/bin/sqlcmd -S ip,port -U user name
/opt/mssql-tools/bin/sqlcmd -S 192.168.1.27,1410 -U SA
#Enable agent
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'Agent XPs',1
reconfigure
go
exec sp_configure 'show advanced options',0
go
reconfigure
exit
# create database and tables , objects
# stop the container
docker stop sql-linux_CE_agent
#Build a new image
docker commit sql-linux_CE_agent
#check the images
docker images
#Sign up the docker and create a repository called databaseimages.
#https://hub.docker.com/repository/docker/muthukumark1986/databaseimages #docker pull muthukumark1986/databaseimages
docker tag <image id> docker user name / repository:tag
docker tag 41e00dad1e8d muthukumark1986/databaseimages:sqlagenton
# remove the old container
docker rm sql-linux_CE_agent
docker images
# create a new container by pulling your images from local SQLagenton and create a sql-linux_CE_agenton
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed' -p 1420:1433 --name sql-linux_CE_agenton -d -h computer-3.broadband muthukumark1986/databaseimages:sqlagenton
docker ps -a
############=============Push images into docker hub
docker login # enter user name & password
docker push username/imagename:tag
docker push muthukumark1986/databaseimages:sqlagenton
# check your docker hub account repository
#Pull it your own image from docker hub and create a new container
docker pull muthukumark1986/databaseimages:sqlagenton
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed' -p 1430:1433 --name sql-linux_CE_agentON_pull -d -h computer-3.broadband muthukumark1986/databaseimages:sqlagenton
####You have to build custom container images according to your standards.
#Test: 1) Create a container 2) Enable the agent on 3) Create a new image of agent on 4) create a new container from the new image
============
# create a container without agent enabled, this is default setting
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed' -p 1410:1433 --name sql-linux_CE_agent -d -h computer-3.broadband mcr.microsoft.com/mssql/server:2019-latest
#Start the SQL agent when we restart the SQL server docker
/opt/mssql-tools/bin/sqlcmd -S ip,port -U user name
/opt/mssql-tools/bin/sqlcmd -S 192.168.1.27,1410 -U SA
#Enable agent
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'Agent XPs',1
reconfigure
go
exec sp_configure 'show advanced options',0
go
reconfigure
exit
# create database and tables , objects
# stop the container
docker stop sql-linux_CE_agent
#Build a new image
docker commit sql-linux_CE_agent
#check the images
docker images
#Sign up the docker and create a repository called databaseimages.
#https://hub.docker.com/repository/docker/muthukumark1986/databaseimages #docker pull muthukumark1986/databaseimages
docker tag <image id> docker user name / repository:tag
docker tag 41e00dad1e8d muthukumark1986/databaseimages:sqlagenton
# remove the old container
docker rm sql-linux_CE_agent
docker images
# create a new container by pulling your images from local SQLagenton and create a sql-linux_CE_agenton
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed' -p 1420:1433 --name sql-linux_CE_agenton -d -h computer-3.broadband muthukumark1986/databaseimages:sqlagenton
docker ps -a
############=============Push images into docker hub
docker login # enter user name & password
docker push username/imagename:tag
docker push muthukumark1986/databaseimages:sqlagenton
# check your docker hub account repository
#Pull it your own image from docker hub and create a new container
docker pull muthukumark1986/databaseimages:sqlagenton
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed' -p 1430:1433 --name sql-linux_CE_agentON_pull -d -h computer-3.broadband muthukumark1986/databaseimages:sqlagenton
Create multiple SQL server container from docker file template by using custom images
#Create multiple container from docker file template by using custom images
# create a SQL file, based on your requirements of your application in the initial build
create table tbl_employee (empid int)
insert into tbl_employee values (1)
# use MSSQL 2019 base image –6 layers
FROM mcr.microsoft.com/mssql/server:2019-latest
# create directory within SQL container for database files
RUN mkdir -p /opt/mssql-scripts
# copy the database files from host to container
COPY create-db.sql /opt/mssql-scripts
# set environment variables
ENV MSSQL_SA_PASSWORD=G0d$peed#12345
RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" \
&& /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'G0d$peed#12345' -i /opt/mssql-scripts/create-db.sql \
# Build image from the files
sudo docker build -tag <image name> location path
sudo docker build -t websitedb .
anaconda-ks.cfg create-db.sql Dockerfile initial-setup-ks.cfg
[root@computer-3 ~]# vi Dockerfile
[root@computer-3 ~]# sudo docker build -t website2019db .
Sending build context to Docker daemon 26.11kB
Step 1/6 : FROM mcr.microsoft.com/mssql/server:2019-latest
Step 2/6 : RUN mkdir -p /opt/mssql-scripts
---> Running in 9f6610e78344
Removing intermediate container 9f6610e78344
Step 3/6 : COPY create-db.sql /opt/mssql-scripts
Step 4/6 : ENV MSSQL_SA_PASSWORD=G0d$peed#12345
---> Running in 1192971b413f
Removing intermediate container 1192971b413f
Step 5/6 : ENV ACCEPT_EULA=Y
---> Running in 629154b42e06
Removing intermediate container 629154b42e06
Step 6/6 : RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" && /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'G0d$peed#12345' -i /opt/mssql-scripts/create-db.sql && pkill sqlservr
---> Running in 8fdc3837fad0
Removing intermediate container 8fdc3837fad0
Successfully built b991c6e8c589
# create a container from you customized image
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed#12345' -p 1411:1433 --name sql-website_V1 -d -h computer-3.broadband website2019db
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed#12345' -p 1811:1433 --name sql-website-2017 -d -h computer-3.broadband websitedb
#Create multiple container from docker file template by using custom images
#open vi editor
# create a SQL file, based on your requirements of your application in the initial build
vi create-db.sql
create database DBA_Test
go
create table tbl_employee (empid int)
go
insert into tbl_employee values (1)
# create a Docker file
vi Dockerfile
# use MSSQL 2019 base image –6 layers
FROM mcr.microsoft.com/mssql/server:2019-latest
# create directory within SQL container for database files
RUN mkdir -p /opt/mssql-scripts
# copy the database files from host to container
COPY create-db.sql /opt/mssql-scripts
# set environment variables
ENV MSSQL_SA_PASSWORD=G0d$peed#12345
ENV ACCEPT_EULA=Y
# run initial scripts
RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" \
&& /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'G0d$peed#12345' -i /opt/mssql-scripts/create-db.sql \
&& pkill sqlservr
# Build image from the files
sudo docker build -tag <image name> location path
sudo docker build -t websitedb .
[root@computer-3 ~]# ls
anaconda-ks.cfg create-db.sql Dockerfile initial-setup-ks.cfg
[root@computer-3 ~]# vi Dockerfile
[root@computer-3 ~]# sudo docker build -t website2019db .
Sending build context to Docker daemon 26.11kB
Step 1/6 : FROM mcr.microsoft.com/mssql/server:2019-latest
---> d60e9ac97708
Step 2/6 : RUN mkdir -p /opt/mssql-scripts
---> Running in 9f6610e78344
Removing intermediate container 9f6610e78344
---> d6d8e7e8d8c2
Step 3/6 : COPY create-db.sql /opt/mssql-scripts
---> 16a4d9a8d482
Step 4/6 : ENV MSSQL_SA_PASSWORD=G0d$peed#12345
---> Running in 1192971b413f
Removing intermediate container 1192971b413f
---> 49985f938d47
Step 5/6 : ENV ACCEPT_EULA=Y
---> Running in 629154b42e06
Removing intermediate container 629154b42e06
---> a6b25afd60dc
Step 6/6 : RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" && /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'G0d$peed#12345' -i /opt/mssql-scripts/create-db.sql && pkill sqlservr
---> Running in 8fdc3837fad0
(1 rows affected)
Removing intermediate container 8fdc3837fad0
---> b991c6e8c589
Successfully built b991c6e8c589
# create a container from you customized image
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed#12345' -p 1411:1433 --name sql-website_V1 -d -h computer-3.broadband website2019db
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed#12345' -p 1811:1433 --name sql-website-2017 -d -h computer-3.broadband websitedb
#Create multiple container from docker file template by using custom images
#open vi editor
# create a SQL file, based on your requirements of your application in the initial build
vi create-db.sql
create database DBA_Test
go
create table tbl_employee (empid int)
go
insert into tbl_employee values (1)
# create a Docker file
vi Dockerfile
# use MSSQL 2019 base image –6 layers
FROM mcr.microsoft.com/mssql/server:2019-latest
# create directory within SQL container for database files
RUN mkdir -p /opt/mssql-scripts
# copy the database files from host to container
COPY create-db.sql /opt/mssql-scripts
# set environment variables
ENV MSSQL_SA_PASSWORD=G0d$peed#12345
ENV ACCEPT_EULA=Y
# run initial scripts
RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" \
&& /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'G0d$peed#12345' -i /opt/mssql-scripts/create-db.sql \
&& pkill sqlservr
# Build image from the files
sudo docker build -tag <image name> location path
sudo docker build -t websitedb .
[root@computer-3 ~]# ls
anaconda-ks.cfg create-db.sql Dockerfile initial-setup-ks.cfg
[root@computer-3 ~]# vi Dockerfile
[root@computer-3 ~]# sudo docker build -t website2019db .
Sending build context to Docker daemon 26.11kB
Step 1/6 : FROM mcr.microsoft.com/mssql/server:2019-latest
---> d60e9ac97708
Step 2/6 : RUN mkdir -p /opt/mssql-scripts
---> Running in 9f6610e78344
Removing intermediate container 9f6610e78344
---> d6d8e7e8d8c2
Step 3/6 : COPY create-db.sql /opt/mssql-scripts
---> 16a4d9a8d482
Step 4/6 : ENV MSSQL_SA_PASSWORD=G0d$peed#12345
---> Running in 1192971b413f
Removing intermediate container 1192971b413f
---> 49985f938d47
Step 5/6 : ENV ACCEPT_EULA=Y
---> Running in 629154b42e06
Removing intermediate container 629154b42e06
---> a6b25afd60dc
Step 6/6 : RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" && /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'G0d$peed#12345' -i /opt/mssql-scripts/create-db.sql && pkill sqlservr
---> Running in 8fdc3837fad0
(1 rows affected)
Removing intermediate container 8fdc3837fad0
---> b991c6e8c589
Successfully built b991c6e8c589
# create a container from you customized image
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed#12345' -p 1411:1433 --name sql-website_V1 -d -h computer-3.broadband website2019db
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed#12345' -p 1811:1433 --name sql-website-2017 -d -h computer-3.broadband websitedb
Create multiple SQL server container creation by using docker compose with YML script
## Create multiple container creation by using docker compose with YML script
sudo curl -L "https://github.com/docker/compose/releases/download/1.25.5/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose
sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose
sudo rm /usr/local/bin/docker-compose
image: mcr.microsoft.com/mssql/server:2019-latest
SA_PASSWORD: "G0d$peed#12345"
MSSQL_DATA_DIR: "/var/opt/mssql/data"
MSSQL_LOG_DIR: "/var/opt/mssql/log"
image: mcr.microsoft.com/mssql/server:2019-latest
SA_PASSWORD: "G0d$peed#12345"
MSSQL_DATA_DIR: "/var/opt/sqlserver/data"
MSSQL_LOG_DIR: "/var/opt/sqlserver/log"
image: mcr.microsoft.com/mssql/server:2019-latest
SA_PASSWORD: "G0d$peed#12345"
MSSQL_DATA_DIR: "/var/opt/sqlserver/data"
MSSQL_LOG_DIR: "/var/opt/sqlserver/log"
MSSQL_BACKUP_DIR: "/var/opt/sqlserver/backup"
- sqlsystem:/var/opt/mssql/
- sqldata:/var/opt/sqlserver/data
- sqllog:/var/opt/sqlserver/log
=================== ###image: mcr.microsoft.com/mssql/server:2017-latest
#Create a container from docker-compose by using coustomized docker and SQL files
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed#12345' -p 1910:1433 --name sql-website_V2 <image id>
## Create multiple container creation by using docker compose with YML script
# install docker compose
sudo curl -L "https://github.com/docker/compose/releases/download/1.25.5/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose
sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose
docker-compose --version
# uninstall
sudo rm /usr/local/bin/docker-compose
docker version
vi docker-compose.yml
version: '3'
services:
server1:
image: mcr.microsoft.com/mssql/server:2019-latest
ports:
- "1910:1433"
environment:
SA_PASSWORD: "G0d$peed#12345"
ACCEPT_EULA: "Y"
MSSQL_DATA_DIR: "/var/opt/mssql/data"
MSSQL_LOG_DIR: "/var/opt/mssql/log"
sleep 35:
server2:
image: mcr.microsoft.com/mssql/server:2019-latest
ports:
- "1911:1433"
environment:
SA_PASSWORD: "G0d$peed#12345"
ACCEPT_EULA: "Y"
MSSQL_DATA_DIR: "/var/opt/sqlserver/data"
MSSQL_LOG_DIR: "/var/opt/sqlserver/log"
##====================
version: '3'
services:
sqlserver1:
image: mcr.microsoft.com/mssql/server:2019-latest
ports:
- "1910:1433"
environment:
SA_PASSWORD: "G0d$peed#12345"
ACCEPT_EULA: "Y"
MSSQL_DATA_DIR: "/var/opt/sqlserver/data"
MSSQL_LOG_DIR: "/var/opt/sqlserver/log"
MSSQL_BACKUP_DIR: "/var/opt/sqlserver/backup"
volumes:
- sqlsystem:/var/opt/mssql/
- sqldata:/var/opt/sqlserver/data
- sqllog:/var/opt/sqlserver/log
volumes:
sqlsystem:
sqldata:
sqllog:
# run the docker compose
docker-compose up
docker-compose down
=================== ###image: mcr.microsoft.com/mssql/server:2017-latest
#Create a container from docker-compose by using coustomized docker and SQL files
vi docker-compose.yml
version: '3'
services:
server1:
build: .
docker-compose up
docker images
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed#12345' -p 1910:1433 --name sql-website_V2 <image id>
# docker error log read
docker logs --tail 50
## Create multiple container creation by using docker compose with YML script
# install docker compose
sudo curl -L "https://github.com/docker/compose/releases/download/1.25.5/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose
sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose
docker-compose --version
# uninstall
sudo rm /usr/local/bin/docker-compose
docker version
vi docker-compose.yml
version: '3'
services:
server1:
image: mcr.microsoft.com/mssql/server:2019-latest
ports:
- "1910:1433"
environment:
SA_PASSWORD: "G0d$peed#12345"
ACCEPT_EULA: "Y"
MSSQL_DATA_DIR: "/var/opt/mssql/data"
MSSQL_LOG_DIR: "/var/opt/mssql/log"
sleep 35:
server2:
image: mcr.microsoft.com/mssql/server:2019-latest
ports:
- "1911:1433"
environment:
SA_PASSWORD: "G0d$peed#12345"
ACCEPT_EULA: "Y"
MSSQL_DATA_DIR: "/var/opt/sqlserver/data"
MSSQL_LOG_DIR: "/var/opt/sqlserver/log"
##====================
version: '3'
services:
sqlserver1:
image: mcr.microsoft.com/mssql/server:2019-latest
ports:
- "1910:1433"
environment:
SA_PASSWORD: "G0d$peed#12345"
ACCEPT_EULA: "Y"
MSSQL_DATA_DIR: "/var/opt/sqlserver/data"
MSSQL_LOG_DIR: "/var/opt/sqlserver/log"
MSSQL_BACKUP_DIR: "/var/opt/sqlserver/backup"
volumes:
- sqlsystem:/var/opt/mssql/
- sqldata:/var/opt/sqlserver/data
- sqllog:/var/opt/sqlserver/log
volumes:
sqlsystem:
sqldata:
sqllog:
# run the docker compose
docker-compose up
docker-compose down
=================== ###image: mcr.microsoft.com/mssql/server:2017-latest
#Create a container from docker-compose by using coustomized docker and SQL files
vi docker-compose.yml
version: '3'
services:
server1:
build: .
docker-compose up
docker images
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=G0d$peed#12345' -p 1910:1433 --name sql-website_V2 <image id>
# docker error log read
docker logs --tail 50