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.

PSA: Check Your Backups

This post comes to you from the Spanish countryside. I travel a lot these days, sometimes even for work. The best part about travelling, apart from food, is knowing that my backups are in good shape.

So my Public Service Announcement for you today is to make sure your SQL Server backups are valid.

Step 1: Back up your SQL Server databases. All of them. That’s your production, test, QA, and development environments, user and system databases. The only database you should not be backing up (and cannot) is tempdb.

Step 2: Test your databases by restoring them. Yes, use the checksum option when backing up, but as we all know, a good backup is one that you can restore.

Step 3: Check the consistency. Run DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS. If the database is really massive, you can break this up into DBCC CHECKALLOC, DBCC CHECKCATALOG, and then run DBCC CHECKTABLE per table.

Step 4: Take lots of photos of the Spanish countryside.

If you’d like to share your photos, or discuss how you test your backups, leave a comment, or find me on Twitter at @bornsql .

On Best Practices

We hear a lot about “best practice”, but what does it mean in the context of SQL Server? Wikipedia describes best practice as follows:

A best practice is a method or technique that has been generally accepted as superior to any alternatives because it produces results that are superior to those achieved by other means or because it has become a standard way of doing things, e.g., a standard way of complying with legal or ethical requirements.

We will take a high-level look at high availability and disaster recovery, an important part of database management and administration and a good example of how a certain way of doing things has become a standard. Then we will discuss what it means to implement a best practice for disaster recovery.

High Availability

There are many ways to achieve high availability (HA) — the idea that our database has zero (or close to zero) downtime — and usually this is dictated by our budget. We want business continuity in the face of planned and unplanned downtime. This may take the form of database mirroring, Availability Groups, clustering, or log shipping.

Disaster Recovery

Disaster recovery (DR) is slightly different. It assumes that a disaster has occurred, our highly-available environment has gone down, and now the clock is ticking. Company losses are mounting by the minute.

How this affects me, the DBA

HA/DR is usually expressed in a Service Level Agreement using two terms:

  • RPO, or recovery point objective: the maximum amount of data loss allowed by the business.
  • RTO, or recovery time objective: the maximum amount of time it should take to recover from a disaster.

What we want is a reliable, predictable and repeatable disaster recovery plan, even if we have a high availability solution that has never failed before. If our Availability Group doesn’t fail over cleanly, or the drive array is on fire, or that Windows Update managed to kill the network interface, we need to roll up our sleeves and activate the DR plan.

So, according to guidance, that means having regular full backups, differential backups, and transaction log backups ready to restore at a moment’s notice, right? Yes, but that’s not where our job stops.

Putting our best foot forward

This is where best practice comes in. It’s all very well having a good disaster recovery plan, but it’s useless if we don’t test it. Best practice in this case would be having both a good backup strategy and a methodology to make sure our DR plan is tested often, so that when something goes wrong, we have a script to follow. That way we will be able to recover with a small RPO and a short RTO, so that business is impacted as little as possible.

We call this a Run Book. Anyone in our organisation should be able to follow the script and perform a successful recovery.

What if we are incapacitated in some way, or in my case recently, stuck on an aircraft with no access to the Internet? What if the only person who is able to restore the environment is the CEO after hearing that her building is on fire?

Our backups must not only be stored securely somewhere (and in my post last week, I provided a budget-friendly solution on how to do off-site backups), but we should be able to restore to a known point in time, within the recovery time objective, losing as little data as possible.

Our run book should be clear enough so that the person who is testing the disaster recovery plan understands it so as not to ask us questions.

Earlier we saw the word “superior” repeated in the definition of best practice. A superior solution doesn’t need to be the most expensive one, using the latest and greatest tools. A run book could be an Excel file containing enough information to rebuild a server and locate magical scripts in the cloud that are tested weekly.

Know the problem we’re trying to solve. Build a solution that is practical and cost-effective for our environment. Test and iterate until it is flawless. That’s best practice.

Compressed and Encrypted Backups on the Cheap

