Product articles SQL Clone Development and Testing with Clones
Unwrapping, Unboxing and Installing SQL…

21 October 2019

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.

21 October 2019

Unwrapping, Unboxing and Installing SQL Clone

If you are evaluating a tool such as a text editor or spreadsheet, it is easy: you just install it, you run it, you decide whether you need it. Job done. However, a similar 'unboxing' or 'unwrapping' of SQL Clone, and installing across a network, is not so quick and easy. Phil Factor's solution is to install and run a complete installation of SQL Clone on a single box. This allows you to try everything out, creating images and deploying clones, while isolated from the network. It can then be extended across a network, subsequently, when it's been fully tested.

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.

Contents

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

    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 …

    … and then a list of images

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

    And we can also get all clones by server and instance

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

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

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

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

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

    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.

    Creating two or more images

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

    Removing a clone

    Removing all clones from an image

    Remove all clones for an instance

    Replacing all clones for an image with a new image

    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.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    SQL Clone: the aspirin for your database provisioning headaches

    For teams who use a shared development database out of necessity rather than choice, SQL Clone might make the dedicated model viable for the first time.

  • Webinar

    How to ensure SOX, HIPAA, & GDPR Compliance in Dev and Test

    Chris Unwin, Data Privacy and Protection Specialist at Redgate shows you how to spin up fresh database copies for dev and test in seconds, with whatever level of protection you need – masked data, unmasked data, or synthetic data sets.

  • Webinar

    How to treat compliance in DevOps as serious business

    We hosted this webinar to discuss how, by embedding ‘Privacy by Design’ in your Database DevOps practices, you can introduce a framework that builds a bridge between fast AND secure software development. We arm you with practical tips on how to discover, classify, protect and monitor your SQL Server estate, and therefore ensure that your Database DevOps practices are secure

  • Webinar

    Is HIPAA impacting your data delivery processes?

    The HIPAA legislation has been around for several years, and in order to remain compliant changes to your data delivery processes have been necessary. But how has this impacted the speed and performance of your work?

  • Webinar

    Data privacy & protection: A logical extension to DevOps

    Are you considering data privacy and protection as part of your DevOps process? In light of legislation like GDPR, making sure that any personally identifiable information (PII) is protected as it moves through your development and testing environments, is now an essential part of the process to ensure that your Database DevOps practices are compliant.

  • Forums

    SQL Clone Forum

    Create SQL Server database copies in an instant