What resources are required for involved machines using SQL Clone

The potential machines involved when using SQL Clone are:

  • Machine hosting clone databases: runs SQL Server and Clone Agent, executes queries, and has SQL Server caches. Stores clone differencing data locally. CPU will affect compute-intensive query execution speed, while memory will primarily affect SQL Server caching (which will reduce the need to make repeated queries to the image). Network I/O to file share is important for retrieving image data and is likely to be the limiting factor in most cases. Needs enough local disk to store database changes (depending on usage patterns, but certainly avoid e.g. reindexing).

  • Machine creating images: runs SQL Server and Clone Agent. Either needs to have the database you want to image on it (to image from live - but we don't recommend doing this directly from production) or uses SQL Server to restore from a backup. This entails reading from the local disk or wherever the backup is and writing to the image file share. This will also be where Data Masker is run, if applicable, along with any other scripts run during image creation. CPU would affect backup restore speed running Data Masker/scripts, but most likely to be I/O bound reading and writing database files. SQL Clone does not store any significant data locally when you create images, it writes directly to the image file share.

  • Machine hosting image file share: serves unmodified blocks from images over SMB (doesn't need to run any specific software, anything that can provide an SMB3 share works). Network and disk I/O is important to serve these requests. CPU just needs to be enough to keep up with SMB requests, and memory is mostly useful for disk caching to improve I/O speed. Needs database size * number of images disk space.

There isn't any requirement for any of these to be different machines, but they often are. The overriding concern is that if they are different machines, the connection between them should be low-latency, high-bandwidth and above all, reliable, as interruptions result in I/O errors which can cause SQL Server to go into fault states that can't be resolved without restarting it.

You can read this for some general principles: http://assets.red-gate.com/products/dba/sql-provision/sql-provision-best-practices-guide.pdf
And to understand more about what's happening under the hood: https://www.red-gate.com/hub/product-learning/sql-clone/why-is-my-clone-so-small

Was this article helpful?

0 out of 0 found this helpful
Have more questions? Submit a request