1. Home
  2. Training Library
  3. Microsoft Azure
  4. Courses
  5. Introduction to Azure Storage Solutions

Azure SQL Database

The course is part of these learning paths

AZ-203 Exam Preparation: Developing Solutions for Microsoft Azure
course-steps 20 certification 1 lab-steps 7
Getting Started with Azure
course-steps 2 certification 1
Developing, Implementing and Managing Azure Infrastructure
course-steps 10 certification 7 lab-steps 2
more_horiz See 1 more
play-arrow
Azure SQL Database
Overview
DifficultyIntermediate
Duration1h 13m
Students2760
Ratings
4.7/5
star star star star star-half

Description

This course provides an introductory tour of Azure Storage Solutions such as its SQL offerings (SQL DB and third party offerings of MySQL), managed NoSQL databases (DocumentDB and MongoDB), managed Redis Cache service, Azure Backup (backup-as-a-service), Site Recovery (for handling disaster recovery) and StorSimple (a hybrid cloud storage solution).

Target Audience / Experience

  • This course is for developers or IT professionals looking for an introduction to Azure’s Storage Solutions.
  • Some familiarity with the Azure platform is helpful, but not required.

Learning Objectives

  • Understand what SQL DB is and how to get started using it
  • Learn the options offered by Azure for managed MySQL
  • Understand what DocumentDB and MongoDB are and how to get started using them
  • Learn what Redis Cache is and some cases of when you would use it
  • Get an understanding of what Azure Search provides
  • Learn how Azure Backup can solve common backup needs
  • Understand what role Site Recovery can play in disaster recovery
  • Learn how StorSimple can extend your on-premise storage capacity


Outline

  • Course Introduction
    • Overview
  • Azure SQL Database
    • What is Azure SQL Database?
    • Understanding the service tiers
    • Create a SQL Database
    • What is a Data Throughput Unit (DTU)?
    • Scaling Azure SQL
  • MySQL
    • Options for Using MySQL in Azure
  • Azure CosmosDB DocumentDB API
    • What is DocumentDB?
  • Azure CosmosDB MongoDB API
    • Options for Using MongoDB in Azure
    • How does DocumentDB compare to MongoDB?
  • Azure Redis Cache
    • What is Azure Redis Cache?
    • Common scenarios for using Redis Cache
    • Understanding the service tiers
    • How does Redis work?
    • Create and manage a Redis Cache
    • Scaling Azure Redis Cache
  • Azure Search
    • What is Azure Search?
    • Indexes and Search Features
    • How do you use the Search Service?
    • Create a Search Service and Index
  • Azure Backup
    • What is Azure Backup?
    • Backup Scenarios
    • Import/Export Service
    • What is the Import/Export Service?
    • Why would you use Import/Export?
  • Site Recovery
    • What is Site Recovery?
    • Workload recovery scenarios
  • StorSimple
    • What is StorSimple?
    • How does StorSimple work?
  • Course Summary
    • Conclusion and Wrap-Up

Transcript

Welcome back. To start off, let's take a look at SQL Database.
 
What is SQL Database? and how is it different than products like, say, SQL Server 2016?

Well, before getting into what exactly SQL Database is, it is important to note that since SQL Server 2014, the same team that develops the boxed software also develops the cloud version.
In fact, these days new features are released to the cloud version first as a preview. Doing this allows the team to get feedback from real customers and iterate on features before the product is fully baked and ready to ship in the boxed software.
The boxed version of the software is available afterwards - and since major version releases tend to be two or so years apart - you can start to get an idea of where things are going … which is to the cloud of course!
If you look at some of the new features in the latest boxed version (SQL Server 2016) you may notice there are features that are now hybrid and take advantage of the cloud. One example of this is a new feature called Stretch Database. This is a feature that allows your cold data - this is data that hasn't been accessed for a while - to be put in Azure, which can be an effective way to reduce your on-premises storage while still keeping those old archived rows of data in what appears to be the same place.
 
One of the questions I want to answer is, "Why would I use it?" - well the simple answer is if you are already using a version of SQL Server or need a relational database, then SQL Database is a great product to start with.
So, for options. First of all, you can run the SQL Server 2016 product in a virtual machine in Azure.
And you can also use Azure SQL Database, which is the Database-as-a-Service product that we want to learn about in this section.
 
SQL Database, is a relational database-as-a-service product. You may be wondering "what is database-as-a-service?". Well, database-as-a-service means your databases are provisioned on-demand when you ask for them and are fully managed by Microsoft - so you don’t need to do any installations or infrastructure work. You just get your database - which is configured for high availability, backups, … all resources are taken care of for you.
SQL Database is fully compatible with the SQL Server 2016 databases - notice this doesn't include features that deal with the file system or server specific features like SQL Agent.
It has an SLA of four nines (99.99%) availability, which is awesome!
And with Azure being in 34 regions around the world, you can take advantage of SQL Database's ability to geo-replicate and recover to other regions of the world.
SQL Database, like many products in Azure, has multiple service tiers and sizes to fit your needs.
Plus, with SQL Database you can scale up or down on demand. Scaling up compares to adding more resources (like CPU or memory) to a machine when you need it and scaling down compares to removing resources when you don't need it, which is also really nice in helping you stay agile.
Elastic database pools allow you to have multiple databases sharing the same resources, this is important when you have many databases and allows you to reach a better density for the resources you are paying for - we'll learn more about elastic pools later.
 
