Perform Automatic Clone Database Refresh with Templates?

We have the business requirement to automatically refresh our images/clones at regular intervals.

An example was provided in the SQL Clone documentation to refresh all clone databases to use an updated image:

How can this be accomplished for clones that are using a template? 

The 'New-SqlClone' cmdlet has a -Template parameter, but the template does not exist in the new image, so the operation fails.

The 'New-SqlCloneTemplate' cmdlet could resolve that issue by creating the template for the new image, but it has no parameter that can accept a 'CloneTemplateResource' object. This prevents the template object from being directly transferred.

The template could potentially be re-created from the original modification scripts/files, however, the 'CloneTemplateResource' object in PowerShell does not appear to have a property containing the modification scripts and/or their original file paths (even though the SQL Clone GUI does show the original filenames on mouse hover). This prevents passing existing scripts from the template object to the 'New-SqlCloneSqlScript' cmdlet (for use with the -Modifications parameter of 'New-SqlCloneTemplate').

The only solution I can think of so far is to manually create templates at image creation (not clone creation), regardless of their use by clones, and then try to match on template name during the refresh. But... this solution brings with it a whole new host of problems too: clones dictate the need for templates and clone/template requirements may not be available at image creation, scripts used in the old image template may not match up with those used in the new image template of the same name and create discrepancies, the clone creation will fail if an expected template is unavailable in the new image, many templates could go unused and create clutter in every new image, and increased manual intervention in the "automatic" clone refresh and image creation processes.

Am I overlooking the proper way to accomplish this task? Feedback would be appreciated. Thanks.


  • At the moment there isn't a way to persist/copy clone templates between images - they're scoped to a particular image. Also, although we store the filename, we don't store the full path (in particular, we don't have access to it if files are uploaded in the web UI, and it's possible to specify scripts directly without a file in PowerShell too).

    Therefore the best workflow for now is to maintain the scripts that you want for particular images in a well-known location and to have a script that recreates the image and templates together, using those scripts. You could also use the -Sql parameter of New-SqlCloneSqlScript pass the script to SQL Clone as a string if you want to manage how it's loaded in a different way.

    Thanks for your feedback about this being a problem! We'll keep that in mind for our future work.
  • SeanPerkinsSeanPerkins Posts: 12 New member
    Thanks for the response. We were hoping to avoid the idea of hardcoding clone-specific template script paths into our image/clone refresh scripts, but it sounds like that is our only option for now.

    We could better automate our clone refreshes if the 'CloneTemplateResource' object contained these properties (accessible using the 'Get-SqlCloneTemplate' cmdlet):
    • Filename. The original filename(s) of the modification script(s). Even if a full path is not stored, it would be nice to access the filename itself (like the GUI can) so that we can maintain a script location and match the existing template scripts to files there during clone refreshes.
    • SqlScript. The 'ModificationScript' objects associated with the template (whether it was originally imported from a file or passed as a string). Alternatively, a reference to 'ModificationScript' objects by ID that could be used with a 'Get-SqlCloneSqlScript' cmdlet to return a 'ModificationScript' object or the original script as a string value. These methods would allow the template script(s) to be captured as a variable and passed as a parameter during template creation for the new image.
    Either of these options would better allow an image to be updated regularly and have all its clones refreshed without requiring change review and script revisions prior to every clone's initial creation. I hope this feedback will assist you in further improving the SQL Clone product. Thanks again.

Sign In or Register to comment.