When considering using Azure SQL Database, one of the most common questions that arises is related to the choice of the service tier needed. The service tier is measured in DTUs (Database Transaction Units), which can be determined by measuring the resource utilization (CPU, memory, and I/O) on your on-premises server.
To determine the number of DTUs and decide the service tier you need for an Azure SQL Database based on the workload for your on-premises SQL Server, you can follow these steps:
Step 1: Capture Performance Metrics
First, you will need to capture several performance metrics on your SQL Server using the Perfmon utility. This will provide the most accurate measurement by running a representative production workload during a time period that captures the expected range of usage.
Open Performance Monitor by using the Windows key + R keyboard shortcut to open the Run command. Then type “perfmon” and click OK to open the utility.
When you first open the tool, it will display a brief overview and a system summary with real-time data about memory, network adapter, physical disk, and processor usage. On the left, you will find the navigation pane with access to Performance Monitor, Data Collector Sets, and Reports.
Expand Data Collector Sets, right-click “User Defined,” select New, and click on Data Collector Set. Give it a descriptive name and select the “create from a template (Recommended)” option. Manually add the following performance counters:
- Processor – % Processor Time
- Logical Disk – Disk Reads/sec
- Logical Disk – Disk Writes/sec
- Database – Log Bytes Flushed/sec
Once the performance counters are selected, save and close the data collector set.
Step 2: Convert Performance Counters to CSV
The performance counters are captured in the .blg format, but the Microsoft DTU Calculator requires a CSV file. To convert the .blg file to a .csv format, follow these steps:
- Open a command prompt (Start–>Run–>Type cmd–>Enter) or from Tiles just type cmd and press enter.
- Use “cd” to change the directory to the one containing your .blg file.
- Type the command:
relog NameOfYourFile.blg -f CSV -o NewNameOfFile.csv
This command will create a new .csv file with the converted data.
Step 3: Use the DTU Calculator
Open Excel and import the new .csv file. Then, visit the website http://dtucalculator.azurewebsites.net/ and enter the number of cores and upload the CSV file.
Click the Calculate button to view your recommended Service Tier/Performance Level and DTUs. The charts provided on the website will give you an analysis of your database resource consumption.
Conclusion
Choosing the right service tier for Azure SQL Database is crucial for optimal performance and cost efficiency. By capturing performance metrics and using the DTU Calculator, you can accurately determine the number of DTUs and select the appropriate service tier for your workload.
Remember that Azure service models are split into DTU-based and vCore-based, and each has its own limitations. Consider using the decision matrix provided to help make the best choice for your specific workload.