Voice of the DBA

Author: way0utwest

Automating SQL Clone Creation with PoSh

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.

Changing Development

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.

Moving On

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.

December 1, 2017
Hit Refresh

I read Satya Nadella’s Hit Refresh recently, and it was a very enjoyable book. He’s had an interesting journey and I do think he’s dramatically transformed Microsoft. The book is recommended.

Anyway, the Microsoft MVP Program asked is MVPs would share a refresh moment from their lives, so here’s mine:

Hitting Refresh, Over and Over

I think I’ve started to wear the finish off the refresh button, but it’s been a great life and career.

When I entered university at 17, I expected to study computer science and spend a career writing code for some organization. This was the era before the Internet when terminals were hardwired to mainframes, printers were housed in a separate room (with a page budget each semester), and my 300 baud modem allowed me to watch the screen slowly fill with characters. After two years of learning new languages and solving relatively simple problems, I hit refresh and decided finance and business were the direction for me.

I completed a degree in economics, but injured myself playing rugby during my final year. I hit refresh again, not wanting to spend most of my life draped in a suit and tie, pouring over financial documents at a bank. I took a year off, working, playing volleyball, and surfing at the beach. Midway through this year, I became bored and hit refresh a third time, returning to school to study computer engineering.

A year later, after securing an internship writing code and managing a Novell network, I hit refresh again, abandoning my schoolwork and going to work fulltime. I discovered SQL Server and hit refresh yet again. I moved into the data world, spending the next fifteen years working with SQL Server in a variety of industries and positions.

I hit refresh again after starting SQLServerCentral.com and having that become successful enough to support me in a fulltime role. I left employment for my own venture, which was a completely different type of work from writing code for an employer. I became an educator and journalist, seeking to help others grow their SQL Server skills. I was honored to be recognized as a Data Platform MVP by Microsoft for my work here.

Finally, I hit refresh one last time when Redgate Software purchased my business, and I became an evangelist for them. Today, I continue to educate and train others in Database DevOps, and hopefully won’t hit refresh again until I retire.

This post is part of the collection of #HitRefresh moments. Read more at
www.hitrefreshbook.com

November 30, 2017
Certifying Schools

We’ve debated the value of schools in technology careers plenty of times on this site. I have been torn on the value of schools, and I tend to go back and forth as to whether I’d recommend them for many people. Right now I tend to shy away from schools, mostly because I think the ROI is hard for a college. Working in technology can be a great job, one that pays well, but the cost of a degree seems high. Especially when there are plenty of ways that you can learn the same skills.

I’m not saying that university makes you a better or worse developer or IT professional. I think that really depends on the individual. Plenty of smart people go through a university curriculum and go on to build great systems and applications. The same is true for self-taught individuals that learn on the job. Ultimately, you need to take responsibility for your own career, your knowledge, and your education, both in getting started and continuing as long as you work in this field.

However, I do think that your responsibility also extends to researching and learning about the way in which you choose to learn. If that’s a school, be wary of their claims. I ran across this piece about a school that was fined for their marketing. They didn’t quite lie about the results from the curriculum, but they were deceiving. I think everyone should understand that finding a job can be hard in your area, especially when you are getting started. Whether you come out of a school of some sort, or you’ve been building your own projects. Ultimately, you need to convince someone you can do the work they need done in a competent manner at some reasonable price.

Education has become a business, one where the profit and loss seem to be more important than the results experienced by their graduates. Some of the lack of success by schools is because students don’t make enough of an effort to become good at their craft. I see plenty of evidence of that, even in professional places, where workers aren’t learning to become better at their craft over time. That’s something we can all do, and it’s something I ask you to do on a regular basis throughout your career. However, some of the issues are inherent in modern curriculums.

As we look for more secure coding practices, ask for more testing, and still see frameworks and platforms continue to advance, I don’t know if schools can keep up? I’d like to think that there are enough professional teachers that could update and alter curriculums on a regular basis, perhaps even every semester, to adapt to the changing workplace. I’d like to see more practical projects, still taught using basic, well known principles in computer science, but using modern platforms. That might change the nature of the professor in colleges, but perhaps groups of colleges should jointly develop curriculums and keep them current over time. There’s still a place for teachers to learn and bring their skills to teaching, but using information that is evolving too rapidly for one person to manage.

I don’t know if we’ll get there, but regardless, each of you needs to look to improve your skills over time. Don’t expect that what you knew last year will be good enough for next year. Continue to learn, grow, evolve, and question the ways in which you build software and systems.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

November 30, 2017
SQL in the City 2017
It’s almost time for SQL in the City 2017, coming Dec 13, live from Cambridge, UK. I’ll be traveling over to England for the event, and you can stream it to your office live. Once again, we’ll be presenting the conference twice, 11am and 6pm (both GMT), and you can pick the times that work best for you.

We’ve got a variety of sessions scheduled, and you can tune in to ask questions live, which we’ll try to answer. The full schedule is on the site, but here’s a few sessions you may want to fit in:
- Keynote  – Data Privacy and DevOps
- Why code standards are important and how code analysis can help
- From DBA to DevOps hero – rapid database provisioning with SQL Clone
- What SQL Server performance metrics should you monitor?
- Extend DevOps to the database by automating your deployments
We’ve been working hard at Redgate to improve our tools and build new ones that will need the needs that many organizations will have as GDPR takes effect next year. And, as always, we’re trying to save developer time with all our tools.

Take a training day to learn a bit about privacy, security, DevOps, code analysis and more. We’ll show how Redgate can help solve some of your issues along with plenty of good general information.

Hope to see you online, Dec 13.
November 30, 2017