Learning Objective in Relational Databases Section
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
Attrbitues of Azure SQL Database
(8)
- 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
IaaS & PaaS
(definition in the context of relational databases)
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
Benefits (4) & Responsibilities (4)
w/ IaaS Implementation
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
Benefits (7) & Responsibilities (4)
w/ PaaS Implementation
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
IaaS (1) v. PaaS (2) Deployment Options
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)
Purchasing Model for Relational Databases
(2 & attributes & purchasing options)
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
Compare & Contrast
DTU (3) v. vCore (4)
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
When to convert DTU to vCore?
- 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
vCore & DTU Service Tiers Details
(what do you get?)
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