Ok, now that you have an idea of what SQL Database is, we need to discuss the abstract concept of Data Transaction Unit or DTU as it is often called.
Instead of focusing on the underlying compute and storage resources a database has, SQL Database uses the concept of DTU's to provide a predictable way to determine performance between service tiers.
As we'll see next the lowest level of a database has 5 DTUs, the next level up has 10 DTUs. That means your database resources double between those two levels - so it gives you a relative measurement to determine the expected performance.
The DTU is a blended measure that includes CPU, Memory and disk read/writes … you know - all the things a database needs to perform.
So, keep this in mind as we go to the next topic of service tiers.
 
Each SQL Database, unless it is in an elastic pool, has a service tier. The service tier determines the resources, features and the rate you have to pay for that database.
For example, the Basic tier only has a single size - B. The maximum size for a Basic B database is 2 GB, it gets 5 DTUs and it cost you $5 a month.
If you move up one level to the Standard S0 database, you now get a maximum size of 250GB, 10 DTUs and the cost goes up to $15 a month.
If you go to S1, it is more or less 2 times everything from the S0. I'm sure you can see the pattern in the numbers.
What this table doesn't show is the difference between Basic, Standard and Premium.
The Basic tier is not meant for production systems, it for development or proof of concept scenarios.
The Standard tier is meant for production systems.
Premium is designed for systems with high transaction volume with high IO performance requirements.
The nice thing is, you can move between tiers if you need to, which leads us to scaling.
 
How does SQL Database scale?
Well, we just covered the service tiers - so you now have an idea of the size of resources you have to choose from.
To scale a database up or down, you just need to move up or down a level in that table.
The scaling event is an online event - so your database will be available as it scales up - it is not instantaneous, though. The way it works is the new db size is provisioned and then the data gets moved to it before it is fully online. Once it is online, then the traffic is pulled off the old instance.
 
At this point I've mentioned Elastic Database Pools a few times, so let's look at what they really are. When talking about service tiers, I mentioned that a database is in a service tier - unless it was in an elastic pool.
This is the biggest difference between a single Database and a database in an elastic pool.
An Elastic Pool has its own service tier - which is different than ones we covered earlier, but are more or less the same idea.
With Elastic Pools, there can be more than one database, a max size for all databases in the pool, an amount of eDTUs (which is the same concept as a DTU) and cost.
When you have multiple databases, elastic pools give you a great way to share resources and usually reduce cost.
When you setup an elastic pool, you get to configure things like the min/max DTUs a single database can consume - which helps you with expected performance of the databases in a pool.
Just like with single databases, you can move elastic pools between their service tiers, allowing you to scale up and down when needed.
Some of the common scenarios elastic pools are useful for are: include when a system needs to have database isolation between client databases - such as a multi-tenant SaaS application. Another common scenario is sharding. Sharding is when you want to “scale out” a database by splitting it in pieces - usually horizontally. An example would be to split a customer system into two databases – one for customers from A-M and one for customers N-Z.
With two databases, you double the resources allocated storing your data and you make the tables smaller.
 
Ok, so what are the main features of SQL Database?
Well, this is where those pre-requisites I mentioned in the introduction come into play - I'm not going to cover the features of a relational database or what type of scenarios you would use one, I'm assuming you already know that OR if you're not a developer, maybe don't need to know that.
Instead what I'm going to focus on are some of the unique features of SQL Database.
SQL Database protects your databases with automated backups. Depending on the service tier your database is in, these backups are kept either 7, 14 or 35 days. The point in time feature allows you pick any date and time from those backups to restore a database from.
SQL Database also has an IP Firewall, which allows you to control access to your database.
In the portal there is a performance dashboard that shows you recommendations for things like indexes that should be added, any recent tuning activity, the ability to turn auto-tuning on and charts showing the top 5 queries by CPU consumption and the DTU usage in the past 24 hours.
Auditing & Threat detection is available in the portal on either the server or database level. Once it is enabled, it will watch the traffic going to your database and detects potential vulnerabilities and SQL injection attacks as well as abnormal access activities. If suspicious activity is detected, alerts can be configured to notify you.
Ok, now you should have a pretty good idea of what SQL Database is, let's go to portal and create one.
 

About the Author

Students2821
Courses1


Jason Haley is an independent consultant who focuses on Azure and Angular. He has over 20+ years’ experience architecting, designing, developing and delivering software solutions using mostly Microsoft technologies. Jason recently achieved his MCSE: Cloud Platform and Infrastructure certification in May 2017 (by passing all of the 70-532, 70-533 and 70-534 exams). He is also a Microsoft Azure MVP and leads the North Boston Azure User Group. He frequently speaks at community events on topics relating to Azure.