This course explores how to plan and implement data platform resources specifically with regards to Azure SQL offerings. In particular, we will explore the benefits and features of the SQL PaaS offerings along with billing models, service tiers, and high availability options. We'll also cover migration scenarios and provide a demo that migrates a sample database from an on-premise SQL Server to an Azure SQL managed instance using DMS.
If you have any feedback relating to this course, feel free to contact us at firstname.lastname@example.org.
- Get a general understanding of Azure SQL databases
- Learn how to deploy Azure SQL databases
- Understand business continuity and security tie in with SQL databases
- Learn how to scale, upgrade, and partition your databases
- Learn how to migrate a database from an on-premise SQL Server to an Azure SQL instance
- Anyone who wants to learn about Azure SQL Offerings
- Those preparing for Microsoft’s DP-300 exam
To get the most out of this course, you have should a general understanding of the fundamentals of Microsoft Azure. Experience using databases — especially SQL Server — would also be beneficial.
In this demonstration, we will see how to scale a different Azure SQL database offerings and look at the various service tiers. I've already provisioned the database and server resources for this demonstration. And I have links to the various resource types, in the main menu here on the left.
Clicking on Azure SQL, we can see a summary of the database resources I've provisioned. This goes through the different resources here. I have an SQL managed instance at the top, then an Azure SQL server, which is backing the elastic pool. There is one database test one in the pool showing as Elastic Basic. And I also have a single instance database also sharing that server.
In the service tier column next to resource type, we can see the SKU or product designations of the different database resources. The SQL managed instance uses a provisioned general purpose vCore product, while the elastic pool and standalone database are using DTU based products.
Let's first examine the SQL managed instance. If we go into compute and storage underneath settings, we can see the general-purpose tier radio button is selected, but we have the option of upgrading to the business-critical service tier.
Each service tier specifies the ranges of cores and storage available. Below this, we have two sliders that let us adjust the number of vCores and storage capacity. As I adjust the amount of computing power and storage, we can see the cost summary is reflecting those changes.
If I go back up to service tier and change back to general purpose, we can see a dramatic cost saving compete with business-critical as you would expect. So running a general-purpose service tier with the minimum vCores and storage costs just around $500 a month, but that doesn't include the SQL server license, which I've said I'm providing myself.
If I uncheck that we can see that the estimated cost goes up by about $300, or really having an SQL server license and using it equates to significant savings. Let's now have a look at the settings for the elastic pool. So not the server that hosts it, but for the resource that manages the pool of databases.
The elastic pool is slightly different from the managed instance, and that we go into configure under settings to see the resources service tier settings. The first thing that we can notice is that we are running a basic version of the product with a DTU setting of 50. Although as an elastic pool, they are elastic DTUs.
If I switch from basic to premium, we can see that the eDTU range is far greater. Per database settings allow me to allocate a minimum and maximum DTU range to guarantee performance for a particular database. As with the managed instance, the cost summary will dynamically adjust to your resource settings.
Not only do you get a total cost, but you get a per DTU cost. We have the option of changing from a DTU purchasing model to a vCore based purchasing model. When I switch to vCore, I get the option of selecting general-purpose or business-critical.
Once again, we can save a considerable amount of money by checking already have a SQL server license and the confirmed checkbox. We can see the significant difference between general purpose and business critical, the IOPS and latency performance. Business critical IOPS increases by a factor of 10, while worst-case latency improves by a factor of five and cost increases about five fold.
Okay, back to the summary. And let's have a look at the single instance database. Like the elastic pool, go into configure under the settings and we can see very similar information. Obviously, we're now dealing with normal DTUs instead of elastic ones, but we have the same options of basic standard or premium workloads, or change our pricing model from DTU to vCore. The difference here is that we have the additional option of hyperscale and we can select either provisioned as it is now or a serverless compute model.
Selecting serverless means that we can specify minimum and maximum vCore settings to bound our serverless database. The other important feature of serverless databases is the auto pause feature. We can enable auto pause and then specify what period of inactivity will cause the database to pause. Here we can see that after one hour of nothing happening, the database will go into a pause state.
Course Introduction - Azure SQL Databases Overview - Deployment Options - ARM Templates Deployment - DEMO: Deploying Azure SQL Databases - Business Continuity - Security Database Services - Scale and Performance - Partitioning Data - Migrating to Azure - Migration Scenarios - DEMO: DMS Azure SQL Database Migration Process - Upgrade Scenarios - Summary
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.