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.

Interview Questions for a SQL Server DBA

When I’m interviewing a SQL Server DBA, I have three questions that I always ask.

My favourite interview question is this:

“What is the difference between a clustered and a non-clustered index?”

My second favourite question is this:

“What is the right disaster recovery strategy for my company?”

The first question is one of basic understanding. Depending on their work experience, the candidate may have an inkling about why such a distinction matters, and this lets me know how much I have to teach. What it does not do is disqualify them. I am not averse to teaching, which is why I write on this website in the first place.

The second question should be answered with “it depends”, followed by the candidate asking me questions about my company so that they can figure out a way to begin answering it. If the candidate blurts out an answer without asking questions, well, their résumé goes in the garbage. This is the disqualification question.

I’m mostly self-taught, with formal training to fill in the gaps much later in my career, so I know the difference between “I know this thing, but not the textbook definition”, and “I read this somewhere on Google and have never used it”.

If the candidate uses words that are blatantly wrong but have the right idea (“the blob is sorted on the disk, but the non-blob one is a copy of the blob, but smaller, because you can only have one blob, and it’s shaped like a Christmas tree”), then that’s a pass for me. SQL Server doesn’t care how you describe an index to an interviewer, as long as the syntax is correct, and that’s what Books Online is for.

Heck, I might even say “if you can’t explain it with words, draw it for me on the board”. I love pictures. I’ve drawn many lopsided databases in my time. That’s what the dry-erase board is for: working through a problem that exists in the real world. It’s not there to make someone regurgitate a textbook definition. Again, that’s what Books Online is for.

Here’s a practical example from my school days, as a student and as a teacher: the notorious open-book exam.

In this scenario, the exam taker is allowed to refer freely to their textbook throughout the exam, and the invigilator is there to make sure no one is distracting their fellow students.

The open book exam relies on the exam takers having a working knowledge of their subject. In the case of English literature, for example, it helps to have read the book. Summarised study guides only get you so far — if I’m asking why the love between Catherine and Heathcliff can be described as “demonic”, providing examples from the text, you would have to know where to look.

Even so, the open book exam is unfair for those people who haven’t read the book but have seen the movie ten times and can quote their favourite lines from it. Or if they prefer the audio book, because they struggle to read words on a page, especially under stressful situations. Or their parents read the story to them many times in their youth. Or English isn’t their first language. Or their second language.

If you have haven’t read the book, you will run out of time to answer the questions, because you’ll be frantically reading the textbook for the first time, trying to understand why anyone wants to know what a linked list is for (a very typical interview question for software developers). Meanwhile, you’ve used linked lists before in that job you had writing code for your cousin’s bicycle store, but you didn’t realise that was their actual name.

If I’m asking you, with only a few years of experience administering an Access database for your uncle’s grocery store, to create an index in SQL Server, without using the graphical interface of Management Studio, the first place you’re going to look is Books Online.

As my friend Gail Shaw (b | t) once said many years ago, when you are working on a SQL Server database, you should have two windows open, and one of them is Books Online. Why guess arcane syntax when you can simply look it up?

Which brings me to my third favourite question:

“What was the last mistake you made, and how did you recover from it?”

I’ll go first.

This morning I was working on a stored procedure for a new system. The code called for an upsert-style stored procedure (INSERT and UPDATE in one block of code, to check for the existence of a row of data in order to update it, or insert it if it doesn’t exist). My UPDATE statement was missing a WHERE clause.

Fortunately, the tools that I have at my disposal managed to catch this before the code was ever run. My point is, even with decades of experience, and being able to recite all of the keywords in an UPDATE statement, even the most battle-tested person forgets a WHERE clause once in a while and updates or deletes the entire table.

This is why I love my second favourite interview question, because I get to ask the candidate this bonus question:

“Does the disaster recovery strategy you came up with in question 2 cater for the scenario you just described?”

After all, backups matter only if you can restore them promptly and accurately.

Final Thought

This discussion has made a resurgence thanks to my friend Janie Clayton, who has posited (in my interpretation anyway) that technical interviews are designed to exclude certain people from applying for a position, for arbitrary reasons (university degree, culture, gender, language, etc.), for the sake of some puritanical ideal.

My take is obvious: If you have working knowledge that you can demonstrate, and some experience, you can learn the rest on the job. Every system is different, with different goals guiding a decision. It takes time to become accustomed to a new system, and even the smartest person in the room will have to ask questions.

The question is not “what have you memorised?”, but “what can you learn?”.

By all means, if you’re applying for a specialised role (for instance, performance tuning), then you need to have specialised domain knowledge. For everything else*, there’s Books Online.

* MasterCard did not pay for this post.

Backup Retention Policies

Today’s post is just a bunch of questions, but they are very important questions, all around the main topic of a Backup Retention Policy.

  • How long do you keep backups for every database in your environment (including development, testing, quality assurance and production)?
  • Do you keep backups of system databases?
  • Do you keep backups of system databases in development, testing, quality assurance and production?
  • Out of those database backups you keep, how many of them are just-in-case full backups, or even just copy-only backups (out-of-band)?
  • Of the important ones with transaction log backups, do you also keep differential backups?
  • How often are these differential backups taken?
  • Do you check to see that the differential backup file sizes approach or exceed the size of a full backup?
  • Of the transaction log backups, how often are these backups taken? Every fifteen minutes?
  • How long do you keep transaction log backup files?
  • How soon will it be until you run out of space for backups?
  • What is your contingency plan for when you do run out of space?
  • How do you test your backups?
  • How frequently do you test your backups?
  • Do you have an offsite backup solution?

Since each environment is unique, there is no perfect answer for these questions, but I urge you to consider them, especially in the context of the recovery time objective (RTO) and recovery point objective (RPO).

We may not want to retain three months of transaction log backups for a test environment that is purged every week, but we may want to ensure that the model or msdb databases are regularly backed up.

Want to talk about this with your employer or client? Check out Brent Ozar’s updated High Availability and Disaster Recovery Planning Worksheet to see how you fare in the case of a disaster.

Let me know in the comments, or on Twitter at @bornsql.