Steve Jones

12 July 2018

2 Comments

• SQL Provision
• Development and Testing with Clones

Getting Started with Database Development Using SQL Provision

Developers, when working on databases rather than the application code, often find they have less freedom to experiment than they are used to. It’s not necessarily easy to set up a database for testing, especially if the process isn’t automated. They’ll need to dig around in source control, build the database at the correct version, and then fill it with a set of anonymized test data. Once it’s done, it tends to be a ‘protected’ asset, to avoid repeating the whole process. However, during the early ‘proof-of-concept’ phases of development, this is precisely what it required. Developers need to feel free to experiment, and if they break something, simply tear down the database, rebuild it in its previous consistent state, and try again, just as they would with any application code.

If the database is large, and they need to test with realistic data. in realistic volumes, then to recreate it on demand, in seconds rather than minutes, requires a different approach. SQL Provision, which integrates SQL Clone and Data Masker for SQL Server, handles this use case very well. SQL Clone builds one full copy of the source database, called an image. From that image, developers can create multiple clones very quickly. You can think of a clone as a virtualized database copy that functions just like a normal database.

Migrating an existing development database to a clone can seem a bit cumbersome, the first time you try it, but it’s a one-time operation, per database, and this article will demonstrate how easy it is to automate the process, using a little PowerShell. I’ll use only SQL Clone in this article, but if you need to perform database masking, before deploying clones to your development machines, then my previous article demonstrates the basics of how to do that.

The Process

Having installed the SQL Clone server, I started to migrate all my development work over to using clones. What I really like is that a can reset a clone, in seconds, after any destructive data changes, and then immediately continue with my work. Getting the databases moved is a multi-stage process that varies only by the name of the database and the SQL Server instance. This makes it a great candidate for a scripted solution.

This is the high-level process that I follow:

1. Create an image from the existing database
2. Drop the existing database from the instance
3. Create a clone with the same name as the database

I’ll demonstrate how this process works for one database on my system, called PrestigeCars. This is a sample database used in a series of T-SQL articles from Adam Aspin on SQLServerCentral.com, and in his book, Query Answers with SQL Server. Of course, you can just as easily use any other database.

As defined by the above process, we need to perform these specific steps:

1. Create the PrestigeCarsBase image from the PrestigeCars database and store this in c:\SQLCloneImages
2. Drop the PrestigeCars database
3. Create a PrestigeCars clone on the instance using the PrestigeCarsBase image.

That’s it. Once this is complete, I can easily take an existing database and migrate it to a cloned copy in a rapid and consistent fashion. Let’s look at the details.

Using PowerShell

Listing 1 shows the complete code for a PowerShell function that I wrote, called Create-DevDatabase, which implements the previous three steps, to migrate a development database to a clone. As you will see, it’s designed for easy reuse; it accepts a set of parameters, the values for which you simply amend, as required, each time. This section will walk through the code, but if you want to just see the process in action, skip ahead to the Migrating Databases to Clones section.

Listing 1 – The CloneFunctions.ps1 script, which creates the Create-DevDatabase function

This function accepts several important parameters that are needed for creating an image and a cloned database, as follows:

• $CloneServer – This is the URL path to your SQL Clone Management Server
• $DatabaseName – The name of the database to be migrated. In my example, I’ll use PrestigeCars here.
• $MachineName – The host name of the machine that is running SQL Server and contains the database that is being migrated.
• $InstanceName – The name of the named instance, if one is being used.
• $ImageLocation – This is the URL of the location where the SQL Clone images are being stored. This is usually a file share, but if a developer wants to work with a clone for a short period, during proof of concept, then it could just be a local folder.

Since I always run this script on my local system, I’ve set some default values for a few of the parameters. Just change these to suit the needs of your own system. Having set up all the parameters, the script implements each step in our process, allowing for any errors that might occur. In the event of an error, I set a variable, $Proceed, to false, which prevents the rest of the script from running. This is useful in debugging, but it also allows me to find an error and perhaps finish the process manually.

