• SQL Clone
• Development and Testing with Clones
• Database DevOps

Running SQL Clone on an Azure VM with Azure File Storage

SQL Clone is designed primarily for use on a corporate network (WAN or LAN) to help an organization create and deploy copies of very large databases, for development and testing work. However, often we need to work on a clone from home, or just remotely, on a different network. We might just use SQL Clone via a VPN connection, but this can result in unresponsive clones and slow queries. This is because, as for any SQL Server database, there must always be a fast and stable network connection between a clone and its file storage, in this case held in the remote ‘image’ files from which the clone was created (see How SQL Clone Works for details). Besides that, using a corporate VPN solution often comes with security policies that will prevent you from using other resources than the corporate network.

Another solution is to work with Remote Desktop and do your database magic on that session. One of the downsides of this approach is that everything is happening in the RDP session only, so debugging your local Visual Studio session against a database on the network, available in the RDP session, is not possible.

This article will describe how you can continue to work with SQL Clone, reliably and without relying on access to the corporate network, by running SQL Clone on an Azure Virtual Machine, storing the images on an Azure File Share, and finally tweaking the DNS configuration of SQL Clone so we can access the remote Azure-hosted SQL Clone Server from a local machine, and deploy clones locally.

This solution has the added security benefit that you do not necessarily need to have the SQL Clone Agent service running continuously on the server hosting the database you want to copy. For example, I am doing some database work for a customer, the Clone Agent service can be turned on interactively, only at the point I need to create the image. Once the image is stored in the Azure cloud, the Clone Agent on the customer network can be deactivated. This can save external developers like me a great deal of time, since we can now use the image freely to create the development and test copies of the database.

Architectural overview of SQL Clone

Steven Jones, Chris Unwin and Phil Factor have already written articles that explain perfectly well the how and the what of SQL Clone, so I won’t repeat those details here. I’ll also assume that you are familiar with the basics of Azure Storage and of configuring virtual machines in the Azure cloud.

What I will do, briefly, is remind you of the main components of SQL Clone. Beside this overview I will link to the SQL Clone product documentation for more details, if required.

SQL Clone “LAN Edition”

The following diagram shows use of SQL Clone on a typical local network, with one local SQL Clone user, and one remote user accessing it on a VPN connection.

We basically need 5 parts:

• SQL Clone Server –the machine on which you install SQL Clone. It runs the SQL Clone service, which coordinates the actions of SQL Clone Agents, and hosts the SQL Clone Web UI. It also keeps track of clones and images in its config database.
• SQL Clone Agents – the little red gear icons are SQL Clone Agents, responsible for creating images and clones. They must run as a service on the server hosting any SQL Server instance that is the source of images or a destination for clones.
• Source database – the database we use to create an image. This could be any database such as production database where the data has been masked or anonymized, or just the latest development build, filled with generated data. Somewhat unusually, and for reasons I’ll explain shortly, this is hosted on the same server as the SQL Clone service.
• (Windows) File Share – where we store the image files. All SQL Clone agents must be able to access this location.
• SQL Clone Users (Dev / Test / QA) – SQL Server instances that will host clones, this can be any SQL Server on the Local Area Network or a laptop connected via VPN

SQL Clone “Azure Cloud Edition”

The Azure implementation basically contains the same parts as above, to create the “Azure edition” of the SQL Clone architecture:

SQL Clone part	Azure implementation
SQL Clone Server	Azure Virtual Machine (VM)
Source Database (SQL Clone Agent)	Azure VM For simplicity, and to save costs, the SQL Server instance containing the source database is installed on SQL Clone Server VM
(Windows) File Share	Azure File Share (part of Azure Storage accounts)
SQL Clone User 1 (SQL Clone Agent)	Azure VM, connected via a Virtual network
SQL Clone User 2 (SQL Clone Agent)	My laptop linked to the Azure SQL Clone Server

Installing SQL Clone in Azure

The basic installation steps are the same as those for installing SQL Clone locally, or for creating Azure resources like a VM or an Azure Storage Account. I will focus more on steps that are particular to setting up SQL Clone in Azure.

Set up the Azure File Share

To store the image files, we need to set some cloud storage space, namely an Azure file share. This will be deployed into our Azure Storage account so, of course, we first need to create an Azure Storage account. For the purpose of the demo, and to reduce costs, I choose Locally-redundant storage (LRS) and a storage account of the type Standard general-purpose v2. This is of course the bare minimum, but it worked for me on these settings.

Within the Azure Storage Account, create a Transaction optimized File Share tier. This seems the best choice, from the available options, considering what we’ll store on this file share (Virtual Hard Disk files containing each image).

On every machine that needs to access the images we store here (every machine hosting a SQL Clone agent), we’ll need to map this Azure File Share are a Windows network drive. It is therefore important that we generate the PowerShell script that will create this mapping now, so we can then use it later.

