Azure SQL Database has four different service tiers which define the size, performance, features, and, most importantly, the cost of an Azure SQL Database. These service tiers are Basic, Standard, Premium, and Premium RS:
The Basic tier is the lowest tier available and is applicable to small, infrequently used applications, usually supporting one single active transaction at any given point in time.
The Basic tier has a size limit of 2 GB, a performance of 5 DTU, and costs $5/month:
This is the most commonly used service tier and is best for web applications or workgroups with low to medium I/O performance requirements. Unlike the Basic service tier, it has four different performance levels: S0, S1, S2, and S3. Each performance level offers the same size (250 GB), however, they differ in terms of DTUs and cost. S0, S1, S2, and S3 offer 10, 20, 50, and 100 DTU, and cost $15, $30, $75, and $150 per month, respectively:
The Premium service is used for mission critical, high-transaction volume applications. It supports a large number of concurrent users and has a high I/O performance compared to the Basic and Standard service tiers.
It has six different performance levels: P1, P2, P4, P6, P11, and P15. Each performance level offers different sizes and DTUs. P1, P2, P4, P6, P11, and P15 are priced as $465, $930, $1,860, $3,720, $7,001, and $16,003 per month, respectively:
The Premium service is used for low availability, high I/O intensive workloads such as analytical workloads. It has four different performance levels: PRS1, PRS2, PRS3, and PRS4, which are priced at $116, $232, $465, and $930 per month, respectively:
The amount of resources (CPUs, I/O and RAM) to be assigned to an Azure SQL Database in a particular service tier is calculated as Database Transaction Units (DTUs).
DTUs guarantee that an Azure SQL Database will always have a certain amount of resources and performance (offered under a particular DTU) at any given point of time, independent of other SQL databases on the same Azure SQL Server or across Microsoft Azure.
The ratio for the afore mentioned resources was calculated by Microsoft by running an OLTP benchmark.
DTU signifies how powerful an Azure SQL Database is. For example, if a workload of, say, five queries takes 80 seconds on a Basic tier with 5 DTUs, then it'll take four seconds on a Standard S3 tier with 100 DTUs.
In this section, we’ll learn how to scale up the Azure SQL Database service tier for better performance. Let’s go back to our previous example of Mike, who observes that there is an increase in the load on the SQL database in Azure. To overcome this problem, he plans to change the service tier for the database such that it can handle the overload. This can be achieved via the following steps:
- Open a new PowerShell console. In the PowerShell console, execute the following command to create a new Azure SQL Database from a
bacpacfile:C:CodeLesson02ImportAzureSQLDB.ps1
- Provide the Azure SQL Server name, SQL Database name, Azure SQL Server administrator user and password,
bacpacfile path, andsqlpackage.exepath, as shown in the following screenshot:
The script will use sqlpackage.exe to import the bacpac file as a new SQL Database on the given Azure SQL Server. The database is created in the Basic Service Tier, as specified in the PowerShell script.
It may take 10–15 minutes to import the SQL Database.
- Open
C:codeLesson02ExecuteQuery.batin Notepad. It contains the following command:ostress -Sazuresqlservername.database.windows.net -Uuser -Ppassword -dazuresqldatabase -Q"SELECT * FROM Warehouse.StockItems si join Warehouse.StockItemholdings sh on si.StockItemId=sh.StockItemID join Sales.OrderLines ol on ol.StockItemID = si.StockItemID" –n25 –r20 -1
- Replace
azuresqlservername,user,password, andazuresqldatabasewith the appropriate values. For example, if you are running the preceding command against Azure SQL Database withtoystorehosted on Azure SQL Servertoyfactorywith the usernamesqladminand passwordPackt@pub2, then the command will be as follows:ostress -Stoyfactory.database.windows.net -Usqladmin -PPackt@pub2 -dtoystore -Q"SELECT * FROM Warehouse.StockItems si join Warehouse.StockItemholdings sh on si.StockItemId=sh.StockItemID join Sales.OrderLines ol on ol.StockItemID = si.StockItemID" -n25 -r20 -q
The command will run 25 (specified by parameter -n25) concurrent sessions, and each session will execute the query (specified by parameter -Q) 20 times.
- Open the RML command prompt, enter the following command, and press Enter:
C:codeLesson02ExecuteQuery.bat
This will run the
ostresscommand. Wait for the command to finish execution. Record the execution time:
It took around 1 minute and 52 seconds to run 25 concurrent connections against the Basic service tier.
- The next step is to scale up the service tier from Basic to Standard S3. In the PowerShell console, execute the following command:
C:CodeLesson02ScaleUpAzureSQLDB.ps1
Provide the parameters, as shown in the following screenshot:
Observe that the database
Editionhas been changed toStandard. - Open a new RML command prompt and run the same
ostresscommand as in step 4. You should see faster query execution time in the Standard S3 tier than in the Basic tier.Here's the output from the
ExecuteQuery.batcommand:
It took around 42 seconds to run 25 concurrent connections against a Standard S3 Service Tier. This is almost 60% faster than the Basic tier. You get the performance improvement just by scaling up the service tier, without any query or database optimization.
You can scale up or scale down an Azure SQL Database at any given point of time. This gives the flexibility to save money by scaling down to a lower Service tier in off-peak hours and scaling up to a higher service for better performance in peak hours. You can either change a service tier manually or automatically. Service tier change is performed by creating a replica of the original database at the new Service Tier performance level. The time taken to change Service tier depends on the size as well as the Service tier of the database before and after the change.
Once the replica is ready, the connections are switched over to the replica. This ensures that the original database is available for applications during the service tier change. This also causes all in-flight transactions to be rolled back during the brief period when the switch to the replica is made. The average switchover time is four seconds, and it may increase if there are large number of in-flight transactions.
You may have to add retry logic in the application to manage the connection disconnect issues when changing Service tier.