Configuring scheduled database backups has always been an important activity for database professionals. As we transition our SQL Server workloads into Microsoft Azure Infrastructure-as-a-Service (IaaS), we now have a number of options available for backing up databases which at first glance can be overwhelming. In this blog post we'll start to explore the different options along with their features, limitations and suitability
Backup to URL allows database backups to be written directly to Azure page blobs or block blobs. Essentially, blobs allow for large amounts of data to be stored using the Azure Blob storage service. To support blob data in Azure, a Storage Account is required to provide a location for the blob data files. A Container can then be created within that Storage Account to group these blob data files and provide access through what’s known as a Shared Access Signature (SAS) token. This token can then be used by applications and users that need access to the files in the Storage Account.
CREATE CREDENTIAL CredentialNameWITH IDENTITY = 'StorageAccountName',SECRET = 'AccessKey'BACKUP DATABASE ContosoDB TO URL = N'http://contoso.blob.core.windows.net/dbbackups/ContosoDB.bak'WITH CREDENTIAL = N'CredentialName', NAME = N'ContosoDB-Full Database Backup', STATS = 10As you can see, it's relatively easy to configure and doesn't differ greatly from our experience of backing up to disk. Using this method with SQL Server Agent jobs is perfect for database professionals that still want a traditional level of control over their backups, whilst also benefiting from the storage benefits of Azure.
Microsoft has attempted to integrate the task of configuring database backups with the virtual machine provisioning process in Azure. Whilst Automated Backup is an Azure configuration option for SQL Server virtual machines, it actually uses the Managed Backup feature within SQL Server behind the scenes. The SQL Server IaaS Agent Extension has a login to the instance which provides a method for the Automated Backup feature to access the instance, configure Managed Backup and subsequently backup the databases.
Where do the backups go? Managed Backup was introduced in SQL Server 2014 and was designed to work on top of the Backup to URL feature. As such, it will write the backup files to Azure Blob storage. Since Automated Backup uses the Managed Backup feature within SQL Server, it is limited to the features and functionality provided by Managed Backup in that product version. Putting it simply, you can think of Automated Backup as a managed service for database backups where you fill in the blanks and it will take care of the rest.
Automated Backup for SQL Server 2014 virtual machines provides a basic set of configuration options such as; a retention period, a storage account for storing the backups, an encryption setting and an encryption key password. The backup schedule is controlled by the Managed Backup feature and will perform backups based on how much of the data has changed. Finally, Page blobs provide the underlying storage for the backup files, which means a single backup file cannot exceed 1TB.
Automated Backup v2 for SQL Server 2016 virtual machines introduced a few enhancements which reflect some of the new capabilities of Manged Backup in SQL Server 2016. System databases can now be backed up and users can define schedules for full and transaction log backups.
Overall, Automated Backup v2 provides a simple, flexible and integrated approach to database backups in Azure. It reduces the overhead of managing traditional SQL Server Agent jobs and the ability to define a retention period will save you from writing automation code to delete old backups in blob storage. However, for those that want a traditional level of control; such as the ability to perform differential or filegroup backups; SQL Server Agent jobs and other backup utilities will continue to provide more flexibility.
As has been the case for many years on-premises, databases can be backed up to disks in Azure virtual machines. However, there are certain factors to consider that we wouldn’t ordinarily worry about on-premises. For example, Microsoft imposes a limit on the number of data disks that can be attached to an Azure virtual machine, depending on its instance size. Therefore, if maximising the number of IOPS for data and/or log files is critical to you, attaching a set of disks just for backups might not be very cost-effective, especially if the backups are large and disk space is consumed quickly. In this scenario, the maximum number of data disks could be reached within a short space of time, after which you'd need to incur a cost to scale-up the virtual machine to attach more disks. This is why Backup to URL is so compelling, data disks aren’t needed and Azure blob storage is virtually limitless.
Conversely, there might be several reasons why databases must be backed up to Azure data disks. An example might be where an organisation uses enterprise backup software that doesn't support backing up to Azure blob storage. In this scenario, backing up to data disks may be the only option.
Microsoft Azure wouldn't be complete if it didn't provide a set of integrated backup and restore capabilities. For a long time, organisations have used enterprise backup software to centralise their backup and restore processes. It will probably come as no surprise then that Microsoft's own enterprise backup software; System Center Data Protection Manager (DPM), has been extended to provide customers with a number of backup options in Microsoft Azure.
Azure Backup is a collective term that describes a set of components used to backup resources in Azure. At the time of writing, these components are:
The Azure Backup Agent backs up files and folders to an Azure Recovery Services Vault, an Azure Resource Manager (ARM) resource used to store and retain backups for user-defined periods of time. This supersedes the Azure Backup Vault, a legacy feature deployed via Azure Service Manager (ASM), which is no longer recommended. Azure Backup Agent is perfect for organisations with a small number of servers and workstations who just want to backup files and folders.
In combination with the Azure Backup Agent, DPM backs up files, folders, virtual machines and application workloads such as SQL Server. To support this solution, DPM should be installed on an on-premises server OR an Azure virtual machine, requiring a System Center license and SQL Server instance to host the DPM databases. Both deployment options allow backups to be stored on locally attached disks or sent to an Azure Recovery Services Vault but only the former will allow backups to tape. DPM Protection Groups define backup schedules and retention policies for sets of protected databases which can then be restored through the DPM console.
Built on the same technology as DPM, Microsoft Azure Backup Server (MABS) provides a slightly cut-down, simple and potentially cheaper alternative to DPM. As with DPM, a SQL Server instance is used to host the MABS databases. MABS is a free download that can be installed on a Windows virtual machine running in Azure or on-premises, and provides a centralised point where Protection Groups can be managed. Once configured, it can be used to backup workloads to locally attached disks or Azure Recovery Services Vault. In addition, with the latest release of Azure Backup Server v2, SQL Server 2016 databases running on-premises or in Azure can now be backed up.
Snapshotting virtual machines is nothing new to us. In fact, it's provided a method for us to protect virtual machines on-premises for the last decade. Azure IaaS VM Backup relies on the VMSnapshot extension; installed during the initial virtual machine backup. This extension coordinates the backup service with the local Volume Shadow Copy Service (VSS) to perform a consistent snapshot of the virtual machine. Furthermore, it allows for consistent SQL Server virtual machine backups using the SQL VSS writer service, which freezes I/O momentarily to obtain a consistent snapshot. Once the snapshot has been taken, it's placed within Azure Recovery Services Vault.
So which one should I pick? There's no right or wrong answer but as usual, business requirements such as Recovery Point Objectives (RPOs) and Recovery Time Objectives (RTOs) come into play. All the options described above offer varying levels of RPO depending on the backup schedule defined. For example, production copies of a database that are kept in sync with a secondary replica to minimise the recovery time, will have a much lower RTO than a standalone server that relies on backups to restore service, as it could take several days to transport and restore a database from a tape backup. Therefore, whilst it’s important to understand what you’ll get with an Azure backup solution, it’s equally important to understand that relying on backups alone will typically result in a higher service RTO when compared with disaster recovery solutions.
If you're already have investments and skills in System Center, then you won't go wrong with DPM. It provides a flexible, centralised solution for the majority of your workloads and with over 10 years of mainstream usage, it's solidified its position as Microsoft's primary enterprise backup offering. Azure Backup Server, provides a suitable alternative for smaller/mid-sized organisations without System Center licenses and without the need for on-premises functionality (e.g. tape backups). Whilst the MABS download is free, you'll still pay for the storage in Azure and the number and size of the backed-up databases, so it's important to understand the potential size of all the backed-up databases before making a decision between using DPM and MABS. These deployments also require some additional consideration, particularly around the availability of the DPM and MABS infrastructure as well as the roles and responsibilities, i.e. who will administer the environment and coordinate restore operations with the DBAs. Finally, whilst VM snapshot backups provide a far simpler approach, they provide the least amount of flexibility since the entire virtual machine will need restoring just to get to a single database.
Overall, Microsoft provides an array of options for backing up SQL Server workloads in Azure; some of which overlap, whilst others are designed to complement one another. If you feel there are too many overlapping options - you’re not alone! From experience, organisations moving to Azure have different levels of expectation, capability and maturity. What I feel Microsoft is trying to do is ensure that organisations moving to Azure have a broad set of supported alternatives to ensure that workloads are protected in Azure, regardless of skillset within the organisation. We can all agree that having a working backup and restore strategy that’s well-rehearsed and meets business requirements is important for everyone, whichever method you choose.
Coeo can advise you on whether a move to Azure is appropriate for you with our Architecture Workshop: