HPE Storage Tech Insiders
cancel
Showing results for 
Search instead for 
Did you mean: 

Multi-platform Zero-Copy Clones for Containerized SQL Server 2017

dgonzalez59

Earlier this year, Microsoft released SQL Server 2017 for public preview. This new version of SQL Server broke the traditional Microsoft mold, by including support for Linux OS and containers (Microsoft, Linux, or macOS), while maintaining all of the advanced features of SQL Server 2016 (and including new ones). 

Now that SQL Server is a more platform agnostic database, enterprises can now leverage SQL Server for a broader range of DevOps scenarios. The following is an excellent blog post from Microsoft about the possible use cases for SQL Server 2017 in DevOps: SQL Server 2017 containers for DevOps scenarios | SQL Server Blog 

I recently set up a Docker environment to test SQL Server 2017 for Linux in containers, and wanted to share how easy it was to duplicate data from one of my other SQL Server instances, running on Windows, to a SQL Server 2017 for Linux container, by leveraging our Docker Volume Plugin.

Building the Environment:

I began by setting up the Nimble Linux Toolkit (NLT), that includes the Nimble Docker Volume Plugin. NLT has several prerequisites for installation, and information can be found in the Linux Integration Guide from Infosight: Linux Integration Guide.

After I installed NLT, I made sure to configure the appropriate array information, and verify array connectivity. The Docker Volume Plugin leverages NLT for authentication. Also, make sure to enable the Docker service.

Configuring NLT

# Establish array connectivity

nltadm --group --add --ip-address 10.0.0.1 --username admin --password XXXXXX

nltadm --group --verify --ip-address 10.0.0.1

# Enable the Docker Service

nltadm --start docker

nltadm --enable docker

Next, I installed Docker on a Centos 7.3 host. For detailed instructions on how to install Docker, refer to the following: Get Docker CE for CentOS | Docker Documentation 

Docker-Compose and the latest ntfs-3g and ntfsprogs drivers came next. I installed the NTFS drivers because the database I wanted to clone to the Docker container was running on a Windows SQL Server instance. In order for the Docker host to be able to mount the NTFS file system, I required the appropriate drivers.

Installing Docker-Compose and NTFS Drivers

# Install Docker-Compose

yum istall python

yum install python-pip

pip install docker-compose

# Install latest NTFS packages

yum --enablerepo=extras install epel-release

yum install ntfs-3g -y

yum install ntfsprogs -y

# Load Fuse module

modprobe fuse 

Now that I had my Docker environment ready to go, the final step in my setup was downloading the Microsoft SQL Server 2017 for Linux image.

Download the SQL Server Image

# Use docker pull for download

docker pull microsoft/mssql-server-linux

Creating the SQL Server 2017 on Linux Container and Importing Data:

Getting data (or a subset of data) from a production environment into a development environment is a common first step in many DevOps workflows. In my lab environment, the "production" database is running on a Windows version of SQL Server. Microsoft's recommended way of porting data from Windows to Linux is to do a full backup of the Windows SQL Server database, and then restore that full backup to SQL Server on Linux.

Migrate a SQL Server database from Windows to Linux | Microsoft Docs 

However, I wanted to see if we could use our zero copy clones to duplicate the data for the Linux instance, save the time required for a long running restore, and reduce the amount of space required (imagine two complete copies of a large database). So instead of doing a backup and restore, I leveraged our Docker Volume Plugin to clone and import the production database volumes to the SQL Server 2017 on Linux container.

Below is my Docker-Compose file with the appropriate options:

Docker-Compose.yml
volumes:
  'sql03-tl':
    driver: nimble
    driver_opts:
      importVolAsClone: sql03-tl
      forceImport: "true"
  'sql03-udb':
    driver: nimble
    driver_opts:
      importVolAsClone: sql03-udb
      forceImport: "true"
services:
  db:
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: XXXXXXXX
      MSSQL_PID: Developer
    image: microsoft/mssql-server-linux
    ports:
      - '1401:1433'
    volumes:
      - sql03-udb:/var/opt/mssql/sqlclone/udb
      - sql03-tl:/var/opt/mssql/sqlclone/tl
version: "3"

Now you may be wondering, "How do I get volume information from my Windows environment? I am a Developer, not a Storage Admin." I recently did a series of blog posts that cover how to leverage the new functionality in our Nimble Windows Toolkit for DevOps workflows. I cover how to get the appropriate information by using application metadata. 

NOS4 Use Case: Leveraging NWT4 cmdlets for SQL Server Reporting or Dev/Test Workflows 

NOS4 Use Case: Rapid Deployment of SQL Developer Containers with Nimble Storage 

After creating my Docker-Compose file, all that was left was for me to bring up the container. 

Console Output
[root@dockerhost1 virtdb1_dev41]# docker-compose up -d
Creating network "virtdb1dev41_default" with the default driver
Creating volume "virtdb1dev41_sql03-tl" with nimble driver
Creating volume "virtdb1dev41_sql03-udb" with nimble driver
Creating virtdb1dev41_db_1 ...
Creating virtdb1dev41_db_1 ... done
[root@dockerhost1 virtdb1_dev41]#

Seconds! That is all it took to bring up a new instance of SQL Server, complete with cloned copy of my production database volumes. Our clones do not take up any space, only new writes are recorded, so I saved ~500GB of space that would have otherwise been required if I had restored the data to the SQL Server container.

I quickly connected to the instance with SSMS, and attached the database as "virtdb1_dev42." To make sure I could read and write to the cloned database, I also executed a stored procedure. Success!

SSMS

More Information:

For more information on our Docker Solutions or our Docker Volume Plugin for Linux, please check out some of our other blog posts:

Make It About Apps, Not Infrastructure 

Tech Preview of Nimble Linux Toolkit 2.0: Docker plug-in 

For more information on our SQL Solutions, refer to the following blog posts:

NOS4 Use Case: Leveraging NWT4 cmdlets for SQL Server Reporting or Dev/Test Workflows 

NOS4 Use Case: Rapid Deployment of SQL Developer Containers with Nimble Storage

Also, please feel free to reach out to your Account Teams if you would like to see any of our solutions in action!

About the Author

dgonzalez59

Events
See posts for
dates/locations
HPE at 2018 Technology Events
Learn about the technology events where Hewlett Packard Enterprise will have a presence in 2018.
Read more
See posts for dates/locations
Reimagine 2018
Join us at one of the Reimagine 2018 stops and see how we Simplify Hybrid IT, innovate at the Intelligent Edge and bring it all together with HPE Poin...
Read more
View all