Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Caution - Azure SQL Version 12 Restore Times Take Longer


By:   |   Last Updated: 2015-06-16   |   Comments (5)   |   Related Tips: More > Azure

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:

  1. I used the same bacpac file and restored it to a version 11 server and version 12 server.
  2. 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.
  3. Both databases before the point in time restore were Standard S0.
  4. 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).

SQL SERVER Database Settings

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.


Last Updated: 2015-06-16


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, September 11, 2015 - 1:17:00 PM - Eli Fisher Back To Top

As mentioned in my comment below, I am notifying people on this thread that we have fixed the restore performance issues for V12 servers. You should be able to see more consistent and faster restore speeds.

Regards,

Eli Fisher (Microsoft Program Manager Azure SQL Database)


Tuesday, July 21, 2015 - 10:51:41 AM - Eli Fisher Back To Top

Hello,

My name is Eli Fisher. I am a Program Manager working on Azure SQL Database. I first wanted to thank everyone here for their feedback regarding the performance of our restore capability on V12 servers. We have identified the issue and are currently working on a fix which should align the restore speeds to be much more similar to the pre-V12 servers.

I will respond to this forum when the fix reaches production. In the meantime, if you have any questions or concerns around Azure SQL Database Backup and Restore, please feel free to reach out to me at elfish at Microsoft dot com.

We also have a survey to collect feedback regarding the Azure SQL Database restore capabilities. If you would like, please fill out this survey so we can learn more about your experience using these capabilities.

Thank again,

-Eli Fisher

 


Tuesday, July 14, 2015 - 11:27:23 AM - Jan Vilimek Back To Top

No problem :) actual situation: about 8 GB DB...still restoring... more than 27 hours straight...50% same all the time..

Database restore of XXXXX.TempSnapshot completed 50% running from 07/13/2015 11:47:18 for 1656.03916855167 minutes

 

wow.. just .. wow :-(


Tuesday, July 14, 2015 - 11:08:00 AM - Tim Back To Top

@Jan Thanks for the comment, and yes, we've seen insane restore times in version 12.  Comments like these help me strengthen my case to Microsoft support that this issue needs to resolved ASAP.  I appreciate you taking the time to post your experience as well.


Monday, July 13, 2015 - 10:09:59 AM - Jan Vilimek Back To Top

Hello Tim. Thanks for the post. In fact we experience the same issues. Restore of DB about 150 MB in overall size took about 165 minutes (!!!). Another 2GB DB about 135 minutes now and not finished yet... we are using PIT restore for staging/test data refresh process... sooo annoying when this is taking so much time :-(

Will appreciate any update in that case,

BR

Jan


Learn more about SQL Server tools