Back in June 2018, Microsoft announced the public preview of their new Azure Backup capability: SQL databases running in Azure VMs. Testing it out, I found that many features proved useful for the project we were working on (easy to set up and manage, support for SQL 2012 and later, central management of all database backups, point-in-time restores from the Azure portal, and support for Availability Groups).
We were excited to deploy this in our production environment as it solved many issues we were running into with other backup solutions (ex: Managed Backup available in 2014 but not 2012, fragmented management for each instance, multiple repositories for database backups, etc).
Having experience with a few SQL backup solutions (Managed Backup in SQL 2014+, Automated Backup in the Azure portal, and Ola Hallengren’s SQL Server Maintenance Solution), I was reasonably confident we could enable the Azure Backup solution for SQL databases with relatively little impact…
This is the part where you point at me and laugh…because then things went horribly wrong.
So, what happened? The primary instance for one of our production SQL Availability Groups completely locked up and all databases started to time out when anything tried to connect to them. When we finally managed to log onto the server, we found that disk throughput for the SQL database and log volumes was maxed out.
Until we opened a case with Microsoft support, we were unaware of two VERY important points with the preview for Azure Backup for SQL Server (these are now documented in the Microsoft document link listed below).
- Azure Backup does NOT support running a full backup (copy-only) from secondary replicas. Full backups can only run on the primary replica.
- By default, Azure Backup will attempt to back up 50 databases at once.
Because of this, the resulting events occurred on the primary SQL replica:
- Azure Backup for SQL Server was enabled and full backups were kicked-off.
- Azure Backup attempted to back up 50 databases at the same time.
- The read IO on the SQL data disks maxed-out their throughput (for 1TB premium disks, this caps at 200MB/s) as Azure Backup attempted to transfer data from the primary replica to the Recovery Services Vault.
- Since the primary replica was still technically “up” on the network, no failover occurred.
- Attempts to connect to these databases timed-out as all disk IO was occupied by the backup process, so no reads/writes on the SQL disks could occur
- To resolve, we disabled Azure Backup for the SQL databases and killed any backup processes on the primary instance.
Microsoft did implement a workaround for this issue. While you still can’t run a full backup from secondary replicas (at least, not yet), you can throttle the number of databases that Azure Backup will attempt to back up. See https://docs.microsoft.com/en-us/azure/backup/backup-azure-sql-database#faq.
To learn more about the Azure Backup for SQL Server solution, I highly recommend reading through Microsoft’s thorough documentation, which provides step-by-step instructions for setting this up. See https://docs.microsoft.com/en-us/azure/backup/backup-azure-sql-database.
Hopefully, this will be integrated into the Azure Backup policy in the near future rather than requiring a manual change on each SQL instance.
Update:
As of 3/18/2019, the Azure Backup for SQL server solution is now Generally Available. Along with this, the solution was updated to include support for copy-only full backups from secondary replicas in an Availability Group. Note that secondary replicas must be set to read-only in the AG settings and backup preferences must be set to Secondary or Secondary Only.
See updated documentation at:
https://docs.microsoft.com/en-us/azure/backup/backup-azure-sql-database
If you’re interested in learning more about related Hybrid IT & Cloud topics, please take a look at these recent posts. And if you need answers to any questions around RSAT—or any aspect of Windows Server—please don’t hesitate to contact us. We’d love to help you out.
Related Content

Tim O'Sullivan
Consultant
Tim O’Sullivan is a consultant with Anexinet’s Managed Services. As a member of the Infrastructure group, Tim specializes in on-premises and cloud infrastructure management in Azure/O365.
Let’s get the conversation started
Reach out now to begin your digital transformation
+ 16,659
ZOOM MEETINGS
+ 9,789
HAPPY CLIENTS
+ 5,075
FINISHED PROJECTS
+ 133,967,432
LINES OF CODE
© 2000 - 2021 Anexinet Corp., All rights reserved | Privacy Policy | Cookie Policy
This website uses cookies
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL cookies.
Manage consent
Privacy Overview
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie | Duration | Description |
---|---|---|
cookielawinfo-checbox-analytics | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics". |
cookielawinfo-checbox-functional | 11 months | The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional". |
cookielawinfo-checbox-others | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other. |
cookielawinfo-checkbox-necessary | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary". |
cookielawinfo-checkbox-performance | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance". |
viewed_cookie_policy | 11 months | The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data. |
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.