These pages cover SQL Clone 1, which is not the latest version. Help for other versions is also available.

Skip to end of metadata
Go to start of metadata

Do clones have the same data as the source?

All clones created will start with the same data as when the image was taken from the source - it’s a 2-stage process. A data image is taken from a live SQL Server database or a backup (this takes about the same time as a backup restore). Multiple clones can then be created, each using the image (which can be on a file share) and a local differencing disk.

Can a clone be used like a real database?

Yes, the clone is a real database in every sense - it just has most of the .mdf and .ldf files stored on a network share (i.e where the image is). This means that the network link's stability and latency will affect the database's performance.

How do I check no one in the team is using a clone before I delete it?

This isn't currently supported within the system. The Activity Monitor in SSMS (or running `sp_who2`) can tell you about current usage of a database.

How do I check no one in the team is using an image before I delete it?

Images can only be deleted when they have no active clones created from them. As long as you delete images through SQL Clone, it will not be possible to delete an image until you have deleted all clones first.

Can I identify which databases are clones (so I don't back them up for example)?

When SQL Clone creates a clone database, it adds an Extended Property to the database object named as "IsSQLCloneDatabase" with the value "1". You can take advantage of this mark to find all clone databases under a SQL Server instance, for example with the below query:

Selecting clone databases
CREATE TABLE #TempCloneDatabases (DatabaseName VARCHAR(MAX));

INSERT INTO #TempCloneDatabases EXEC sp_MSForEachDB 'Use [?];
SELECT db.name AS DatabaseName
FROM sys.extended_properties AS prop
JOIN sys.databases AS db ON db.name = DB_NAME()
WHERE prop.[class_desc] = "DATABASE" AND prop.name = "IsSQLCloneDatabase" AND prop.value = 1';

SELECT DISTINCT * FROM #TempCloneDatabases ORDER BY DatabaseName;
DROP TABLE #TempCloneDatabases;

How much disk space do I need to create an image from a backup file (and where)?

Creating an image from a backup file does NOT require the disk space for a restored database to be available on the machine with the SQL Clone agent which is performing the operation.

SQL Clone gets around this by performing the database restore operation (the writing of the bytes) into a virtual mount point to the final destination of the image – usually a file share (in a kind of reverse of the create clone operation).

Will open transactions that are not committed be part of the image file?

Open transactions will not be applied, the clone will have the uncommitted state.

Can images be made from log shipped databases in standby or read-only mode?

The image creation process involves copying .mdf and .ldf files, so it shouldn't pose any problems. Also the image would be in the same state as the database it was created from.

How can I change the default storage location of the clones?

Clone data files are created in the default data location for the SQL Server instance on which they are created. You can change this in SSMS.

Does the cloning process pick up replication topology?

We expect this to be problematic, but we haven’t tested this yet.

Is it possible to use SQL Clone from the command line?

There is a PowerShell interface, documented here: PowerShell cmdlet reference

Is there a plan for SQL Clone to support password protection when working with SQL Backups?

This is already possible using the PowerShell cmdlets, but not in the web UI.

Do all my SQL Server versions have to be the same to use SQL Clone?

An image persists the database version of the instance on which it was created (backups may be upgraded during this process) so providing the SQL Server instance supports that version the image/clone should work. In practice, this means that you can create a clone on an instance the same version or newer than that used to create the image.

Can an image from SQL Server 2016 be cloned onto an older server version?

We don’t have any near-term plans to provide database downgrade functionality within this product.

It should be possible to achieve this in a script combining our SQL Compare command line, BCP and the Powershell scripting in SQL Clone.

Is there a version with a 32bit C++ redistributable available?

No, the 'instant' technology SQL Clone uses is 64-bit only.

Can I create an image from a clone?

Yes, but the operation creates a full-sized image. We've looked at a 'VHD chaining' approach previously (which makes more efficient use of disk space by chaining a differencing disk to an image), but It's currently a little way down our backlog - the more it's requested, the sooner it will happen.

Can I clone a database protected by TDE (Transport Data Encryption)?

Yes, the SQL Server instance used to host the Clone will need the certificate used to encrypt the data (as would the instance used if taking an image from a backup). You could find more information about the topic on this blog post.

Would I expect same execution plans when running procedures on a clone?

Generally, yes – a clone contains all the same data and statistics as the original database. Instance-specific settings may alter the execution plans in some cases (e.g. changing the maximum degree of parallelism[CH1] ).

Does SQL Clone support data masking?

While SQL Clone doesn’t yet have masking functionality built in, you can mask a clone and create a new image from this, or mask a database before imaging using SQL Clone by using a tool like SQL Data Generator or running your own scripts. We are very interested in offering more integrated data masking solutions, and would love to hear more about your requirements.

Since the clones are built from an image, what happens to them if the source image is deleted?

Clones will not work without the source image since all or most of the data will be stored in the image. You should always delete images through SQL Clone, which will not permit you to delete images which have active clones.

Why is there a 2TB limit?

Version 1 of SQL Clone exclusively uses Microsoft’s Virtual Hard Disk (VHD) format, which has a limit of 2TB when using differencing disks. Version 2 of SQL Clone can also make use of the VHDX format, which supports larger databases (up to 64TB), but that format only works on Windows 8/Windows Server 2012 and above.

What's a user entitlement?

A SQL Clone Entitlement is required by anyone creating clones using the SQL Clone Server, as well as anyone connecting to cloned databases to develop software.

For example, an entitlement would be required by anyone doing the following:

  • Database development
  • Using a cloned database as part of application development, instead of mocking a database
  • Provisioning other people with cloned databases

Entitlements are not required in the following cases (providing they don’t also fall into the above), although clones may be used:

  • Reporting
  • Contributing changes to an automated process which use database clones
  • UAT for testing
  • Manual testing of software

Is this based off of what was once HyperBac?

No, SQL Clone is unrelated to HyperBac and is built on Microsoft technology built into Windows and SQL Server.

Is it feasible to back up the clones on each instance?

Clones work like normal databases, so you can back them up using SQL Server or SQL Backup if you wish. However, the backups will be the full size of the database, not just the clone’s data files. We recommend the use of clones for development and testing scenarios, not for production, so in most cases regular backups of clones should not be required, and important changes saved using source control instead.

What’s the performance of clones like?

It depends on your circumstances; the speed of your network and disks. We think for most dev and test tasks it’s acceptable and users of the technology have found it to be perfectly workable. We would not recommend using clones for realistic load testing, however.

Does SQL Clone work on Azure?

SQL Clone does not currently work with Azure SQL Database (PaaS) because we cannot control the storage tier. However, SQL Clone works fine with Azure virtual machines (IaaS).

Can clones participate in availability groups?

 A clone could act as a primary AG replica, but secondary replicas will use the full amount of disk space. Clones cannot be secondary AG replicas.

Does SQL Clone work with clustered instances?

You cannot currently directly create an image from a clustered instance, but you can do so from a backup from a cluster. You cannot create clones on clustered instances.

Could an image be made from a non-recovered database?

No, you cannot create an image from a database if it is not online. You can create an image from a backup if you have a backup of the database.

Do changes always have to be stored locally?

At the moment, the changes in clones are all stored on the same machine as the SQL Server instance with the clone database. In most cases clone data files are much smaller than the original database, so we have not found finding space for the clone data to be a problem.

Can you use SQL Clone remotely?

Because the data is split between the image and the clone files, machines with clones must maintain a connection to the image file and the SQL Clone Server in order for clones to work. If you are able to connect to the network remotely (VPN) then clones should work, but performance may be affected. If you are unable to access the network, clones will not function.

You can control the SQL Clone system from any machine on which you can reach the SQL Clone Server.

Can you update an image live?

An image is a point-in-time copy of a database and cannot be updated. This means that you can delete and recreate a clone database to get back to the original point in time. We intend to explore what options exist in this area in the future.

Can a cloned database be used for performance tuning work, are query plans, statistics etc impacted by working against a clone?

Yes, you can perform performance tuning against a clone, as it will have the same statistics and in most cases the same query plans as the original database. Because there will be some additional latency, absolute performance is likely to be lower, but optimising by comparing relative performance is definitely feasible.

Given that the clone is using virtualization, does that work in an already virtualized environment (e.g. VMWare Server and Player)?

Yes, this works fine. We use virtualized environments including VMWare, VirtualBox and Azure virtual machines during our development and testing, as do many of our users, and have not experienced any problems.

Can an image be made from a log shipping target?

Images can only be made from live databases if they are online and fully accessible.

What’s the difference between SQL Clone and SQL Server snapshot?

Both are point-in-time copies of a database, but they are otherwise quite different.

A snapshot exists on a single machine and is linked to a particular database. This link means that write performance on the original database will be degraded, and reading from snapshots will cause contention on the original database’s files.

SQL Clone images are independent from the original instance and database once they are created, and can be used to create clone databases on any machine on the network. There is no performance impact on the original database.

What’s the difference between SQL Clone and DBCC CLONEDATABASE?

DBCC CLONEDATABASE is a diagnostic feature in SQL Server 2014 Service Pack 2 and SQL Server 2016 SP1. It creates a new database on the same machine which contains the schema and statistics of the source database, but none of the data.

SQL Clone works with SQL Server 2008 and above and creates independent images which contain the entire database, including all its data. These can be used to create full clone databases on any machine on the network.

Does SQL Clone work well with drive encryption software like BitLocker?

Storing images and clones on BitLocker-protected drives should work, but SQL Clone has not been tested in this scenario. However, if you have group policy mandating that all drives have BitLocker, SQL Clone will be unable to work, as the virtual storage it uses cannot be protected by BitLocker.

  • No labels