Product articles SQL Clone Development and Testing with Clones
SQL Clone PowerShell Scripting: The…

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.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

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.

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.

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.

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.

We can list all the clone templates along with the images with which they are associated:

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.

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):

Removing a clone becomes scarily easy:

And you can, if you want, remove all the clones for an image (or refresh them by changing the final cmdlet):

Or remove all clones from an instance:

We’ll end by showing how easy it is to replace all clones with ones created from an updated image:

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

SQL Clone

Create SQL Server database copies in an instant

Find out more