I have been trying to do a PITR of a 2GB S0 Azure SQL Server db. It has been running for over 24hrs. The DB restore progress has been saying 50% complete for 18 Hrs without any errors. Should I upgrade the server DTUs and size or the actual service tier?
Asked
Active
Viewed 1,863 times
2 Answers
3
According to this post. On SQL Database, the "horsepower" is measured by Database Throughput Units, or just "DTUs". This unit is measured by an integer and may variate from 5 to 1750. Every database edition has an offer of one or more "Service Objectives", which are directly related to the number of DTUs and the price to be played.
In the following image, you can find the list of "Service Objectives" (S0, P3, Basic, P11, S3, etc…) per SQL Database Edition and its respective prices. Notice that Microsoft is always updating its offer, so those prices and Service Objectives per Edition may be outdated when you read this post:
One option is a more conservative, responsible and dignified way to choose the number of DTUs, and is based on real data about your database activity. It is the DTU Calculator (http://dtucalculator.azurewebsites.net/), an online service that helps us by advising about the most appropriate Service Objective for a database. You just need to download a PowerShell script, available on the DTU Calculator website, and run it in the server where your database is located. As soon as you run this script, the following data will be measured and recorded in a CSV file:
- Processor – % Processor Time
- Logical Disk – Disk Reads/sec
- Logical Disk – Disk Writes/sec
- Database – Log Bytes Flushed/sec
Once the collection is done, you just need to upload the file generated by the script and interpret the results. Here is a sample of one of the charts generated by the DTU Calculator, indicating that 89.83% of the database load would run well with the Service Objective S3, of the "Standard" SQL Database edition.
Here is a decision tree that will help you to reach the optimal point for your database.
So I think you can increase the DTU appropriately to speed up the process. :)
Joseph Xu
- 5,607
- 2
- 5
- 15
-
1Thanks, that's super helpful. I started the restore again with an instance using 50 DTU's and this did the trick. – Joshua Alexander Stewart Jun 21 '21 at 07:36
0
If you are on a S0 you are using Azure SQL Database, not a Managed Instance.
2GB is quite small, it should have recovered the point in time restore in an hour or so.
Contact Microsoft Support.
Francesco Mantovani
- 10,216
- 13
- 73
- 113