We back up our databases regularly. We store them on a secure network location so that if the database drive fails, we can still satisfy the requirements for a rapid disaster recovery time (RTO, or Recovery Time Objective).

Some of us store these backups off-site as well, as an added security measure, either on tape or using cloud storage solutions (Azure Blob Storage, Amazon S3).

And we encrypt these backups, right?

Right?

SQL Server 2016 has some very neat features for encrypting databases, tables, rows, backups, and even in-memory stuff.

But what if we are still slogging away on the ancient SQL Server 2008 or 2008 R2 Standard Edition, and we need to compress and encrypt our backups, using industry standard AES-256 encryption?

Enter 7-Zip. It is free, open-source, and governed by the LGPL licence.

7-Zip Logo

The GUI (referred to as the 7-Zip File Manager) is installed by default. It looks something like this:

7-Zip File Manager GUI

7-Zip File Manager GUI

Additionally, there is a command-line version (7za.exe), which has the same features as the 7-Zip GUI. You can get the command-line version by downloading the 7-Zip Extra package.

This command-line tool can be used for scripting in batch files or PowerShell scripts, which means we can automate a whole lot of things with our backups. There is both a 32-bit and a 64-bit version available.

7-Zip (a) [64] 15.14 : Copyright (c) 1999-2015 Igor Pavlov : 2015-12-31

Usage: 7za <command> [<switches>...] <archive_name>
       [<file_names>...] [<@listfiles...>]

[Tip: I highly recommend using the default 7z archive format, instead of Zip. It provides a significant disk savings over the Zip format and requires about the same time to compress at the Normal compression level.]

The scenario

Let’s assume that we have a limited budget. We can afford SQL Server Standard Edition and 1TB of off-site storage, in the form of Azure Blob Storage, Dropbox, or Google Drive, to name just a few.

We can use 7-Zip on the command line to produce an encrypted file, even if we don’t want to compress it (7-Zip can create what I call “container” files, which are simply an encryption wrapper around the file) and push that to our Dropbox sync folder.

Remember that SQL Server 2008 R2 and higher allows us to compress backups in Standard Edition. It makes very little sense to compress an already-compressed backup.

Command-line switches

I use this page as a reference for 7-Zip command line examples. Of particular interest is -mx0, (which will store, but not compress, the source file), and -pSECRET (which allows the use of a password, where SECRET is the password).

Putting the pieces together

Step 1: Back up our databases (with compression if available)

Step 2: Copy the files to a secure network location

Step 3: Use 7-Zip to encrypt (and maybe compress) these files

Step 4: Copy the encrypted files to off-site storage

There are myriad tools to perform these steps, and there’s no single correct way to do it, though I prefer PowerShell for a number of reasons. It’s modern, it is still updated, and it can interact with a number of Windows features in a consistent way, which means we can automate a lot of our everyday tasks.

For example, if we stay on the Microsoft stack with Azure, we can use PowerShell to iterate through the backup directory, encrypting each backup file, then copy it to Azure using a tool like AzCopy, or even my Azure Blob Storage Sync tool.

Summary

We don’t need the latest and greatest (and expensive) tools to implement best practices. Using 7-Zip, Dropbox and PowerShell, we can easily ensure that our databases are secure in more ways than one.

98% of I/O is taken up by tempdb

During a recent engagement, the customer explained that the performance on their relatively small system was especially slow on Monday morning, but generally bad that entire day.

The SQL Server 2008 R2 instance, which runs in a virtual machine, has 30GB of RAM allocated, but the SQL Server memory usage never exceeds 16GB.

Digging deeper, we discovered that 98% of all I/O was consumed by tempdb, described by Brent Ozar as the public toilet of SQL Server.

The system makes extensive use of temporary tables and table variables in its queries.

The tempdb data file hovers around 2.5GB, and the log file is around 1.5GB. Certainly not a huge database, and puzzling why this small company is experiencing performance issues.

It all fits in memory! Which is fast!

