SQL Clone is a neat product from Redgate that I wish I’d have had when I was doing database software development. It lets me have a consistent image for all developers, and create/reset databases to that starting point in seconds.
There are two parts to this process: image creation and clone database creation. I’ve written about both in different places, but in this post I want to tackle a weekly image creation process with some tips and recommendations for how to handle this.
If you want a basic image creation post, read Creating a SQL Clone Agent and a First Image.
The Goal
There are a few goals with a weekly image refresh process for database developers:
- I don’t want to interrupt developers’ work
- I want consistency that allows other people’s scripts to just run
In this case, as I create a new image with updated schema and data, I don’t want to require developers to stop working for me to update the image. I also don’t want them to stop while I switch out images. This means I need multiple images for a short period of time.
The other thing, which wasn’t a recommendation early on, was in naming. Lots of early customers, and us Advocates, were naming images with timestamps or some unique value. However, in an ongoing process, this doesn’t work well.
This post is the result of some learning, experiments, and feedback from customers.
The Process Outline
Rather than start at the beginning of a project, let’s assume we are in an ongoing development process. There is an image, and multiple developers are using this in their cloned databases. For simplicity, let’s say I have this setup:
- A production database – ADW_Prod
- Developer Kathi, with a cloned database against ADW_Current – AWD_Kathi
- Developer Grant, with a cloned database against ADW_Current– AWD_Grant
- An Image, ADW_Current
Given all this in use, how do I update ADW_Current with the latest version of production?
The basic process to follow is this:
- Create a new image from production, ADW_New
- Check if there is an ADW_Old image.
- If so, remove the cloned databases from ADW_Old
- remove the ADW_Old image
- Rename ADW_Current to ADW_Old
- Rename ADW_New to ADW_Current
That’s it. In an ongoing process, I need image rotation, hence the _New->_Current->_Old. If there isn’t an old image, I skip a couple steps.
In this process, developers that are using the current image, ADW_Current, are left alone, though they are now using ADW_Old as the image.
If developers are 2 versions back, on ADW_Old, their databases are dropped. I could deploy new copies of from ADW_Current (or ADW_New), but really, I want developers to be thinking about saving their changes in a VCS often, and not making special little databases they keep for days or weeks.
Really, I want a developer to finish some work, commit it, and then destroy and recreate their dev database. That’s the whole point of SQL Clone. In about 7sec, I have a new copy of the database. I can then pull everyone else’s changes from VCS and be up to date.
The Code
How does this work? Well, I have a single script that I added to a repo on GitHub. I’ll use some images here to show parts, but get the code from there.
The newimagerotation.ps1 is the script you want. In here, I have some help at the top to give you parameters from PoSh. Then we set some items.I set defaults and then add some standards for my New/Current/Old structure. Feel free to change if that doesn’t make sense to you.
The next part is where I create the New image. If this exists for some reason, like an error, I remove it. Possibly you want to check if there are clones against this and stop, but I never want someone using this.
After this, we want to rename the current image to old. However, if an old image exists, we remove it. Before we can do that, we need to loop through and remove cloned databases. Protection against someone accidentally removing an image, but I am purposefully doing it here.
Once this is done, we rename the new to current, and we’re done.
Summary
This is an easy process to follow weekly, and it rotates your image so that if people are using scripts or the GUI, they always know to use the _Current image to create a new database.
This also gives developers a grace period that equals your image refresh process for using an old image. If you run this daily, they can use an image for 2 days. If you run it weekly, they can use it for two weeks.
If you want to warn them, add a call in the “rename current to old” section to send a message to developers that there are databases that will be removed when the next image is created.
If you haven’t tried SQL Clone , download an eval and give it a try today. It’s a great way to speed up developer’s experimentation and ensure consistency in dev and test environments.
Voice of the DBA 