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:
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:
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:
If we open a docker file in the samples folder, we will note that it is easy to read and understand:
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.
Contact tech support for your login and password and press login:
The button deliver will deliver containers based on the images:
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.
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 writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023