SQL Clone is designed to make it easy to provision copies of a database for development and test environments in seconds. Often, these ‘clones’ are copies of production databases, which contain sensitive data that needs to be protected from unauthorized access.

Transparent Data Encryption (TDE) is one technique that many organizations have implemented in order to protect their sensitive data. It encrypts the data at rest, both in the database files and backups. Since SQL Clone copies data from either a live database or backup, does it work with TDE?

Let’s find out with a clone from a live database.

Enable TDE for a database

The process for setting up TDE on a database is fairly simple. There is a good article and description of TDE at SQLServerCentral, but we’ll show the basic steps here.

I have a simple database called TDE_Primer. This database is unencrypted right now, so let’s protect it with TDE.

I’ll start by creating a Database Master Key (DMK) in the master database that will protect my certificate. This step can be skipped if a DMK already exists.

Next, I’ll create a certificate, which will protect the Database Encryption Key (DEK) in my TDE_Primer database. I also perform a backup of this certificate, encrypting the private key before inclusion in the backup, as I’ll need this certificate for other instances on which I might potentially restore my TDE-encrypted database.

Now I change to the TDE_Primer database, create the DEK, and then encrypt the database.

Once this completes, I check the encryption status. Note that I’ve included tempdb in my query, because tempdb is encrypted on any instance where TDE is enabled in any database.

Figure 1

Now let’s make a SQL Clone.

Creating a data image from an encrypted database

The first step to creating a clone of a database is to create a data image. When I choose to create an image, I get the choice of using a live SQL Server database or a backup. In this case, let’s use a live SQL Server database.

SQL Clone TDE 2
Figure 2

Next, I choose the SQL Server where my database is located, and then select the database. You can see below that I’ve chosen my TDE_Primer database.

SQL Clone TDE 3
Figure 3

I choose a location where the image will be stored. This needs to be a location that all instances that will create clones can access. For this test, I’ll use a local drive, but a network share can be used as well.

SQL Clone TDE 4
Figure 4

Lastly I give the image a name (TDE_Primer), double check the settings, and click Create Image. I’ll get a progress bar while the image is built from the database.

Once that’s done, I can see my image on the SQL Clone page.

SQL Clone TDE 5
Figure 5

So far, so good. The image isn’t mounted on a SQL Server yet, so I can’t check anything right now. Let’s create a clone. Firstly, I’ll create one on the same SQL Server instance, which should work as the certificate exists, and then I’ll create a clone on another instance.

Create a clone on the same SQL Server instance

To create a clone, I click CLONE on the page in Figure 5. I then get a dialog box asking me to choose a SQL Server instance and give the new, cloned database a name.

SQL Clone TDE 6
Figure 6

I confirm my choices by clicking NEXT, and then clicking CLONE in the Confirm and create clone dialog box.

SQL Clone TDE 7
Figure 7

The process takes seconds and I’ll see a clone listed below my image.

SQL Clone TDE 8
Figure 8

Note the clone is only 48MB in size while the image is 106MB. That 48MB size doesn’t change much, unless you make changes directly to the clone database, even if my image size were 1TB.

This worked fine, and if I check SSMS, I can see that my database clone exists and is marked as being encrypted.

SQL Clone TDE 9
Figure 9

Create a clone on a separate instance

One of the great advantages of SQL Clone is that I can have copies of databases on other instances, allowing each developer or tester to have their own, independent copy of the database without all the storage for the database. In this case, each clone would use only 48MB for each copy unless you make changes to that clone database.

Let’s create another clone, this time on a separate instance. I’ll click CLONE again and select a different instance.

SQL Clone TDE 10
Figure 10

I’ll give this clone the same name, though this doesn’t matter. The name just needs to be unique amongst all databases on this instance. Once I click CLONE on the following dialog box, the process begins, but I get a new result. In the dashboard, I see this:

SQL Clone TDE 11
Figure 11

There is an error at the top of the screen. If you’ve practiced restoring your TDE databases on various servers, I suspect you’ve run into this error at some point. The data image contains the Database Encryption Key (DEK), but on this new instance there is no certificate to protect the DEK and so SQL Server cannot decrypt the DEK, and thus, cannot read data in the database. Let’s fix that.

All you need to do is copy the database and certificate backups created at the beginning of this walkthrough over to the new instance, in preparation for the restore. You’ll also need a need a DMK on this instance, though it can be different from the DMK on the first instance. Note, in the lower right corner of the window of the image below, that I’m now connected to the new instance.

SQL Clone TDE 12
Figure 12

Now let’s create the new clone again. I’ll clear the error and go through the process with the same choices. This time the clone is created successfully.

SQL Clone TDE 13
Figure 13

If I check SSMS, I can see the clone, and it is encrypted. This shows the is_encrypted flag for tempdb is not set, though the encryption state is set to 3 (encrypted). I suspect this is some artifact of TDE not being explicitly enabled.

SQL Clone TDE 14
Figure 14

This shows that if you’ve properly deployed the certificates protecting your DEK, you can easily use SQL Clone with your TDE-encrypted databases.

Next Steps

In my next post, I look at how SQL Clone works with backups of TDE-encrypted databases and the additional steps you need to take. It’s well worth reading, because then you’ll have a complete picture of how SQL Clone works with TDE.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Blog

DevOps – a DBA’s perspective

Earlier this year, we ran a DevOps 101 webinar in conjunction with Redgate to a predominately DBA audience. In this blog post our own DevOpsGuys DBA, Paul Ibison, gives his views on what DevOps means ...

Also in Database DevOps & DLM

SQL Clone and backups protected with TDE - part 2

My last article demonstrated that you can use SQL Clone to make a copy of a live database, even when that database is protected with Transparent Data Encryption (TDE).

However, not everyone will want...

Also in Redgate products

Getting started with SQL Doc and PowerShell

SQL Doc keeps everyone in the development team informed about a database. It provides more information than what it is source control, including server settings, and it formats and color-codes the inf...

Also about Database DevOps

Continuous Delivery - how do application and database development really compare?

Our recent State of Database DevOps survey showed that DevOps practices like continuous delivery are still far more commonplace in application development than in database development. So why is that...

Also about SQL Clone

Database provisioning: getting started with SQL Clone

For large databases, it's a hard and time consuming task to provide database developers with development or test copies of that database that resemble the production database, in terms of the size, di...