SQL Clone PowerShell Scripting: The Basics
Phil Factor demonstrates some simple examples of how to use SQL Clone's PowerShell library to pass objects between cmdlets, and simplify common tasks, such as creating and deploying clones from various images. He then documents the objects and cmdlets, and illustrates their inputs and outputs.
In order to write effective PowerShell scripts, it is often as useful to understand the objects passed between cmdlets, and the properties they contain, as it is to know about the cmdlets themselves. It can save time and enable you to condense the PowerShell scripts.
Objects and Cmdlets
There are two things you need to know: how the objects are used (consumed or produced) by the cmdlets, and the information that you can get from the objects.
At a basic level, it means that you can do reports, knowing what the objects contain. If we are aware, for example, that the Get-SqlClone cmdlet returns a cloneResource object, and that this has the properties of Name, CreatedBy, CreatedDate, CreatedDateutc, State, SizeInBytes, ID, LocationID, ParentImageID, and TemplateName, then we can get useful reports.
|
1 2 3 4 |
#get a list of all your clones Get-SqlClone|select name,state #get a list of all your clones, with their size in Mb Get-SqlClone|select name,state, @{label="SpaceTaken";expression={$_.SizeInBytes/1024/1024}} |
Likewise, we can list the names of the Server and database from which all the images have been taken if we know that the imageResource object, returned by the Get-SQLCloneImage cmdlet, has this information.
|
1 2 |
#get a list of all your images Get-SqlCloneImage|select name,OriginServerName,OriginDatabaseName |
We can, of course, do far more by passing objects from one cmdlet to another, using pipelines. We can, for example, see what images are being used by all the clones. To do this, we get a list of all the images (the imageResource Objects) from Get-SqlCloneImage and then pipe each one over to the Get-SqlClone cmdlet, which returns all the cloneResource objects for each image. Using this, we can list the name and state of each clone (and whatever other information you need) alongside the name of its parent image.
|
1 2 |
Get-SqlCloneImage -OutVariable TheImage |Get-SqlClone| select name,state, @{label="ImageName";expression={$TheImage.name}} |
It is just as easy to do this by the server and instance if we start by getting the SqlServerInstanceResources objects from the Get-SqlCloneSqlServerInstance cmdlet and using the list to get all the SQL clones on them.
|
1 2 3 4 5 |
#get all clones by instance Get-SqlCloneSqlServerInstance -OutVariable TheInstance |Get-SqlClone| select name,state, @{label="Server";expression={$TheInstance.server}}, @{label="instance";expression={$TheInstance.instance}} |
We can list all the clone templates along with the images with which they are associated:
|
1 2 3 4 |
# get a list of all image templates along with their image Get-SqlCloneImage -OutVariable TheImage |Get-SQLCloneTemplate| select Name, @{label="Image";expression={$TheImage.Name}} |
With a pipeline, we can iterate easily to do several tasks. We can use code like the following to create as many clones as required; just two in this example, one on each instance for illustration purposes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# Let’s create some clones. We will provide an array of objects with the details. @(@{'Image'='WorldwideImporters';'Clone'='WorldwideClone'; 'Instance'='AnInstance';'Server'='AServer'}, @{'Image'='Adventureworks2016;'Clone'='AdventureWorksClone'; 'Instance'='MyInstance';'Server'='MyServer'} )| foreach{ Get-SqlCloneImage -Name $_.Image | New-SqlClone -Name $_.Clone -Location ( Get-SqlCloneSqlServerInstance ` -MachineName $_.Server ` -InstanceName $_.Instance ` )| Wait-SqlCloneOperation } |
With a pipeline we can neatly create a clone on every SQL Server instance accessible to the user. (you’d expand the script to check whether a clone of that name already exists):
|
1 2 3 4 5 6 |
# this delivers a clone of an image to all instances registered with clone $SourceDataImage = Get-SqlCloneImage -Name 'MyDatabase' $CloneName = 'NameOfTheClone' Get-SqlCloneSqlServerInstance| foreach{New-SqlClone -Name $CloneName -Location $_ -image $SourceDataImage| Wait-SqlCloneOperation} |
Removing a clone becomes scarily easy:
|
1 2 3 4 5 6 7 8 |
<# Removing a Clone If you pass a cloneResource to Remove-SqlClone cmdlet, you can then remove an image. Be sure to be careful that nobody is using it! #> $DestinationServerInstance = Get-SqlCloneSqlServerInstance -MachineName MyServer -InstanceName SQL2017 Remove-sqlclone -clone (Get-SqlClone -Name 'NameOfTheClone' -Location $DestinationServerInstance) # or Get-SqlClone -Name ' NameOfTheClone' -Location $DestinationServerInstance| Remove-sqlclone|Wait-sqlcloneOperation |
And you can, if you want, remove all the clones for an image (or refresh them by changing the final cmdlet):
|
1 2 |
<# remove all clones for an image #> Get-SqlCloneImage -Name $ImageName|Get-SqlClone|Remove-sqlclone|Wait-sqlcloneOperation |
Or remove all clones from an instance:
|
1 2 3 |
<# remove all clones for an instance #> Get-SqlCloneSqlServerInstance -MachineName WIN-ILCH1809MQP -InstanceName sql2017 | Get-SqlClone|Remove-sqlclone|Wait-sqlcloneOperation |
We’ll end by showing how easy it is to replace all clones with ones created from an updated image:
|
1 2 3 4 5 |
<# replace all clones for an image with new ones#> $clones=@(); $CurrentImageName='MyCurrentClone';$NewImageName='MyNewClone' Get-SqlCloneImage -Name $CurrentImageName |Get-SqlClone -OutVariable +Clones |Remove-sqlclone|Wait-sqlcloneOperation $newCloneImage=Get-SqlCloneImage -Name $NewImageName $clones|foreach{New-SqlClone -Image $newCloneImage -Name $_.Name -Location (Get-SqlCloneSqlServerInstance|Where Id -eq $_.LocationId)|Wait-sqlcloneOperation} |
In these ways, we transform what would otherwise be quite complex tasks, merely by creating pipelines that pass objects between cmdlets. To do this requires knowledge of both the objects and the cmdlets, but once you’ve amassed a collection of code snippets, like these, they become very useful in PowerShell scripting.
The Objects
We’ll start by summarizing the SQL Clone objects, and how we can use each one, explaining which cmdlets accept it as a parameter or as piped input (with its parameter name in brackets), which ones return it as a return value, and the properties it contains. (return values can, of course be piped to the following cmdlet). We’ll then go on to tackle the cmdlets.
System.Management.Automation.PSCredential
Used to provide a credential to SQL Clone. This credential is used to identify you as a clone user and determine your team memberships, roles (admin, standard or clone-only) and the instances or images that you can access. A PSCredential is of no other use in SQL Clone. The PowerShell module allows only one connection to a clone server at any one time.
As parameter
(-credential) Connect-SqlClone
As Input
Connect-SqlClone
RedGate.SqlClone.Client.Api.Objects.ImageResource
This object identifies a SQL Clone image. These images are stored on a file share and are used by individual database clones. It contains the original data for any clones that use it. Images can only be deleted through SQL Clone, and only after any clones created from them have been removed.
As parameter
(-image) New-SqlClone, Get-SqlClone, Get-SqlCloneTemplate, New-SqlCloneTemplate, Remove-SqlCloneImage, Rename-SqlCloneImage
As Input
New-SqlClone, Get-SqlClone, Get-SqlCloneTemplate, New-SqlCloneTemplate, Remove-SqlCloneImage, Rename-SqlCloneImage
As Return Value
Get-SqlCloneImage
Contains the properties
BackupFilenames, CreatedBy, CreatedDate, createdDateutc, id, Name, OriginDatabaseName, OriginServerName, SizelnBytes, State
RedGate.SqlClone.Client.Api.Objects.ImageLocationResource
This object identifies the path to the network location where an image is to be stored. Currently, it is only ever used to specify the destination, when creating new images.
As parameter
(-destination) New-SqlCloneImage
As InputAs Return Value
Get-SqlCloneImageLocation
Contains the properties
Id, Path
RedGate.SqlClone.Client.Api.Objects.AgentResource
Used to specify the machine that hosts a SQL Clone Agent. This Agent service is required to allow a SQL Server instance to either host clones or provide the source for an image, or both. All machines that host SQL Server instances require a one-time-only installation that can be obtained via the GUI.
As parameter
(-Machine) Remove-SqlCloneMachine, Rename-SqlCloneMachine
As Input
Remove-SqlCloneMachine, Rename-SqlCloneMachine
As Return Value
Get-SqlCloneMachine
Contains the properties
MachineName, StatusDescription, Version, Environment, DataMaskerinstalled, DataMaskerVNextInstalled
RedGate.SqlClone.Client.Api.Objects.SqlServerInstanceResource
Used to specify the SQL Server instance that contains the source database for a new image or is the intended host for a new clone (if you’re refreshing an existing clone you just need to pass a CloneResource). Any machine that has an Agent service may host several SQL Server Instances on which we can create clones or that we can use as the source of images.
As parameter
(-SqlServerInstance) New-SqlCloneImage, New-SqlClone
As Input
new-SqlCloneimage, New-SqlClone
As Return Value
Get-SqlCloneSqlServerInstance
Contains the properties
Environment, id, Instance, Machine, Server, ServerFullyQualifiedDomainName and Username
RedGate.SqlClone.Client.Api.Objects.CloneTemplateResource
This object represents any template for an image, which can be run while creating a new clone from that image. The Get-SqlCloneTemplate and New-SqlCloneTemplate cmdlets return a CloneTemplateResource, which can be passed to the New-SqlClone cmdlet.
As parameter
(-CloneTemplate) Remove-SqlCloneTemplate, New-SQLClone
As Input
Remove-SqlCloneTemplate
As Return Value
Get-SqlCloneTemplate, New-SQLCloneTemplate
Contains the properties
Id (CloneTemplate), Name, ImageID
RedGate.SqlClone.Client.Api.Objects.TeamResource
Represents a SQL Clone Team, used to make it easy to organise the access rights of users or groups to SQL Clone resources.
As parameter
(-teams) New-SqlCloneImage
As Return Value
Get-SqlCloneTeam
Contains the properties
Name, ID
RedGate.SqlClone.Client.Api.Objects.OperationResource
The OperationResource object makes it easy for a PowerShell process to wait until a long process, such as image creation, is finished, so that a simple, single process can easily perform every step, in sequence.
As parameter
(-Operation) wait-SqlCloneoperation
As Input
wait-SqlCloneoperation
As Return Value
New-SqlClone, new-SqlCloneimage, remove-SqlClone, Remove-SqlCloneImage, Remove-SqlCloneMachine, Reset-SqlClone
Contains the properties
Id (an operation ID, used internally only)
RedGate.SqlClone.Api.ScriptRunning.ModificationScript
Represents a modification script that will be used on an image or a clone.
As parameter
(modifications) New-SqlCloneImage, New-SqlCloneTemplate
As InputAs Return Value
New-SqlCloneMask, New-SqlCloneSqlScript
Contains the properties
Name, script
RedGate.SqlClone.Client.Api.Objects.CloneResource
A CloneResource object represents a clone and is used to specify the existing clone that is either to be reset or removed. It isn’t returned by New-SqlClone.
As parameter
remove-SqlClone, Reset-SqlClone
As Input
remove-SqlClone, Reset-SqlClone
As Return Value
Get-SqlClone
Contains the properties
Name, CreatedBy, createdDate, createdDateutc, state, SizelnBytes, id, Locationld, Parentlmageld, TemplateName
The Cmdlets
In these diagrams, a left-side blue arrow denotes a pipeline input, and a right-side red arrow represents an output, whether pipeline or assignment. The lower dark-green arrows represent the parameters by name. A parameter with an -o at the end of the line is obligatory (required), either as a pipeline input or as a parameter.
Connect-SqlClone
Get-SqlClone
Get-SqlCloneImage
Get-SqlCloneLocation
Get-SqlCloneMachine
Get-SqlCloneSqlServerInstance
Get-SqlCloneTeam
Get-SqlCloneTemplate
New-SqlClone
New-SqlCloneImage (from database)
New-SqlCloneImage (from Backup)
New-SqlCloneMask
New-SqlCloneSqlScript
New-SqlCloneTemplate
Remove-SqlClone
Remove-SqlCloneImage
Remove-SqlCloneMachine
Remove-SqlCloneTemplate
Rename-SqlCloneImage
Rename-SqlCloneMachine
Reset-SqlClone
Wait-SqlCloneOperation
Summary
If you are using any PowerShell library or module, whether it is SQL Server or SQL Clone or a host of others on the PowerShell gallery, you will find that it helps a great deal to find out about the objects generated and consumed by the cmdlets, as well as learning the cmdlets.
These objects have useful properties. Sometimes, as with SQL Server, there are methods too that can be used with PowerShell. With SQL Clone, the cmdlets are designed to be used in conjunction, passing objects between them, and if you have a good understanding of both, then you’ll find a lot of shortcuts to getting the reports you want, and a range of one-liners for the common SQL Clone processes.
Tools in this post
You may also like
-
Article
Create, protect and manage non-production databases with SQL Provision
SQL Provision allows users to create copies of SQL Server databases in seconds, using a fraction of disk space, and mask any sensitive data to help address data privacy and protection concerns. It serves as a gateway between production and non-production environments, to ensure the safe distribution of database copies from one central location, without blocking the team's development and release processes.
-
Article
How to reset your development database in seconds using SQL Clone
Owen Hall describes the new "clone reset" feature, how it works and the database development and testing processes it enables, or makes simpler and quicker.
-
Article
Using striped backups with SQL Clone
Can we use a striped backup as the source for a SQL Clone image? Yes we can!
-
Article
Team-based database development with SQL Clone
For most development teams, the database provisioning process involves some element of compromise. Either the process is slow, but the database is realistic or the process is fast but the database unrealistic. Chris Hurley explains why SQL Clone can allow the team to develop and test with a real database, without the compromises.
-
Article
Automated Provisioning of Multi-Database Environments in a Flyway Development
This article describes how we implemented lightweight database provisioning, using clones, within an automated Flyway release process . It allows users to self-serve personal copies of a complex multi-database system, in a way that is transparent, supports flexible Git branching and merging strategies and agile releases, but does not compromise data security nor add significant admin overhead.
-
