By: Tim Smith | Comments (5) | Related: > Azure Backup and Restore
Problem
We recently updated SQL Azure version 11 databases to version 12. After doing so, we noticed issues with point in time restores taking longer than they did in version 11.
Solution
This tip will address the point in time restore feature in Azure SQL. I've raised alerts to Azure engineers in that restores to point in times consume much more time in version 12 than in version 11. Depending on your environment or your clients, this may not be something that's acceptable. For instance, when testing restores to point in times, generally in version 11 the length of time is anywhere from 4 to 16 minutes (testing up to 5GB); also a 500MB database may restore as fast or slow as a 5GB database.
When one of my clients moved to version 12, we suddenly saw spikes in restore times. One of our regular processes involves cloning a database environment and since the point in time feature is so quick in version 11 and allows us to use cloned databases for reporting, we use this feature daily (which is why we immediately noticed the spike in times post upgrade to version 12). Because there's always the possibility that this could be a subscription issue, I used a ceteris paribus approach to testing in a separate subscription:
- I used the same bacpac file and restored it to a version 11 server and version 12 server.
- I waited a full day before restoring to a point in time; for both servers, the point in time was the exact same time UTC.
- Both databases before the point in time restore were Standard S0.
- According to Azure status, "all services were working properly."
The results of testing were that the version 11 database restored in 4 minutes and 19 seconds in round one, and 3 minutes and 39 seconds in round two. The version 12 databases restored in 52 minutes and 53 seconds in round one, and 6 minutes and 18 seconds in round two. The reason that led me to do these tests was that three different times, I had to cancel restore point in times because the time exceeded 15 hours on a version 12 server (after we upgraded the server from version 11 to version 12). This issue never happened when the server was version 11 (and given our frequency of restores to points in time, this is a large reference point for precision). In addition, version 12 restores to point in times, even when successful, consume much more time than version 11 restores to points in time and this is with similarly sized databases (all less than 5 GB).
For some environments, this may not matter as much because they can be offline for an extended time. However, some clients - like Wall Street traders - would not smile at waiting 15+ hours before having access to their data, so I'd highly advise to stay away from upgrading to version 12 and I'll update this tip when I see this issue consistently resolved (restores to point in times are a normal process).
Generally, I recommend financial clients or clients with massive data sets (a terabyte or more) use SQL Server, as it provides a ton of tools and support that allows DBAs to get things back online quickly. For those smaller clients or environments, this issue may be a reason to hold off upgrading for now, especially if the point in time restore is a feature you may use and you'll want your database online within a short time period.
Some helpful queries when running tests:
SELECT major_resource_id , percent_complete , operation , start_time , last_modify_time , (DATEDIFF(MINUTE,start_time,last_modify_time)/CAST(60.0 AS DECIMAL(13,4))) AdminOpHours FROM master.sys.dm_operation_status
This will tell you the status of the operations (see the column "operation" for the specific operation); you may barely notice this in a version 11 database because the restore times are so quick.
DROP DATABASE [OurRestoredDBThatIsTakingTooLong]
This will remove a database that is currently restoring if it's caught for too long. Unfortunately, stopping a restore and then restarting it does not guarantee that it will restore the database the second time.
If restores to points in time are a feature you use frequently, consider running multiple tests to confirm that the time frame of a version 12 restore does not affect any of your normal processes. If it does, I would suggest remaining in version 11. If you find yourself in version 12 and are unable to revert back to version 11 (keep in mind that you can create a version 11 server and migrate your databases back to it), then I would suggest dropping a restoring database if you think that it's hung (you'll have an idea through testing) and restart the restore.
As an example, if I've tested a restore on a database that generally requires less than 20 minutes, if it exceeds an hour, I can drop the restoring database and restart it. At this time, this seems to be one way to work around this issue (though I'd love to hear other ways).
When creating a new server, you can unselect the option "Enable latest SQL database update (v12)" and use version 11. Finally, I have interacted with a few Azure engineers about this and will update this tip when I get information about when this will be resolved, or when it resolves since this is a feature we use quite frequently.
Next Steps
- Run some tests, compare times, and provided you face no issues, go ahead and upgrade. If you see issues, do not upgrade.
- This issue may be resolved in the future.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
