• Image and Clone SQL Server version compatibility
• Search box syntax

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.

What backup formats and versions are supported?

SQL Clone can create images from Redgate SQL Backup .sqb files and SQL Server's native .bak format. In order to create an image from backup, you need to use a temporary instance of equal or greater SQL Server version as that used to create the backup. As this process will upgrade the database to the version of this instance, potentially restricting which instances can accept clones of the image, you should select an instance of an appropriate version. In order to use Redgate SQL Backup, you must install the SQL Backup Agent on the temporary instance.

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.

For a custom PowerShell-scripted solution, see: Safely Deleting Clones and Images during Database Development and Testing.

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:

CREATE TABLE #TempCloneDatabases (DatabaseName VARCHAR(MAX));

INSERT INTO #TempCloneDatabases EXEC sp_MSForEachDB 'Use [?];
SELECT db.name AS DatabaseName
FROM sys.extended_properties AS prop WITH (NOLOCK)
JOIN sys.databases AS db WITH (NOLOCK) 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).

How do I work around the 1.8 TB limitation?

Maximum database size for images and clones is limited by the agent's operating system:

• 1.8 TB for Windows 7 and Windows Server 2008 R2 
• 64 TB for later operating systems

To work around the 1.8 TB limitation, either:

• Decrease the total combined size of the database files (i.e. mdf, ldf, any ndfs)
  1. Remove any obsolete data
  2. Use DBCC SHRINKDATABASE with the TRUNCATEONLY option to remove empty space allocated in the files
• Backup the database to a shared location, create the image from that backup using an agent with a later operating system

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.

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.

Is there a process to move a configuration database (SQLClone_Config) to another instance?

Yes, you can backup and restore the database to another SQL instance as you would usually, and then re-run SQL Clone Server Setup from the Windows start menu. This utility is installed with clone and allows you to specify a new clone database location.

How can I remove a deleted SQL Server instance from SQL Clone web UI?

If you delete an existing SQL Server instance and still have an active SQL Clone agent on that machine, then the instance name will still appear in the SQL Clone UI dropdowns (e.g. when creating an image). For example, assuming that MYSQL2016 has been deleted:

If you try to access the instance, then you will get a The agent on [MACHINE_NAME] needs additional permissions to access the server error.

This is because SQL Clone still has a reference to the instance in the SourceLocations table of its configuration database (SQLClone_Config).

Even if you reinstall the agent, the configuration will persist by default. To effectively get rid of the deleted SQL Server instance, you have two choices:

1. Manually update the record in the SQLClone_Config database where SQL Clone is hosted by running (replacing with the right deleted instance id):

   1. UPDATE [SQLClone_Config].[dbo].[SourceLocations] SET DeletedOn = getdate() WHERE Id = <InstanceId> -- Replace <InstanceId> as needed

2. Alternatively, you could stop/uninstall the agent and then run the Remove-SqlCloneMachine Powershell cmdlet to unregister it from the database and then restart/reinstall it.

Can I move SQL Clone Server from one machine to another?

Yes, although the process is more involved, since the agents must be made aware that the server has moved by editing their configuration files. First, follow the steps above for moving the configuration database to another instance, moving it to the new machine and setting up Clone Server to use it. Next, open certlm on the new machine to view your machine's locally-installed certificates. In Personal, find the new SQL Clone Server Agent Certificate, and copy its hexadecimal thumbprint (deleting any spaces).

On each of your agent machines, find the agent's settings.json file (usually in %ProgramData%\Red Gate\SQL Clone Agent on newer OSes, or CommonAppData on older). (Take a backup of this file in case of any issue.) Within this file, update the management service's thumbprint to the new thumbprint, and the URL to the new URL (taking care to use HTTPS, and the agent port of 14146). Restart your agent via Services, and it should connect to the new Clone Server.

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

Does SQL Clone support password protection when working with SQL Backups?

Yes, SQL Clone allows users to enter a password for encrypted backups both from the PowerShell cmdlets and in the web UI.

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 move an image without dropping the clones?

No, unfortunately you can't at present as the clone files are physically tied to their source image location. If you want to move the image file, you'll need to drop the clones beforehand and modify the SQL Clone configuration database directly:

1. Drop all the clones associated with that image.
2. Stop all the SQL Clone agents.
3. Move the image file to the new desired location.
4. Add a new entry to [SQLClone_Config].[dbo].[ImageLocations] with the new image location path.
5. Modify the entry for the moved image in [SQLClone_Config].[dbo].[Images] to point to the new ImageLocationId from the previous step.
6. If there is no other image file pointing to the old location, you can remove it from [SQLClone_Config].[dbo].[ImageLocations].
7. Bring all the SQL Clone agents back online.

Can I clone a database protected by TDE (Transparent 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 (from a live database) and its follow-up (from a backup).

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).

Does SQL Clone support data masking?

Yes. SQL Clone 2.5.0 and later can run Data Masker for SQL Server masking sets during image creation. See here for more details.

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.

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) (e.g. Azure SQL Managed Instance) because we cannot control the storage tier and have no access to portable backups. 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.

See SQL Clone on your Laptop for a PowerShell script to disconnect your laptop without risking error 21, if you're working with SQL Clone and need to go offline. The same script will then bring the clone database back online smoothly, once you're reconnected.

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 (such as the 'deny write access to fixed drives not protected by BitLocker' policy), SQL Clone will be unable to work, as the virtual storage it uses cannot be protected by BitLocker.

Can an antivirus affect SQL Clone performance?

If an antivirus is scanning an image before allowing it to be read that could slow down the image creation process. Having antivirus exclusions on the images shared folder or the SQL Clone agent process could help improving performance.

I'm creating an image from a live database instead of a backup and there's still pending operations to run like re-indexing which increase the clone's size. How can I minimize the size increase?

You can use a modification script during the image creation with WAITFOR DELAY to specify some waiting time for those operations to complete and then let SQL Clone continue with the image creation process.