Phil Factor

21 October 2019

• SQL Clone
• Development and Testing with Clones

Unwrapping, Unboxing and Installing SQL Clone

Contents

• • Unwrapping SQL Clone
  • Unboxing SQL Clone
  • Overview of the installation process
  • Pre-installation tasks
  • Testing out a 1-box SQL Clone installation
    • Can you connect to SQL Clone Server?
    • Testing out SQL Clone with PowerShell
  • Scaling out the SQL Clone system and making changes
  • Conclusion

  Unwrapping SQL Clone

  SQL Clone allows you to create copies, or clones, of databases that behave just like any others but, even when they are large, they are quick and easy to install, update, revert to their original state, and remove. This makes clones ideal for development work and testing, where you need several copies of a database, and you need to update each copy, as or when you check in developments.

  SQL Clone makes no changes to SQL Server and so a clone is just a normal database, except that each clone takes up very little initial disk storage on its host server. Without SQL Server being aware of it, the database and its data (i.e., its data and log files) are held in the clone’s parent image file, in a shared folder on the network, and only the changes made to each clone are held locally, in a differencing file, on the SQL Server instance hosting the clone. In effect, the database is, invisibly to SQL Server, ‘split’ between the image and the clone files, and so there must be a constant connection between these files.

  SQL Clone creates and manages these image and clone files using Virtual Machine technologies built into the Windows operating system. SQL Clone Agents, local to each SQL Server instance participating in the SQL Clone service, do all the work of creating images and clones, as well as orchestrating the VM technology that enables the ‘data virtualization’. All SQL Clone operations are managed via a web console or can be automated using PowerShell.

  Given all this, SQL Clone relies on the following:

  1. Having a consistent and fast network between the various components – and especially between each clone and its image.
  2. A patched and reliable Windows server – for each SQL Server instance that will be used to host clones.
  3. A fileserver with a fast, sustained throughput and response time – to host the image files
  4. A SQL Clone Agent service that runs as a local administrator – on each SQL Server instance that will host clones or provide the source for images, or both

  Without 1-3, SQL Clone is not recommended, because it relies on Microsoft VM technologies that assume them. Without 4, SQL Clone will not work.

  Unboxing SQL Clone

  A typical SQL Clone installation consists of the following logical components, though these can all be hosted on one server if necessary:

  • One SQL Clone Server – A machine designated to run the SQL Clone service that coordinates with, and supervises, the SQL Clone Agents over the network. It also provides a browser-based application and can be accessed via PowerShell so as to automate routine tasks.
  • One SQL Clone configuration database – this stores the details required for SQL Clone Server to manage the clones, images, users and permissions. You can create it locally to the SQL Clone service, or an instance running on a server attached to the network.
  • One or more Servers running SQL Server – these participate in the Clone service by hosting databases that are the source of images or hosting clones, or both. If required, they can be used for the restore when the source is a backup, or to execute templates when the images are created.
  • SQL Clone Agents – each agent is a Windows service that deals with SQL Clone tasks on a specific server (‘Machine’ in SQL Clone jargon). An Agent is installed on every machine hosting a SQL Server instance that participates in the Clone service, as described above.
  • One or more Network file shares – one or more of these network file stores are required, and they must host the image files that are the source for the clones. They require Windows authentication.
  • SQL Clone Web console – Each user accesses the Clone service via a web console, served by SQL Clone Server, using a browser.
  • PowerShell cmdlets that can connect to SQL Clone Server and automate its operations. I’ve already written some sample PowerShell applications using these cmdlets to provide a basis for some typical uses.

  This is the logical diagram of a typical Clone installation which can range from a single machine to an entire domain.

  

  Overview of the installation process

  Regardless of whether you’re doing a 1-box installation, like me, or a full multiple-server network installation, the steps are as follows:

  • Step 1: Install the SQL Clone application
    This part of the installation establishes SQL Clone Server and will require you to:
    1. Enter your Redgate ID and password, for license checks.
    2. Tell SQL Clone Server where its config database lives, if you created it already, or where to create it otherwise
    3. Supply the credentials for the account that will run the SQL Clone Server service
    4. Authenticate yourself to SQL Clone Server – if successful, SQL Clone Server will serve the Clone Browser-based application and you can proceed to step 2
  • Step 2: Install a SQL Clone Agent
    On any machine hosting a SQL Server instance that will be used to either take an image of a database or create a clone from an image. The same agent is used to do either. This will require you to supply details for an account that will run the SQL Clone Agent service.
  • Step 3: Configure a file share
    Where you’ll, initially at least, store the image files, created from the databases you wish to clone

  Pre-installation tasks

  Here’s a checklist the tasks that I’d advise before starting the installation process, to ensure it goes smoothly. If you’re doing a 1-box installation, for initial assessment and testing, then it’s worth performing all the checklist tasks in one go, then working through each installation step. If you’re setting up across multiple networked servers, you can perform tasks 1-4 (the Step 1 checklist tasks), then go ahead and install the SQL Clone application, and then proceed step-wise from there.

  1. Decide on the Windows Server that will become the SQL Clone Server.

  This is where you will install the SQL Clone application. It should have a minimum of Windows Server 2012 or Windows 8. If you’re doing a 1-box install, it is still useful to choose a SQL Clone Server that would be suitable for a real rollout and is visible on the network to all machines that will host Clone Agents. Otherwise, you can either move it to a new location when you’re ready (I show how to do it later), or just reinstall from scratch.

  2. Choose the SQL Server instance that will host SQL Clone configuration database

  SQL Clone Server’s service account will need to be able to manage the SQL Clone configuration database. It doesn’t touch any other database, or any file; everything else is done by Agents.

  • 1. Install the instance, if required.
       A stand-alone local SQL Server Express, version 2008 or later, would seem a good option. It must be accessible from SQL Clone Server.
    2. Create the configuration database (optional)
       If you don’t, SQL Clone will create it as part of the installation. It will call it SQLClone_Config and will create the files at the default location and with default file size and growth settings.
       If you do create it now, similarly to above, you won’t need to grant to the SQL Clone Server Service account the CREATE ANY DATABASE permission (see step 3)

  3. Create an account to run ‘SQL Clone Server’ service

  On SQL Clone Server:

  • 1. If you are in a Windows domain, create a domain account. If not, create a local user.
    2. Make it a member of the db_owner fixed database role, on the SQL Server instance hosting the configuration database, or at least assign it SELECT/UPDATE/INSERT/DELETE permissions
    3. Assign to it the CREATE ANY DATABASE permission – only required if you’re going to let SQL Clone create the config database, during installation

  4. Make sure you can log into the Redgate website

  Open Internet Explorer/Edge browser on SQL Clone Server and make sure you can log onto redgate.com using the Redgate ID and password. You may need to set up a browser rule that allows the server to contact Redgate for license information, to avoid possible difficulties during the install.

  5. Create an account for a SQL Clone Agent Service

  On any machine hosting a SQL Server instance that will be used to either take an image of a database or create a clone from an image:

  • 1. If you are in a Windows domain, create a domain account on the machine. If not, create a local admin user.
    2. Make it a member of sysadmin fixed server role – the agents are the workhorses of SQL Clone and needs these “superuser” permissions in order to create and delete clones and to make the Virtual Disk Service calls

  6. Configure the initial file share

  This is where you’ll store images. After this initial install, you can create as many other subsequent file shares as you need. Only SQL Clone Agents should be allowed access to any file share.

  • 1. Set up the shared folder (for a quick reminder, see here)
    2. Grant read-write access only to the SQL Clone Agent account(s) you created in step 5 – then add Agent accounts as you create them.

  Testing out a 1-box SQL Clone installation

  It is possible to be over-ambitious in initially setting up SQL Clone to evaluate whether it is appropriate. It is possible to get everything running on a single machine, and this is the way I check out new releases of a new product before rolling them out. I already have a previous version working in daily use.

  As I write this, I’m staring at a bewildered network router that was repurposed just because it happened to have enough hardware to host everything. Following the instructions provided earlier, I have turned it into a complete SQL Clone system. It has Windows Server 2019 Essentials, two instances of SQL Server 2019, and a file share.

  The objective was to have a copy of Microsoft’s sample databases as clones so I could delete and change things on them and instantly revert them to their pristine state. It is a spurious use-case as a restore isn’t too much slower, because they are so small, but 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.

  I connected to my 1-box SQL Clone system from a workstation and logged in to the SQL Clone Server via http://<hostaname>:14145/dashboard to check out the basic install. If using a non-domain network, you might need to give credentials at this point and will need to make sure this user is present on SQL Clone Server as a local user and has been assigned the necessary permissions on SQL Clone. You’ll need to create local users for every person or process needing to access SQL Clone, after completing the installation; see Adding More SQL Clone Users.

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

  As a first step, it makes sense to work from the web console, create a few images, either from database connections or from database backups. From each image create and deploy a few clones. There is not much harm you can do and the worst that can happen would be a certain cruelty to a re-purposed router. Once you’ve created a few images and clones then it is time to try out PowerShell.

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

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

  Testing SQL Clone out with PowerShell

  If you can connect, you have probably won the struggle to set up SQL Clone. After that, 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 lists of clone objects

  First, we get a list of our clones …

  1	Get-SQLClone|select name,state

  … and then a list of images

  1	Get-SqlCloneImage|select name,OriginServerName,OriginDatabaseName

  OK, but it would be nice to know the image associated with each clone, so we get all clones with their image name

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

  And we can also get all clones by server and instance

  1 2 3 4

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

  What about getting a list of the locations where your images are stored …

  1	Get-SqlCloneImageLocation|Select Path

  …and all the machines that have an agent installed on them

  1	Get-SqlCloneMachine|Select MachineName #

  While we’re about it, what about a list of all the SQL Server instances

  1	Get-SqlCloneSqlServerInstance|Select Server, instance

  And to round it off, get a list of all the teams that have been defined…

  1	Get-SqlCloneTeam

  …and a list of all image templates that have been defined

  1	GetSQLCloneTemplate

  Creating an image

  To create an image, we need to specify the SQL Server instance on which there is the database from which we want to take an image. As described, this server will need to have a Clone agent running on it.

  We also need to specify the ImageLocation where we want to store the image. You create the file share, then provide 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.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

  <# 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 two or more images

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

  1 2 3

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

  Removing a clone

  1 2 3

  <#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

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

  Remove all clones for an instance

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

  Replacing all clones for an image with a new image

  1 2 3 4

  $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}

  Scaling out the SQL Clone system and making changes

  Once you’ve tested out SQL Clone on a single server, it is time to extend it. The simplest technique is to do a new install, but you have the alternative of just extending the trial install. I would keep the Clone Server and configuration database on the original server as it does not require a great deal of horsepower. However, I’ll describe how to move that later in this section.

  Adding more Image locations

  You can leave the original image location in place, but an obvious first extension is to create more. In a full commercial installation, the choice of the file share servers is very important. It must have a fast and reliable network connection to machines hosting clones, which should be on the same local area network. As noted in step 6 of the pre-installation tasks, only Clone Agents should have access to the file share.

  Any file share will need plenty of free space, enough to hold images (exact, full-size copies) of each source database that you want to clone. Check the sustained throughput to be certain that it performs well. For several reasons, this can be surprisingly inconsistent on a local network and the only good way to check is to run a few timed file copy operations at various times of day.

  Adding more SQL Clone machines (Agent resources)

  Every machine hosting a SQL Server instance that will be used to either take an image of a database or create a clone from an image needs a Clone Agent Service. The same agent is used to do either.

  On each of these machines, you’ll need to

  • Create a Clone Agent user account: See Step 5 of the pre-installation tasks for the details
  • Install the Agent: open the Clone web console, download an Agent and install it using the above credentials

  Adding more SQL Clone users

  Access rights for all SQL Clone users are managed within the SQL Clone Server by assigning them to Clone roles and Teams.

  A SQL Clone administrator can use the web console to create users or grant access to Active Directory users in the domain and assign authenticated users to their required roles and teams.

  These users can correspond to Windows domain users or to local users requiring credentials. Each user must then provide theses credential when access the SQL Clone service, via their browser. Credentials can be provided by users whether accessing the system via the browser or by Connect-SqlClone in PowerShell. This can help in tying down permissions so that scheduled processes can run with only the required access rights. It also allows SQL Clone to be used outside a Windows domain.

  Move a SQL Clone Server

  The ‘SQL Clone Server Setup’ installation process can be re-run from the Windows start menu if you need to make changes such as changing the clone database location

  To move the Clone server to another machine

  • If necessary, move the configuration database to another instance, and then set up Clone Server to use it.
  • open certlm on the new machine to view your machine’s locally installed certificates. In Personal, find the new SQL Clone Server Agent Certificate, and copy its hexadecimal thumbprint (deleting any spaces).

  On each of your agent machines,

  • Find the agent’s settings.json file (usually in %ProgramData%\Red Gate\SQL Clone Agent on newer operating systems, or in CommonAppData on older).
  • Take a backup of this file in case of any issue.
  • Within this file, update the management service’s thumbprint to the new thumbprint, and the URL to the new URL (taking care to use HTTPS, and the agent port of 14146). Restart your agent via Services, and it should connect to the new SQL Clone Server.

  Conclusion

  SQL Clone is not hard to install, but it can’t be done easily in a lunch break, even if you have free range on your network. When you think about what is happening at the operating system level, it is hardly surprising. , unless you are a DBA with ops skills who can charm network admins into creating VMs or spare machines, and letting a new untested application run wild across the network, with sysadmin rights, you are likely to be disappointed.

• I suspect that the full network installation of SQL Clone is best done by a DevOps team, because parts of this install are blindingly obvious to a DBA, whereas other parts are completely transparent to an Ops person. When it all bursts into life, the PowerShell is startlingly easy for a developer.In this article, I’ve taken a somewhat pessimistic approach in outlining a few aspects that need care and attention. The reason for this is that ‘forewarned is forearmed’. I also like to test out a new version of SQL clone that I’m going to use by installing the whole system on one machine within a DMZ, away from the local network. I then either reinstall it on the network or, less stressfully, just introduce the installed system into the network and extend it as described.My approach to installing SQL Clone has always been to start small, with everything on one Windows Server, and then expand once everything works well. This gives you plenty of clues about the locus of any subsequent problems and makes it an easier experience. Above all, before you start, make sure that the infrastructure is working well.Once everything is working as expected, my experience is that it just carries on working to the point that the commonest problem is that one forgets that SQL Clone is there and working away in the background.