Tag Archives: Backup
Developing a backup plan
The most important task for a DBA is to be able to recover a database in the event of a database becoming corrupted. Corrupted databases can happen for many different reasons. The most common corruption problem is from a programming error. But databases can also be corrupted by hardware failures. Regardless of how a database becomes corrupt, a DBA needs to have a solid backup strategy to be able to restore a database, with minimal data loss. In this article, I will discuss how to identify backup requirements for a database, and then how to take those requirements to develop a backup strategy.
Why develop a backup plan?
You might be wondering why you need to develop a backup plan. Can’t a DBA just implement a daily backup of each database and call it good? Well, that might work, but it doesn’t consider how an application uses a database. If you have a database that is only updated with a nightly batch process, then having a daily backup of the database right after the nightly update process might be all that you need. But what if you had a database that was updated all day long from some online internet application. If you have only one backup daily for a database that gets updated all day online, then you might lose up to a day’s worth of online transactions if it was to fail right before the next daily backup. Losing a day’s worth of transaction most likely would be unacceptable. Therefore, to ensure minimal data loss occurs when restoring a database, the backup and recovery requirements should be identified first before building a backup solution for a database.
Identifying backup and recovery requirements
Each database may have different backup and recovery requirements. When discussing backup and recovery requirements for a database, there are two different types of requirements to consider. The first requirement is how much data can be lost in the event of a database becoming corrupted. Knowing how much data can be lost will determine the types of database backups you need to take, and how often you take those backups. This requirement is commonly called the recovery point objective (RPO).
The second backup requirement to consider is how long it will take to recover a corrupted database. This requirement is commonly called the recovery time objective (RTO). The RTO requirement identifies how long the database can be down while the DBA is recovering the database. When defining the RTO, make sure to consider more than just how long it takes to restore the databases. Other tasks take time and need to be considered. Things like identifying which backup files need to be used, finding the backup files, building the restore script/process, and communicating with customers.
A DBA should not identify the RTO and RPO in a vacuum. The DBA should consult each application owner to set the RTO and RPO requirements for each database the application uses. The customers are the ones that should drive the requirements for RTO and RPO, with help from the DBA of course. Once the DBA and the customer have determined the appropriate RTO and RPO then, the DBA can develop the backups needed to meet these requirements.
Types of backups to consider
There are a number of different backup types you could consider. See my previous article for all the different backup types. Of those different backup types, there are three types of backups that support most backup and recovery strategies. Those types are Full, Differential, and Transaction log.
The Full backup, as it sounds, is a backup that copies the entire database off to a backup device. The backup will contain all the used data pages for the database. A full backup can be used to restore the entire database to the point in time that the full backup completed. I say completed because, if update commands are being run against the database at the time the backup is running, then they are included in the backup. Therefore, when you restore from a full backup, you are restoring a database to the point-in-time that the database backup completes.
A differential backup is a backup that copies all the changes since the last full backup off to a backup device. Differential backups are useful for large databases, where only a small number of updates have been performed since the full backup. Differential backups will run faster and take up less space on the backup device. A differential backup can’t be used to restore a database by itself. The differential backup is used in conjunction with a full backup to restore a database to the point in time that the differential backup completed. This means the full backup is restored first, then followed by restoring the differential backup.
The last type of backup is a transaction log backup. A transaction log backup copies all the transaction in the transaction log file to a backup device. It also removes any completed transactions from the transaction log to keep it from growing out of control. A transaction log backup, like a differential type backup, can’t be used by itself to restore a database. It is used in conjunction with a full backup, and possibly a differential backup to restore a database to a specific point-in-time. The advantages of having a transaction log backup are you can tell the restore process to stop at any time during the transaction log backup. By using the stop feature, you can restore a database right up to the moment before a database got corrupted. Typically, transaction logs are taken frequently, so there might be many transaction log backups taken between each full or differential backup. Transaction log backups are beneficial for situations when there is a requirement of minimal data loss in the event of a database becoming corrupted.
Developing a backup strategy for a database
When determining a backup plan for a database, you need to determine how much data can be lost and how long it takes to recover the database. This is where the RTO and RPO come in to determine which types of database backups should be taken. In the sections below, I will outline different database usage situations and then discuss how one or more of the backup types could be used to restore the database to meet the application owner’s RTO and RPO requirements.
Scenario #1: Batch updates only
When I say “Batch Updates Only”, I am referring to a database that is only updated using a batch process. Meaning it is not updated online or by using an ad hoc update processes. One example of this type of database is a database that receives updates in a flat-file format from a third-party source on a schedule. When a database receives updates via a flat-file, those updates are applied to the database using a well-defined update process. The update process is typically on a schedule to coincide with when the flat-file is received from the third-party source. In this kind of update situation, the customer would have an RPO that would be defined something like this: “In the event of a corrupted database, the database would need to be restored to after the last batch update process”. And would have an RTO set to something like this: “The restore process needs to be completed within X number of hours”.
When a database is only updated with a batch process, that is run on a schedule, all you need is to have a full back up right after the database has been updated. By doing this, you can recover to a point in time right after the database has been updated. Using the full backup only will meet the RPO. Since the time needed to restore a full backup is about the same time as it takes to backup, the RTO needs to be at least as long as it takes to run a restore process, plus a little more time for organizing and communicating a restore operation.
Scenario #2 – Batch updates only, with short backup window
This scenario is similar to the last scenario, but in this situation, there is very little time to take a backup after the batch processing completes. The time it takes to back up a database is directly proportional to the amount of data that needs to be backed up. If the time for a backup is short, it might be too short to take a full back up every time the database is updated. This might be the case when the database is very, very large. If there isn’t time to do a full database backup and the amount of data updated is small, then a differential backup would be a good choice to meet the RPO/RTO requirements. With a differential backup, only the updates since the last full backup are copied to the backup device. Because only the updates are backed up and not the entire database, a differential backup can run much faster than a full backup. Keep in mind, to restore a differential backup, you must first restore the full backup. In this situation, a full backup needs to be taken periodically with differential backups being taken in between the full backups. A common schedule for this would be to take the full backup when there is a large batch window, like on a Sunday when there is no batch processing, and then differential backups during those days when the batch window is short.
Scenario #3 – Ad hoc batch updates only
Some databases are not updated on a schedule but instead are updated periodically but only by an ad hoc batch update process that is manually kicked off. In this situation, there are a couple of different ways of handling backing up of databases that fall into this category. The first one is just routinely to run full database backups on a schedule. The second is to trigger a backup as the last step of the ad hoc batch update process.
A routine scheduled full backup is not ideal because the backups may or may not be run soon after the ad hoc batch update process. When there is a period of time between the ad hoc process and the scheduled full backup, the database is vulnerable to data loss should the database become corrupted for some reason before the full backup is taken. In order to minimize the time between the ad hoc update and the database backup, it would be better to add a backup command to the end of the ad hoc update process. This way, there is a backup soon after the ad hoc process, which minimizes the timeframe for when data could be lost. Additionally, by adding a backup command to the ad hoc update process, you potentially take fewer backups, which reduces the processing time and backup device space, over a routine backup process.
Scenario #4 – Online updates during business hours
In this scenario, the database gets updates from online transactions, but these online transactions are only run during business hours, say 8 AM to 5 PM. Outside of regular business hours, the database is not updated. In this situation, you might consider a combination of two different types of backups: Full and Transaction log backups. The full backup would be run off-hours, meaning after 5 PM and before 8 AM. The transaction log backups will be used during business hours to back up the online transactions shortly after these transactions have been made. In this situation, you need to review the RPO to determine how often to run transaction log backups. The shorter the RPO, the more often you need to take transaction log backups. For example, suppose a customer says they can lose no more than an hour worth of transactions, then you need to run a transaction log backup every hour between the hours of 8 AM and 5 PM.
Scenario #5 – Online updates 24×7
Some databases are accessed every day all day. This is very similar to Scenario #4, but in this case, the database is accessed and updated online 24×7. To handle backup and recovery in this situation, you would take a combination of full and differential backups along with transaction log backups.
With a database that is updated 24×7, you want to run the full and differential backups at times when the databases have the least number of online updates happening. By doing this, the performance impact caused by the backups will be minimized. There are way too many different database situations to tell you exactly how often a full or differential backup should be taken. I would recommend you try to take a full backup or differential backup daily if that is possible. By doing it daily, you will have fewer backup files involved in your recovery process.
The transaction log backups are used to minimize data loss. Like scenario #4, the frequency of transaction log backups is determined by the RPO requirements. Assuming that the customer can lose one hour’s worth of transitions, then transaction log backup would need to be run hourly, all day, every day to cover the 24×7 online processing.
Developing a backup plan
It is important for a DBA to work with the application owners to identify the backup and recovery requirements for their databases. The application owners determine how much data they can lose (RPO), and how long the database can be down while it is being recovered (RTO). Once the RTO and RPO requirements are defined, the DBA can then develop a backup plan that aligns with these requirements.
Dynamics 365 Daily Solution Backup
Credits
Our goal was to build a system which would provide daily snapshots of a Dynamics 365 environment in daily flux as modifications were made continually by a development team. Such a system would guard against catastrophic tenant events, have the added benefit of speeding deployment to new tenants and streamlines the steps to move between environments (dev, test, prod).
For such a solution to be effective, the following conditions or their analogues should exist:
- A git repository exists for the project, where resides the source code for any customizations (plugins, workflow actions, etc.).
- The development team is storing their configuration changes (new/changed entities, workflow processes, etc.) in either their own solutions or in a central working solution, and not the Dynamics 365 Default solution.
- A Virtual Machine, local machine, or some other host environment exists to run the PowerShell script. (If this is not the case, alternate approaches may be used).
Approach 1: PowerShell Script
The first approach centered on a PowerShell script and might best be applied in on-premise or IAAS scenarios.
The basis of this solution is the PowerShell script provided in this document. In our case, it was configured to run at a certain time each day on an Azure Virtual Machine that was already running 24/7. A Windows Scheduler job was created on the Virtual Machine using saved credentials that had appropriate permissions to both the D365 Organization and the git repository in the teams’ Azure DevOps instance.
Daily backups generated by this script were saved in the git repository under a folder structure according to Month and date.
######################################################################
# #
# Script to backup daily git repo changes and D365 solution packages #
# Prepared by: #
# pHil Rittenhouse (philirit) #
# and #
# Kevin Neely (keneely) #
# #
######################################################################
# Allow and install the required modules
Set-ExecutionPolicy RemoteSigned -Scope CurrentUser
Import-Module Microsoft.Xrm.Data.Powershell
# Get today's date for use as the folder name
$ date = Get-Date -Format MM-dd-yyyy
$ month = Get-Date -Format MMMM
# Create the folder name from the date variable
$ path = "C:\<path>$ month$ date"
$ gitpath = "C:\<path>\"
# If today's folder already exists, delete it
If(Test-path $ path) {Remove-item $ path -Recurse}
# Create today's folder
New-Item -ItemType directory –Path $ path
# Change working directory to new folder
Set-Location -Path $ gitpath
# Pull from github
git pull
# Get the environment details
$ CrmConTimeOutMinutes = 10
$ CRMServerURL = "https://<org>.crm.dynamics.com"
# Build a connection string
$ cs = "RequireNewInstance=True"
$ cs+= ";Url=$ CRMServerURL"
$ cs+= ";AuthType=Office365"
$ cs+= ";SkipDiscovery=True"
# REPLACE WITH YOUR USERNAME/PASSWORD
$ cs+= ";Username=<user>@<org>.onmicrosoft.com"
$ cs+= ";Password=<password>"
# Create a connection using ConnectionString (for Online)
$ Conn = Get-CrmConnection -ConnectionString $ cs -MaxCrmConnectionTimeOutMinutes $ CrmConTimeOutMinutes
# OR # Create a connection using ConnectionString (for On-Premise)
#$ Conn = Get-CrmConnection -ConnectionString "AuthType=AD;Url=https://myserver/Contoso;Domain=contosodom;UserName=user1;Password=password" $ cs -MaxCrmConnectionTimeOutMinutes $ CrmConTimeOutMinutes
# Publish ALL updated customizations:
Publish-CrmAllCustomization -conn $ Conn -Verbose
# Export each of the solutions - APPEND TO THIS LIST IF ADDITIONAL SOLUTIONS ARE CREATED
Export-CrmSolution -SolutionName _pHil -SolutionFilePath $ path -SolutionZipFileName _pHil.zip
Export-CrmSolution -SolutionName _Kevin -SolutionFilePath $ path -SolutionZipFileName _Kevin.zip
# Add changes
git add .
# Commit changes
git commit -m "Backup for $ date"
# Push changes
git push
# To enable mobile push notifications signup at notify17.net
# Notify upon completion
$ notifyParams = @{title="Solution Bakup Completion";content="Solutions have been backed up for $ date"}
Invoke-WebRequest -Uri https://hook.notify17.net/api/raw/<api> -Method POST -Body $ notifyParams
# DEPRECATED - use if want to collect all output into a single zip file
# Get the name/path for the zip archive
#$ archive = "C:\<path>\Solutions_$ date.zip"
# If the archive file already exists, delete it
# If(Test-path $ archive) {Remove-item $ archive}
# Add-Type -assembly "system.io.compression.filesystem"
Approach 2: Azure DevOps Pipeline
The PowerShell/on-prem/IAAS solution was effective and met the need, but we saw the advantage in leveraging newer technologies and rebuilt the solution as an Azure DevOps Pipeline.
Prerequisites
The following components or configurations are required for this approach:
- A Dynamics Organization.
- Appropriate access to publish customizations and export solution packages will be required.
- As in the other approach, developers should be using named/specified solutions in Dynamics rather than customizing the Default Solution directly.
- Azure DevOps Organization, with the following configured:
- A Repository for the project. A Git repository was used in our case. If Team Foundation Version Control is configured for your project, changes will need to be made in the scripts and configuration elements of the Azure Pipeline. These changes are out of scope of this version of the solution documentation.
- Admin rights to the ADO Organization in order to build and configure the Pipeline.
- PowerApps Build Tools will be used as a key component in the Pipeline.
Building and Configuring the ADO Pipeline
- Identify which solutions are to be backed up from Dynamics.
- In Azure DevOps, select Pipelines from the left navigation:
- Create a new Pipeline.
- Select “Use the classic editor” on the first screen:
- Select the appropriate options here and click Continue:
- Click Start with an Empty Job:
- Save & Queue the Pipeline. Make sure the Repo exists or the Job will fail.
- Next, we must ensure the identity running the Pipeline has appropriate permissions. Click the Settings icon and then Repositories:
- Select the Repository to be backed up, then the User for the Build Service, and change the Contribute permission to “Allow”:
- Return to the Pipeline and enter Edit mode to start creating the steps.
- The first two tasks needed are PowerApps Tool Installer and PowerApps Publish Customizations:
- To configure these steps, we must first configure the environment connection. Click “Manage” for the PowerApps Environment URL:
- Click on “Create Service Connection”.
- Select “Generic” and click “Next”.
- Configure the connection with your endpoint URL and credentials. Click Save when complete.
- Return to the Pipeline build and select the newly created URL:
- Next, we will add the script to set up the dated folders. Click Add Task > Utility > Command Line:
- The script for this first Command Line step is as follows:
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do ( set dow=%%i set month=%%j set day=%%k set year=%%l ) @echo ##vso[task.setvariable variable=year]%year% @echo ##vso[task.setvariable variable=month]%month% @echo ##vso[task.setvariable variable=day]%day% - Create three variables for the Pipeline: “year”, “month”, and “day”:
- Add and configure a PowerApps Export Solution task:
- Add another Command Line Script task to handle the Git commit, using the following script:
echo commit all changes git config user.email user@domain.com git config user.name "Automatic Build" git checkout master cd Backup mkdir $ (year)$ (month)$ (day) cd $ (year)$ (month)$ (day) git add –all git commit -m "solution init" echo push code to new repo git -c http.extraheader="AUTHORIZATION: bearer $ (System.AccessToken)" push origin master
- The last step is to ensure the Job can use your OAuth token. Click on the job name, and scroll down to enable the OAuth checkbox:
- Save & Queue, then Run the job.
- Once the Job has completed, go back to your repo and verify that the backup folder and file(s) have been created:
3 Tips for Leveraging Existing Infrastructure for Backup
Vision Solutions, which recently merged with Syncsort, has been in the business of high availability and disaster recovery for more than two decades. The article below about the value of utilizing existing data infrastructure originally appeared on their blog.
It seems everyone is facing discouraging budget cuts at the same time they’re facing unprecedented data growth. It’s a conundrum that’s here to stay – but, you know what they say: when the going gets tough, the tough figure out how to use what they’ve got while still dreaming of a shiny new rack of servers. Wait, is that the saying?
Smart IT administrators will refuse to compromise data integrity just because a few hundred thousand dollars got snatched from their budget. If you are revamping your backup and recovery plan to account for growing data, here are three ways you can maximize the use of your existing infrastructure and save money without sacrificing safety:
1. Love the WAN You’re With
Choose backup and recovery software that works with your existing WAN. Replication software should allow you to either “throttle” the data queue, or let you control when backups are transmitted so it won’t disrupt daily traffic. If you can’t throttle, then it should allow you to compress the data. Good software will allow you to do both.
2. Send only changes
It’s not necessary to send the same data over and over again – it’s inefficient and takes up processing power and bandwidth. That terabyte of data that you have to protect across a T1 is not going to fit in within your 8 eight hour backup window, so how about just moving the changes instead of everything? Replicating only the changed data also saves you space on your repository, as you are only storing one copy of the information, versus multiple copies that a full backup would require.
3. Consider the Cloud
The undeniably awesome thing about cloud storage is you only pay for what you need and you don’t need to add one single piece of hardware or infrastructure to use it. There’s no reason to add to or build a data center when someone has already built one for you. And it probably runs on wind energy or the happy dreams of little fluffy bunnies. Cloud backup and recovery can be fully automated and storage can scale automatically too, saving you administrative time. And hey, if it’s good enough for the feds, it’s good enough for you.
We’re partial to our software, of course, but when you’re shopping around for backup and recovery software be sure to consider software that lets you use your existing infrastructure.
Want to learn more about current IT trends and what the future holds? Check out 2017 State of Resilience Report from Vision Solutions, which reviews the developments of the past decade while summarizing this year’s research findings.
Data Backup vs. Disaster Recovery: Yes, There’s a Big Difference
You back up your data. You’re prepared to handle the next big disaster, right? Wrong. Disaster recovery requires much more than data backup.
The Difference Between Data Backup and Disaster Recovery
Let me begin this discussion with a personal revelation. I don’t have a disaster recovery plan for my personal data. I do back virtually all of it up, however, using cloud services.
If the unexpected happens – I lose my laptop, my hard disk suddenly gives up the ghost or my computer suffers a fatal coffee spill – I can use my data backups to recover from the event.
But recovering will take me a long time and a fair amount of effort. I’ll have to remember the password to my cloud backup service. I’ll have to download all my files and put them on my new computer, probably losing some of the original directory structure in the process. I’ll probably also have to reconfigure some of my applications manually because some of them depend on configuration files that are not part of my backup routine.
This is all fine because I’m just an individual. If it takes me a few days to recover from a disaster, no one’s going to go insane or get sued.
If I were a business, however, my current data backup strategy would fall far short of providing the complete disaster recovery solution that I’d need to ensure I could recover from an unexpected event quickly enough to prevent serious damage to the business or its customers. I might also be responsible for keeping pace with compliance regulations that require me to be able to back up and restore sensitive data within a specific time frame. (Related: Planned or Unplanned, All Downtime is Bad)
Building a Complete Disaster Recovery Plan
I mention my personal backup strategy to illustrate why backing up data is only the first step in a complete disaster recovery plan.
To prepare fully for a disaster, you should not only back up data somewhere, but also do the following:
1. Ensure that all relevant data is backed up.
You may not need to back up every bit of information on your file systems. Temporary files, for example, probably don’t need to be backed up. On the other hand, it can be easy to overlook certain types of files that you would want to back up (such as configuration files in the /etc. directory on a Linux operating system – which I’d back up from my personal system if I were more responsible).
2. Secure your data backups.
Backed-up data is no good if it is damaged or data quality errors are introduced into it. In addition, data backups can be a fertile source of information for attackers in search of sensitive data. For both reasons, it’s crucial to ensure that your data backups are secured against intrusions.
3. Determine how frequently backups should be performed.
Performing continuous real-time backups of all your data is the ideal, but it is usually not feasible. Instead, most organizations determine how frequently they should back up data by determining how much of a lag in data they can tolerate without a critical disruption to business operations.
If you could afford to lose a day’s worth of customer records (or recover those records manually in a reasonable period of time), then you can perform daily backups. If you can tolerate only an hour’s worth of lost data, then do a data backup every hour. (Also read: How to Calculate RPO and RTO)
4. Include your personnel in the plan.
Determine who will perform backups and who will be on call to restore data in the event of an emergency.
5. Have a process in place for recovering data.
Backing data up is one thing and restoring it is another. You should have a recovery plan in place for different scenarios: One in which your infrastructure remains intact but your data is lost (in which case you can recover from backups to your original infrastructure), and another for a situation where you need to stand up totally new infrastructure, then recover data to it. In both cases, your data recovery plan should include as much automation as possible so that you can get things back up and running quickly. However, you should also build in safeguards to ensure that important data is not overlooked during backups, or files corrupted.
6. Ensure the quality of data backups and recovered files.
The data you back up and recover is only useful if it is free of errors and inconsistencies. This is why data quality should be built into your recovery plan.
Again, if you’re an individual, you can get away with just backing up your data. But any business hoping to survive a major unexpected event that impacts its software or data needs a complete disaster recovery plan in place. Data backups are only one part of that plan.
The 2017 State of Resilience Report reviews the developments of a decade while summarizing this year’s research findings. This comprehensive report discusses that and all the pressing trends regarding high availability and disaster recovery, migrations, data sharing and the cloud.
Unable to restore a backup – Msg 3241
I worked on an interesting issue today where a user couldn’t restore a backup. Here is what this customer did:
- backed up a database from an on-premises server (2008 R2)
- copied the file to an Azure VM
- tried to restore the backup on the Azure VM (2008 R2 with exact same build#)
But he got the following error:
Msg 3241, Level 16, State 0, Line 4
The media family on device ‘c:\temp\test.bak’ is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 4
RESTORE HEADERONLY is terminating abnormally.
We verified that he could restore the same backup on the local machine (on-premises). Initially I thought the file must have been corrupt during transferring. We used different method to transfer file and zipped the file. The behavior is the same. When we backed up a database from the same Azure VM and tried to restore, it was successful.
We were at a point I thought there might be a bug and I was planning to get the backup in house to reproduce the problem until this customer told me that they are using a tool called “Microsoft SQL Server Backup to Microsoft Azure Tool” which is only necessary to use for SQL 2008 R2 or below because SQL 2012 and above have builtin functionality to backup to and restore from Azure blob storage. Then I said how about we stop that service to see what happens. After stopping that service (screenshot below), restore now works perfectly fine. After restarting it, the same error.
After a little research, I found out more about this tool. The backup tool basically is a filter driver that watches any files with certain extension you have configured when SQL Server tries to access the file.
- when SQL Server does a backup, the tool will redirect the file to Azure blob storage and leaves a small stub file on local computer
- when SQL Server does a restore, the tool will try to access the same file from Azure blog stroage and give it to SQL Server
Now you can see the problem. In this customer’s scenario, the *.bak file was transferred from on-premise server directly to the Azure VM. there is no corresponding file on the Azure blob storage. Since the tool can’t find the file to provide content to SQL Server, the restore fails with the error.
Here are ways how you fix the issue
The cleanest way is to configure the rule to watch files only to specific path instead of watching the whole computer. By default, all path on the local machine is checked. But you can add multiple paths to watch. If you do this, you can simply put the backup you copied from remote machine (which is not part of the backup from the server watched by the tool) to a different folder and perform restore.
Alternatively, you can stop this tool when you restore a backup that you copied from a different machine.
You can also use a different file extension for the file you copied from remote machine and try to restore
Jack Li |Senior Escalation Engineer | Microsoft SQL Server
Backup to remote Azure URL failed due to throttling at VM level
There are multiple levels of throttling with Azure. You can get throttled at disk level, storage account level or VM/host level. These types of throttling can manifest into something that may not be that obvious.
Recently we received a call from customer who was backing up databases from Azure VM to Azure blob storage. The VM was DS13 using premium storage and backup URL is standard storage. Periodically, he received the following error:
Backup BackupIoRequest::ReportIoError: write failure on backup device ‘https://<accountname>.blob.core.windows.net/dbbackup/backupfilename.bak’. Operating system error Backup to URL received an exception from the remote endpoint. Exception Message: The client could not finish the operation within specified timeout..
The error message seemed to indicate that something is wrong from remote storage. So we engaged our storage team to analyze it. In this case, it turned out that the throttling occurred at VM level. Customer was on DS13, this size of VM only allows 256MB IO throughput per https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sizes/. Below is a screenshot (“max uncached disk throughput:IOPS/MBps). Note that throttling at VM level includes all IO incurred and combined including backup, data and log IO.
So the question is how do know if you are throttled at VM/host level? You won’t get any errors in that nature. But you can use two counters in the Azure Portal for the VM to estimate how much data was pushed through to estimate. You can enable “Disk Read Guest OS” and “Disk Write Guest OS” to measure data going in and out.
So what’s the solution for this scenario? The easiest is to upgrade VM size. DS14 allows you to have twice as much throughput. You can also make sure you don’t do backups all at once (spread them out)
Jack Li |Senior Escalation Engineer | Microsoft SQL Server