Over the time, the code grew up and we had to support different versions of SQL Server from 2008 to SQL Server 2017. In the same time, the number of scenarios we have to manage and to test regarding the new features shipped with service packs and cumulative updates as well\u00a0increased drastically over the time. So we decided to move on docker containers from different obvious reasons:<\/p>\n
- \n
- Containers are easy to provision for unit testing<\/li>\n
- Lower footprint on our local machine<\/li>\n
- We can share easily SQL images between members of our \u201cdevelopment\u201d team in a private registry<\/li>\n
- We will able to integer containers in a potential \u201cfuture\u201d CI pipeline<\/li>\n<\/ul>\n
In our context, each developer has a local docker engine installed\u00a0on a Windows 10 Pro laptop\u00a0but for medium\/large companies, internal rules may prevent installing\u00a0such product\u00a0on each individual developer laptop. Therefore, they prefer likely to provide a dedicated environment for developers that meet the internal rules and to keep the focus on flexibility.<\/p>\n
In such shared infrastructure a main concern will be probably\u00a0disk space issues because of the potential testing database(s) size and the number of containers provisioned at the same time. Let\u2019s say that several developers would like to provision\u00a0one SQL Server instance each with a testing database attached on it and that\u00a0comes from the production through a sanitized database pipeline. The total size of this database is enough big to warn about a potential disk space issue because each instanced container will include its own image size (roughly 10GB with SQL Server containers on Windows) + the size of the testing database – let’s say\u00a0more than 50\u00a0GBs and\u00a0likely more\u00a0for usual cases.<\/p>\n
If you\u2019re in one of the aforementioned scenarios, Windocks<\/a> may be a good alternative scenario.<\/p>\n
During this month I had the\u00a0opportunity to test the product in the context of our internal project and after some experimentation stuff I admit I was agreeably surprised by some built-in capabilities as:<\/p>\n
- \n
- Windows authentication mode supported<\/li>\n
- SQL Server database cloning capabilities<\/li>\n
- The ability to use both Docker CLI and Web UI (even if I\u2019m in favor of Docker CLI)<\/li>\n
- The ability to refresh a cloned database image from a\u00a0differential backup<\/li>\n<\/ul>\n
Before to dig further into the above features let\u2019s just take few seconds to see the Windocks architecture design<\/p>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-1-windocks-architecture-e1527183485304.jpg\")
<\/p>\nWe may notice some differences here. With a traditional Docker architecture, containers run on shared operating system kernel (either Windows or Linux) whereas Windocks is a strictly application construct-oriented solution and requires installing a SQL Server instance on the host as base instance for SQL Server containers. This is a fundamental change that provides some interesting advantages:<\/p>\n
- \n
- We don\u2019t have to rebuild the containers to get OS & Framework patches through base image updates<\/a> because containers rely only on the SQL Server base instance<\/li>\n
- We don\u2019t break compatibility with some Windows storage functionalities as VSS and SQL Writer as well if you rely on them with third-party backup tools<\/li>\n
- We benefit from the underlying security and configuration of the SQL Server based instance meaning we may use Windows authentication for instance<\/li>\n<\/ul>\n
Let\u2019s talk now about one interesting feature shipped with Windocks that is cloned databases. Here a picture (from Windocks) that shows a high-level workflow of using SQL Server containers with cloned databases.<\/p>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-2-windocks-cloned-db-architecture.jpg\")
<\/p>\nThe starting point is a full database backup or a snapshot and Windocks will generate the corresponding VHD parent image of the backup media. Then each SQL Server container generated will use a writable clone of this parent image reducing drastically the disk footprint of each container (at least when the SQL Server container is generated). This is a common workflow that customers may implement for unit testing or for\u00a0CI\/CD pipeline to refresh development environments. The production database may be big in size and in this context the interest of using cloned databases becomes obvious. Another way provided would be to rely on mounted snapshot-based volumes from the storage provider but at this stage I didn\u2019t test it. Maybe a next time!<\/p>\n
To give a better picture of what it is possible to do with cloned databases, let me show you one way to implement it in the context of our DMK maintenance tool project. The development workflow of the development project is as follows:<\/p>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-3-windocks-dev-workflow-e1527183795133.jpg\")
<\/p>\nWe use SSDT and GitLab to manage our DMK maintenance tool sources and we perform unit testing by provisioning one or several SQL Server containers regarding the target version we want to validate at this moment. Furthermore we developed testing scenarios based on tSQLt <\/em>framework we run after provisioning the SQL Server containers. With such architecture, we initially have to maintain images of different SQL Server versions and each time we want to create a container we have to attach one copy of the customized AdventureWorks_dbi<\/em> database. Let\u2019s say we want to work with 4 containers (SQL Server 2016) at time. We must to ensure we have sufficient disk space for 4 copies of this database (5 x 4 = 20GB) + space required for each container (10GB x 4 = 40GB) = 60GB. The dbi_tools<\/em> database is intended to stay very small (512MB up to 1GB) comparing to other testing components, this is way I didn’t include it to the math.<\/p>\n
Let\u2019s now apply Windocks with the above context and the new architecture becomes as follows:<\/p>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-4-windocks-dev-workflow-e1527183932421.jpg\")
<\/p>\nIn this second scenario, we may include both the AdventureWorks_dbi<\/em> and dbi_tools<\/em> databases (including tSQLt framework<\/em> objects) in a VHD parent image. For the demo, I used a SQL Server 2016 instance installed on the host that will be controlled by Windocks service during the container generation.<\/p>\n
As I said previously we may rely on the configuration and the security of the base instance. Thus, I configured my security based on both Windows Authentication (dbi-services\\clustadmin<\/em> domain account) for SQL Server sysadmins and SQL authentication for development purpose (windock user).<\/p>\n
In terms of configuration, tSQLt framework requires enabling CLR on the server level, so I changed the configuration directly on the SQL based instance to allow all my containers to inherit this configuration change.<\/p>\n
SELECT \n\t[name],\n\tprincipal_id,\n\t[type_desc]\nFROM \n\tsys.server_principals \nWHERE \n\t[type] IN ('U', 'S')\n\tAND [name] NOT LIKE '##MS_%##'\n\tAND [name] NOT LIKE 'NT SERVICE%'<\/pre>\n\u00a0<\/a><\/p>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-41-windocks-base-instance-security.jpg\")
<\/p>\nHere the\u00a0content of my docker file.<\/p>\n
FROM mssql-2016\nSETUPCLONING FULL AdventureWorks_dbi \\\\Win2012sql16ls\\windock\\AdventureWorks_dbi_2008.bak\nSETUPCLONING FULL dbi_tools \\\\Win2012sql16ls\\windock\\dbi_tools.bak<\/pre>\n
You may notice some new commands here:<\/p>\n
- \n
- FROM mssql-2016<\/strong> indicates we will use the SQL Server 2016 instance as base image.<\/li>\n
- SETUPCLONING FULL<\/strong> indicates to generate the VHD parent image that we will include both the AdventureWorks_dbi<\/em> and dbi_tools databases in a remote network path<\/li>\n<\/ul>\n
Go ahead and let\u2019s generate the corresponding SQL Server docker cloned image with the special -t<\/strong> flag<\/p>\n
docker build -t 2016withdmk C:\\DMK\\WithClone\\BasedImage\\<\/pre>\n
\u00a0<\/a><\/p>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-5-windocks-cloned-database-image.jpg\")
<\/p>\nThe process may take some times to generate the VHD parent image depending on the different database sizes and the location (local fast disk, network share bandwidth etc \u2026)<\/p>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-6-windocks-cloned-db-size-e1527184506576.jpg\")
<\/p>\nAs expected, because the VHD parent image is a full byte copy of the data, the size is basically the sum of both AdventureWorks_dbi<\/em> and dbi_tools<\/em> database sizes.<\/p>\n
Compared to a traditional approach, the provisioning of the associated containers will be faster irrespective to database size. Let\u2019s create 2 containers from the image generated previously with the following docker commands:<\/p>\n
PS C:\\DMK\\WithClone> docker run --name sql20161 -d 2016withdmk\nPS C:\\DMK\\WithClone> docker run --name sql20162 -d 2016withdmk<\/pre>\n
Note that by default, Windocks will choose a random port between a configured range in the node.conf (START_PORT<\/strong>\/ PORTS_PER_USER<\/strong>) unless you override the default behavior using the -p<\/strong> parameter.<\/p>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-7-windocks-create-containers.jpg\")
<\/p>\nLet’s get a picture of the existing containers. My 2 containers have been generated correctly from the 2016withdmk base image.<\/p>\n
PS C:\\DMK\\WithClone> docker ps\nCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS\n89344d99758b 2016withdmk \"\" 5 minutes ago Started 1433\/\n4dfd1a198626 2016withdmk \"\" 4 minutes ago Started 1434\/<\/pre>\n
Let\u2019s take another look at the storage side:<\/p>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-8-windocks-cloned-databases-diff-e1527184778690.jpg\")
<\/p>\nThe interesting point here is the two differential disks created during the creation of my 2 previous containers\u00a0are very small size (~70MB per container).<\/p>\n
Let\u2019s try to connect from SSMS to\u00a0the new fresh containers:<\/p>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-9-windocks-ssms-connection.jpg\")
<\/p>\nIt works and did you notice I was connected with my domain account? \ud83d\ude42<\/p>\n
Last topic I wanted to share with you is the Windocks capability to update a base image from differential backups. In a real context, you may need to refresh frequently your environment with recent data for developers and regarding your full backup size it might be advantageous to deal with differential backups.<\/p>\n
Let\u2019s consider this process with my internal project environment. During the development process we already had to scale the database schema of our testing database with the features shipped with new versions, service packs or cumulative update over the time. Let\u2019s say we have to add a new dbo.bigTransactionhistory_cci<\/em> table to test columnstore index scenarios. We first add the concerned table. Then we will perform a differential backup and finally we will update the 2016withDMK base image with it. Obviously in the context of my demo, the database size is likely not big enough to take full advantage of this feature but I trust you to draw a comparison with a more realistic scenario.<\/p>\n
The image I want to update is named 2016withdmk. Note the mssql-xxx images that are in fact images from SQL Server base instances installed on my host server.<\/p>\n
PS C:\\DMK\\WithClone> docker images\nREPOSITORY TAG IMAGE ID CREATED VIRTUAL SIZE\n2016withdmk none b3a249ba-2cf Less than a second ago 0 B\nagitated_heisenberg none bbd0ce26-4bb Less than a second ago 0 B\ndotnet-4.5 none dotnet-4.5 3 years ago 0 B\nwindows none windows 3 years ago 0 B\nmssql-2008r2 none mssql-2008r2 3 years ago 0 B\nmssql-2016 none mssql-2016 3 years ago 0 B\nmssql-2014 none mssql-2014 3 years ago 0 B<\/pre>\n
My new docker file content\u00a0to update the 2016withdmk base image is as follows.<\/p>\n
FROM 2016withdmk\n\nSETUPCLONING DIFF AdventureWorks_dbi \\\\Win2012sql16ls\\windock\\AdventureWorks_dbi_2008.DIFF\nSETUPCLONING DIFF dbi_tools \\\\Win2012sql16ls\\windock\\dbi_tools.DIFF<\/pre>\n
I used the differential backup of the AdventureWorks_dbi<\/em> database with the SETUPCLONNING DIFF command.<\/p>\n
Let\u2019s start updating the 2016withDMK base image with the following docker command (I tagged my new image with 1.2 suffix):<\/p>\n
docker build -t 2016withdmk1.2 C:\\DMK\\WithClone\\DiffImage\\<\/pre>\n
- SETUPCLONING FULL<\/strong> indicates to generate the VHD parent image that we will include both the AdventureWorks_dbi<\/em> and dbi_tools databases in a remote network path<\/li>\n<\/ul>\n
- We don\u2019t have to rebuild the containers to get OS & Framework patches through base image updates<\/a> because containers rely only on the SQL Server base instance<\/li>\n
![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-10-windocks-cloned-database-diff-backup.jpg\")
<\/p>\n![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-11-windocks-cloned-db-size-diff-e1527185121654.jpg\")
<\/p>\n![\"blog](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog-133-12-windocks-ssms-connection-2.jpg\")
<\/p>\n