Daniel Calbimonte
Database Cloning Winddocks Web app

Windocks; Database cloning for SQL Server dev/test on “live” production data

March 19, 2019 by

Introduction

Today, with DevOps, the need for database cloning is urgent. The development and testing process is shorter and it is required to work and test the data faster.

How can we test a Database that is already on production and online? The answer is database cloning.

What is database cloning?

Database cloning allows to clone a database. This is a writable replica that can be used for testing, development, and is delivered in seconds, with minimal storage consumption.

In this article, we will talk about Windocks. This software provides the ability to clone SQL Server databases (also named volume snapshots). With Windocks you can create clones of your database with minimal disk consumption, and work with the database clones with SQL Server containers or conventional instances.

What is Windocks?

Windocks is a port of of Dockers’s source to Windows used to create SQL Server containers. Docker is mainly Linux based whereas Windocks is specialized in the Windows market.

Windocks SQL Server images are based on a local installed instance, which is cloned to deliver a SQL Server container, with support for all editions of SQL Server version starting at SQL Server 2008.

Windocks is also a database cloning solution, and supports the creation of clonable database images from storage arrays or SQL Server backups and Windows Virtual Hard Drives (VHDs). Database clones are writable, and are delivered in seconds even for large databases. Windocks supports the delivery of clones to SQL Server containers, as well as SQL Server instances, and even SQL Server Linux containers.

What is the difference between an image and a container?

Basically, images define what is delivered (based on a Docker file). Images can deliver a SQL Server container, or a SQL container with database cloning, or a database clone that is for use with a SQL Server instance. A SQL Server container is a SQL Server instance.

Windocks containers for SQL Server

The Windocks containers are handled like SQL Server instances. SQL Server licenses allow for unlimited “instances” created by cloning a licensed installed instance, so Windocks SQL Server containers are “free” under existing MS licenses.

Is Windocks part of Docker?

No, Windocks is an independent company that ported the Docker source technology for SQL Server Database Cloning.

Are the Containers replacing the Virtual Machines? Is Docker replacing the Virtual Machines?

Yes and no. Each VM runs a full operating system, which requires CPU and RAM. Containers make it practical to run up to 20 or more containers on a single host. Windocks reports that clients typically use a 4 core machine with 32 GB of RAM to support 20 or more containers. So, most users reduce the use of VMs for dev/test by up to 5-10:1.

For testing and development, it is recommended for containers to run on a VM.

Getting Started

Let’s start with it. We will use the free Community edition of Windocks for database cloning that you can download here:

The scenario is the following. We have a live database in production, but we need to test that live database in different environments. We need to clone the production database for the QA team and for the developers. We have 20 QAs and 30 developers and each one needs a clone of the production database. To have 50 VMs would require many resources and time, so that is why we need a Database cloning software. We need to clone the database easily, automatically, and efficiently.

Installation of the software

The software installation is simple. You just need to check the following:

  • Windocks supports Windows 8.1 and 10 Professional or Enterprise editions, Windows Server 2012 R2, and Windows Server 2016
  • A locally installed SQL Server instance, from SQL Server 2008 or later
  • Use a local machine administrator login
  • Windocks includes a web app (Chrome or Firefox), open ports 3000,3001, and 5985, 5986

The Windocks folder

Once installed check your installation a Windocks folder is created like this:

containers folder

I will not explain all the folders, but just some of them. First of all, the docs folder that contains several pdf and txt documents about installation, licenses, configuration for the database cloning software.

If you want the full list of documents you can go to the website documentation.

The images and containers are also listed in folders, you can see the list of images and containers in the folders.

In the images you will see the YAML file, which is a configuration file inside the metadata folder and it is named manifest:

docker manifest file

This file contains names, versions, description and other configuration information. The # is used for comments.

Another interesting folder is the samples. It contains several useful examples to handle PowerShell, Database cloning, deploy in Git and more:

samples folder

If we open a docker file in the samples folder, we will note that it is easy to read and understand:

dockerfile example

The following example is adding the customerdata.mdf (the sample database in Windocks) database to the mssql-2017 image and coping the cleanseData.sql and executing it.

If you have errors, the log folder contains the logs with useful information that can provide you deeper details or it can help to the support team.

Getting started with the command line

OK, less bla, bla and more action…. Now we will show some useful examples to run the command line. In DevOps it is essential to have a fast automated process for database cloning. This is possible in Windocks using the command line which allows to automate all the process.

Open the command line and run the following command:

Docker ?

You will notice that docker started in Linux because you will receive the following message:

docker: ‘?’ is not a docker command. See ‘docker ––help’.

If you are familiar with Linux, all the configuration files, command lines will be familiar for you. If you are not familiar with Linux, do not worry, this tutorial will help you on that.

Let’s try docker ––help:

Docker ––help

This will show the list of commands available.

If, for example, you want to get information about the docker search, write the following command:

Docker search ––help

This will help you with the synaxis to search images.

To list the images, use the following command:

>docker images

To create a stopped container, you can use the following commands:

>docker create full1

The following command list all the containers of database cloning:

>docker ps

To start the container:

docker start <ContainerID>

The following example, will create a container based on the image mssql-2017 and clone the database customers from the backup and copy and run the script named cleanseData.sql.

FROM mssql-2017
SETUPCLONING FULL customers C:\windocks\dbbackups\customerdatafull.bak
COPY cleanseData.sql .
RUN cleanseData.sql

The Windocks web app

The web app that comes with the installer helps to deliver containers using the UI and without the need of the command line. It is simple to use and intuitive for database cloning.

To open it, just go to your browser and type the local IP: HTTP://127.0.0.1

In the textbox type the local IP 127.0.0.1 and press the Get button. The Community Edition does not include user authorization, as shown below.

Connect to local server

Contact tech support for your login and password and press login:

Windocks user name and password

The button deliver will deliver containers based on the images:

Database Cloning Winddocks Web app

You can set the name, port and password using the web app.

Once that your database is cloned, you can use it for testing or development. As you can see, the usage is very simple.

Conclusions

In this article, we learned how to clone a database using Windocks. Windocks is a modern technology based on docker that supports both SQL Server containers and database cloning. It is possible to clone SQL Server databases, SSRS and target to any SQL version in Windows or Linux (the supported SQL Server Linux versions).

To automate the process, you can use batch files or even PowerShell. We show the command line, so writing your own automated scripts will be a straightforward process.

We also learned that the software is simple to use, fast and can help to deliver databases for testing and development efficiently.

A Terabyte database cloning can be done in seconds with this new technology and reduces the need of several VMs. It can also work in Azure, Docker containers in Linux with clusters and more.

Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

Continuous delivery (CD), DevOps, Testing

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

248 Views