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

Lets just re-cap on the environment I am using.
RoleFQDNIPOSRAMCPUDocker plugin
Docker Virtual Machinedocker.localdomain192.168.111.198OEL 75G53.8
Note: Latest version of the plugin is 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
  • Let's have a look at the array to confirm the new snapvol1 volume has been created and note its serial number ( 7012 )

  • 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.
Now let's see how we can use Pure Storage Snapshots to refresh the clone container.
  • 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>

Summary

We have seen how by leveraging the Pure Storage Docker Plugin we can create persistent volumes for SQL Server containers environments, we have also seen how using Pure Storage Snapshots we can clone and refresh these volumes. All the above steps can be fully automated using a variety of methods including CLI, Shell, Python, API, Ansible etc.. I have shown you the manual method to get a better understanding of how the technology works.


      Comments

      Popular posts from this blog

      SQL Server container with Pure Storage Docker volumes

      Oracle 19c Database clones using the Pure Storage Docker plugin