With Remote Desktop Services 2016 we can use Azure SQL Database for hosting your RD Connection Broker Database (RDCB). Back in the RDS 2012 days we had to either build a SQL Mirroring or SQL Always On solution to provide High Availability to the RD Connection Broker database. Both SQL HA solutions were expensive especially on Azure. As a best-practice SQL needed to have premium storage for hosting the data and log files. Now with RDS 2016 we can use Azure SQL database for the RD Connection Broker database, but how about sizing the Azure SQL database service. In Azure SQL database you cannot simply chose the number of CPU cores and Memory which you want to use. On the Azure SQL database platform the performance is measured by Database Transaction Units (DTU’s). In this blogpost I want to explian how you can collect some performance metrics from your existing SQL Server and size your Azure SQL database.
Let’s start with exploring what a DTU is. Microsoft has documented this very well on his own documentation site. So I’m quoting:
‘A DTU is a unit of measure of the resources that are guaranteed to be available to a standalone Azure SQL database at a specific performance level within a standalone database service tier. A DTU is a blended measure of CPU, memory, and data I/O and transaction log I/O in a ratio determined by an OLTP benchmark workload designed to be typical of real-world OLTP workloads. Doubling the DTUs by increasing the performance level of a database equates to doubling the set of resource available to that database. For example, a Premium P11 database with 1750 DTUs provides 350x more DTU compute power than a Basic database with 5 DTUs.’
If you want to read more about DTU’s, check this documentation: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-what-is-a-dtu. So the performance of a SQL Database is measured by DTU’s, but what type of performance tiers are available for Azure SQL database. Again this is very well documented on the documentation site of Azure so I’m quoting again:
‘Azure SQL Database offers three service tiers, Basic, Standard, and Premium, with multiple performance levels to handle different workloads. Higher performance levels provide increasings resources designed to deliver increasingly higher throughput. You can change service tiers and performance levels dynamically without downtime. Basic, Standard, and Premium service tiers all have an uptime SLA of 99.99%, flexible business continuity options, security features, and hourly billing.’
If you want all the details around the different SQL database performance tiers, check this documentation: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers
Now we know how the performance is measured and which performance tiers are available let’s dive into how to size the Azure SQL database service for your Remote Desktop Services 2016 environment based on the current load of SQL Server hosting the RD Connection Broker database. Justin Henriksen has built a great tool which converts the current load of the SQL Server to DTU’s and the Azure SQL Database Service Performance Tier. The following steps are needed to get your sizing:
- Go to http://dtucalculator.azurewebsites.net/ and download either the Command Line Utility or the PowerShell Script to measure the current load of your SQL Server.
Prepare your SQL Server for running the tool or the script by:
- Stopping al processes other than SQL Server which are generating load (CPU, Memory and Disk) on the server;
- Isolate the RDCB database, the utility and script are collecting the server performance. If your RDCB is on a shared SQL Server try to stop the other databases during the collection of the performance;
Open the script and change the output path to the correct path where you want to save the output:
- Now execute the script in an elevated PowerShell session, I’m advising you to run the script multiple times. Important moments within a RDS environment are during logon and logoff storms. So try to capture these moments;
- Collect the CSV file from the path you have entered in step 3.
- If you don’t want to upload your Server Name to the calculator replace your Servername in the CSV file to a random name.
- Upload the file on the DTU Calculator website: http://dtucalculator.azurewebsites.net/
Based on the CSV file the DTU calculator will give a report and advise on which Service Tier you need to configure. See below some screenshots of my calculation:
Service Tier / Performance Tier Level:
DTUs Over Time:
Service Tier/Performance Level for CPU:
Service Tier/Performance Leve for IOPS:
Service Tier/Performance Leve for Log:
Service Tier/Performance Level for CPU, IOPS and Log:
Personally I think this is a great starting point for sizing the Azure SQL database for your RDS 2016 environment. But as stated on the DTU Calculator website the recommended Service Tier/Performance Level is only an approximation. When your database is running on Azure SQL database you can use the existing performance guidance of Azure SQL Database for additional tuning.