Backup to the Cloud: The Silver Lining

Offsite backups are vital to data integrity, allowing for preservation and backup of data in catastrophic circumstances. Cloud backups have been around for a while, but are now really coming into their own. Tony Davis looks at how to get set up with cloud backups for restores and disaster recovery.

If you believe that offsite backup isn’t important, watch this clip from the Data Center Security Cam of the 09.09.09 flood at Vodafone Istanbul, Turkey. If you remain unconvinced, browse through Continuity Central (paraphrased extract):

Following a major disaster, such as a fire, one in two businesses never returns to the marketplace. Of those that do, many never recover completely, losing orders, contracts, key employees or may go out of business resulting in lost jobs and services to the community. The ones that survive plan their response to a disaster before it strikes.

Cloud storage solutions have been around for a while now, and offer more-or-less limitless storage space that you can access from anywhere with an internet connection, and pay for according to use. As the cloud environment evolves, tools are becoming more specialized, with cloud storage solutions emerging aimed specifically at SQL Server backups.

Using cloud-based database backup as part of a Disaster Recovery strategy seems to offer a simple and cost-effective solution, but can be a rather unsettling idea for a DBA. It isn’t so much the idea of storing an off-site encrypted backup in the cloud, as the practicalities of timely restoring. It would take a long time to download a typical production database over limited internet bandwidth, even if the data is compressed.

However, having any offsite backup is better than having none at all and most organizations will have some use for cloud backups for risk reduction. This article explores one possible way to design a database, using multiple filegroups and table partitioning, in order to allow us to restore it to a functioning state, without the overhead of downloading the entire database, including data that isn’t critical to basic business operations.

SLA the Beast

For every database in their care, DBAs must work within a Service Level Agreement (SLA), agreed with the stakeholders and managers. This sets out, amongst other things, the recovery point and time objective for that database. The DBA is obliged to set up backup jobs accordingly, to meet those objectives, and schedule regular restores, with database integrity checks, to verify them.

SLAs generally require that systems can be recovered even in the event of a major disaster. This will almost certainly compel you to use some form of secure offsite storage for encrypted copies of your database backups. This could mean replicating backups to a SAN drive on another site, or copying backups to tape or external hard drive in another facility, whether managed by you, or a third party. Of course, many organizations, especially start-ups and SMEs don’t have the means to lease purpose-built, secure, off-site storage space or to contract a specialist company to deal with this. In such cases, cloud backup could help you to be compliant.

Even organizations where there is a finely tuned offsite backup policy will still find a use for cloud backup; there are always databases that contain important data but slip through the net of corporate backup policy. A few example of this might include:

  • SQL Servers with valuable production data sited in hosted storage – especially where there is better connectivity between hosted server and that cloud than to your site
  • Outlying databases – database outside your domain (e.g. within sub-offices), where you do not have direct write access to your offsite backup provision
  • Databases on servers in what some IT departments refer to as Bandit country – where servers set up by the business do not conform with the corporate backup regime and have not the resources or permission to regularly take backups offsite
  • Databases on laptops, perhaps with out-workers or travelling sales representatives – these databases contain data that is valuable to the enterprise and which has not yet been merged.

These are all good uses for cloud-based backup, where the recovery-time objectives permit it, but the larger the database the more problematic this becomes. Beyond a certain database size, it pays to take a hard look at the database, to see if there is a way to separate out the data that is essential to the day-to-day working of the system from that which is held for work that can bear short delay such as reporting, year-end analysis, and maintenance. If possible, one can also separate ‘derived’ data such as indexes.

Then, even if the database backups are offsite, the DBA can download backups of the most important data and partially restore the system, allowing the essential parts of the service to be available whilst the other parts are being retrieved.

Strategic Cloud Backup

