2016 in review

Lots of interesting things happened this year in the world of data.

As 2016 draws to a close, I’d like to encourage you to find a piece of Azure SQL Database, or SQL Server, and play with it, understand it, and teach someone else how it works.

One of the projects I’m looking forward to in 2017, is dealing with a massive volume of data from Internet of Things (IoT) devices. SQL Server seems up to the task.

See you on the other side!

Remote Storage Sync and Restore updated

Recently, I presented my “Back up and Restore your Database with Azure Blob Storage” session to the Bellingham PASS Chapter.

There are two components to the Azure Blob Storage Sync and Restore utility: Sync and Restore.

I wrote the Sync tool to work around difficulties I’d experienced with Azure Blob Storage’s Infinite Lease, and limitations in AzCopy. Lately these problems have been made mostly redundant, but the Restore tool remains extremely valuable, and I use it regularly for testing backups.

I’m happy to announce that new versions of both the sync and restore components have been released today, and they have a new name.

Both include some new features, which I’d like to highlight below, mostly to the Restore tool.

New Features

Point-In-Time Restore

During the Bellingham session, one of the questions asked was whether the Restore tool could perform a point-in-time restore using the RESTORE ... STOPAT keyword.

You can now perform a point-in-time restore using the Restore tool, and it will only download the files from the latest backup that it needs, up to and including that point in time.

Note: This feature only works on the latest available backup. A feature enhancement will be included in a later release to perform a point-in-time restore for any backup, if the requisite files exist.

Download Indicator

It’s handy to know how fast a file is downloading, but there aren’t easy ways to do this on a command-line tool, and it can get confusing in a disaster recovery scenario if you don’t know if the download is stuck.

AzureBlobStorageRestore will now show your download progress, using a rotating cursor similar to one I first saw on the MS-DOS version of Doctor Solomon’s Antivirus.

Big shout-out to Daniel Wolf for his ProgressBar gist (available free under the MIT License).

Sort Order

Previously, the Restore tool would download files from smallest to largest, but this could be disconcerting in a disaster recovery scenario as it might look like files are missing.

Now files can be downloaded in alphabetical order, or the default of smallest to largest.

One More Thing

There’s a lot of emphasis on off-site storage these days, and not everyone uses Azure Blob Storage.

For example, one of my customers has a 5TB file share, mapped using UNC (in the format \\server\share) and wants to copy files there.

What if you already keep tape backups, managed by a company that takes your tapes off-site in a fireproof lockbox, and all you need to do is get the files to a network share to be backed up to tape?

What if you just want to copy your database backup files to a local network share as well as Azure Blob Storage?

What if you know you should be moving your backup files to a network share but keep forgetting to do so?

More importantly, what if your backup files are already on a local network share and you have a disaster recovery situation. Downloading from Azure Blob Storage will take too long, and you just want to restore the latest backup from a local network share.

Introducing File Storage Sync and Restore.

FileStorageSync

Like its older sibling, FileStorageSync synchronises the contents of a local directory with a network share using a UNC path (in the format \\server\share). You can provide a username and password as well, which is convenient for creating scheduled tasks. All other features from AzureBlobStorageSync are included.

FileStorageRestore

Like its older sibling, FileStorageRestore parses all the files in a UNC share, and downloads only the the latest full, differential and transaction log backup files required to perform a restore of your database.

It also includes Point-In-Time Restore (for the latest backup) and Sort Order customisation.

A download indicator is not provided in this version because local network speeds are usually so quick that it would add unnecessary overhead.

All other features from AzureBlobStorageRestore are included.

Note: A future enhancement which restores the latest backup from the UNC network share, without copying the files first, is coming soon. You will be able to restore your database as fast as possible.


If you want to play with the new features, you can find them in the GitHub repository.

This project is released free under the MIT License (free for commercial and non-commercial use), and all you have to do is give me credit.

Find me on Twitter at @bornsql if you have any thoughts. Feature requests can be submitted on the GitHub repository itself.

A look back, and a look forward

Time flies. My father used to wear a t-shirt that claimed, “When you’re over the hill, you pick up speed.”

I’m turning 40 in a few days. I still feel like a teenager in many respects.

SQL Server, released in 1989, is 27 years old now. It’s about the same age I was when a career change became necessary. I became a lecturer, briefly, then a high school teacher, and a tutor on the side, sharing my knowledge, all the while learning how people learn.

