Blog

Azure Backup: SQL Databases and How To Back Them Up

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. Having Azure backup plans can be a big help here.


There are a number of different solutions for Azure backup. SQL databases are just one use case for these tools that we’ll look at in depth in this post. 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 cloud hosted databases in Azure.

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. This goes for Azure database backup as well as for database backup on any other platform.

1. Full Backups

Full backups 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 back up 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.

Azure Backup: SQL Hosted Databases and Other DBaaSs

Now let’s take a look at Azure database backup. 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 backup SQL databases are retained for either seven or 35 days, depending on your service-tier. You can read more about Azure backup SQL Databases, 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 in Azure database backup is to back up your database directly to a cloud storage service. SQL Server enables you to back up your data to Azure Blob Storage, which is a fairly cheap storage service, frequently used for Azure cold storage. 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. This dispersal is one of the clear benefits to cloud hosted databases.

Backing up your data to a URL is a feature that was introduced in SQL Server 2012 SP1 CU2.

Additional enhancements were introduced to this feature in SQL Server 2016 (13.x) that enables usage of block blobs for data storage, Shared Access Signatures for enhanced security, and striping for enhanced performance.

Let’s go through the steps required to configure backup for SQL database to Azure.

Prerequisite:

The Azure storage used for storing the SQL database backup should be created and configured as a prerequisite.

From the Azure management portal, go to “Create a Resource,” then select “Storage,” and then “Storage account.” Provide details such as resource group name, storage account name, and location based on your preference. Optimal settings for account type, replication, and access tier can be configured as shown below. When you’re done, click “Next.”

Backups 1

In the advanced section, configure the settings as you can see in the image below. Blob soft delete is enabled to ensure protection against accidental deletion of backup data from the storage account.

Backups 2

In the next step, add any tags that are relevant to your environment, as shown in the sample below.

Backups 3

Review the settings and proceed to create the storage account.

backups 4
 Once the storage volume is created, provision a container for storing the backup data. From the storage account, click on “+Container.” Provide a name for the container and set its access level to private.

backups 5

Backup files (.bak) will be uploaded to this container using storage keys or SaS access tokens for authentication.

Backup configuration: 

The following steps will show you how to back up a database. Use SQL management studio to connect to your SQL server. Right click on the DB to be backed up. Select “Tasks” and then choose ”Back Up”. 

backups 6

In the General page, select destination as “URL” from the drop-down menu.

backups 7
Click on “new container.”

backups 8

 You will get a prompt to sign into your Azure subscription. Select the container we had created earlier and create a credential. Click “OK” to proceed.

backups 9

The backup URL will be listed in the wizard now. Click on “OK” to start the backup. 

backups 11

Once the backup is completed successfully, the following message will be displayed:

backups 12

From the Azure portal, if you browse from the storage account to the container name, you can see the backup file has been uploaded: 

backups 13

Note: One problem that might come up 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 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 back up 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 and Restoring Azure Backups

When databases back up to Azure storage, 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. 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.

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. 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 cloud computing and high-speed internet connectivity, it’s now easy to host backups on a reliable, geographically-dispersed infrastructure at a fraction of the price it costs to back up and host data using on-premises environments. 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.

Another way that users are backing up their cloud hosted databases is with Cloud Volumes ONTAP and SnapCenter®. Cloud Volumes ONTAP is NetApp’s solution for data management in the cloud, and relies on the same cost-saving storage efficiencies and highly-efficient snapshot technology that NetApp users have been relying on for database protection for years. These features provide some useful ways to avoid Azure backup limitations.

To read more about using Cloud Volumes ONTAP for Azure backup, check out these posts:

And to try out Cloud Volumes ONTAP for your database in Azure, sign up for a 30-day preview today.

-