I was interviewed in Redmond a couple months ago and the video was just published. I show and demo some database migration work with ReadyRoll in this video:
https://channel9.msdn.com/Shows/Visual-Studio-Toolbox/Database-DevOps-with-RedGate-ReadyRoll/player
I think SQL Clone is one of the game changing products from Redgate. This product really fits into a DevOps mindset, allowing me to quickly and easily build (and rebuild) a dev database.
While the agent web pages make this easy, they’re slightly cumbersome and the PowerShell cmdlets fit better with a DevOps flow. In setting up a Query Store demo, I found myself changing some data and needing to reset my database rapidly, so I built a quick PoSh function to do this for me.
Here’s my function:
function Add-ADWClone {
param([Parameter(Mandatory=$true)][string] $CloneName)
$mycredential = Get-Credential
Connect-SqlClone -ServerUrl ‘http://socrates:14145’ -Credential $mycredential
# remove the image if it exists
$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName Plato -InstanceName SQL2016
$Clones = Get-SqlClone -Location $SqlServerInstance
if ($Clones.Name -contains $CloneName) {
$CloneToDelete = Get-SqlClone -Location $SqlServerInstance –Name $CloneName
Remove-SqlClone -Clone $CloneToDelete | Wait-SqlCloneOperation
}
# Create the new image
$image = Get-SqlCloneImage -Name ‘Adw2014Base’
$sqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName Plato -InstanceName SQL2016
$image | New-SqlClone -Name $CloneName -Location $sqlServerInstance | Wait-SqlCloneOperation
}
In this function, I’m taking the name of a cloned database. I’ll use that to check if the clone exists, and if so, remove it. I do this by using Get-SqlClone. Once that’s done, I get the image, which is static here (this is a function for a specific project) and then I’ll create the new clone.
This works great. If I run the command, I’ll get a clone being created. You can see my client in the back creating the clone database.
If I re-run the command, I’ll see the delete.
And then the clone create again.
When I first saw a prototype a few years ago, I could immediately think back to being a full time developer and the hassles of manipulating my development database, making data changes to test code, trying to reset them, writing scripts to undo changes and more. At some point I tried to perform backups and restores of a standard database, but I’d keep forgetting to update things.
SQL Clone makes this easier, and together with some way to push/pull code from a VCS to your database, it means that I can quickly reset a database back to a known state. One of the things that I’d like to easily do is whack my development database, recreate a new one, and then move on with writing code. If I make a mistake, I repeat the process.
This is a basic Proof of Concept, something I just whacked together for a project, so I’ve coded in the image name, and I get the credentials from the user. I could clean this up, and have it as a simple up-arrow, enter from the PoSh command line that I use as I need to reset my system. Or code in a saved, secure credential that lets me double click some batch file to run this for me.
Automation speeds up development by removing simple tasks from the developer. SQL Clone makes it easy to reset my database to a known state (the image) and create new databases as needed.
If you haven’t tried SQL Clone, give it a try today by downloading an evaluation.
I read this nice piece on CosmosDB and setting performance levels. It covers how you set some level of performance for Request Units (RU) and CosmosDB handles the rest. Great, right? Set the level of performance you need and that gets handled. The question I have is how do you know what level of performance you need?
This is one of the issues with the Cloud services that I see. It doesn’t matter if you use PaaS or IaaS, most of us really don’t know what level of performance works. We tend to guess, and far too many of us don’t use regular monitoring to decide if we are over or under powered. I find many DBAs and sysadmins would prefer to be over-provisioned and then let the server trundle along at a lower resource usage so that no one complains.
When we move to a cloud type service, we tend to be more cost conscious, which makes sense when we’re paying by the minute. We want to minimum level of hardware we can get, but we don’t want to cause unnecessary complaints, either from customers because the system is slow, or from the CFO because costs are high. Using your old method of over-provisioning hardware (or DTUs) usually causes complaints from the CFO.
The piece goes into some ways that you can start to evaluate your performance level for CosmosDB, and how to deal with throttling while you tune your RUs with a new application. This works great if you’re in a (more) greenfield area of development. Not so great if you’re lifting and shifting some application from another platform. Then we might need to ensure that we are responding quickly to issues, or better yet, have scripted responses that scale up or down.
The same thing should be done for our relational systems. The Ops part of DevOps needs to be using monitoring and instrumentation to measure performance, adding capacity as appropriate, which should be before users realize there’s an issue. With today’s virtual systems, adding CPU and RAM usually is fairly easy, and it’s easy in the cloud as well.
Of course, all this monitoring isn’t just to add capacity. Having a better sense of what’s going on can help you pinpoint poor code. Getting someone to fix that code becomes a lot easier if you can show that better code would cost less for our systems. It can be amazing how much more developers care about their code when the CFO gets involved.
Steve Jones
Listen to the MP3 Audio ( 3.6MB) podcast or subscribe to the feed at iTunes and Libsyn.
The travel countdown is down to 2 trips this year. The second to the last is this week, as I’m off to DevOps East, a conference in Orlando, FL that tries to bring together lots of DevOps experts and advocates to share ideas and learn more. This is my first time at this event, and I’m looking forward to talking DevOps for a few days.
I’ll be in Orlando all week, and then I have a month off before one last trip for SQL in the City.
If you’re at the conference, say hi. And certainly talk databases with everyone that you see.
Voice of the DBA 