Cloning From Home… a consideration.

“I know there’s a proverb which that says ‘To err is human,’ but a human error is nothing to what a computer can do if it tries.”
– Agatha Christie

Working from home is fine because we still have the right connections in place. Televisions to watch only the most stimulating and educational shows, telephones to get instantly in touch with those we hold most dear and crucially the internet to do everything we can to still be as (and sometimes more) productive at work as possible.

But it’s happening more and more now. We have to work from home and this is starting to turn up some problems.

And no. I don’t just mean that my wife is now acutely and accurately aware of how annoying I am.

Judge Judy Reaction GIF

I’m talking about clones. Specifically SQL Clone, and clones do not work great from home.

Now for anyone out there who has never heard of SQL Clone, where have you been?? It’s an incredibly intuitive, reliable tool for rapid provisioning of database copies (*cough* definitely not just lifted from the website) – in any case, it’s pretty darn cool.

One of the coolest things about this technology though is how it seamlessly plugs into the Microsoft Ecosystem by leveraging the VHD(X) technology available in x64 Windows. This means there’s no special file systems, no hard or software “appliances”, it’s very much plug and play (with a little tinkering around ports 14145 and 14146 of course!)

Naturally though it does come with it’s challenges, as does all technology, and it has been highlighted recently more so than ever.

The Problem

Whilst SQL Clone is a beautiful, elegant and easily automated answer to the database provisioning problem – there are 2 gotcha’s that you must be aware of when you start using the solution. SQL Clone relies on the relationship between the Image file (the centralized, virtualized parent, as it were that exists on a windows fileshare) and the Clones themselves (the 40mb diff disks on the hosts).

Now many people choose to put Clones onto their workstations, which for many of us is our laptops. We have SQL Server Developer installed and we pull down a few clones for different branches, for testing etc. etc. and all is well with the world.

When you’re in the office that is.

When a user queries or works with a SQL Clone and it requires any of the schema/data that was in the original copy and is not in the changes the user has made, a call is made back to the image file (the VHD mounted copy) to fetch it. When you’re in an office setting (with your cup of coffee in one hand and a colleague sat next to the other telling you about their weekend) this is fine because you’re connected directly to the company network and therefore the link between the clone on your laptop and the image file on the fileshare is short, strong and stable.

At home though this isn’t the case. Many of us work on VPNs that are “ok” on internet connections that can only be described as temperamental. So what happens when a clone tries to call back to the image file across this VPN, which is now much further away, across a sea of uncertainty and poor connection?

Bad things happen. Either the Clone cannot connect to the Image and it decides it no longer knows what it is, and it falls into Recovery Pending for a while until the connection is re-established, or if the connection is present it is just so slow.

sloth dmv GIF

This isn’t a fault of the tool, I hasten to add, it is just the nature of the technology. Much as we would expect our RDP sessions to be a little laggy if we were based in Greenland and the server was in Australia, it is just part of life.

So… are there solutions? You bet there are!

Option 1: The “jump box”

Many people I have worked with have found that they are still able to leverage SQL Clone whilst working from home by reducing the physical distance between the Clone and the Image, and have done so by introducing a Dev/Test “jump box”.

The way this works is by having an instance of SQL Server available within the company network onto which the Clones are provisioned.

This works great because it means that the link between the Clone and the Image is once again short and strong and stable, relying on the company network, but you can easily connect to or RDP onto this jump box if you need to work with them. Still using your VPN and internet from home? Check! Able to work with a Clone now though? Check!

Option 2: The Cloud

Welcome… to the world of tomorrow!

season 1 sb 129 GIF by SpongeBob SquarePants

By which of course I mean, the world that has been available to us for a while. Infrastructure as a Service (or IaaS) allows us to very easily spin up an additional dev/test server which can be used in the interim. Whilst this does incur a little extra cost in infrastructure it still means that you won’t need as much space on the VMs themselves, thank you SQL Clone!

As long as you have a fileshare available in say Azure or AWS and a Windows VM you’re pretty much good to go and once you’ve got Clones and Images up in the Cloud, you’re reliant on the networks of the providers, and I’ve got to say, they’re not too shabby at all!

Sneaky side note: ALL demos I give of SQL Clone actually run on an EC2 VM with a 91GB Image on a fileshare and it works great!

Option 3: Roll-your-own solution

Ok. I realize this one is not really a solution. But the thing we’ve highlighted here is that it’s all about the distance between the Clone and the Image. Those two love-birds cannot exist long distance.

So if you might have another ingeniously simple way of solving this, I would recommend having a read of this magnificent document written by an incredibly clever ex-colleague of mine and the “How It Works” documentation and then let me know what YOU come up with:

