Perform Automatic Clone Database Refresh with Templates?

SeanPerkins

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:
https://documentation.red-gate.com/clone4/automation/powershell-worked-examples/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.

ChrisHurley

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.

SeanPerkins

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.

SeanPerkins

Since this was originally posted, I've been actively developing workarounds to the problem (mainly through the use of CSV files as reference documentation) and recently came across a similar situation regarding SQL Clone teams and permissions.

There appear to be no SQL Clone PowerShell cmdlets that permit teams/permission management (e.g. creating/deleting a team, adding/removing a team assignment from a clone image or SQL Server location, etc.). Even more surprisingly, I found there is no way to determine team/permission assignments via PowerShell commands. There are no properties under the 'TeamsResource', 'ImageResource' or 'SqlServerInstanceResource' objects that indicate relationships between these objects. This is very similar to the deficiencies I previously noted for SQL Clone templates.

As we attempt to automate clone refreshes, which include transferring templates and team-based permissions between images, it would be awesome if these relationships could be identified within PowerShell object properties and, especially in the case of team assignments, have some additional CLI options to manage them.

Last I heard, my feedback was being discussed with the development team about a year ago:
https://forum.red-gate.com/discussion/85836/perform-automatic-clone-database-refresh-with-templates

I'm curious if there have been any updates since then that can be shared? Thanks.

James_Murtagh

Hi Sean,

Thank you for you feedback, it’s incredibly useful to learn how you are looking to automate with SQL Clone. And while this isn’t something we are currently working on, making improvements in this area is a candidate for our long-term roadmap, so your suggestions are very helpful for us and have been saved.

As soon as we have any updates, we will be sure to share these with you.

Kind regards,

James