In this video, Sharon provides an overview of Azure SQL databases, including when to use Azure SQL Database vs. a SQL Server. Sharon also explains the different Azure SQL Database tiers and appropriate workloads.
- [Narrator] Azure SQL Database is Microsoft's platform-as-a-service database offering. An Azure SQL Database is a relational database that is offered as-a-service. It offers predictable performance, it's scalable, and each database is isolated. In addition, Azure SQL databases provide monitoring and alerting as well as failover options. You may be asking yourself, "What is the difference between a SQL Server," whether that be a virtual machine or on-premise, "and an Azure SQL database." Well, it's a good question and there are some key differences that you need to be aware of.
With SQL Server, you manage all the upgrades and patching. When when you use Azure SQL databases, Microsoft manages everything for you. For your own SQL Servers, whether those are virtual machines or physical servers, and whether or not they sit on-premise or in Azure, you have to configure high availability. The platform-as-a-service SQL database, high availability is already configured and managed for you. You have to manage all the backups on your SQL Server. Azure SQL Database has backups built in.
If you require full administrative rights then you will want to use a SQL Server, because in the Azure SQL Database world you only have administrative rights to the database only. I know some of you might be cringing right now when you see licensing because you know what's required for licensing your SQL Servers. The nice thing about moving to an Azure SQL Database is the machine costs and the SQL licensing costs have been eliminated. If you require a very customized solution for your server, then the SQL Server will be your choice because in the Azure SQL Database the focus is on the application layer only.
And finally, when we talk about SLA's for SQL Server, the SLA only covers virtual machines, not SQL. In Azure SQL databases, the SLA is 99.99%. When it comes to Azure, there are always different tiers and SQL databases are no exception. You may recall in a previous Azure storage course, we only examined three service levels. Those being: Basic, Standard, and Premium. Since then, Microsoft has introduced another level called Premium RS.
Let's go ahead and examine each of these tiers in a little more detail. The Basic tier is recommended for very small databases, or databases that have a single active operation, or for dev and test deployments. Next, we have the Standard tier. This tier supports low to medium input/output databases and supports concurrent queries, as well as Azure web applications. The Premium tier is designed for high transaction volumes and input/outputs.
It supports multiple concurrent users, and Microsoft recommends that you use this database for mission-critical databases. And finally, we have that new Premium RS tier. This tier is recommended for input/output intensive workloads, when high availability is not required. The key word here is, "not." But you can use it for high performance test and dev workloads. For example, to simulate an intensive workload. And you can also use this database when the database is not the system of record.
Before we jump into the details about those tiers, we must first talk about DTUs. SQL database performance is based on a DTU. And of course, I know your next question is, "Well, what is a DTU?" According to Microsoft, a DTU is a unit of measure of the resources that are guaranteed to be available to a single Azure SQL Database at a specific performance level within a single database tier. A DTU combines CPU, memory, data I/O, and transaction I/O.
The DTU is based on a real-world OLTP benchmark. For more information on DTUs and the benchmarks, please see the associated link. So let's go ahead and compare the single database tiers. As you can see, we've broken this down into Basic, Standard, Premium, and Premium RS. Some of the key things to note here is in the basic tier, your maximum database size is two gig, your maximum DTUs is five, and the backup retention is period. Whenever we're talking about Standard and Premium tiers, your backup retention period is always 35 five days.
And then depending on the tier will dictate how large a maximum database size can be and the maximum number of DTUs. Now you will notice in the new Premium RS tier, that our maximum database size is only five gig. For some of you who taken a previous course of mine, you will notice that this chart does look different. This is why you'll always hear me say, "Always refer to the Azure documentation," as it does change. I've only included a few of the service features here. For a comprehensive list with all the nitty-gritty details, please refer to the Azure documentation.
Next, we have an eDTU, and this is a DTU that can be shared among the elastic pools. Let's spend a few moments learning about elastic pools. Elastic pools combine and share resources for all databases. This enables multiple databases to share a pool of resources, whereas a single database has it's own set of resources. Elastic databases scale automatically. And finally, elastic pools provide predictable costs because there is no price-per-database but instead, the price is based on the number of eDTUs within the pool.
Databases that can take advantage of elastic pools are the ones that have a lot of activity and then very little activity. If your database has constant activity level, it may be better to use the Basic, Standard, or Premium tiers instead. Let's look at a quick chart to compare the tiers for the elastic databases Again, you'll notice here we have a very similar design. Our Basic, Standard, and Premium increase as required, with the exception of the number of the elastic DBs per pool.
And then that Premium RS tier decreases somewhat. Key things to note here is the number of days the backup is retained for, as well as the maximum elastic database size. To recap, we talked about the differences between SQL virtual machines and a SQL database, SQL databases or elastic pools, and the difference between the tiers. The Basic, Standard, Premium, and the new Premium RS tiers.
- Designing data storage
- Azure Blob storage
- Creating Blob storage using PowerShell
- Azure Cosmos DB
- Securing Azure SQL Database
- Selecting the appropriate storage option
- Virtual machine storage tiers
- Managed vs. unmanaged disks