Phil Factor

30 August 2019

• SQL Clone
• Development and Testing with Clones

DevOps Self-Service for Databases with SQL Clone

SQL Clone allows developers to create, use and delete SQL Server databases, as clones, on their development database instances, and without the usual overheads that tend to slow this task down. It is the difference between waiting to be served lunch and being able to use a self-service canteen.

The administrators of the system keep control over matters that they are required to, such as data security and access control. They can determine the roles assigned to each user, and which servers each person can install or delete clones. They can specify what data must be masked, redacted or removed. They can specify the databases that can be accessed. The developers can ‘pull’ databases and delete them, as required, but without needing an administrative login to the instances to which they are assigned.

The joys of database self-service

There is nothing new about database self-service. The joy of using Docker SQL Server containers is that you can just select the container with the database you want and it just, sort of, appears on your workstation or server. It is great to have a SQL Server container that you can spin up quickly, for occasional use. You can use it for your tests and then tear it down. Sure, it requires plenty of scripting and file-copying but once you’ve tasted the freedom of not having to pester an admin to install a database for you, you never want to give it up. DevOps makes sense.

With databases that are helping to run an organisation, it’s inevitable that the key concern is security. In our enthusiasm for Docker, it is easy to forget this problem and no amount of hand-waving will disguise this awkward fact. Docker is improving. It has a UI for Docker Enterprise Edition that allows team-based and user-based access control for the Docker images, but Database access is difficult to automate in a container because the VM-hosted SQL Server instance in a running container is inevitably outside the Windows Domain. It requires a Group Managed Service Account (gMSA).

Of course, in addition to this, each container needs its own copy of the database so we need to make as many database copies as there are developers working on their own containerized database. A big advantage of using SQL Clone as a database canteen is that the database is only stored once, as an image on a network share, and each user only stores any differences caused by modifying the clone directly, on their local database instance. This means that the process is very quick because it doesn’t involve large data files being heaved around. As the databases get larger, the savings become very significant.

How SQL Clone self-service works

Although SQL Clone could provide a database canteen system in previous releases, the web-based GUI had originally (prior to v4) been geared more for the administrator rather than the clone user. Administrators could, if they felt lucky, allow users to create and deploy clones from a set of images, but they could not control the images to which each user had access, or the servers and instances to which the clones could be deployed. In a nutshell, different groups of users, all working on different projects but all using the same SQL Clone installation, could each see and access each other’s images, clones, and instances. This would be considered a risk by a GDPR Data protection risk assessment (DPIA).

SQL Clone needed to provide not only an access control system for images but also a relatively simple way to protect any sensitive data within the database via data masking and data generation. That done, each user sees only the information, images and clones that are relevant.

Basically, all the ‘consumer’ needs is a SQL Server instance with an installed SQL Clone Agent of the correct version. Unlike a container, which requires its own instance to run in a VM on the server, the clone instance has a very small footprint. It doesn’t require much storage space or a huge amount of memory, but just a fast network connection.

A clone appears just like any other local database on the instance, which is no surprise because that is what it is. However, it uses shared network storage to access the database ‘image’. Only the subsequent changes made to the database are stored on the local server, hosting the instance, but SQL Server is unaware that there is anything ‘different’ about a clone.

To make their choice of database, the team member needs only access the SQL Clone Server just like any other local website. Their ID is either their network Windows user or, if there is no domain, a local User ID for the Clone Server.

The ‘canteen’ list of available images and SQL Server instances is selected to be appropriate to the user. To achieve this, Clone users are mapped to Windows authentication. SQL Clone adopts the Docker model of roles, users and teams for the permission system, but with the addition that the users can be mapped directly to Windows logins, as well as to UserID/Password credentials.

The Clone permission system

The Clone permission system allows organisations to comply with current legislation on privacy when database images contain real personal data or sensitive business data. It also means that users are presented with only the relevant and appropriate information and nothing else.

SQL Clone identifies users via Windows logins and groups and uses this information to assign them to Clone user roles, which control who can do what with SQL Clone. In SQL Clone v4 and later each SQL Clone user can now be assigned to a team and will have access only to those resources (images and instances) that are also assigned to that team. All this can be done via the web-based user-interface or PowerShell.

SQL Clone users

The Domain Admin can assign users to Active Directory groups which are then recognised by SQL Clone as principals that can be mapped to SQL Clone Users. Users in each group all share the one role and team memberships. This is an important point because it means that domain admins can assign new staff to SQL Clone users via groups and remove staff who have left.

If there is no suitable domain, the users can be mapped to local Windows users and groups on the Clone Server that can then use these credentials to log into the Web server. This allows SQL Clone to be available to users who aren’t in a Windows Domain.

SQL Clone Roles

These are pre-set.

• The Admin role gives full control over SQL Clone, with the ability to add, change or remove roles, update licenses, and create images or clones.
• The Standard role allows users to create images and then create clones from those images.
• The Clone only role limits users to creating clones from images.

SQL Clone Teams

Once SQL Clone users are in a team, they have access to all the resources (images and instances) that are assigned to that team. Unless the users are in the team, they don’t see the images and instances. This allows anyone within the local network to use SQL Clone without accidentally knocking heads or causing security issues.

When users are added to a team, they will get access to the images and instances associated with that team. Users can belong to more than one team in order to increase access. Teams are more like collections, since a collection of users, a collection of instances, and a collection of images, or a mix of all three, can all be ‘teams’.

Arranging Clone Users, Roles and Teams

Here is a hypothetical diagram of how one might arrange Clone permissions.

Joe is an Admin and can do anything, whereas the other uses are either standard users, or clone-only users. Each team defines a set of instances, images and users and the Developers team, for example, has access to all five images, and all six instances.

However, if each developer has their own development machine, along with access to any shared instances, then every developer will have access to each other’s development machine. To avoid this, it may be easier to use teams in the same way as one might use a collection. In the following example, the In Common team, of which all users are a member, is just a collection of images. The DevMachineX teams simply give each developer access to their personal development instance, and the Test cell team is just a collection of instances, and only the testers are members of this team, and so on.

Using Clone self-service during development and testing

SQL Clone allows users ‘controlled’ access to a defined set of images, from which they can create, refresh or delete clones on a defined set of instances. This means that they can automate the use of SQL Clone within their own PowerShell scripts, at the appropriate moment in development.

Although the ad-hoc SQL Clone work of creating or deleting clones is easiest done via the GUI, there are a lot of routine tasks that can be automated, such as spinning up and tearing down databases as part of test runs. This has an obvious advantage with unit tests and integration tests. With SQL Clone, you can create a clone from the image representing the current database, apply to the clone the changes representing the current branch you which you are working, and immediately run all the necessary tests to ensure that the branch works. This reduces the chance that the task of merging the branch into root might introduce an error that subsequently breaks the build and integration.

I’ve written about the many possible uses for clones during testing and development, in A Database Developer’s Guide to SQL Clone. If everything is scripted, much more can be done, more quickly and with less effort and distraction, and I’ve also written a few example scripts for SQL Clone that would be suitable for the development team, such as

• Deploying and Reverting Clones for Database Development and Testing
• Scripting Custom SQL Server Clones for Database Development and Testing
• Safely Deleting Clones and Images during Database Development and Testing
• Reporting on the Status of Clones During Database Development
• SQL Clone on your Laptop
• SQL Clone for Unit Testing Databases
• Database Continuous Integration with SQL Clone and SQL Change Automation
• How to create and refresh development and test databases automatically, using SQL Clone and SQL Toolbelt

Conclusions

There are few things more frustrating for a developer than to be held up waiting for the latest build of a database, or for access to a test database. The temptation always remains to skimp on the work of testing integration, scalability or performance because these all make more demands on resources, and so on other team members who are responsible for admin work. I’ve yet to find a database developer who enjoys frustration, and if self-service can be made compliant with the many rules and constraints that govern the work of an IT department nowadays, then productivity and quality are bound to rise.