Implementing Data Storage Solutions: Relational

Question 1

Learning Objective in Relational Databases Section

Answer 1

SQL Database Offerings

• PaaS v. IaaS inplementation
• Deloyment options for PaaS
• Purchasing Models
• Service tiers
• Elastic v. Managed Databases
• Security
• Compute Tiers

SQL Data Warehousing

• Traditional v. Modern Architecture
• Synapse Analytics
• MPP Architecture
• Storage & Sharding Patterns
• Data Distribution & Table Types
• Distinct between this option and SQL Server

Loading Models (inputing data)

• polybase v. ssis

Question 2

Attrbitues of Azure SQL Database

(8)

Answer 2

• predictable perofrmance
• fully managed
• elastic pools for unpredictable workloads
• 99.99% uptimes
• geo-replication in all pricing tiers (different than non-relational)
• supports existing SQL Server tools & libraries
• scale without downtime
• security
• same networking options as non-relational databases

Question 3

IaaS & PaaS

(definition in the context of relational databases)

Answer 3

IaaS: infastructure as a service - SQL Server inside a fully managed virtual machine in Azure

PaaS: platform and a service - Azure SQL Database / Logical SQL Server (not MS SQL server) hosted in the Azure environment

Question 4

Benefits (4) & Responsibilities (4)

w/ IaaS Implementation

Answer 4

Users Responsibilities

• maintain the operating system
• ensure the database engine is properly patched
• high availability and diaster recovery are your responsibility
• performance, configuration, and security are users responsibilities

Benefits

• full control over SQL Server engine
• private IPs
• 99.99% availability
• easy migration and full parity w/ on prem systems

Question 5

Benefits (7) & Responsibilities (4)

w/ PaaS Implementation

Answer 5

Benefits

• 99.99% responsibilities
• pay-as-you-go model
• multiple deployment options
• backup, patching, and recovery are Azure responsibilities
• commonly used featuresof SQL server are available
• built in security and advanced intelligence
• can assign necessary resources to individual datatbases w/ no downtime

Responsibilities

• user responsible for selecting the service tier
• user must test application to ensure high availability and diaster recovery are working correctly
• user must select correct performance based and security needs
• change controls are users responsibilities

Question 6

IaaS (1) v. PaaS (2) Deployment Options

Answer 6

IaaS

• only one deployment option, SQL Server on a VM within the Azure environment

PaaS

Single Database

• each database is guarenteed a certain amount of storage, memory, and compute
• for apps that need a single source of data
• you can create multiple dbs but they will be seperate and isolated from each other

Elastic Pools

• fixed resources (CPU, Mem, etc.) shared across a group of databases

Managed Instance

• each managed instance has a guarenteed amount of resources and the databases can be assigned to the instance
• designed for easy migration of on prem SQL Server (does not use Logical Servers, instead Instance Servers)

Question 7

Purchasing Model for Relational Databases

(2 & attributes & purchasing options)

Answer 7

DTU

• blended CPU + Mem. + I/O provided to user based on the purchase model chosen up to a maxium
• purchsaing options
  
  • Basic
  • Standard
  • Premium

vCore

• designed for customization of CPU, Mem., I/O based on the needs of the customer, flexibility
• select the number of vCores needed
• purchasing options
  
  • general purpose
  • hyperscale
  • business critical

Question 8

Compare & Contrast

DTU (3) v. vCore (4)

Answer 8

DTU

• not available for managed instance
• for customers wanting preconfigured resources
• might need to calculate the number of DTUs needed before migration

vCore

• availble for all PaaS deployments
• for customers needing flexibility, customization of resources
• straightforward approach for migration on prem databases to the cloud
• recommended approach

Question 9

When to convert DTU to vCore?

Answer 9

• if your database consumes more than 300 DTUs, could see cost reduction w/ vCore
• you can utilize API of your choice for conversion with no downtime
• you have a managed instance on Azure

Question 10

vCore & DTU Service Tiers Details

(what do you get?)

Answer 10

General Purpose & Standard

• 99.99% SLA
• 2 millisecond read - 10 millisecond write
• up to 4 terabytes of data storage

​Business Critical & Premium

• 99.99% SLA
• designed for applications needing low latency
• 1 millisecond read - 2 millisecond write
• up to 4 terabytes of data storage

Hyperscal

• same as business critical & premium but designed for large data, up-to 200 terabytes of data storage