After connecting to the SQL Clone Management Server, I set the image name to be the database name plus the string “Base” string. You can change this, or even add a date to the name of your image if that’s appropriate for you. Since most of the SQL Clone cmdlets don’t take strings as parameters, I need to create some objects, such as $ImagePath and $SqlServerInstance and then use them to look for an existing image of the same name. If one exists, the process stops. If this happens, I’ll choose a new image name and re-run the script. Otherwise, I create the image from the database. It’s at this stage that I could extend the process, modifying the data to mask any sensitive or personal information, as shown in my previous article, before creating the image. In this case, my image is just an exact copy of this database.

Having created the image, the script calls the Invoke-Sqlcmd PowerShell cmdlet, to run the DROP DATABASE command. There are other ways to run this command in PowerShell, but Invoke-Sqlcmd and should be fine here, since this script is intended for sysadmins (i.e. developers who are sysadmins on their own machine). If not, then you’ll need to have CONTROL permission on the database, or the ALTER ANY DATABASE permission, or be a member of the db_owner role for the database, for this to work.

The last part of the script then creates a new clone, using the original name of the database. Since we’ve just dropped the original database, there should be no naming collisions.

That’s it for the code, so how does this work in practice?

Migrating Databases to Clones

I’ll use this PowerShell function to migrate my PrestigeCars database to a clone. Figure 1 shows the database in SSMS Object Explorer, on my development SQL Server instance. It has all the data I need for testing already in it.

Figure 1 – The existing database in SQL Server

Once we’ve migrated this database to a clone, it will still look just the same, and work just the same, which is what we want. The way to tell whether you’re working on a clone or a real database is to check its extended properties, which as currently blank for this database, as you can see in Figure 2. The SQL Clone process will add an extended property to the cloned database, as we’ll see later.

Figure 2 – Extended Properties for PrestigeCars database

Now let’s start the migration process. I saved my PowerShell function, from Listing 1, in a file called CloneFunctions.ps1. Then, from a PowerShell command line shell, I can run this to load my function into the current session:

. .\CloneFunctions.ps1

Note that this is dot sourcing, and I use a period followed by a space and then a period, backslash, and my filename. This doesn’t return any result, but simply imports the functions into the session, as shown in Figure 3.

Figure 3 – Dot Sourcing my function

Alternatively, in the PowerShell ISE, simply hit F5 to run the function. Now, I can call the Create-DevDatabase function, and it recognizes it and interactively fills in the function and parameter names. Here, we just need to supply the database name as a parameter, since all the others have default values, set as appropriate for my development system.

Figure 4 – Executing the function

As soon as I execute the function, I can swtich to the SQL Clone Server dashboard to see the image being created.

Figure 5 – The image being created

Once this is complete, the PowerShell command line displays the messages that the function writes to the console, at each step. Figure 6 shows the output on my system, for the entire PoSh session.

Figure 6 – PowerShell command line messages

Back in SQL Clone Server, we can now see that the image exists, along with one clone. You can see the default values from the script have been used when building the image and cloned database.

Figure 7 – The image and clone details

On the SQL Server instance, we can see that SSMS Object Explorer looks the same, but if we look at the properties of the database (shown in Figure 8), we can see that there is a new extended property called IsSQLCloneDatabase has been set, during the process of creating the clone from the image.

Figure 8 – PrestigeCars database properties

The database has been migrated to a clone, and I can now do any work I need to with this database, and rebuild a clone if I need to reset to the base image. I can alter the schema, change the data, and more. If I want to reset my database and undo the changes, I can drop the clone and rebuild a new one from the existing image, in seconds, as shown in this article.

Conclusion

This article has shown how a developer, who is a sysadmin on their own SQL Server instance, can migrate their existing development databases to clones, using SQL Provision. Using a simple PowerShell function, we have seen a repeatable process that is quick and consistent.

I’ve been using it to migrate my standalone databases to cloned copies. The great advantage of working with a clone is that it takes seconds to reset the data and schema back to how it was at the time I created the image. If I’ve changed any objects in the meantime, while working on the clone, I save those changes using SQL Source Control or SQL Change Automation, drop and recreate the clone, and then re-apply my development changes.

I hope you’ll start to follow my example and migrate all your databases to clones, which you can tear down and recreate on demand. The ability to treat our development databases more like all our other code is another step on the journey to implementing database DevOps.

Find out more about SQL Provision and grab your 14-day fully functional free trial.