We noticed that the SQL Server instance had been restarted the previous Sunday, as part of a maintenance cycle, and that the file allocation for tempdb was set to the defaults:

  • 8MB for data file, 10% growth
  • 1MB for log file, 10% growth

In other words, no matter how big the tempdb database was before the restart, it would always shrink down to that tiny size, needing to zero out the log file when it grew again.

To get to the size it was today, tempdb had to grow the data file 60 times and the log file 77 times. Each time it took 10% longer than before to grow, requiring 10% more space.

Of note is that SQL Server does not have any global trace flags enabled, in particular, TF1118.

Every half hour, a virtual machine snapshot is taken, freezing the I/O on the server as well.

Our resolution was as follows:

  • Set Windows Power Saving Mode to High Performance
  • Increase tempdb data files to match number of CPU cores
  • Change tempdb default file sizes to the current usage
  • Change tempdb growth to fixed value, appropriate for I/O subsystem
  • Shrink and resize tempdb log to current size
  • Enable Trace Flags 1118 and 1117 to improve data file contention
  • Switch from VM snapshots to Full Recovery Mode and enable transaction log backups

We will have to wait to find out what impact these changes had on the environment over the course of a week. I’ll report back next time.

Max Server Memory Matrix

When we perform diagnostics on an existing SQL Server instance, or if we are setting up a new machine, it’s very important to make sure that we change the Max Server Memory setting to an appropriate level.

If we don’t, then there’s a good chance (especially on Enterprise Edition) that SQL Server will use all the available memory on the server. Unfortunately, this means the operating system can be starved of RAM, which creates a performance bottleneck.

Additionally, other components (including other applications, services, and hardware on the machine) may have specific memory requirements, which need to be taken into account.

To get a baseline, I use a formula developed by Jonathan Kehayias (blog | twitter) which he wrote in his book, Troubleshooting SQL Server: A Guide for the Accidental DBA, and repeated in his blog post, “How much memory does my SQL Server actually need?”.

After many months of confusing myself with mental arithmetic, I decided to create a straightforward list based on Jonathan’s algorithm, in 4 GB increments, for my own reference. I would like to share this with you today.

You can bask in the glory that is my Max Server Memory Matrix at https://bornsql.ca/memory/.

Next time you (or I) need to check if the Max Server Memory setting is too high, simply open that link and check the matrix. Over time I will make it look prettier, but for now it is already helping one of my clients.

Let me know what you think on Twitter, with @bornsql.

Update to Azure Blob Storage Sync and Restore

Blob Storage Sync tool updated

During a SQL Server migration this month, I found some inconsistencies in my Azure Blob Storage Sync tool, so I made several improvements, and fixed an outstanding bug.

As you know, it relies on the naming convention provided in Ola Hallengren’s Maintenance Solution and comes in two parts: the AzureBlobStorageSync command-line application, and the AzureBlobStorageRestore command-line application.

New Features

I realised that it was not possible, using this tool, to download every file from the Blob Storage Container in one go. The code only downloaded the files necessary to perform the latest restore for a single database.

To resolve this, and allow all files to be downloaded from Blob Storage, I have added a new configuration key called DownloadFilesFromAzure, which takes True or False values.

Another new feature is an explicit option to upload all local files to Blob Storage during a sync. Previously, it was implied that all local files in the LocalPath should be uploaded, but you may not want to do that. This is implemented as configuration key CopyFilesToAzure, which takes True or False values.

Deleting Files

There are now two ways to delete files from a Blob Storage Container:

  • Files that do not match the source, that must be deleted off the target;
  • Files that are no longer needed on the target.

The first option is a typical synchronisation feature and was implicit in the previous version. It is now implemented using the configuration key DeleteMissingFilesFromAzure, which takes a True or False value. If it is set to True, files that do not exist on the local drive will be deleted from Blob Storage.

The second option is for deleting files that match a certain string in the file name. This is handy for server migrations where the file names generated by Ola’s backup script contain the old server name. While the backup script can perform cleanup tasks based on timestamps, it will ignore files that have a different server name, and you might be left with orphaned files long after the backup retention window has passed.