In the Azure portal, under Azure Storage Accounts > File Share, there is a Connect button which creates the PowerShell script.

Hit this button and a PowerShell script is generated and you can copy and save it.

You might notice that, to keep this article “simple”, we just use the Storage Account Key authentication method, with the Storage Account Key embedded in the script. For general use, Microsoft recommends using Azure Storage integration with Azure Active Directory (Azure AD).

Set up SQL Clone Server (the Azure VM)

With the file storage in place, it is time for us to create the Azure virtual machine on which we’ll later install SQL Clone.

For this demo, we’ll also need to install SQL Server on the same VM, since we’ll host the source database that we wish to clone here (as well as SQL Clone’s config database). In the Azure marketplace, Windows Server images are available with SQL Server pre-installed. For this article I selected the following image from the Azure Marketplace and chose the appropriate plan:

Hit the Create button and we then need to fill out the details to create the VM, which will be linked to the SQL Server image.

On the Basics tab, we fill in the details of the name of the VM and where it is located and supply the credentials of the Administrator Account. We need these credentials for multiple steps in the rest of the installation, e.g., connecting via RDP, installing services, but also for accessing the SQL Clone Web UI from outside this VM

Now switch to the Networking Tab and leave all settings at the default, it will then create the following:

• A new Virtual Network, called <vm name>-vnet
• A new public IP address, this is needed to connect from outside Azure to the VM
• A Network Security Group rule for enabling Remote Desktop (RDP) to the VM

In the SQL Server Settings tab, we see the details of SQL Server image, which comes with SQL Server specific configuration options for SQL data (MDF), SQL log (LDF) and TempDB storage.

If you want to save cost, it is advisable to change the configuration of the server to suit your needs. The default options are 2x 1TB Premium SSDs, which are rather expensive if you are not using them and will result in unnecessary cost.

When the VM is created, connect to it via RDP and install SQL Clone server as described in the Redgate Documentation.

Create the Source database

To prevent Azure overspending, I host the source database, the one from which we will create images, on the Azure VM we just created and on which we will also install the SQL Clone service.

Any database will do and for demo purposes I downloaded the 10GB version of the Stack Overflow backup from Brent Ozar‘s website to the Azure VM. We can create the image directly from a backup, as Tony Davis explains in Database provisioning from backups using SQL Clone, but here I chose to create the image form a live database connection, so simply attached the files to my Azure VM-hosted SQL Server instance. Since I rather think of Whisky than of problems on Stack Overflow, I also personalized the database a bit and renamed it to TasteWhisky-10GB.

Mapping Azure File Share to the VM for storing the Source Database images

With a database up-and-running it is almost time to install our first SQL Clone Agent. This agent will be responsible for creating images. However, first we need to create a connection from this VM to the Azure File Share, so that this Clone Agent can access to store the image files.

Therefore, run the “connect” PowerShell script that we saved earlier on this VM. A successful run will result in a network-share with the letter Z. Note that, generally, SQL Clone Server, which runs the SQL Clone Service, does not need access to the file share; only the SQL Clone Agents need this access. SQL Clone Server is merely the coordinator and bookkeeper.

Please note that the PowerShell terminal on this Azure VM with Windows Server is always in Administrator mode, therefore the Z drive is not visible to the non-Administrator session. The easiest solution for this is running the script in PowerShell ISE.

Create a SQL Clone image

Now we can install the SQL Clone Agent and create our first image. You can find video tutorials on red-gate.com about this if you are not familiar with this process. Below you will find a quick rundown on how it should look if all is setup correctly:

In the Azure Portal under Storage Accounts > File Share a folder, with the image name and containing a Virtual Hard Disk (VHD), will appear. This is your first image, hosted on Azure.

SQL Clone User 1 – Azure VM

The next SQL Clone agent to install will be the one that is responsible for creating clones on the SQL Clone Use 1 Azure VM. First, however, another SQL Server installation is needed to host the clones. The easiest way is to repeat the steps from the SQL Clone Server section:

• Azure VM with a SQL Server 2019 image, with the appropriate plan.
• Tweaking the SQL Server Settings to save cost.

There is one extra configuration needed to get both the SQL Clone Server VM and this VM in the same network. During the configuration of the VM, do not choose “create new” but instead choose the virtual network that was created during the SQL Clone Server VM creation.

Now rerun the “Connect” PowerShell script to allow the SQL Clone Agent service on this Clone user Azure VM to connect to the Azure File Share. The same PowerShell / Administrator mode note applies here as well, and a successful run will again result in a network-share with the letter Z.

Now, since we earlier added our Clone user Azure VM to the same network as SQL Clone Server, we should be able to access we should be able to open a browser and access the SQL Clone Web UI using the private IP address or the computer name can, followed by port 14545. E.g., http://10.1.0.4:14145 or http://sqlclone:14145:

