1.3 Design a SQL Database solution
1.3.5 Determine the appropriate service tier
https://azure.microsoft.com/en-us/documentation/articles/sql-database-service-tiers
Azure SQL Database provides multiple service tiers to handle different types of workloads. You can create a single database with defined characteristics and pricing. Or you can manage multiple databases by creating an elastic database pool. In both cases, the tiers include Basic, Standard, and Premium. But the database options in these tiers vary based on whether you are creating an individual database or a database within an elastic database pool. This article provides an overview of service tiers in both contexts.
Service tiers and database options
Basic, Standard, and Premium service tiers all have an uptime SLA of 99.99% and offer predictable performance, flexible business continuity options, security features, and hourly billing. The following table provides examples of the tiers best suited for different application workloads.
Service tier
Target workloads
Basic Best suited for a small size database, supporting typically one single active operation at a given time. Examples include databases used for development or testing, or small scale infrequently used applications.
Standard The go-to option for most cloud applications, supporting multiple concurrent queries. Examples include workgroup or web applications.
Premium Designed for high transactional volume, supporting a large number of concurrent users and requiring the highest level of business continuity capabilities. Examples are databases supporting mission critical applications.
NOTE:
Web and Business editions are being retired. Find out how to upgrade Web and Business editions. Please read the Sunset FAQ if you plan to continue using Web and Business Editions.
For single databases there are multiple performance levels within each service tier, you have the flexibility to choose the level that best meets your workload’s demands. If you need to scale up or down, you can easily change the tiers of your database in the Azure Classic Portal, with zero- downtime for your application. See Changing Database Service Tiers and Performance Levels for details.
Performance characteristics listed here apply to databases created using SQL Database V12. In situations where the underlying hardware in Azure hosts multiple SQL databases, your database will still get a guaranteed set of resources, and the expected performance characteristics of your
individual database is not affected.
For a better understanding of DTUs, see the DTU section in this topic.
NOTE:
For a detailed explanation of all other rows in this service tiers table, see Service tier capabilities and limits.
Elastic database pool service tiers and performance in eDTUs
In addition to creating and scaling a single database, you also have the option of managing multiple databases within an elastic database pool. All of the databases in an elastic database pool share a common set of resources. The performance characteristics are measured by elastic Database Transaction Units (eDTUs). As with single databases, elastic database pools come in three service tiers: Basic, Standard, and Premium. For elastic databases these three service tiers still define the overall performance limits and several features.
Elastic database pools allow these databases to share and consume DTU resources without needing to assign a specific performance level to the databases in the pool. For example, a single database in a Standard pool can go from using 0 eDTUs to the maximum database eDTU (either 100 eDTUs defined by the service tier or a custom number that you configure). This allows multiple databases with varying workloads to efficiently use eDTU resources available to the entire pool.
Each database within a pool also adheres to the single-database characteristics for that tier. For example, the Basic pool has a limit for max sessions per pool of 2400 - 28800, but an individual database within that pool has a database limit of 300 sessions (the limit for a single Basic database as specified in the previous section).
Understanding DTUs
The Database Transaction Unit (DTU) is the unit of measure in SQL Database that represents the relative power of databases based on a real-world measure: the database transaction. We took a set of operations that are typical for an online transaction processing (OLTP) request, and then
measured how many transactions could be completed per second under fully loaded conditions (that’s the short version, you can read the gory details in the Benchmark overview).
A Basic database has 5 DTUs, which means it can complete 5 transactions per second, while a Premium P11 database has 1750 DTUs.
DTU vs. eDTU
The DTU for single databases translates directly to the eDTU for elastic databases. For example, a database in a Basic elastic database pool offers up to 5 eDTUs. That’s the same performance as a
single Basic database. The difference is that the elastic database won’t consume any eDTUs from the pool until it has to.
A simple example helps. Take a Basic elastic database pool with 1000 DTUs and drop 800 databases in it. As long as only 200 of the 800 databases are being used at any point in time (5 DTU X 200 = 1000), you won’t hit capacity of the pool, and database performance won’t degrade. This example is simplified for clarity. The real math is a bit more involved. The portal does the math for you, and makes a recommendation based on historical database usage. See Price and performance
considerations for an elastic database pool to learn how the recommendations work, or to do the math yourself.
Monitoring database performance
Monitoring the performance of a SQL database starts with monitoring the resource utilization relative to level of database performance you choose. This relevant data is exposed in the following ways:
1. The Microsoft Azure Classic Portal.
2. Dynamic Management Views in the user database, and in the master database of the server that contains the user database.
In the Azure Portal, you can monitor a single database’s utilization by selecting your database and clicking the Monitoring chart. This brings up a Metric window that you can change by clicking the Edit chart button. Add the following metrics:
CPU Percentage DTU Percentage Data IO Percentage Storage Percentage
Once you’ve added these metrics, you can continue to view them in the Monitoring chart with more details on the Metric window. All four metrics show the average utilization percentage relative to the DTU of your database.
You can also configure alerts on the performance metrics. Click the Add alert button in the Metric window. Follow the wizard to configure your alert. You have the option to alert if the metrics exceeds a certain threshold or if the metric falls below a certain threshold.
For example, if you expect the workload on your database to grow, you can choose to configure an email alert whenever your database reaches 80% on any of the performance metrics. You can use this as an early warning to figure out when you might have to switch to the next higher performance level.
The performance metrics can also help you determine if you are able to downgrade to a lower performance level. Assume you are using a Standard S2 database and all performance metrics show that the database on average does not use more than 10% at any given time. It is likely that the database will work well in Standard S1. However, be aware of workloads that spike or fluctuate before making the decision to move to a lower performance level.
The same metrics that are exposed in the portal are also available through system views:
sys.resource_stats in the logical master database of your server, and sys.dm_db_resource_stats in the user database (sys.dm_db_resource_stats is created in each Basic, Standard, and Premium user database. Web and Business edition databases return an empty result set). Use sys.resource_stats if you need to monitor less granular data across a longer period of time. Use
sys.dm_db_resource_stats if you need to monitor more granular data within a smaller time frame. For more information, see Azure SQL Database Performance Guidance.
For elastic database pools, you can monitor individual databases in the pool with the techniques described in this section. But you can also monitor the pool as a whole. For information, see Monitor and manage an elastic database pool.
https://msdn.microsoft.com/en-us/library/azure/dn741340.aspx
Use Microsoft Azure SQL Database service tiers (editions) to dial-in cloud database performance and capabilities to suit your application.
Understand the Capabilities of Service Tiers (Editions)
Basic, Standard, Premium service tiers offer predictable performance, flexible business continuity options, and streamlined billing. In addition, with multiple performance levels, you can have the flexibility to choose the level that best meet your workload demands.
Should your workload increase or decrease, you can easily change the performance characteristics of a database in the Microsoft Azure Management Portal. Select your database, click Scale, and then choose a new service tier. For more information, see Changing Database Service Tiers and
Performance Levels.
You can go a service tier up and fix the performance.
The features available with each service tier fall into the following categories:
Performance and Scalability: Basic, Standard, and Premium service tiers have one or more performance levels that offer predictable performance. Performance levels are expressed in database throughput units (DTUs), which provide a quick way to compare the relative performance of a database. For more detailed information about performance levels and DTUs, see Azure SQL Database Service Tiers and Performance Levels. In addition to the performance level, for all database service tiers, you also pick a maximum database size supported by the service tier. For more information on the supported database sizes, see CREATE DATABASE.
Business Continuity: These features help you recover your database from human and application errors, or datacenter failures. Many built-in features, such as Geo-Restore, are available with Basic, Standard, and Premium service tiers. For more information, see Azure SQL Database Business Continuity.
Auditing: With Basic, Standard, and Premium service tiers, you can track logs and events that occur in a database. For more information, see Azure SQL Database Performance Guidance. Service
Tier Common App Pattern
Transactional Perf. Objective
Basic Small databases with a single operation at a given point in
time Reliability per hour
Standard Workgroup and cloud applications with multiple concurrent
transactions Reliability per minute
Premium Mission-critical, high transactional volume with many
concurrent users Reliability per second
Web Web apps, workgroup, dept. apps, and other lightweight
database workloads N/A
Business Lightweight database workloads that require larger sizes than
supported with Web N/A
Service Tier Database Transaction Unit (DTU) Basic 5 S0 10 S1 20 S2 50 S3 100 P1 125 P2 250 P3 P4 500 P6 1000 P11 1750
Adjust performance and scale without downtime.
SQL databases is available in Basic, Standard, and Premium service tiers. Each service tier offers different levels of performance and capabilities to support lightweight to heavyweight database workloads. You can build your first app on a small database for a few bucks a month, then change the service tier manually or programmatically at any time as your app goes viral worldwide, without downtime to your app or your customers.
For many businesses and apps, being able to create databases and dial single database performance up or down on demand is enough, especially if usage patterns are relatively predictable. But if you have unpredictable usage patterns, it can make it hard to manage costs and your business model.
Elastic database pools in SQL Database solve this problem. The concept is simple. You allocate performance to a pool, and pay for the collective performance of the pool rather than single database performance. You don’t need to dial database performance up or down. The databases in the pool, called elastic databases, automatically scale up and down to meet demand. Elastic
databases consume but don’t exceed the limits of the pool, so your cost remains predictable even if database usage doesn’t. What’s more, you can add and remove databases to the pool, scaling your app from a handful of databases to thousands, all within a budget that you control.
Either way you go—single or elastic—you’re not locked in. You can blend single databases with elastic database pools, and change the service tiers of single databases and pools to create innovate designs. Moreover, with the power and reach of Azure, you can mix-and-match Azure services with SQL Database to meet your unique modern app design needs, drive cost and resource efficiencies, and unlock new business opportunities.
But how can you compare the relative performance of databases and database pools? How do you know the right click-stop when you dial up and down? The answer is the database transaction unit (DTU) for single databases and the elastic DTU (eDTU) for elastic databases and database pools.
https://channel9.msdn.com/Series/Windows-Azure-Storage-SQL-Database-Tutorials/Scott-Klein- Video-02
In this episode, Scott is joined by Tobias Ternstrom – Principal Program Manager Lead for
performance in Azure SQL Database, as he breaks down what the new Database Throughput Unit is and how you can use it to understand what kind of horsepower you can expect from the new services tiers. The DTU is a critical part of providing a more predictable performance experience for
you, A DTU represents the power of the database engine as a blended measure of CPU, memory, and read and write rates. This measure helps you assess the relative power of the six SQL Database performance levels (Basic, S1, S2, P1, P2, and P3).