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.
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.
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.
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.
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 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; |
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).
Open transactions will not be applied, the clone will have the uncommitted state.
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.
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.
We expect this to be problematic, but we haven’t tested this yet.
There is a PowerShell interface, documented here: PowerShell cmdlet reference
This is already possible using the PowerShell cmdlets, but not in the web UI.
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.
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.
No, the 'instant' technology SQL Clone uses is 64-bit only.
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.
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.
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] ).
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.
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.
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.
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:
Entitlements are not required in the following cases (providing they don’t also fall into the above), although clones may be used:
No, SQL Clone is unrelated to HyperBac and is built on Microsoft technology built into Windows and SQL Server.
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
Images can only be made from live databases if they are online and fully accessible.
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.
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.
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.