Voice of the DBA

Author: way0utwest

A Christmas Bug

It’s not really a big deal for me, despite the fact that I travel to Europe in a few days for two weeks. At least, I’m fairly confident this won’t be an issue for me. A few of my fellow #sqlfamily might not be so lucky, as they use a different airline.

Apparently American Airlines has a bug in their scheduling system. It’s not a safety bug, don’t worry about that, but it’s potentially a huge bug for the company in terms of profit and cost. The bug is a part of the system that lets pilots request holiday time off. The system allowed too many pilots to take vacation, and as a result, American Airlines (AA) doesn’t have enough pilots to fly their planes.

In case you wonder about the scope of this, it’s 15,000+ flights that are affected. AA flies about 6,700 flights a day and are the world’s largest airline by some measure. They are offering pilots 150% of their normal rate, which is apparently the max allowed by contract. I’m sure some pilots will take the offer, but I’m also sure there may be some flight cancellations. After all, pilots have likely made their own plans, which they may not want to change. Apparently some have as the news as of this writing is a few hundred flights.

The complexity of airline scheduling software has to be quite high, and while this won’t really endanger lives, it will affect the profitability of AA. This is definitely a development mistake, and one that should have been caught in testing. I don’t know how often this software is updated, nor what their process is. Someone suggested this is outsourced software, but that doesn’t matter. We clearly have a software development failure here.

I certainly think developers are responsible for testing their code and meeting requirements. This includes not assuming the happy path is followed. However, this is also a place where we need (as an industry) to learn to better test the bounds and limits of our software. We need to be sure that we account for not only security, but pushing the limits of use by legitimate users. Software mistakes cost money, and as we continue to use computing more and more, we need to become better at testing our code. Our systems are very complex these days, more complex than a single person can handle. We need better testing everywhere, including the database.

Please, make an effort to test better next year, in both your application and database. Use larger data sets at some point (both numbers and dates), add automated testing as a part of your development routine, and assume that users will do stupid, silly, and malicious things with inputs.

Steve Jones

The Voice of the DBA Podcast

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

December 4, 2017
Not Useless Features

Some time ago I noticed Adam Machanic had written a post titled The SQL Hall of Shame. In it, Adam notes there are features that Microsoft will introduce in SQL Server and then let die. These are the useless features that aren’t widely used, or even useful for many projects. You can read his list, and agree or disagree.

I think there certainly are some features that were silly experiments and I wish they had been introduced as experimental, or beta, features early, allowing users to give feedback on whether they’re useful and where they need refinement. I certainly think too many resources were initially wasted on items like MDW and Query Notifications, and far too little investment later. These days I think we’ll see more early releases of items in Azure where users can experiment and provide feedback to help Microsoft decide if more investment is needed. At least, that’s my hope.

Today I’m wondering if you think there are features in SQL Server that do need more attention. Those features you use, but appear forgotten and you’d hope they are improved in the next version.

For me, I certainly wish replication would get more attention, tooling, and resilience. I’m not sure if this needs an overhaul, like SQL Server 2005 was, or there just needs to be some reworking of the code, but in today’s distributed world, we need a more reliable way to hook up portions of databases and move data around. Not ETL, not Availabilty Groups, a better replication architecture and implementation.

There might be other features you use or want, but let me know today. What’s a not useless feature that you want improved.

Steve Jones

The Voice of the DBA Podcast

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

December 1, 2017
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