Links: Best Practices for SQL Provision and How It Works

Conclusion

There are a few ways of getting around this problem but they’re not always obvious or clear, and at this trying time, you just want solutions you have in place to work. We all do.

So if you are working with any of Redgate’s tools, or even if you just have a question that I or a colleague might be able to help with – please reach out to us, talking to us won’t cost you anything, but it sure as heck might gain you something!

Stay safe, stay well and have a great week!

Me on Twitter: @PlantBasedSQL
Redgate on twitter: @Redgate
Redgate Support Team: support@red-gate.com

Using things weirdly – Part 1: The SQL Clone Config Database

“If the challenge exists, so must the solution.”
Rona Mlnarik

I like to use things in the way they weren’t intended. It leads to behaviors you weren’t expecting… and that’s pretty fun.

Often though, when you work for a software company, people want your solutions to do things they just were not designed to do, or have no capabilities to do at that exact point in time. The answer then that will often come back to you is a resounding: “I’m sorry, that’s not available at this time.” of course that is if you’re lucky enough to get an answer at all.

It’s no secret though, none of us who answer those questions like saying that. There is nothing worse than not being able to help people with what they’re trying to do.

Now don’t get me wrong – some people’s questions should be challenged. You cannot immediately assume that the person you’re talking to has all of the available variables, or has thought of any alternative means of achieving their end goal – which is why it is always important to ask ‘the why behind the why’. Every question has at least one immediate answer:

“I am going to buy a car.”
“Why?”
“Because I want to drive to work.”

This seems like a perfectly reasonable response. But what if we haven’t thought about every available option here? I can take this at face value and sell you a car, but i haven’t asked where you live and work; perhaps you live within the congestion zone in London where driving is impractical. Do you actually have off-street parking available? Will you need the car for other trips or just for work? With a little additional questioning and thinking we might establish that you live right near a London Underground station and it would be more cost effective and slightly faster to instead commute by Tube.

This is not just a sales tactic, rather one that should be used to better understand ever requirement, personal or professional – there is no substitute for good understanding.

But there are times when a perfectly reasonable request is made with good justification and unfortunately you’re still unable to help. But sometimes there are still ways to help, even if it doesn’t natively fall within the remit of the solution you’re working with. I had one such question recently:

“I would like to receive a list of Clones per image that have been created, which are older than 2 weeks and who created them, so I know which developers to nudge to refresh their Cloned environments and so we can create new images.”

Again this is a perfectly reasonable request, and the obvious answer was, well, “check the dashboard”, there’s a full list there with all of that information. When you have a good many developers all using SQL Clone though, it can get a little difficult to scroll through everything and identify the culprits. SQL Clone has no native alerting or reporting (as of writing) so it was assumed that was the best we could do.

Like I said though, I like to use things as they weren’t intended. Tee-hee.

When you install SQL Clone it creates a back-end SQLClone_Config database on an instance of your choosing (normally where you’ve installed the solution) for recording operations carried out within the software. This database is structured really, really logically that anyone can understand it and leverage it to meet their requirements.

By thinking a little more about their requirement we realized that it would actually be possible to leverage the database by running a very small snippet of T-SQL which could show us:

  • The name of the Clone
  • Who created it
  • Which Image it belongs to
  • When it was created and,
  • How old it is

This was as straightforward as:

SELECT C.Name                                AS CloneName
     , C.CreatedBy                           AS CloneOwner
     , I.FriendlyName                        AS BelongsToImage
     , O.Timestamp                           AS CreatedOn
     , DATEDIFF(DAY, O.Timestamp, GETDATE()) AS AgeOfCloneInDays
FROM dbo.Clones              C
    LEFT JOIN dbo.Images     I
        ON I.Id = C.ParentImageId
    LEFT JOIN dbo.Operations O
        ON I.Id = O.ImageId
WHERE O.Type = 1
      AND O.Timestamp < DATEADD(DAY, -14, GETDATE())
      AND C.DeletedOn IS NULL;

This gave us exactly what we needed:

It was just that simple. But we didn’t want to leave it there! So we set up an email to be sent to the heads of each development team from a SQL Server Agent Job with the output of this query each Monday morning so they could raise it with the relevant members just after stand up. Now I had never done this before so of course who would come to my rescue but the wonderful, knowledgeable (and just all round awesome-sauce-person) Jes Schultz with this post from 2014!

And with that – we used something that isn’t exactly supposed to be used in this way, but got something valuable up and running for their teams. It’s a simple answer, but it was quick, and it worked.

What’s a quick win you’ve had recently?