Reputation: 2278
We are importing azure database from bacpac in blob and it takes 2 days with 100 %DTU. Sometimes I accept a quick but expensive restore (but want to pay additional costs only to restore time), how can I do it?
If I will change tier to higher level with 100 or 200 DTUs, will it speed up restore? Also what if I do it after starting of restore, will changes be applied or I have to do it initially while starting import?
After restore I will switch back to desired S2 tier. Another subquestion is what potential risk of downgrading database tier? At least I know about needs to disable geo-replication.
According to https://msdn.microsoft.com/en-us/library/azure/dn369872.aspx, it may takes hours to apply and normalize performance after upgrade and usually momentally after downgrade.
So ideal way it to start restore at maximal P6 tier, then downgrade to S2 and it must be momentally in most cases?
Particular case details:
Database size ~ 60 gb, bacpac ~ 5 gb, S2 Standard (50 DTUs). I saw 100% DTU percentage for whole restore time, and for last day import-export history stuck at "STATUS Running, Progress = 94.81 %" while database size increasing slowly from 30 to 60 Gb.
'select * from sys.dm_db_resource_stats' gives for example this
avg_cpu_percent=42.96
avg_data_io_percent=37.08
avg_log_write_percent=91.65
avg_memory_usage_percent=83.67
Upvotes: 4
Views: 3054
Reputation: 1230
Based on the scenario you have described it would be better to use the database copy TSQL to do a cross server database copy. I'm assuming when you export you first create a database copy to guarantee that you end up with a transactionally consistent BACPAC as suggested by this article. If this is the case, you will save some performance by just directly copying the database to the destination account and server. Cross server copy will work as long as the source server and target server have the same SQL login information.
Once you have confirmed the two servers share the same login credentials, all you'll need to do is login to the master database of the server you want to copy the database to, and run the following TSQL:
CREATE DATABASE [NEW_DATABASE_NAME] AS COPY OF [SOURCE_SERVER_NAME].[SOURCE_DATABASE_NAME]
This should significantly save you time as the exporting method requires you to read out all of data and schema of the database. Then you would need to copy that file (moving the data for a second time). Then you would need to read in all of the data to a new database (moving the data for a third time).
Having said all of this, if you absolutely need to use the export and import service, exporting and importing with a higher service tier will be faster than a lower one because you have more resources to read data out and in faster.
Please reply to this if you want anything clarified.
Upvotes: 3
Reputation: 621
From what you've described Azure SQL DB is performing as expected. The DTUs associated with your service-tier and performance-level do exactly impact the import time. You should upgrade to a higher performance-level as you import and down grade afterwards.
I hope this helps
Upvotes: 1