1. Install Windocks
- Runs on Windows Server 2012 R2, 2016, 2019, 2022 on-prem or cloud
- Containers and database clones for all editions (Express, Developer, Standard, Enterprise) of SQL Server 2008, 2008R2, 2012, 2014, 2016, 2017, 2019, 2022, PostgreSQL 9.6, 10.13, 11.8, and 12.3 and MySQL 5.6, 5.7, and 8.0
- First log into the Windows Server with a user account that is part of the BUILTIN\Administrators group
- Install a local SQL Server instance on the Windows server before installing Windocks
- Run the Windocks installer,be sure that your Windows user account is part of BUILTIN\Administrators before installing Windocks
- Create a file named key.txt in the C:\Windocks directory and put your license key in it (The license key is in your download email). Be careful to NOT name the file key.txt.txt
- Reboot the machine
- From a command prompt run docker images to verify
2. Gather the source database backups and / or dumps
Put the backup files or dumps in a directory on the Windocks server or on a network share. 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$. Windocks service runs as LOCALSYSTEM, do not change that.
If you will be using transaction log backups to incrementally update the image, then put those in a separate directory with the proper permissions for the Windocks machine account.
If you are working with a storage system (SAN), to do the cloning, you do not need the backups or dumps. The SAN will provide the clones.
3. Prepare SQL scripts
Run SQL scripts for masking sensitive data, release (upgrade and rollback), migration, etc. Run scripts either at image build time or at container create time. If the scripts are in a git repo, then running them at container create time applies the latest scripts to the container database clone. By default, scripts in the dockerfile are applied at image build time. To apply them at container create time, put the line ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1 in the dockerfile and the line RUN somescript.sql after this line. Scripts can be stored and copied into the container in one of the following ways:
- Store scripts in a git repo, run them at container create time or at image build time. If you run the scripts at container create time, then the container has the latest scripts applied. Add these lines to the dockerfile:
ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1
# Lines below the ENV line are run at container create time
# If you want to get the scripts and run them at image build time then comment the ENV line
# Clone the git repo into the container in a directory called scripts
RUN "C:\Program Files\Git\cmd\git.exe" clone https://github.com/WinDocks/git-db-scripts-runtime.git scripts
# Run the script
RUN scripts\addColumnAtCreate.sql
More information here - Store scripts on the Windocks machine or a location accessible to the Windocks machine. Add these lines to the dockerfile:
COPY myscript.sql .
ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1
# Lines below this are run at container create time
# If you want to run the script at image build time then comment the ENV line
# Run the script
RUN myscript.sql
Apply masking scripts at image build time for all situations except if you plan to update the image incrementally with transaction log backup files. In the exceptional case, the image must be in the same state at production to receive transaction log backups.
4. Create the dockerfile
Make sure you have Windocks installed and you have the samples in windocks\samples. Then based on your type of database source, choose the right method below:
- Source: Full backup of SQL Server and transaction log backups for incremental refreshes. (Windocks Enterprise only) First open windocks\node.conf and comment the line FULL_BACKUPS_ONLY="1". Save the node.conf file and restart the Windocks service. Then, start with the sample windocks\samples\testIncrementalRefreshWithTransactionLogBackups\dockerfile. The database backups are windocks\dbbackups\cars.bak and windocks\dbbackups\cars1.trn. If you want to change the location of where the VHDX will be stored for this image (this will use storage equal to database file size), open the dockerfile and uncomment the line # ENV IMAGE_VHDX_DIRECTORY=path\to\vhds\for\this\image, create that directory. There is also a global setting for VHDX path in node.conf. After the image is built with windocks\dbbackups\cars.bak ( Step 6 below), and you have delivered containers ( Step 7 below), you can incrementally update the image with the transaction log backup windocks\dbbackups\cars1.trn ( Step 8 below). Then deliver containers from the updated image and see in SQL Management Studio that there is an additionl column in the table for the containers that are created after you applied the transaction log backup.
- Source: Full backup of SQL Server only - See the sample windocks\samples\cloneFromFullBackup
- Source: Bacpac file from SQL Azure - See the sample windocks\samples\cloneFromBacpac
- Source: Full and differential backups of SQL Server - See the sample windocks\samples\cloneFromFullBackup and windocks\samples\cloneFromDifferentialBackup\dockerfile. First build an image with the full backup using the web application (Build menu) or with docker build -t yourimagename path\to\docker\file\for\full\backup. Then with the differential backup dockerfile run the command docker build -t anotherimagename path\to\docker\file\for\diff\backup. Note that the FROM line in the diff backup dockerfile must be the name of the image you built with the full backup. Also ensure that your backup was created by a verion of SQL Server that is compatible with the FROM SQL Server in the dockerfile. So, if you have FROM mssql-2017 in the dockerfile, then the backup should be from SQL Server 2017 or earlier
- Source: PostgreSQL dumps - See the sample windocks\samples\postgres\dockerfile
- Source: MySQL dumps - See the sample windocks\samples\mysql\dockerfile
- Source: Oracle backup - See the sample windocks\samples\oracleLinux\dockerfile
- Source: Oracle data files - See the sample windocks\samples\oracleLinux\dockerfile
- Source: Proprietary Storage system (SAN) - See the samples windocks\samples\testCohesityVmwareToWindocksContainer\dockerfile for Cohesity, windocks\samples\testPureStorageToWindocksContainer\dockerfile for Pure Storage. windocks\samples\testActifioToWindocksContainer\dockerfile for Actifio. Contact support@windocks.com for others
- SSIS containers - See the sample windocks\samples\ssis\dockerfile
- SSRS containers - See the sample windocks\samples\ssrs\dockerfile
- SSRS containers running against engine in SQL Azure - See the sample windocks\samples\ssrsWithSqlAzure\dockerfile
- Blue green cloud migration - See the sample windocks\samples\blueGreenCloudMigration\dockerfile. Windocks delivers ongoing current production databases in containers to do migration testing in the cloud through the migration period. Windocks also updates the future production instance with the same image that is used to deliver the containers for migration testing. So, when you switch over, you can be confident that the production instance will work flawlessly
- Blue green releases, see windocks\samples\blueGreenReleasesAndProductionTest\dockerfile
- Running Sql scripts from a Git repo, see windocks\samples\\testGitCloneDbScriptsRepoSelectRunScriptAtRuntime\dockerfile. This sample shows how to run the scripts at container create time so that containers are delivered with the latest scripts from the git repo applied
5. Configure settings
All global configurations are in windocks\config\node.conf and documented in that file. Each time you change node.conf, you must restart the Windocks service. Some of the settings in the file include the following:
- Setting for incremental updates to SQL Server images with transaction log backups. You must change the setting FULL_BACKUPS_ONLY since the default is full backups only
- Setting for which directory to store containers and VHDXes in. By default, Windocks stores the VHDXes in the same location as the backups or dumps to minimize network copying. This means that the Windocks machine Local System Account (MACHINENAME$) needs write access to the directory where your source backups or dumps are located. To change the VHDX location, you may set the variable in node.conf - VHD_PARENT_DIRECTORY. To change where the containers are stored, change the variable CONTAINER_BASE_DIR.
- Timeout for your SQL scripts
- If you are running Windocks enterprise, you have SQL Server service containers, SSRS containers and containers that run all the SQL Server services (SSRS, SSAS, SSIS, Agent). You may set the accounts that will run the service in node.conf
- If you are applying incremental updates to SQL Server images with transaction log backups
- If you are running Windocks enterprise, you have SQL Server Integration services and to use those you must call the REST API to specify which SSIS container is receiving requests to the host. Since SSIS connection strings do not allow a port number, the SSIS container must be specified with the REST API before an application connects to SSIS
- If you install SQL Server after Windocks and want to use that SQL Server to create containers, then you have to set the instance name in node.conf to the instance you installed
- Verbose logging of SQL scripts
- Setting for return value of the password to access the SQL Server, PostgreSQL or MySQL container - clear text, encrypted or not at all
- Settings to optimize storage for VHDXes
- 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.
6. Build the image from the dockerfile
There are three ways to build the image
- Command line on the Windocks machine: docker build -t yourimagename path\to\directory\containing\dockerfile
- Web application: Open the file windocks\ui\index.html or http://WindocksServerIP in Chrome or firefox, login with your credentials (provided in your download email) and go to Build. Select Build and click Browse. Select all the files in the directory that contains the dockerfile. Enter your image name and click Build
- REST API: Use the build API from the REST API reference.
7. Create containers and/or clones from the image
There are three ways to create containers and / or clones - command line on the Windocks machine, web application or REST API (see below for details). You may view logs in the web application by clicking Containers and clones on the top menu and Log next to your container / clone.
- From the command line on the Windocks machine docker create yourimagename and then docker start containerIdFromCreate . Alternatively combine by using docker run -d yourimagename. Specify the port you want the container to run on with -p port. Specify the sa password with -e SA_PASSWORD=yourpassword. The command would look like docker create -p yourportnumber -e SA_PASSWORD=yoursapassword --name myuniquecontainername yourimagename or docker run -p yourportnumber -e SA_PASSWORD=yoursapassword --name myuniquecontainername -d yourimagename
- Web application: Open the file windocks\ui\index.html or http://WindocksServerIP in Chrome or firefox, login with your credentials (provided in your download email) and go to Images. Find the image and fill in the fields for port, sa password and click Deliver
- REST API: Use the create API from the REST API reference.
For SQL Server, the standard and community Windocks editions deliver SQL Server containers with the SQL engine running as a command line. To run linked servers, Distributed transactions, the enterprise Windocks edition is required. The enterprise editions include three additional base images
- mssqlsvc-20xx - delivers SQL Server and Agent running as services (All versions and editions of SQL Server)
- mssqlall-20xx - delivers SQL Server, SSIS, SSRS, SSAS and Agent running as services
8. Automate backups, container/clone delivery and incremental image updates
There are a few processes to automate
- Getting the database backup sources and dumping them into appropriate directories. You may connect the Windocks machine to storage that already has the full, differential or transaction log backups to avoid copying. Or if that is not possible, you may use SQL Jobs or Task Scheduler to periodically copy backups to the specific directories accessible to Windocks. Make sure that the directories allow the Windocks machine permissions to read the files. Setting for which directory to store containers and VHDXes in. By default, Windocks creates and stores the VHDXes in the same location as the backups or dumps to minimize network copying. This means that the Windocks machine Local System Account (MACHINENAME$) needs write access to the directory where your source backups or dumps are located. To change the VHDX location to something else, you may set the variable in node.conf - VHD_PARENT_DIRECTORY. Or you can do this at an image level as well with the line ENV IMAGE_VHDX_DIRECTORY=path\to\vhds\for\this\image
- Keeping the SQL Server image current with production data - If you have Windocks Enterprise, then use the web application to schedule the periodic update of the image with transaction log backups. In the web application, click Refresh in the top level menu and find your image in the list and use the drop down list to schedule the periodicity and set the directory location where the log backups are located. In the same Refresh view, you may update the image manually by putting in the path to the transaction log backup file or the directory containing the log backups. If you want to use your own automation to periodically update the image with transaction log backups, use the command line docker exec yourimagename path\to\directory\containing\transaction\log\backups
- If you have PostgreSQL or MySQL or if you do not have Windocks Enterprise, then to keep the image current with production data, you may write Powershell, Bash or Windows command automation combined with Windows Task Scheduler to build the image periodically from the dumps / full backups using docker build -t yourimagename path\to\directory\containing\dockerfile. Please contact support@windocks.com for samples
- Delivering the containers/ clones - Use Task Scheduler to deliver the containers with docker run -d yourimagename. Alternatively, your devops pipelines in AzureDevops, Jenkins, Octopus etc can get containers and clones on demand with the same command line or with the REST API here. Please contact support@windocks.com for samples
Troubleshooting
The following logs are useful for troubleshooting:
- Database script logs from image build. In the web application, click on Images, find your image and click on Log
- Database script logs from container / clone create. In the web application, click on Containers and clones, find your container / clone and click on Log
- Windocks system logs in windocks\log\platform.log
- Container specific system logs in windocks\containers\\log\*
- Container specific database logs for SQL Server, SSRS, SSAS, SSIS, PostgreSQL MySQL in the container directory. For example, SQL Server engine logs in windocks\containers\\mssql\bin\MSSQLxx.INSTANCENAME\mssql\log.