Since we did not setup an Active Directory or other users, we need to logon with the Administrator Account credentials of the SQL Clone Server VM. Please remember that this is not a best practice; it is only done to keep this demo simple. The best approach is to authenticate with Azure Active Directory.

From this portal we can start the installation for an Agent and let the cloning begin:

SQL Clone User 2 – Local laptop

Finally, to allow us to create local clones on my laptop we need to establish a connection to the SQL Clones Server Azure VM and, again, we’ll also need to install a local SQL Clone Agent with access to the Azure file share.

There are a few more steps and ‘tweaks’ involved in getting this final part working.

Add Network Security Group Rules

To enable connections from my laptop to SQL Clone Server, we need to tweak the network configuration of its Azure VM. The following statement comes from the requirement section of the Redgate documentation on SQL Clone:

“Firewall must permit incoming connection to Port 14145 (or the custom configured port) and port 14146”

These ports are needed for the SQL Clone Web UI (14145) and SQL Clone Agent connection (14146).

In our case we need to apply this firewall as an inbound security rule to the Network Security Group of the VM hosting SQL Server Clone. This way we permit connections from a machine outside our Virtual network to the Azure VM that hosts SQL Clone.

This rule is not needed for our SQL Clone User 1 (on Azure). For that User we rely on the Azure Virtual Network. The rule is necessary for SQL Clone User 2 (my laptop) which is not on Azure.

Installing SQL Server

For using the SQL Clone agent on the local laptop an important requirement is the version of the SQL Server. It must be the same as (or later than) the version of the SQL Server from which the SQL Clone image was created, or else you will end up with an error like this:

So, to match the Azure VM we need to install SQL Server 2019 (or up); e.g., SQL Server Express 2019 is sufficient.

Mapping Azure File Share to access the images

For the agent to get access to the images, we need to run the “Connect” PowerShell script again, but this time on the local laptop. Once again, a successful run will result in a network-share with the letter Z.

Connecting SQL Clone User Laptop to SQL Clone Server VM

In the previous steps we created an Azure Network Security Group rule for accessing the SQL Clone Web UI from outside of the Azure Virtual Network. To access the Web UI, the public IP address or the DNS name can be used followed by port 14545. E.g., http://sqlclone.westeurope.cloudapp.azure.com:14145 or http://104.45.28.229:14145.

In the Web UI, again logon with the Administrator Account credentials of the SQL Clone Server VM (again, this is for simplicity of demo only; the best approach is to authenticate with Azure Active Directory).

Installing the local SQL Clone Agent

From here we can start the final installation for an Agent. Start the installation process from the Web UI and the installer tries to connect to SQL Clone Server (in Azure) to install the Agent on the local laptop. Unfortunately, however, the installation hangs after a while:

If we look deeper into the network calls of the installer with Fiddler. We see that the installer is trying to reach the sqlclone via HTTP.

The reason for this is that SQL Clone is designed to run inside local networks. The address Sqlclone cannot be found on my laptop; this is a local DNS name that can only be found within the (virtual) local network. The fix for this is adding a new line into the hosts file (C:\Windows\System32\drivers\etc) on the laptop.

Please note that you need to run your editor in Administrator mode to save this file. When the Hosts file is saved the installation continues and the cloning from the portal can start again:

Conclusion

The conclusion of the whole exercise is that SQL Clone works in the Azure cloud! The biggest advantage is working with multiple environments with just one SQL Clone server. This applies to the remote working scenario but could also help you to get the latest database image from a customer environment to the shared location, the cloud.

Besides that, the cloud gives you the usual benefits associated with scaling on demand and so on. Of course, there are also some cons. It is not officially supported because it requires a few ‘network configuration hacks’ to get it working, setting up Network Security Group Rules for Web UI and Clone Agent connections, and tweaking the Local Hosts file, to connect to the SQL Clone Server on Azure. However, in my opinion, any IT professional can do this.

You’ll also want to do more research before implementing this solution, around issues such as latency. I didn’t experience any issues during my ‘proof of concept’, but neither was latency the focus of my research.

There are also some changes you will want to make to my security set-up before this solution can be used outside of your sandbox. For example:

• We worked around the authentication by entering the Administrator credentials of the SQL Clone Server Azure VM in all other sessions.
• Azure Files Share should be authenticated with Azure Active Directory members, not the admin access Storage Account Key for alle users.

Acknowledgements

I would like to end by thanking Chris Unwin for inspiring me to pull this trick. After reading his articles on achieving more or less the same with AWS, I set my self the goal to accomplish the same but with the Microsoft cloud: Azure. It surprised me how easy and fun it was to get a non-cloud product working in the cloud.

Tools in this post

SQL Clone

Create SQL Server database copies in an instant

Find out more