The configuration key, called DeleteExplicitFilesFromAzure takes True or False values, plus an additional configuration key, called ExplicitFilesToDeleteMatchingString. Here you can put a string containing the old server name, and any matching file with that name will be deleted. This particular feature only works with one string at a time. You will have to run it more than once if you need to delete file names that match other strings.

Fixes

Yes, the only logged issue in the repository has been fixed! I now use sp_executesql instead of EXEC for the T-SQL portion of the restore tool. This was probably the easiest thing to fix.

A more critical fix, and the main reason for this blog post and tool update, is to do with downloading files.

Imagine an all-too-common scenario where a download fails before it is complete. In an emergency, the last thing you need is your Restore tool failing. Whereas before I was simply using the name of the file, I now also check file size as well. If the file sizes do not match, the file will be downloaded from Blob Storage again.

Files now download from smallest to largest in size. This is a major benefit if you have a lot of small transaction log files.

Notes

Reading the list of files from Blob Storage takes about ten seconds for 2,500 files, before parsing can begin. This is not a lot of time, but it’s something to keep in mind.

Feature Requests and Bug Reports

If you have any feature requests, or have found a bug, please log that on the GitHub repository. Even better, if you know C#, you can add your own features and fix your own bugs, submit a pull request, and I’ll merge your code into the tool.

Questions, comments or complaints? Twitter is the place: @bornsql is where you’ll find me.

Pre-Announcement Announcement

On Saturday, 27 June 2015, I will be presenting at SQLSaturday #407 in Vancouver BC. The session is titled “Restoring a SQL Server Database from Azure Blob Storage”.

I will be releasing some new content and code at the same time as my talk, which will be available from this site, assuming WordPress plays along.

Once the content is available, you’ll understand why I’ve been so quiet on the blog.

Stay tuned.

End of Life for Microsoft Windows XP and Server 2003: Six-One or Up

Five months from now, Microsoft will stop supporting Windows XP. As Tim Rains points out, running Windows XP after this date will effectively be an open invitation for attack. XP will, in his words, be “zero day forever”.

As you know, Windows XP is the client version of Windows Server 2003, and includes much of the same code. Windows Server 2003 R2 was an incremental update that improved some security aspects, but it’s still the same code base. That will reach its end of life in July 2015, just over 18 months from now.

Why is this important? Simply put, XP and 2003 R2 are old. Security which was state of the art ten years ago, is no longer good enough. It is too expensive, both for you and Microsoft, to stay on antiquated software.

While I am a vociferous supporter of “use what works” and “if it ain’t broke, don’t fix it”, I must add a qualifier: XP and 2003 are broken. Their internals are just not up to the challenge of modern attacks.

The same goes for perennial whipping-boy, SQL Server 2000. Up until two months ago, I was still actively supporting this product at one particular client, but my goal was to upgrade them as soon as possible. Notwithstanding the huge benefit in new features, upgrading to a newer version offers more modern attack mitigation.

I was affected by SQL Slammer in 2003, because I didn’t apply an earlier patch released by Microsoft. I vowed that would never happen again.

It’s time. It’s time to think seriously about security, to harden your defences, to upgrade your software.

If you’re on Windows XP, get on to Windows 7 or Windows 8. Windows 7 with Service Pack 1 is under mainstream support until 2015, and extended support until January 2020. Windows 8 and 8.1 (considered the same product for support purposes) will reach end of life in January 2018 (mainstream) and January 2023 (extended).

As for the Windows Server products, the least you should be running on is Windows Server 2008 R2, which is on the same retirement timeline as Windows 7 SP1.

If it helps, I have this simple reminder: Six-One or Up. The Windows kernel had a major increment to version 6.0 with Vista and Server 2008, and 6.1 for Windows 7 and Server 2008 R2. In other words, version 6.1 is the lowest Windows kernel you should be running anywhere in your environment. If in doubt, type ver in any command prompt on a Windows machine. On this client, I see Microsoft Windows [Version 6.1.7601].