During the development cycle, you'll run regular tests of the current database build, from source control, using a tool such as SQL Change Automation. The following architecture diagram illustrates one possible way to incorporate SQL Clone into a development workflow. It assumes that the team will want to regularly verify the latest build from source control (from the individual object-level DDL scripts, or a single file build script):

Any new development cycle starts by creating a development copy of the current release, meaning the version of the database that was delivered to production, via staging. The team, collectively, need access to one copy of this database, for reference during development.

The schema of the development copy of the current release should be identical to production, except that it might omit additional features such as replication or encryption that aren't part of the database development. Production will often have different users and access control settings that will also not be in the development copy. The current release might have a full copy of the production data, if it's permissible. Otherwise, any sensitive production data will need to me masked or obfuscated, such as by using Data Masker for SQL Server, or the team can use generated data, such as by using SQL Data Generator). In each case the development data must have a volume and distribution matching as closely as possible to what's in production. For testing, it is important that the data is 'controlled', and any masking or data generation is consistent between releases, so that any integration tests use a standard and consistent data set.

The following sections outline how the process might work, and the articles referenced in the tip box below will help you flesh out the technical details:

1: Create the current build

You capture the source code of the current release into GitHub, or whichever version control system you use, and can begin development.

Build latest version from source (1a)

Throughout development, the team will then run regular database builds from source control, as the team commit tested changes to main, or merge changes into main from branches.

These builds can be automated with a tool such as SQL Change Automation

Import data (1b)

The build finishes by importing a standard set of development data. This data might be bulk imported, such as from bcp files, created initially from the current release. These files would be stored outside of the source control system, and must be maintained, in response to any development changes that would affect existing data.

Version the current build (not shown in figure)

This build-and-fill process produces the current build, i.e. the current version of the database as represented by the main branch of source control.

Each developer will need access to the current build, as well as to their own development version of the database.

Each new build should be stamped with a new version, using a versioning scheme such as:

<revision>.<release>.<version><-branch>

Each build increments the version number. Each release increments the release number and zeros the version number.

2: Create a SQL Clone image of the current build

Image created from build (2)

Once the build of a new version succeeds, you can use it to create an image. which is an image is an exact point-in-time copy of the source database, in this case the current build (see How Images Work) . The image is stored on a network share that the team can access and that has a fast, stable connection to all development and test machines that require clones.

It can be created through the UI or, more likely, you'll automate the process using PowerShell. For example, up might have an automated, overnight process that creates and tests the latest build, and if all the tests pass creates a new SQL Clone image. This process may also switch any existing clones over to the new image, and remove the old one.

3. Request and deploy clones

Having configured SQL Clone's permission system for self-service (see SQL Clone for self-service database development), SQL Clone administrators can then leave developers to create, reset and destroy clones on-demand, and as required for each development task.

Request clones (3a)

You can create and reset (or remove) clones through the User Interface, or automate it using PowerShell scripts. You can also create SQL Scripts to run on individual clones, as part of the creation process, to bring the current build to your branch version, or otherwise ensure that the database fits the need (See clone modifications). The PowerShell automation scripts can include built-in safeguards to ensure, for example, that images are not removed in any clones are still needed, or that any uncommitted changes to a clone are saved before removing or replacing them with a new version (e.g. reflecting the latest build).

Dispense clones (3b)

From the image of the current build, SQL Clone can deploy many clones, exact replicas each sharing the same data, stored in the central image, very quickly. When a new build of the current version succeeds, the team can create an image from it and then produce clones on which to run the automated integration tests (See: Using clones to improve database testing). If these tests pass, they can then produce the fresh development copies of each clone, for developers and testers.

4: Commit changes to source control

Commit or merge into main (4)

As each developer makes and tests changes on their local development clone, each working in a dedicated branch if required (see Using clones for branch-based development and testing). They commit each change to their local repo, and then merge them into 'main', in the team's central repo. The build process (1) then delivers the next version of the current build, which includes all commits to main.