In Feodor Georgiev’s series of articles on backup resilience (see, for example, https://www.simple-talk.com/cloud/cloud-data/backups-as-a-cloud-service—an-alternative-view/), he proposes an interesting strategic use for SQL Server cloud backups.

He argues that with gigabytes of data to backup and severe limitations on network and internet speeds, we cannot count on constantly backing up entire databases and uploading the files to the cloud. However, with careful classification of our data, and design of our database architecture, we can back up to the cloud a subset of our data, namely that most vital to the day-to-day operations of our business. We might then perform a partial database restore operation, to get our critical data back online immediately, while we work on restoring the rest.

To offer a basic example of how this might work, we’ll create a table with two partitions, each assigned to a separate filegroup, and split the data across the two filegroups. I’ve simplified the example, deliberately, but in a real situation, we might imagine that one filegroup (FG_A) contains sales data for our primary sales region, and the other (FG_B) contains data for other regions.

We can imagine a situation where we store all backups locally, for a certain period, and in physical offsite storage, but where we also create copies of the backups of the primary and FG_A filegroups in hosted cloud storage. Finally, the example will demonstrate how we can retrieve these cloud backups and perform a partial restore, to get some data back online immediately, while we retrieve and restore the other filegroup backups from offsite physical storage.

We take the backups using Red Gate SQL Backup Pro, and save the cloud backups to Red Gate’s hosted storage. To get started, simply navigate to https://sqlbackup.red-gate.com, and create a hosted storage account. This creates a storage area within Red Gate’s Amazon S3 space, hosted in the US and backed by the Amazon S3 Service Level Agreement. Next, from the SQL Backup Pro GUI, simply link your SQL Server instance to the hosted storage account. Alternatively, for example, with SQL Server 2012 we can now back up natively to a Windows Azure blob (see http://msdn.microsoft.com/en-us/library/jj919148.aspx).

First, in SSMS, we create a CloudRecovery database with a PRIMARY filegroup and two user-defined filegroups, FG_A and FG_B.

Listing 1: Creating the CloudRecovery database

Next, we create a very simple partition function on an INT column, and a corresponding partition scheme. Our FG_A filegroup holds the first table partition and FG_B the second.

Listing 2: Creating the partition function and scheme

Use of RIGHT in the partition function dictates that the boundary value for the partition, zero in this case, belongs in the right partition, and so becomes the lower bound of the second partition. In other words, values <0 will go in our first partition (FG_A) and those >=0 will for in the second partition (FG_B). If we had specified LEFT then zero becomes the upper bound value of the first partition, so FG_A would contain numbers =< 0 and FG_B those >0.

Next, we create a simple CloudTest table and then an index that contains the partitioning column, so the index is ‘aligned’ with the table, and insert 200 rows (the numbers -100 to 99).

Listing 3: Create the CloudTest table and populate it with 200 rows

Before we go any further, let’s check our partitioning scheme and ensure all the rows are where we expect them. According to our scheme, we expect 100 rows in each partition (-100 to -1 in FG_A and 0 to 99 in FG_B).

1868-PScheme-88cee001-ac8d-40c2-9b40-210

Listing 4: Check the CloudTest partitioning

We’re now ready to perform our backups. We can capture the backups through the SQL Backup GUI or a script, and I’ll use the latter here. We’re going to perform filegroup backups but in the first instance, we’ll take a standard database full backup and a log backup, since it is a best practice to perform at least one full database backup before starting file-based backups.

Listing 5: Full database and log backups

Note that we can do a partial restore from a full database backup and so we could simply copy these backups to cloud-hosted storage, and still perform filegroup restores.

However, we’re going to use file backups so let’s add one more row to our CloudTest table (which should go into FG_A) and then start taking them.

Listing 6: File-based database backups and a second log backup

We are storing these backups to our normal storage location, but in the case of the PRIMARY and FG_A file backups, and the log backup, we are also storing a copy of the backup in cloud-hosted storage. The only difference is the addition of COPYTO_HOSTED to the WITH clause of your SQLBACKUP command. We can still use our normal compression, encryption and network resilience settings.

For the cloud-hosted backups, once SQL Backup writes the backup to local disk, it adds the backup to the queue for uploading to your account, over HTTPS. If the internet connection goes down, SQL Backup Pro will retry the upload at regular intervals for up to 24 hours.

Now we’re ready to simulate a restore operation, in response to some disaster. In this case, I’m simply going to drop the database.

Listing 7: Drop the CloudRecovery database

The next step is to retrieve our cloud-based backup copies. Currently, we can’t select these files and restore them directly from cloud storage, from within the SQL Backup GUI (hopefully a feature coming soon), but retrieving them is quite simple. If you’re in the SQL Backup UI, simply navigate Tools | Hosted Storage Website, log in and you should see a list of all your backups.

1868-CloudBackups-13d7e7bd-5de5-470f-ad5

Figure 1: Backups in the SQL Backup Cloud

Click the blue arrow to download each one. In this example, I saved them to a CloudBackups sub-folder of my standard on-site backup location. Having downloaded the files locally, you’ll notice a unique identifier appended to the name of each backup (for example, CloudRecovery_FG_A_635091562884180570.sqb). Note that as a side effect of this approach, use of BACKUP DATABASE...WITH INIT will, rather than overwrite the existing backup, result in two backups of the same name, in cloud storage.

You can remove these unique identifiers from the name of each file (i.e. name it back to CloudRecovery_FG_A.sqb). Listing 8 shows the partial restore operation.

Listing 8: Partial restore of CloudRecovery

The database should be online and accessible, but with the FG_B filegroup still in RECOVERY_PENDING mode. We can confirm this with Listing 9.

1868-partialrestore-47cdd670-8950-4afd-9

Listing 9: Confirming that FG_B is offline

We can try a couple of queries against our partially restored database, as shown in Listing 10.

Listing 10: Test queries against CloudTest (with one partition still offline)

In each case, you should see the 101 rows returned from FG_A (-101 to -1). In each case, unfortunately, you’ll also see the following error message.

When a partitioned table is only partially available (one or more partitions is on an offline filegroup), querying that table will always result in a severity 16 error. Any application that expects to be dealing with partially available tables has to handle that error. Nevertheless, this being the case, the applications can now access all of the data that is in the online filegroup.

Of course, once we’ve retrieved FG_B backup from offsite we can complete the restore and all of the data is now available for querying.

Listing 11: Full restore of CloudRecovery

Summary

Cloud backups, wherever you decide to host them, can be a useful ally to your disaster recovery plan. This article provided only one example of a possible strategic use of cloud backup storage to aid fast, partial database recovery. You could take this idea further, perhaps only backing up changed data to the Cloud, but I hope it will start you thinking about whether and where SQL Server cloud backups could be useful, within your organization.