Learn about two approaches for data capture. This video compares a full SQL Server and a SQL database, outlines different tiers in SQL databases, and uncovers the difference between elastic and standalone SQL databases.
- [Instructor] Traditionally, when we think of SQL Server, we think of full fledged server, whether that's on premise or virtual machine. But what if you don't need the entire functionality of the SQL Server, and just a place to host your data? Then Azure SQL Databases could be your answer. You can still manage the database using your traditional tools, such as SQL Server management studio. But because Azure SQL Databases fall into the platform as a service solution, Microsoft handles everything, the Server, the SQL, you just leverage the database capabilities.
You may be now asking yourself, what is the difference between a SQL virtual machine versus a SQL database? And it's a good question, and there are some key differences that you need to be aware of. In this chart, you'll see that I have SQL Server on the left, and SQL Databases on the right. Some of the differences you need to be aware of include, with a SQL Server, you are responsible for managing the upgrades, the patching, maintaining the operating system, as well as SQL Server. Under platform as a service, or PaaS, Microsoft manages everything.
They handle the Server, the operating systems, SQL, the power and the hardware. When using an on premise or virtual machine SQL Server, you're responsible for configuring high availability, whereas using the SQL Database, this is already configured and managed for you. You don't have to manage the backups when you use SQL Database. That is built in for you as well. When using SQL Server, you have full administrative rights, whereas in a Database solution, you only have administrative rights to that database.
Raise your hand if you love licensing. If you don't, the SQL Databases might be the better solution for you. You don't have to worry about machine costs or the SQL licensing itself, but you do have to be concerned about those licensing costs with a full SQL Server VM. If you need a highly customizable solution, then you will want to look at a SQL Server. The SQL Database, you can only focus on the application layer. It is not nearly as customizable. And finally, SLA.
When using the SQL Database, your SLA is 99.99%, whereas when you're using a SQL Server, you're responsible for your high availability, and the SLA only covers the virtual machine and not SQL itself. When it comes to Azure, there are always different tiers and SQL Databases are no exception. As of this recording, there are three SQL tiers to choose from: basic, standard and premium. The basic tier can be used for small databases, one single act of operation or for dev and test.
The standard tiers would be used for low to medium IO. The standard tier supports concurrent queries and is great for web applications. And finally the premium tier, and typically this is what you'll use in production, this will be great for high transactional volume and IO. It supports multiple concurrent users and is used for mission critical databases. Elastic pools combine and share resources for all databases. This enables multiple databases to share a pool of resources whereas a single database has its own set of resources.
Elastic databases scale automatically. As a database requires more or less resources, then it scales to meet the needs of that database. And elastic pools provide predictable costs because there is no price per database, but the price is based on the number of eDTUs in the pool. You'll want to use elastic pools for databases that fluctuate from high activity to low activity. If your database has a constant activity level, it may be better to use the basic, standard or premium SQL tiers instead.
SQL Database performance is based on a DTU, and I know your next question will be, what is a DTU, or as I mentioned in the previous slide, an eDTU? According to Microsoft, it is the 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. DTUs are database transaction units, combine the CPU, memory, data IO and transaction log IO.
The DTU is based on a real world OLTP benchmark. For more information on DTUs and benchmarks, I've included a link in the handouts. I've also mentioned eDTUs. An eDTU is a DTU that can be shared among that elastic pool. To determine the number of DTUs needed for a workload, you can use the DTU calculator to help you get started. I've provided a link to this calculator in the notes. Now that we understand DTUs and a little bit about elastic pools, and we've touched on backup, we can now start choosing which tier is best suited for our workload.
Again, we have our three tiers, our basic, standard and premium. And you'll notice the variance between these tiers and the limitations. For example, if you're looking for a backup retention period of at least 35 days, you'll be looking at a standard or premium tier. Otherwise, if you choose a basic, your backup is only available for 7 days. You may have to focus on your maximum database size. If you have a very large database, up to one terabyte, you'll be looking at a premium tier. The standard and basic tiers will not be available to you.
Because Microsoft makes changes to Azure and updates quite frequently, always double check the documentation before implementing a workload to make sure that your workload does fit within one of these tiers. There are other variables as well to consider with your service tiers. In this chart, you'll notice under the basic tier, we have a certain number of maximum concurrent logins, versus under a premium tier, which will support a higher number of maximum concurrent logins. Again there are a lot of variables to choose from. Always check the documentation before implementing to ensure that you're picking the right tier for your workload.
And we'll have a similar chart for our elastic pool limits as well. To recap, because I know this was a little long, when we're talking about SQL Servers and SQL Databases, your first decision will be, do I want to build out a SQL virtual machine or can I use a SQL Database? If you decided on a SQL Database, then are you going to use a SQL Database or are you going to use an elastic pool? Again, the elastic pool allowing multiple databases to share a pool of resources. Next, you'll have to choose your tier, a basic, standard or premium.
And then from within that tier, you're going to choose a service level tier. The SQL Databases within Azure, provide all the flexibility of SQL without all the maintenance that is required for a SQL Server. Again, SQL Databases may not meet your need and you'll have to run through all those tiers to determine whether the SQL Database is the right solution for you.
- Implementing storage blobs and Azure files
- Managing access
- Configuring diagnostics, monitoring, and analytics
- Enabling and viewing logs
- Implementing Azure SQL databases
- Implementing recovery services
- Creating an Azure Backup vault
- Configuring the Azure Backup agent
- Backing up and restoring files
- Backing up an Azure virtual machine