Phil Factor

10 January 2020

• SQL Clone
• Development and Testing with Clones

Cloning all the Databases on a Server

A database application often consists of several databases co-located on the same instance and you usually need to deploy them as a unit. During development, this means that you may need to create copies of all the databases on an instance, or a selection of them, on your development and test servers. During testing, you’ll probably want to make changes to one or more of these databases, before quickly resetting them back to their original state. Of course, you’ll also need a way to keep every copy of every database up to date with the changes in the latest build.

With a database application that is designed to run within the same server or instance, there will be times, as when doing full integration testing, that you need to copy not just the databases, but also all the other SQL Server-based objects required for a fully functioning database system, such as linked servers, scheduled jobs and alerts, maybe to several destination servers or instances at once.

In this article, I’ll use SQL Clone and some PowerShell to build a special-purpose, stand-alone way of automating the maintenance tasks for an installation of development or test servers for a multi-database application.

Cloning or containers?

If you need to copy an entire instance, then containers might seem a more natural choice for this, seeing that the application is much more likely to work straight away, after being containerised and then deployed. Where a build is particularly complicated, with service broker, several instances, middleware and so on, it might prove to be the case, but otherwise using containers isn’t necessarily the best choice. A container holds everything, including logins and other artefacts that have a security implication.

Given sufficiently privileged roles for development work on the container, you can, for example do an entirely undetectable brute-force attack on the containerised server to get all the original logins. There’s a lot more penetration you can do besides, but this is no place to go into detail. Suffice to say you need to clear it with your security people first!

The other problem is size, with some containers being relatively gargantuan for an enterprise-scale database application. You can’t share a docker container in the can a SQL Clone image, and you can’t squirrel them away on a massive network storage system. They’re on your servers, and if you have ten developers needing their own containers that is ten elephants in the room. Then, when you need to work on the next build, you suddenly have twenty elephants until you can swap out the containers and do some housekeeping.

The SQL Clone approach requires more up-front work and checking, but once it is running, it is cleaner, and probably safer. This up-front work will include ensuring that all the server-based components that are necessary for running the application are there, as well as all the databases.

To check the differences in the configuration of a working source server and the target server to which you’re cloning the databases, you will need something like this, Comparing SQL Server Instances: Objects by Name.

If some of the required server components that are missing, then you’ll need to copy them across. My article Scripting Out a SQL Server instance provides a PowerShell script that uses the sqlserver module to script out all the main server programmable objects (Agent jobs, Extended Event sessions, triggers and the like). It generates the scripts that you can then use to create the objects you need. You wouldn’t want to copy everything, just the things you need that aren’t in the original server.

Having done this, you can then use SQL Clone to create images of all the databases from the source server and distribute clones of all the them to all your development and test servers.

Automatically cloning all databases on an instance

With SQL Clone, creating all the clones is the relatively easy bit but there are still several other questions to consider regarding how and when you create and refresh the images and clones. In Grant Fritchey’s article, running his script always recreates all images and all clones; if you want to reset clones back to their original state, without touching the image, you do it via the GUI.

My approach is different. I use SQL Clone to distribute clean copies of all the standard practice databases to all my development servers. I like to ‘reset’ the clones occasionally because they tend to get practiced on in a rather destructive way, but I only rarely need to refresh the images. Therefore, each time you run the routine I provide, it will create images only if they don’t already exist, but it will always drop each existing clone and recreate a new one, from its current image.

If you’re using SQL Clone to roll out a daily build to several development servers, then you’d want to create new images from all the latest builds, as an overnight task, and then distribute fresh clones to all the servers. However, you can’t delete images if there are existing clones on them so before you kicked off the routine, you’d first need to delete the existing clones and their images, via SQL Clone’s user interface.

Remember also that this routine, as is, will remove all the current clones, even if one of them has a day’s work unsaved on it. I’ve shown in a previous article, Safely Deleting Clones and Images during Database Development and Testing, how to save unsaved work just in case, so I don’t need to repeat that here.

I present this PowerShell script ‘unwrapped’ so that you can alter and debug it easily. This is not, as one kind reader suggested, because I’m unaware of PowerShell functions. You will need to fill in the data structures at the start of the script with the details of your Clone installation, servers and logins.

The routine will check which databases you want copied from the source instance. You can specify all, or a subset, via one or more DOS-style wildcards in the variable $DatabasesToCopy, or you can just provide a list of database names. So, for example, ‘*’ means all the databases, '*New','*Old' would be all databases ending in ‘Old’ or ‘New’, and 'Dave','Dee','Dozy','Beaky','Mick','Titch' would clone just those databases in the list.

With this list, it looks on the image directory to see if there are current images for all the databases you’ve requested. If not, it creates the image for the database. Then it looks at your list of destinations, and, for every instance that you specify, it creates a clone of each database you need. If there is already a clone there, then it drops it and recreates it.

To keep the script relatively short, I’ve not included the facility of running special purpose scripts on the individual clones as we create or delete them.but I describe how to do that in my previous article, Scripting Custom SQL Server Clones for Database Development and Testing. If you prefer the facility of being able to specify the original databases to copy by wildcard, as I’ve done here, then it is trickier to specify individual pampering of individual clones, as we create them.

Conclusions

If you need to make copies of all the databases required by an application, you could simply do it by hand, by restoring backups. However, using SQL Clone offers two big advantages. The first is that a chore that happens regularly, maybe daily, is best automated for the sanity of all concerned, and for ensuring the accuracy of what is done. The second is that by sharing the image across a network, as SQL Clone does, there is a big saving in the space required to store the copies, and the time it takes to deploy create, drop and reset them.

As the integration of new features gets more rapid, there is less time for artisan admin work. The script that you use needs to be quick, flexible, and expandable to allow for rapid changes and new demands. It needs to allow for pseudonymization, migration scripts and sophisticated test requirements. Whichever way you do it, it isn’t likely to be easy to regularly deploy an entire server and databases for an application, especially if you discover the necessity of doing it some time after the development project has started.