How To Back Up Your Hosted Database in Azure

These days, databases are far more stable and reliable than they used to be, but there's still a possibility that you can lose data due to a bug in the database engine.

Another concern—one which probably won't be resolved anytime soon—is the reliability of the infrastructure you're using to host your database.

Hopefully, you'll never have to restore your backups, but in case that ever happens, you should always backup your data and verify it on a regular basis.

Since storing backups in an on-premises infrastructure can be unreliable and expensive, we're going to cover the whole process of backing up your database using Azure cloud services. 

3 Important Types of Backups

The three most important types of database backups are full, differential, and log backups.

There are other types of backups, but these three form the core backup utility.

1. Full Backups

Full backup contain all the data that you have inside your database. This type of backup takes the most time to produce, but you'll end up with a backup file that contains all the data from your database at a specific point in time.

2. Differential Backups

Differential backup contains all the changes from the most recent full or differential backup. It takes far less time to complete than a full backup, but because it's not a stand-alone backup it can't be used to restore a database by itself.

3. Log Backups

Log backups are used to backup the transaction logs that you can use to reconstruct the state of your database. They take even less time to complete than differential backups, but they can take a significant amount of time to restore a database, since they're basically applying all the transactions one at a time in order to reconstruct the state of the database.

Transaction logs cover the transactions made to the database since the last full or differential backup.

Depending on the use case, you could perform different types of backup operations more or less frequently. A workflow that could serve as a good starting point is to perform a full backup every day during the time of day with the least amount of traffic.

Upon that full backup you can perform differential backups every 1 to 4 hours and backup your transaction logs every 5 to 15 minutes. Keep in mind that differential backups give you a “snapshot” of data at a certain moment in time, while transaction logs enable you to restore your data at any point in time.

Backing Up Hosted Databases and DBaaS

Learning how to backup an Azure SQL Database is somewhat trivial, because that function is performed for you automatically. The only things that you need to do manually are to restore the database if need be, and enable Azure Site Recovery for that database.

Azure Site Recovery has to be enabled because Azure SQL Database backups are retained for either 7 or 35 days, depending on your service-tier. You can read more about Azure SQL Database backups, which will also tell you how often full, differential, and transaction log backups are performed.

Backing up an SQL server is usually done with SQL Server Management Studio (SSMS). It's a straightforward process that hasn't changed much since SQL Server was released. Backup targets are usually disks or tapes, since such formats are still the preferred and most reliable “offline” methods for storing data.

A newer approach is to backup your database directly to a cloud storage service. SQL Server enables you to backup your data to Azure Blob Storage, which is a fairly cheap storage service.

Azure Blob Storage stores your data redundantly in at least three locations inside the same Azure data center; the maximum amount of redundant storage available stores data redundantly across all the 38 Azure regions and data centers currently located around the globe.

Backing up your data to an URL is a feature that was introduced in SQL Server 2012 SP1 CU2. The problem that might exist when uploading directly to Azure or any other storage service on the Internet is that your upload speed may not be very fast. If that proves to be an issue, you should first backup to a drive, and then upload the data to the cloud later.

Archiving Hosted Databases to the Cloud

Your database is probably going to grow over time, and as it grows it will require more storage space. It will also require more storage space for full backups as well as more time to complete full backups.

A simple solution to solve these space requirements would be to delete all the data that you don't need.

However, in most cases you can't do that, either because you may need that data at some future point in the time, or because there are government regulations that oblige you to retain all the data for a specific period of time. In such cases, that is where archiving comes in handy.

The next section will cover some workflows that might suit your archiving needs. If, for some reason, your data needs to remain in the same database, you can move all that data to a separate table.

You should also make sure that that table is in a separate filegroup, possibly on a separate disk. As the users will probably want to access that “archived” data, you can create a view that “merges” all relevant tables, and then the queries should “aim” at the view instead of at the original table. This approach will allow you to perform maintenance jobs in less time, while allowing you to still serve all the data.

If you can afford to move the data to a separate database, you will save yourself some maintenance work on the primary database. The workflow is basically the same as in the example given above, except that you either need to link the SQL Servers to still provide all the data, or make the applications aware of the second SQL Server and query it directly, which isn't optimal since that second database will at that point begin to act as another production database.

In some scenarios, it won't be necessary to keep your data “online” in a database. On those occasions, you just need to backup your data, check at least once that you can restore it, and then delete the data from the database. If you'll ever need it, you can just restore the data from the backup file to the database. 

To make things easier, with SQL Server 2016 Microsoft introduced a feature called Stretch Database. Stretch Database allows you to move all the data you don't need on your hosted database to an Azure SQL Database that will provide much cheaper storage, and where you don't need to worry about infrastructure.

You just need to create a policy on your hosted server indicating which data will be moved. The applications that use your primary database don't need to implement any changes, because SQL Server takes care of fetching the data from the Azure SQL Database.

Verifying Backups

When backing up your database, you can select whether to create it “WITH CHECKSUMS” or without. After the backup is created, you can run the “RESTORE VERIFYONLY” command to see if there's a problem with the backup file. This isn't a required step, but it is a best practice to implement. 

The problem with verifying the backup file is that, if you haven't selected “WITH CHECKSUMS”, the process won't verify the structure of the data on the backup, and you might end up with an unrestorable backup file.

When using “WITH CHECKSUMS,” you will loose some additional time backing up the data and verifying it, but by doing so you will get a much better indication of whether or not your backup file is reliable. 

In the end, you always have the option to restore the backup to another database and check the data there. This is a time-consuming process, and mostly unnecessary, so if you really want to do this, you can always use Azure Automation to run some scripts on each backup upload.

Restoring Backups

If you're trying to restore an Azure SQL Database, it will take you just a few clicks through the Azure Portal to restore it to a desired point in time. Just keep in mind that those backups are retained for either 7 or 35 days, depending on your service-tier.

If you need to restore to something older, you should start using Azure Site Recovery, which can hold your data for 10 years.

Keep in mind that that data retention policy is not set automatically in Azure Site Recovery; it has to be configured through the Azure Portal or else your old backups get deleted.

Restoring backups to an SQL Server is usually done with SQL Server Management Studio. The restoration process is straightforward, following these guidelines. If you're in a situation where you need to restore a transaction log backup because your last backup isn't covering all the transactions that took place before something went wrong, you can follow the steps outlined here.

Conclusion

With the dawn of cloud computing and high-speed internet connectivity it is now easy to host backups on a reliable, geographically-dispersed infrastructure at a fraction of the price it costs to backup and host data using an on-premises environment. But since that means databases are no longer being kept in-house, it is crucial to make sure that data is consistently backed up and secure.

Azure provides users with tools such as Stretch Database, Azure Blob Storage, and Azure Site Recovery that make the task of archiving, backing up, and hosting data easier than ever. Backing up databases in the cloud with Azure is a straightforward process that can be employed and customized as it suits your database.