CloudBerry Lab Resources
Get started with cloud backup and management solutions
SQL Server Backup to Azure

Microsoft SQL Server Backup to Azure

Published: by on Post Type: Categories: CloudBerry Backup, Microsoft

In this article, we will provide an overview of methods for backing up Microsoft SQL Server databases to Microsoft Azure. This information is helpful for both backup and disaster recovery procedures. You will learn how to configure database backup to the cloud, how to implement a disaster recovery solution using Azure VM instances and how to back up SQL Server databases to the Azure SQL database service.


Table of Contents

    SQL Server Database Backup to Azure Blob Storage

    Reasons for Backing Up to Azure

    Cloud storage is typically used to provide an additional level of protection in case something goes completely wrong with local data backups. This is why users normally choose full or differential SQL Server backups for backups to the cloud. Transaction log (T-log) backups are a part of “hot” data backups that can be used first during disaster recovery, and which therefore make most sense to store on-site.

    Let's take a look at how Azure Blob Storage can host your database copies.

    BLOB is an acronym that stands for Binary Large OBject. To put it in plain English, a blob is a file. Thus, Microsoft Azure Blob Storage is a type of storage for files.
    Further reading Microsoft Azure Storage Types Explained

    Storing backups in Azure cloud allows you to eliminate hardware-related headaches and provides access to unlimited storage space. More importantly, it gives you a flexible disaster recovery solution, which can be implemented within a few hours.

    Microsoft SQL Server will send your backups to a dedicated Azure container, which you need to configure.

    Now, let’s take a look at how to set up an Azure-based backup environment for Microsoft SQL Server and create a backup plan for your SQL Server databases using the backup wizard in CloudBerry’s software for MS SQL Server backup.

    How to Back Up SQL Server Databases to Azure

    First, you need a Microsoft Account in order to gain access to Azure Cloud. Create an Azure account if you don't have one (a free trial is available). If you run into any difficulties, check our separate guide.

    Note: We will use Microsoft SQL Server 2017 in this example, but most steps are similar for earlier versions of SQL Server.

    1Install CloudBerry Backup for MS SQL Server onto the server with the database, using the default installation options.

    2After installation, launch CloudBerry Backup Management Console and click MS SQL Server in the top left-hand corner to launch the backup wizard.
    Launching CloudBerry Backup for MS SQL Server

    3Choose Local or Cloud Backup and click Add new storage account to connect to Azure Blob. You will need to provide an account and shared key (you can create these in the Home - Storage accounts section, or just type “storage account” into the search field).
    Adding Azure storage account - CloudBerry Backup screenshot

    4Keep the default options on the next few screens until you get to Select MS SQL Server Instance step. On this step, choose your SQL Server instance and authentication method (Windows Authentication is good for most cases).
    Choosing SQL Server instance for backup to Azure

    5Next, choose which databases you need to back up. You can choose Back up all user databases only in order to skip system databases and protect only the users’ data.
    Selecting MS SQL Server databases for backup to AzureIf you do not configure a regular backup plan, consider checking the copy-only flag, which allows for the creation of a data copy with no effect on previous backup chains; it will therefore not truncate transaction logs.

    6 On the following screens of the backup wizard you can specify other backup options, such as compression, deduplication, retention policy, scheduling, etc. Then, click Finish to complete the configuration wizard.

    Image-Based Backup of SQL Server to Azure VM

    Use Cases

    If you have an Azure cloud subscription, you can implement a disaster recovery solution for SQL Server even more easily, as well as for any other server apps. CloudBerry Backup allows you to create image-level server backups and place them into Azure as a VM instance.

    The key difference between this approach and regular cloud backup is that you can instantly run a server instance as a virtual machine in the cloud and use it to restore server operations.

    In such cases, you only need to change the path to the SQL database for web apps or clients, then continue normal operations. Although the overall performance will be slower, your users will be able to continue their work.

    You do not need to create a separate backup plan: you can use one image-level backup for system restoration as well as for cloud-based disaster recovery.

    Here is how it works:

    Create an image-level backup for the server with Microsoft SQL Server and point to Azure Blob Storage as the target backup storage. You can even use Amazon S3 as a storage for your backup and still be able to restore the backup to Azure VM, if desired. However, storing the data in one single cloud storage location will improve restoration performance, because you won’t have to wait for data to transfer between clouds.

    Follow the steps below to create an image-based backup of your SQL Server.

    How to Create an Image-Based Backup of SQL Server

    1In CloudBerry Backup, click the Image Based button in the top left-hand corner, then choose Local or Cloud Backup as a template.
    Setting up image-based backup for MS SQL Server with CloudBerry Backup

    2In the next step, select the previously created Azure blob storage account as a target, click Next and then select the Image Based Backup option.

    3The Select Partitions step allows you to determine whether CloudBerry Backup should create a backup of only selected partitions, only system ones, or all partitions present.
    Selecting partitions for SQL Server image-based backup to AzureWe suggest using the Backup All Drives option only in situations where SQL data is stored on a separate partition. This will protect you from mistakes in the future.

    4Set the rest of the backup options on the next few steps of the wizard, such as block-level backup (which backs up only modified image blocks to decrease the amount of data transferred to the cloud), compression, deduplication, scheduling, etc. Then, finish the creation of the backup plan.

    SQL Server Restore to Azure VM

    In case you ever need to quickly recover the entire SQL server from malfunction, just run Restore to Azure VM wizard from the home panel.

    Launching 'Restore to Azure VM' wizard in CloudBerry Backup for MS SQL ServerFollow the wizard’s steps as described in the How to Restore to Azure VM Using CloudBerry Backup guide, to complete the restoration.

    Backup to Azure SQL Database

    There is one more SQL Server database cloud backup scenario worth mentioning: migration to Azure SQL database. We can split such situations into two cases:

    1. Replication to a cloud SQL Server instance for high availability or faster disaster recovery.
    2. Copying an existing local SQL database to a cloud SQL service if you would like to move the entire database to the cloud.

    For replication to a VM-based SQL instance, you can use Azure Data Sync client, which allows you to:

    • Optimize network transfers.
    • Connect to a database behind a firewall.
    • Perform replication between cloud-based and on-premises SQL Servers.
    • Replicate the data as frequently as every five minutes.

    To avoid sync issues, you need to provision the database schema first. Learn how to do that in this configuration guide from Microsoft.

    If you want to migrate a local database to Azure, you only need to create an empty SQL database in Azure, then use the Data Migration Assistant (DMA) to import the local database. SQL compatibility mode is available for imported databases with an older schema version. Here is a full configuration guide by Microsoft.

    Conclusion

    If you are running a business-critical SQL Server database, we suggest implementing local and off-site backup schemes (you can read more in our 3-2-1 backup strategy tutorial); this allows you to restore the data in any situation, as well as to resume an entire SQL Server using an Azure VM.

    If you need a higher RTO, consider implementing Azure Data Sync, which allows you to create geo-spread mixed SQL replication. Data Sync also allows you to lower the RPO to 5 minutes.

    Remember to implement local SQL Server backups using full/differential and T-log backups as a “hot” data protection measure. See for yourself how CloudBerry Backup for MS SQL Server can simplify your data protection management using our free 15-day trial.

    Vladislav M avatarAuthor
    Vladislav M
    Vlad is the chief analyst at CloudBerry. He has spent years tinkering with Microsoft SQL in the form of queries, and servers’ management. He shares his knowledge with CloudBerry so that it can be shared with CloudBerry clients. View all posts
    Published: by on Post Type: Categories: CloudBerry Backup, Microsoft
    Ratings:
    1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 5.00 out of 5)
    Loading...