Combined with my so-called “train the trainer” learning a decade prior in college and the help desk support job from around the same time, it allowed me to unlock ways to explain technology to people. In so doing, I realised that I had a real arrogance about technology. Whereas I wanted people to bend to its ways, I realised that it was technology that had to adapt. Now when I build products, the aim is to be as simple as possible.

When I went back into the tech world after teaching (for money, as it turns out—it’s always about the money), I tried to keep this new perspective. We need to review our thinking and assumptions more often than we do.

SQL Server, and Azure’s Platform as a Service, have come a long way in a relatively short amount of time. Things we had to worry about as little as four years ago are being made much easier by the newest versions. Self-tuning databases, the Query Store, In-Memory OLTP, and recent changes to SQL Server 2016’s Service Pack 1 are all things Microsoft has learned and borrowed from competitors and customers alike, to produce probably the best relational database engine currently on the market.

Microsoft challenged their own assumptions. The market required it. Five years ago it was unheard of, the thought of Azure hosting Linux and Windows equally, with NoSQLs and Red Hats and Oracles and SAP HANAs, oh my! We even have Linux on SQL Server now. I still don’t know what it’s for, but it’s really very clever how they did it.

(Full disclosure: I didn’t know what cameras on phones were for, either.)

I’m a technology Luddite. I literally break technology more often than I care to admit, though admittedly not because I think it’s taking my job. I call myself a living edge case and a human beta tester. It’s usually accidental, but if something is going to break, it’ll break around me.

As a result, while I tend to have the latest shiny computers and electronics, I write my important notes in a Moleskine notebook I carry with me. It was a gift from my spouse and has Smaug on the cover. It has no lines on the pages, because sometimes I need to sketch things in meetings. I prefer a resilient hard copy any day of the week, and flipping back the pages of the book has a certain feeling that technology can’t touch.

When SQL Server 2000 was released, Microsoft’s marketing claimed that the database server could look after itself. Of course this turned out to be false, and I was still working with 2000-era databases until quite recently. Marketing played nicely into my bank account, thank you.

However, the trend towards not having to manage databases is continuing apace. Azure SQL Database takes much of the administrative burden away so that most people who need a database can get one easily, without concerning themselves about maintenance tasks and how the data drive was formatted. Technology is getting better. It is adapting better to people.

Looking back on 2016, I recognize my only fondness for past eras of computing is borne by nostalgia. SQL Server 2016 is the best version of the stand-alone product. Azure SQL Database keeps getting new features so frequently that it obsoletes my blog posts, sometimes only days later.

I am looking forward to the new challenges this brings. I feel overwhelmed sometimes, but I know I’m not alone. There will always be performance issues, and database corruption, and customers not taking or testing their backups. I’ll find a gap there, and as for all the other stuff, I’ll keep learning.

If you would like to chat about this some more, feel free to contact me. I’ve taken a break on Twitter, but you can find me there at @bornsql , and I respond to direct messages.

SQL Saturday in Calgary!

I am very happy (and terrified) to announce that Calgary will host its very first SQL Saturday, #607, on 29 April 2017.

Noel Tan and I are organising it (though Noel is doing all the hard work), and my company is sponsoring the main prize, a Microsoft Surface Pro 4.

I would like to invite all my readers to attend this event, and if you are in the general vicinity of Alberta and can take the week off to also attend Edmonton’s own SQL Saturday a week prior to ours, on 22 April 2017.

It’s a comfortable drive from Edmonton to Calgary, and you can take in many sights, sounds and tastes over that week. Both Calgary and Edmonton have international airports only 30 minutes from their respective city centres.

Speakers are welcome to apply. We already have interest from Canada, USA, and Australia. You can speak on any number of topics, including Azure SQL Database, SQL Server on Linux, Best Practices, SQL Server 2016, and so on.

To register to attend, speak, or sponsor the event, visit the official Calgary SQL Saturday website (http://www.sqlsaturday.com/607/) by visiting and clicking on the Register Now button. We will be Event #607.

To stay abreast of what’s happening in the lead up to this event, you can also watch the #sqlsatcalgary and #sqlsat607 hashtags on Twitter.

If you would like to know more, find me on Twitter as well, at @bornsql .