TECHNICAL DOCS

Build a SQL Server image for database cloning, apply periodic incremental transaction log backups and deliver containers with the clones

This article describes building an image (from SQL Server full backups and applying SQL Server transaction log backups to keep it current with production and delivering database clones and containers from the image. This is useful when your database sizes are very large (usually more than 1TB). There is a working sample in windocks\samples\incrementalRefreshWithTransactionLogBackups. The steps are to do Gather backups, do global configuration, build the image (one time operation) and then deliver containers with database clones on an ongoing basis on demand.

 

 

Step 1: Gather the source database backups and set up the directory for automatic transaction log backup updates

Put the full backup files for your databases in a directory on the Windocks server or on a network share. A full backup is used only once. If the backups or dumps are on a network share, provide full permissions for the Windocks machine name to read and write from BOTH the network share AND the directory. For example if your Windocks machine name is PC1, then provide permissions to the account PC1$. In Computer management / Network shares, find the network share on which the backup lives and provide permissions to PC1$. Also in File Explorer, provide full permissions on the highest parent directory to PC1$. The Windocks service runs as LOCALSYSTEM, do not change that.

Designate a directory that will hold the transaction log backups on an ongoing basis as they are copied from production. Apply the same permissions as above. You may use the same directory as above for transaction log backups. This directory may be on a network share as well.

 

 

 

Step 2: Global Configuration

  1. Note that after making global configuration changes you must restart the Windocks service
  2. Open the file windocks\config\node.conf and comment the line
    FULL_BACKUPS_ONLY="1"
    Put a # sign in the beginning of that line, so the finished line looks like
    # FULL_BACKUPS_ONLY="1"
    If you make this change, prior images you built will no longer work. But going forward you may build images both for incremental updates as well as Full back up only images. The full backup images moving forward will need a line in the dockerfile as outlined in the sample windocks\samples\cloneFromFullBackup
  3. All database files used by Windocks are stored in virtual disks (VHDX). Typically you would set up a fast storage for the VHDXes. Create a directory (network path is OK as well) to store the VHDXes. Assign permissions to this directory or network path for the Windocks server to read or write. For example if your machine name is PC1, then provide permissions to the account PC1$.
  4. In the file windocks\config\node.conf, locate the line that begins with:
    # VHD_PARENT_DIRECTORY=
    If you want to change the location of where the VHDXes will be stored, then put in the directory you created and uncomment the line, so the line looks like:
    VHD_PARENT_DIRECTORY="\\yournetworkserver\yournetworksharename\subdirectory"
    It can also look like
    VHD_PARENT_DIRECTORY="D:\subdirectory"
  5. When Windocks applies transaction log backups to an image periodically, to maintain performance, it is necessary to prune these images and the sequence of changes. By default, Windocks automatically prunes such images after 100 updates. To change this or other storage optimizations, make any advanced configuration changes you need based on these instructions
  6. Make sure that the SQL Server instance used by Windocks for your desired SQL Server version is correct. The Windocks installer automatically does this, but you may verify it. At the top of the windocks\config\node.conf file, locate the line for your SQL Server version and ensure that the instance name is correct. This instance used by Windocks must be stopped (all the SQL services for this instance such as SSRS, SSAS, SSIS, etc must be stopped).
  7. Restart the Windocks service
  8. Open the firewall ports for the default SQL Server (usually 1433, but double check), the Windocks auth service / UI ports (3000 and 3001) and for a range of container ports 10001 to 11000. These are the ports the containers run on.

 

 

Step 3: Create the dockerfile

  1. Copy the dockerfile located from windocks\samples\incrementalRefreshWithTransactionLogBackups to a fresh directory such as C:\myDir.
  2. If you want to run SQL scripts before delivering database clones, then create those SQL scripts with the extension .sql into C:\myDir. In the example below, there is a SQL script named maskingScript.sql. You may have multiple scripts. You may also have multiple databases. The script is optional. Note that for incrementally updated images, the scripts are run each time for each container and clone delivery. The scripts cannot be run at the one time image build.
  3. dockerfile

# Change the version number from 2017 to your SQL Server version

FROM mssql-2017

SETUPCLONING FULL cars C:\windocks\dbbackups\cars.bak

# SETUPCLONING FULL anotherdb C:\Path\to\Another\Db.bak

COPY maskData.sql .

#COPY filterData.sql .

ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1

# This script is run at container create time
RUN maskData.sql
#RUN filterData.sql

 

 

 

Step 4: Build the image from the above dockerfile

  1. In a command line on the Windocks server:
    docker build -t yourimagename C:\MyDir
  2. Alternatively, use the web application at ServerIp:/index.html or the file windocks\ui\index.html in Chrome or Firefox. Put in your server's IP address (127.0.0.1 if your browser is on the Windocks server) and click on Build in the top menu and click Browse. Go to C:\MyDir and select all the files in the directory (dockerfile, any script files) and then Open in the dialog. Then click Build in the web app.
  3. You may also use the REST API to build the image

 

 

Step 5: Create containers and clones from the image

  1. In a command line on the Windocks server:
    docker run -d yourimagename
  2. This will deliver a container with a writeable database clone of the customers database and the script maskData.sql applied to the database
  3. You may also create containers with clones from the web application at ServerIp:/index.html or the file windocks\ui\index.html in Chrome or Firefox. Put in your IP address (127.0.0.1 if your browser is on the Windocks server), Click on Images, find yourimagename, put in a container name and click Deliver
  4. You may also create containers with the REST API here

 

 

Step 6: Set up the image for incremental updates

  1. You may set up the image for automatic incremental updates or manually apply incremental updates
  2. Use the web application at ServerIp:/index.html or the file windocks\ui\index.html in Chrome or Firefox. Put in your server's IP address (127.0.0.1 if your browser is on the Windocks server) and click on Refresh in the top menu. Find your image in the list.
  3. For manual updates, put in your path to the directory containing the transaction log backups (or put in the path to the log backup file) and click Refresh. If you put in a directory, Windocks will automatically find out the correct sequence of TRN files based on LSN numbers and apply them in the correct order
  4. You may also manually apply from the command line using
    docker exec yourimagename path\to\trn\file\or\directory
    If you select this approach and run the refreshes automatically from your script, then you have to also merge the refreshes with the command below after every number of refreshes (the number of refreshes is defined in windocks\config\node.conf MAX_REFRESHES_BEFORE_PRUNING)

    docker exec put_your_image_name_here pruneupdates

  5. For automatic periodic updates, click on Refresh in the web application, fill in the Polling directory path, Period type (hour, day, etc) and period number (1 hour, 1 day or 0230 for 2.30 am everyday for example) and click Schedule. After that Windocks will automatically poll the directory for fresh transaction log backups and apply them correctly.
  6. Monitor the results of transaction log backups by clicking Monitor in the web application
  7. All of the monitoring data is available using the REST API as well
 

Extend CI/CD to your data layer