Data Storage: Relational Data Stores

Question 1

The three SQL Server (PaaS) Deployment Options

Answer 1

1. Single Db: Each Db with its own guaranteed compute, memory, and storage. Used for single data source.
2. Elastic pool: Fixed resources shared by all the databases in the pool.
3. Managed instance: each managed instance has its guaranteed resources, a set of dbs that can be used together, easy migration of on-prem databases.

Question 2

SQL Database Purchasing Model & Service Tiers (DTU / vCore)

Answer 2

• Service Tier DTUs: Basic, Standard, Premium
• DTU combines I/O speed, CPU, and memory together
• only for Single Db and Elastic Pool
• Service Tier vCore: General purpose, business critical, hyperscale
• vCore independently scale compute and memory (more flexible)
• Supports Single db, Elastic pool, Managed instance

Question 3

Four layers to secure Azure SQL Server

Answer 3

1. Network Security: Firewalls, only accept requests from subnet on VNet.
2. Access Management: Authentication via AD/Admin credentials. Authorization via RLS.
3. Threat Protection: Auditing SQL Monitor logs and Event Hubs. Advanced threat protection, analyzes SQL Server logs for harmful attempts.
4. Information Protection: Always uses Transport Layer Security (encrypts client/server connection), Transparent Data Encryption encrypts data at rest from offline access and keys are stored in Azure key vault, Dynamic Data Masking protects sensitive data from non-privileged users

Question 4

Azure SQL Server Security Management (two services)

Answer 4

1. Vulnerability Assessment: discover track and remediate potential db vulnerabilities.
2. Data discovery and classification: Identify and label sensitive data for monitoring and alerting.

Question 5

Two Types of Horizontal Scaling

Answer 5

1. Read-Scale Out: Increase availability for increased reads to a database for read-only application. Read-only workloads become isolated from read-write workloads.
2. Global Scale-Out / Sharding: Split db into multiple db nodes based on function or location.

Question 6

Types of db Backups

Answer 6

1. Full Backup: whole db, taken every week or day, RA-GRS
2. Differential backup: captures data changes since last full backup, taken every 12hrs
3. Transaction log backup: records all of the committed and uncommitted transactions, taken every 5-10mins

Question 7

Data Warehouse Unit (db compute)

Answer 7

DWU = CPU + Memory + I/O

Charged DWU based on how many DWUs are provisioned to the data pipeline, not based on number of retrievals. Can be paused.

Storage is based on volume stored, not storage transactions.