Cloning SQL Server Containers With Pure Storage Docker Volumes
In my first blog https://bannaych.blogspot.com/2020/04/mssql-server-on-docker-with-pure.html I showed you how we can create SQL Server containers using the Pure Storage Docker plugin. In this blog I will show you how we can leverage the Pure Storage Docker plugin to instantly create a clone volume from the source volume, then use Pure Storage crash consistent snapshots to refresh the clone volume.
Crash consistent snapshots provide us with a point in time copy of the volume/database, because we use metadata pointers to the original blocks on disk, the snapshots are created instantly with no initial space consumption, then those snapshots can be used to refresh volumes or create new ones.
The big advantage of using snapshots is that we no longer need to backup and restore large databases to provision and refresh TEST/DEV/UAT environments. In the world of DevOps being agile is imperative and Pure Storage space efficient snapshots are key to maximising productivity of database administrators and developers.
Containers are transforming the way we develop applications, the ability to quickly spin up containers has empowered developers to manage their own environments, a huge advantage is the ability to spin up containers quickly and present them with persistent storage that they can manage themselves, and for that storage to consume only a fraction of the space compared to what is used to is a key differentiator. Pure storage provides that differentiator with our FlashArray product coupled with our industry leading data reduction and container plugins.
Environment
Role | FQDN | IP | OS | RAM | CPU | Docker plugin |
---|---|---|---|---|---|---|
Docker Virtual Machine | docker.localdomain | 192.168.111.198 | OEL 7 | 5G | 5 | 3.8 |
- Let's log into the docker server and confirm the SQL Server container is running
# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
f97a85f18e3f mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04 "/opt/mssql/bin/perm…" 5 days ago Up 5 days 0.0.0.0:1433->1433/tcp sql4
- The sql4 docker container is running on docker volume sqldata1
# docker volume list|grep sqldata1
pure:latest sqldata1
- Let's log onto the FlashArray and confirm the volume and serial number. ( S/N 168A )
- Let's log into the container and triple check, as you can see the mapper device ends in 168a
# docker exec -it f97a85f18e3f bash mssql@f97a85f18e3f:/$ df -k Filesystem 1K-blocks Used Available Use% Mounted on overlay 68328860 48448380 19880480 71% / tmpfs 65536 0 65536 0% /dev tmpfs 2922740 0 2922740 0% /sys/fs/cgroup shm 65536 0 65536 0% /dev/shm /dev/mapper/ol-root 68328860 48448380 19880480 71% /etc/hosts /dev/mapper/3624a9370a21265762db64ece0005168a 19521008 236864 19284144 2% /var/opt/mssql tmpfs 2922740 0 2922740 0% /proc/acpi tmpfs 2922740 0 2922740 0% /proc/scsi tmpfs 2922740 0 2922740 0% /sys/firmware
- Let's log in the SQL Server and create a new database called blogdemo, This time I'll be doing this using the sqlcmd utility instead of SSMS - sqlcmd comes with the linux mssql-tools unixODBC-devel packages ( either use yum or apt-get ) to install these
# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P P@ssw0rd
1> 1> select name from sys.databases
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
sqldemo
chrisb
(6 rows affected)
1> create database blogdemo
2> go
1> select name from sys.databases
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
sqldemo
chrisb
blogdemo
(7 rows affected)
1>
- Now let's create a new clone Docker volume called snapvol1 using the source docker volume sqldata1 ( sqldata1 is the volume we created the source container on )
# docker volume create --driver=pure --name snapvol1 -o source=sqldata1
# docker volume ls|grep snapvol1
pure:latest snapvol1
- Now that we have created our new container volume, let's use this volume to create a new docker container called sqlsnap
NOTE: We will be using a new port mapping number of 1430:1433 as the source container is already using 1433:1433
# docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=P@ssw0rd" -p 1430:1433 --volume-driver pure --volume snapvol1:/var/opt/mssql --name sqlsnap -d mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04
1dfce919128d435452d4ec75488b7c6f8fcc8f77955388922deecfdc7eecf923
- Let's have a look at the containers on our system now, two containers ( sql4 and sqlsnap ))
# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
1dfce919128d mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04 "/opt/mssql/bin/perm…" 10 seconds ago Up 3 seconds 0.0.0.0:1430->1433/tcp sqlsnap
f97a85f18e3f mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04 "/opt/mssql/bin/perm…" 5 days ago Up 5 days 0.0.0.0:1433->1433/tcp sql4
- The moment of truth, let's log into the new sqlsnap SQL Server and confirm we have the correct data
]# /opt/mssql-tools/bin/sqlcmd -S localhost,1430 -U sa -P P@ssw0rd 1> select name from sys.databases 2> go name -------------------------------------------------------------------------------------------------------------------------------- master tempdb model msdb sqldemo chrisb blogdemo (7 rows affected) 1>
- So Lets re-cap what we have done so far:
- Created a new clone volume of our original source docker volume.
- Created a new container using the clone of the source volume.
- Confirmed the clone was an exact copy of the source volume.
- First lets add some more data to the source database
/opt/mssql-tools/bin/sqlcmd -S localhost,1433 -U sa -P P@ssw0rd
1>
2> create database blogdemo2
3> go
1> select name from sys.databases
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
sqldemo
chrisb
blogdemo
blogdemo2
(8 rows affected)
1>
- Let's log on to the array and take a snapshot of the source volume sqldata1 - This time we will do it from the command line ( CLI )
#ssh pureuser@192.168.111.130
pureuser@192.168.111.130's password:
Last login: Wed May 13 00:06:19 2020 from 192.168.111.198
Wed May 13 00:14:30 2020
Welcome pureuser. This is Purity Version 5.3.4 on FlashArray Pure-SYD-m20-1
http://www.purestorage.com/
pureuser@Pure-SYD-m20-1>
pureuser@Pure-SYD-m20-1> purevol snap -
--help --suffix -h
pureuser@Pure-SYD-m20-1> purevol snap --suffix demo2 docker-docker-sqldata1
Name Size Source Created Serial
docker-docker-sqldata1.demo2 19531250K docker-docker-sqldata1 2020-05-13 00:15:16 AEST A21265762DB64ECE0005704A1
- The snap name is called docker-docker-sqldata1.demo2, we will use this snapshot to overwrite the sqlsnap container volume snapvol1. But first we need to stop the container, then refresh the target volume.
# docker stop sqlsnap sqlsnap [root@docker ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES f97a85f18e3f mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04 "/opt/mssql/bin/perm…" 5 days ago Up 5 days 0.0.0.0:1433->1433/tcp sql4
# ssh pureuser@192.168.111.130
pureuser@192.168.111.130's password:
Last login: Wed May 13 00:14:30 2020 from 192.168.111.198
Wed May 13 00:28:23 2020
Welcome pureuser. This is Purity Version 5.3.4 on FlashArray Pure-SYD-m20-1
http://www.purestorage.com/
pureuser@Pure-SYD-m20-1>
pureuser@Pure-SYD-m20-1>
pureuser@Pure-SYD-m20-1> purevol copy --overwrite docker-docker-sqldata1.demo2 docker-docker-snapvol1
Name Size Source Created Serial
docker-docker-snapvol1 19531250K docker-docker-sqldata1 2020-05-13 00:15:16 AEST A21265762DB64ECE00057012
- Now let's start the container, and confirm our target volume has been refreshed from the source.
# docker start sqlsnap sqlsnap [root@docker ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 1dfce919128d mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04 "/opt/mssql/bin/perm…" 50 minutes ago Up 5 seconds 0.0.0.0:1430->1433/tcp sqlsnap f97a85f18e3f mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04 "/opt/mssql/bin/perm…" 5 days ago Up 5 days 0.0.0.0:1433->1433/tcp sql4
[root@docker ~]# /opt/mssql-tools/bin/sqlcmd -S localhost,1430 -U sa -P P@ssw0rd 1> select name from sys.databases 2> go name -------------------------------------------------------------------------------------------------------------------------------- master tempdb model msdb sqldemo chrisb blogdemo blogdemo2 (8 rows affected) 1>
Comments