SQL Clone for self-service database development
Published 16 September 2020
A self-service canteen system for development databases
SQL Clone delivers a "self-service canteen" system for databases. Administrators can configure SQL Clone in a way that protects sensitive data, enforces data governance (i.e. control over who can have access to which databases), and ensures the clones used for development work are stable and reliable. Developers can then decide when they want a clone. Teams can deploy them on-demand, apply any migration scripts required to bring the cloned database to the state required, and then remove or reset them as often as required.
Configuring SQL Clone for self-service databases
SQL Clone's permissions system allows administrative users to control the instances from which developers can create clones and images, and the instances on which they can create, refresh, or delete clones. To do this, they assign add SQL Clone users to one of the pre-set clone user roles (administrator, standard, or clone-only), and then assign these users to a SQL Clone team. Each team member will have access only to those SQL Clone images and SQL Server instances that are also assigned to that team. A user can access a clone if they have access to the source image and the SQL Server instance hosting the image, through SQL Clone.
The following diagram shows one possible way to configure a self-service system, which allows for developers to work on an isolated SQL Server development machine, if required:
Jill is an Admin user and can perform all SQL Clone operations on any image, or clone or instance, and so does not need to be assigned to a team.
The two developers, Mo and Ben, are members of a Common team, which is just a collection of the individual images to which they need access. Each developer is also a solo-member of their own "team", which simply gives each of them access to their personal development instance (meaning no-one else can accidentally add, edit or remove a clone running on someone else's instance). The BI-Dev Windows group has access to a different image location and can deploy clones to their own instance.
Members of one team cannot access, or even see, those images, clones and SQL Server instances that are used by the other teams to which they aren't assigned membership.
With SQL Clone, 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).
For data originating from production systems, sensitive data will need to be removed, or masked, first. The admin can add Data Masker masking sets to image templates, which runs as part of the image creation process, so that the image of the production database is always masked before any clones are created and deployed (See Image Modifications). Each clone has a connection to this image, which can be hosted on a secure server, and only any subsequent changes, made during development and testing, will be stored locally. Typically, an administrator in the team might be responsible for creating and refreshing sanitized images of these productions databases and will then assign permissions to other team members to access them, and create clones. This whole process of creating and refreshing sanitized images, and dispensing clones, can be fully automated using SQL Clone's PowerShell API.
Stability and reliability of clones
The clone administrators must ensure, primarily, that the clone installation architecture is such that there is always a fast and reliable connection between the shared file server, hosting the images to which a team needs access, and the SQL Server instances hosting their clones. See How SQL Clone Works for more details.
If necessary, they can restrict the servers to which each team can deploy clones to those with strong, fast connections to the parent images.
Self-service clones for development and testing
Once SQL Clone is configured correctly, developers can deploy clones on-demand, and each member of a team will have access only those clones that are relevant to them. They can apply Clone modifications, manually or automatically as part of clone creation, to ensure that the clone is set up correctly for their work.
For clones created from the latest development build, stocked with test data, developers are free to create images and deploy, reset or remove clones, as often as required, and without fear of disrupting the work of others. If team members need to perform all of these actions, then they will need to be members of the Standard clone user role.
For production databases, they might instead have clone-only access to the masked images, with permissions, via team membership, to deploy clones only to secure SQL Server instances.