Docker

Install SQL server on Linux CentOS Redhat create multiple Docker in laptop using VMware Workstation – Part 4

Advertisements

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.

  1. Install VMware workstation and CentOS
  2. Install SQL server on Linux CentOS
  3. Create and install multiple Docker SQL server
  4. Create volumes with persist data for SQL server database files
  5. 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.

  1. Backup and restore SQL server databases from Linux to windows

 

  1. 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:

  1. Create your customized image and container (create Db, agent enabled) push it to Docker hub and pull back.
  2. Test your production changes on Docker before on prod like deployments.
  3. Create container from Docker file template by using custom images.
  4. 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

#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

#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

# 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

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: