James Murtagh

12 August 2019

• SQL Clone
• Development and Testing with Clones
• Compliant Database Provisioning

Self-service and Delegation with SQL Clone 4 Teams

SQL Clone 4 introduces a new access control feature called Teams, allowing granular control over the SQL Server instances, images and clones to which each group of users has access.

I’ll explain how Teams makes it easier to manage the safe distribution of database copies throughout the organization, to the various teams that need them for development, testing, training or analysis. By ensuring that no SQL Clone team has access to data or to SQL Server instances to which they do not have permission, or that are simply irrelevant to them, SQL Clone delivers a “canteen-style” of database provisioning, where each user simply ‘pulls’, on demand, a clone from one the images available to their Team, and then deploys it to one of the team’s designated SQL Server instances.

Teams: self-service database copies, with governance

SQL Clone makes ‘self-service’ database provisioning possible because it dramatically reduces the storage overhead of creating each local copy, and the time it takes to do it. SQL Clone also allows administrators to ‘mask’ the data using Data Masker for SQL Server, if required, before distribution to non-production servers.

SQL Clone requires that we create only one complete copy of that database, an image, located on a network share. Each ‘clone’, created from an image, has virtualized access to all the data as it existed at the time of image creation, and only stores any subsequent data changes locally. A clone, typically starting at 40MB in size, can be created in seconds and, despite being a fraction of the size of the original, looks, behaves and works just like a normal database. This light footprint frees up a team of developers to create multiple local database copies for experimentation, testing, bug fixing and so on.

Previous versions of SQL Clone allowed ‘self-service’ clone delivery, primarily via the “Clone-only” user role, which allowed users to create and deploy clones from a set of images, typically provided by a DBA. However, it wasn’t possible to control the images to which each user had access, or the servers and instances to which the clones could be deployed. Different groups of users, working on different projects but using the same SQL Clone installation could each see and access each other’s images, clones, and instances.

The new Teams feature introduces a new level of control. Each SQL Clone user, a member of one of the pre-set user roles, 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. An administrator can create a set of images, tailored for the project requirements and security clearance of each team. The data in each image can be masked, unmasked or synthetic, according to the regulatory requirements governing distribution of that data (GDPR, CCPA, HIPAA and so on). If required, they can also control the locations to which each team can deploy clones.

In these ways, Clone allows administrators to introduce the necessary data governance, and then delegate resources to the various teams, allowing members of each team to ‘self-serve’ only those resources that are relevant to them. Users cannot access, or even see, those images, clones and instances that aren’t assigned to the team. This helps to boost productivity by keeping teams focused on their project.

Each team member has access to only the necessary images, and can then create, refresh and destroy clones from these images, as often as required, without fear of disrupting the work of others.

For developers this canteen-style database delivery system has obvious appeal, and many advantages. Suddenly, it is safe and easy for each developer to work on their own local database copy. When working on a branch, a developer can create a clone from the latest image, apply to it the changes for the branch, and then run series of tests to make sure everything works. Developers can also, for example, spin up 10 copies of a database for a test cell, and run tests on each one, in parallel.

Working with Teams

The teams feature starts disabled, and to enable it, go to the Permissions page within Settings, and go to the Teams tab. When you first enable teams, only administrators will have access to resources, until you place your users into teams.

Teams can correspond to the actual teams in your organization, or to projects, to different responsibilities, or just to sets of resources, and you can provide access to either specific users or Active Directory groups.

Once you’ve created a team, you assign users to it, and add the images and instances to which they need access. Clones are only visible if the team has access to both the parent image and the host instance.

By adding instances to teams, administrators can limit users’ access to instances. For example, you can let a member of the Admin team create an image from a production server, and then allow the Dev team to access that image, but not create clones on the production server! You can also use this feature to ensure that different teams use different instances, to avoid collisions and resource contention, and possibly to restrict clone deployment to servers where the image-clone connection speed and bandwidth is known to be good.

Many customers use SQL Clone to allow each developer to have their own local development database. In this case, you can create one team for all developers, for shared images and instances, and then each developer will have his or her own “team” to deploy clones to their own instance.

From the SQL Clone Users tab, we can view and manage users and assign them to one or more teams. Now the users can work with resources that were assigned to them and provision images and clones for themselves and their teams.

Conclusion

As DevOps practices spread through organizations, more teams request more copies of the data. SQL Clone’s Teams feature further enables safe delegation and self-service database provisioning, by allowing granular control over the data that each team can access, and the locations to which copies of that data can be deployed. With the continued roll-out of global GDPR-like legislation, Teams also helps to ensure that users are accessing data in line with their level of access permissions, since the data in each Team’s images can be masked, or even synthesized, in a way that supports data privacy policy

This means that organizations can use the data that drives their business for development, reporting and training purposes, while also ensuring that data governance standards are maintained.

If you already have SQL Clone, you can try this out today by upgrading to the latest version. Alternatively you can download a trial.