SQL Clone 5

Testing out a New SQL Clone Installation

Having worked through Getting Ready for SQL Clone, a useful next step, if you're setting up SQL Clone to evaluate whether it is appropriate, is to run the installation process on a single machine. You can turn a Windows Server, with enough disk storage space, into a complete SQL Clone system. It will need to be running at least one SQL Server instance, and host a file share. It's useful to also have one additional user machine from which you can test remote connections to your SQL Clone Server.

A good initial objective might be to create copies of all Microsoft's sample databases, as clones, make changes to them, instantly revert them to their pristine state and then delete them. While this is not a realistic use case, since these databases are quite small, it's still a useful first test of the technology.

Can you connect to SQL Clone Server?

SQL Clone serves a web app accessible in your browser at http://machinename.fullyqualifieddomain:14145 that requires access to the clone server's IIS server.

An obvious first test is to make sure you can connect and authenticate to SQL Clone Server from a user workstation, at http://<hostname>:14145/dashboard .

On each user machine, make sure that the web service on 14145 that is used by SQL Clone can get through the firewall. This is necessary also to allow the PowerShell process to interact with it. This may, if you hit a problem, mean tweaking the firewall rules that are inserted by the installation process of SQL Clone and SQL Clone Agent to ensure that they work for your type of network (the firewall now has separate rules for private, public or domain networks).

The first PowerShell command to try is to make sure you can also connect to SQL Clone Server from PowerShell:

Connect-SqlClone -ServerUrl 'http://machinename.fullyqualifieddomain:14145'

If you can connect both ways, you're ready to start testing out the installation.

Creating images and clones from the Web UI

As a first step, it makes sense to work from the web console, create a few images, both from database connections and from database backups. From each image create and deploy a few clones.

Redgate University Online video tutorials

Once you've created a few images and clones, it is time to try out PowerShell.

Testing out a new SQL Clone installation with PowerShell

The purpose of this section is simply to provide a few quick PowerShell scritps that you can use to explore and test out a new SQL Clone Installation. For full details of all the SQL Clone PowerShell cmdlets, and some example scripts see: Automation.

The most immediate and easy thing you can do is to get lists of the various objects you have configured in the GUI. Then you're ready to start creating images, and then creating and dropping clones.

Getting a list of Clones

Get-SQLClone|select name,state

A list of images

Get-SqlCloneImage|select name,OriginServerName,OriginDatabaseName

A list of clones and their image name

Get-SqlCloneImage -OutVariable TheImage |Get-SQLClone|
  select name,state, @{label="ImageName";expression={$TheImage.name}} 

All clones by server and instance

Get-SqlCloneSqlServerInstance -OutVariable TheInstance |Get-SQLClone|
  select name,state,
   @{label="Server";expression={$TheInstance.server}},
   @{label="instance";expression={$TheInstance.instance}}

List of locations where images are stored

Get-SqlCloneImageLocation|Select Path

All machines hosting an Agent

Get-SqlCloneMachine|Select MachineName #

List of all SQL Server instances

Get-SqlCloneSqlServerInstance|Select Server, instance

List of Teams that have been defined

Each SQL Clone user can now be assigned to a team and will have access only to those resources (images and instances) that are also assigned to that team.

Get-SqlCloneTeam

List of clone templates that have been defined

You can use clone templates to create ‘bespoke’ clones, such as by applying a migration script that allows a developer to work on a special variant, or branch, version of the database.

Get-SQLCloneTemplate

Creating images and clones

To create an image, specify the SQL Server instance that has the database from which you want to take an image. As described, this server will need to have a SQL Clone Agent running on it.

We also need to specify the ImageLocation where we want to store the image, providing its UNC path, and the GUI registers it as an image location. You can then choose between any of the image locations that are registered in the GUI. You cannot create a new image location in PowerShell yet.

Creating an image and a clone

<# find where the images are located.  SQL Clone can have more than one of these network locations #>
$WhereTheImagesAre=Get-SqlCloneImageLocation #This gets details of image locations from a SQL Clone Server.
<# each ImageLocationResource has properties for the Path and id
if you have only one image location, that's all you need to do. If you have more than one you can filter by ID or path. #>

$SourceInstance=Get-SqlCloneSqlServerInstance|where {$_.serveraddress -eq 'MyMachine' -and $_.instance -eq 'sql2014'}

<# Create an image from an instance and put it in an imageLocation #>
$ImageName="AdventureWorks-$(Get-Date -Format yyyyMMddHH)"

#create the unique name of the image
$OurImageOperation = New-SqlCloneImage -Name $ImageName `
    -SqlServerInstance $SourceInstance `
    -DatabaseName 'AdventureWorks2016' `
    -Destination $WhereTheImagesAre
#now we wait for the image to be generated.  
Wait-SqlCloneOperation -Operation $OurImageOperation

#now we can inspect it to check that all is well
$image=Get-SqlCloneImage -Name $ImageName
<# This ImageResource can tell us the ID, name, CreatedBy, CreatedDate, 
createdDateutc, state, SizelnEytes, OriginDatabaseName, and OriginServerName

Now we have everything we need to make a clone #>
$DestinationServerInstance = Get-SqlCloneSqlServerInstance -MachineName MYSERVER -InstanceName INSTANCENAME

$OurCloneOperation=New-SqlClone -Name 'AdventureWorks-clone1' -Location $DestinationServerInstance -Image $Image
Wait-SqlCloneOperation -Operation $OurCloneOperation
<# you can check that it  is there, of course #>
Get-SqlClone -Name 'AdventureWorks-clone1' -Location $DestinationServerInstance

<# The .CloneResource object passed back provides information about
Name, CreatedBy, createdDate, createdDateutc, state, SizelnBytes
id, Locationld, Parentlmageld, TemplateName #>

Creating clones from two or more images

We carry on, using the $DestinationServerInstance and $image objects we've saved.

<# let's make a couple of these images #>
@('FirstAdventureClone','SecondAdventureClone')| foreach {
   New-SqlClone -Name $_ -Location $DestinationServerInstance -Image $Image|Wait-SqlCloneOperation}

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! #>
Remove-sqlclone -clone (Get-SqlClone -Name 'NameofMyclone' -Location $DestinationServerInstance)

Removing all clones from an image

<# remove all clones for an image #>
Get-SqlCloneImage -Name $ImageName|Get-SqlClone|Remove-sqlclone|Wait-sqlcloneOperation

Remove all clones for an instance

Get-SqlCloneSqlServerInstance -MachineName  MYSERVER -InstanceName InstanceName |
   Get-SqlClone|Remove-sqlclone|Wait-sqlcloneOperation

Replacing all clones for an image with a new image

$clones=@()
Get-SqlCloneImage -Name $ImageName |Get-SqlClone -OutVariable +Clones |Remove-sqlclone|Wait-sqlcloneOperation
$newCloneImage=Get-SqlCloneImage -Name 'NameofMyclone'
$clones|foreach{New-SqlClone -Image $newCloneImage -Name $_.Name -Location (Get-SqlCloneSqlServerInstance|Where Id -eq $_.LocationId)|Wait-sqlcloneOperation}


Didn't find what you were looking for?