Date: Thu, 28 Mar 2024 17:52:45 +0000 (GMT) Message-ID: <1903501767.467.1711648365600@ip-172-31-38-4.eu-west-1.compute.internal> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_466_1499192552.1711648365599" ------=_Part_466_1499192552.1711648365599 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
All clones created will start with the same data as when the image was t= aken from the source - it=E2=80=99s a 2-stage process. A data image is take= n from a live SQL Server database or a backup (this takes about the same ti= me as a backup restore). Multiple clones can then be created, each using th= e 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 t= he .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 da= tabase's performance.
This isn't currently supported within the system. The Activity Monitor i= n SSMS (or running `sp_who2`) can tell you about current usag= e 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 possib= le 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 n= amed as "IsSQLCloneDatabase" with the value "1". You can take adva= ntage of this mark to find all clone databases under a SQL Server instance,= for example with the below query:
CREATE T= ABLE #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 =3D DB_NAME() WHERE prop.[class_desc] =3D "DATABASE" AND prop.name =3D "IsSQLCloneDatabas= e" AND prop.value =3D 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 agen= t 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 destinat= ion of the image =E2=80=93 usually a file share (in a kind of reverse of th= e create clone operation).
Open transactions will not be applied, the clone will have the uncommitt= ed state.
The image creation process involves copying .mdf and .ldf files, so it s= houldn'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 Se= rver instance on which they are created. You can change this in SSMS.
We expect this to be problematic, but we haven=E2=80=99t 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 c= reated (backups may be upgraded during this process) so providing the SQL S= erver instance supports that version the image/clone should work.= In practice, this means that you can create a clone on an instance the sam= e version or newer than that used to create the image.
We don=E2=80=99t 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 Comp= are command line, BCP and the Pow= ershell 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 'VH= D chaining' approach previously (which makes more efficient use of disk spa= ce by chaining a differencing disk to an image), but It's currently a littl= e 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 certif= icate used to encrypt the data (as would the instance used if taking an ima= ge from a backup). You could find more information about the topic on this blog post.
Generally, yes =E2=80=93 a clone contains all the same data and statisti= cs as the original database. Instance-specific settings may alter the execu= tion plans in some cases (e.g. changing the maximum degree of parallelism[CH1] ).
While SQL Clone doesn=E2=80=99t 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 r= unning your own scripts. We are very interested in offering more integrated= data masking solutions, and would love to hear more about your requirement= s.
Clones will not work without the source image since all or most of the d= ata will be stored in the image. You should always delete images through SQ= L Clone, which will not permit you to delete images which have active clone= s.
Version 1 of SQL Clone exclusively uses Microsoft=E2=80=99s 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 support= s larger databases (up to 64TB), but that format only works on Windows 8/Wi= ndows 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 devel= op software.
For example, an entitlement would be required by anyone doing the follow= ing:
Entitlements are not required in the following cases (providing they don= =E2=80=99t also fall into the above), although clones may be used:
No, SQL Clone is unrelated to HyperBac and is built on Microsoft technol= ogy built into Windows and SQL Server.
Clones work like normal databases, so you can back them up using SQL Ser= ver or SQL Backup if you wish. However, the backups will be the full size o= f the database, not just the clone=E2=80=99s data files. We recommend the u= se of clones for development and testing scenarios, not for production, so = in most cases regular backups of clones should not be required, and importa= nt changes saved using source control instead.
It depends on your circumstances; the speed of your network and disks. W= e think for most dev and test tasks it=E2=80=99s acceptable and users of th= e 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 Azu= re 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 repli= cas.
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 o= n 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 dat= a files are much smaller than the original database, so we have not found f= inding space for the clone data to be a problem.
Because the data is split between the image and the clone files, machine= s with clones must maintain a connection to the image file and the SQL Clon= e Server in order for clones to work. If you are able to connect to the net= work remotely (VPN) then clones should work, but performance may be affecte= d. 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 r= each the SQL Clone Server.
An image is a point-in-time copy of a database and cannot be updated. Th= is means that you can delete and recreate a clone database to get back to t= he 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 performa= nce 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, a= s 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 quit=
e different.
A snapshot exists on a single machine and is linked to a particular databa=
se. This link means that write performance on the original database will be=
degraded, and reading from snapshots will cause contention on the original=
database=E2=80=99s files.
SQL Clone images are independent from the original instance and database o=
nce they are created, and can be used to create clone databases on any mach=
ine on the network. There is no performance impact on the original database=
.
DBCC CLONEDATABASE is a diagnostic feature in SQL Server 2014 Service Pa= ck 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 i= mages 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.= p>