Module 1 - Role of Azure DBA

Question 1

What are some of the reasons to use a PaaS DB Solution

Answer 1

Less management required
Automated Patching
Automated Backups
More scaling options

Question 2

What are some of the reasons to use an IaaS DB solution

Answer 2

If you need an older version of SQL Server
Use of SQL Fringe products (SSIS, SSAS, SSRS) even thought there are Azure offerings for these
Application Incompatability with PaaS (i.e. Azure SQL DB cannot use Cross DB Queries
No additional services can be colocated with PaaS

Question 3

Which has the easier migration path, IaaS or PaaS?

Answer 3

IaaS

Question 4

What does the SQL IaaS extension allow for?

Answer 4

SQL Automated Backups
SQL Automated Patching
Azure KeyVault Intergration
Viewing of SQL Config and Storage Info via the Azure Portal

Question 5

What features have been developed to support the use of Azure whilst running SQL on a VM

Answer 5

Backup to URL - use of the SQL backup syntax to backup to Azure Storage
Azure Backup for SQL VMs - Complete enterprise backup solution to automatically handle backups across your infrastructure

Question 6

Azure Resource Manager (ARM) allows for the management and deployment of Azure Resources, how are ARM templates stored

Answer 6

ARM Templates are stored as JSON files

Question 7

What does the use of ARM templates allow

Answer 7

Deploying full set of resources in a single declarative template
Include Parameters to supply values at the time of deployment
Build dependencies into the template

Question 8

What options do you have to carry out the deployment of an ARM template

Answer 8

Azure DevOps Pipe line

Custom Deployment via the Azure Portal

Question 9

Azure is a Fault Tolerant and Quick Recovery platform however, what is the requirement of a single VM to have an uptime of 99.9%

Answer 9

A single VM with PREMIUM STORAGE has a 99.9% uptime SLA

Question 10

Azure SQL Managed Instance allows an easy migration path - what feature makes this so

Answer 10

Allowing restores from On-Prem backups

Question 11

Azure SQL MI Provides a SQL instance allowing how many DBs

Answer 11

Up to 100DBs + System databases

Question 12

List some of the other features of Azure SQL MI that Azure SQL DB does not have

Answer 12

Allows Cross DB Queries
Allows access to the CLR (Common Language Runtime)
Allows SQL Agent jobs and the use of the Sys DB MSDB

Question 13

What levels of offerings does azure SQL MI have

Answer 13

General Purpose

Business Critical

Question 14

What are the advantages of using Azure SQL MI in Business critical as opposed to General Purpose

Answer 14

Business Critical allows:
In-Memory OLTP,
Readable Secondaries
as well as more memory per core and direct attached storage (as opposed to network attached storage)

Question 15

How can you reduce you PaaS Licensing costs and what are the specifics of this

Answer 15

Using existing SQL licences with PaaS Reduces cost
1x Enterprise Core License = 1vCore of Azure SQL PaaS at Business critical OR 8vCores at General Purpose
1x Standard Core Licence allows 1vCore of General Purpose
This can reduce costs up to 40% and cause you only to pay for storage and compute costs

Question 16

What is the uptime SLA for Azure SQL MI and Azure SQL DB

Answer 16

MI and Azure SQL DB have similar architecture and a 99.99% uptime

Question 17

What are some of the other key points regarding HA for Azure SQL MI and Azure SQL DB

Answer 17

Windows & SQL updates handled by backend with no app impact however it is important for Apps to have retry logic
HA is automatic and built in so committed data is never lost
No DB SPOF
Exact solution is dependant on Service Tier

Question 18

What is the headlines of the PaaS connectivity architecture

Answer 18

Connections made to them via TDS endpoints.
There are different solutions however fundamentally the gateway component handles the connection and routes to the database service

Question 19

What are the headlines of PaaS Backup and Restore

Answer 19

Fully managed Full, Diff and Log Backups taken regularly

Copy only backups can be taken to Azure Storage

Question 20

What are the auto tuning capabilites for Azure PaaS SQL

Answer 20

Identify Expensive Queries
Force last good execution plan
Adding and Removing Indexes
(The addition of indexes uses built in intelligence and advance heuristics to identify the best indexes, Indexes are also test on a shadow copy of the DB)

Question 21

What are the Migrations options from IaaS to MI (elaborate on reasonings)

Answer 21

Reetore from a Backup - More downtime as cannot restore w/NO_RECOVERY and apply log backups.

Use DB Migration Service - Managed Service connects to Source and Destination, effective carrying out an automated log shipping

Question 22

If Azure SQL MI is good for migrations what is Azure SQL DB good for?

Answer 22

New Application

Question 23

Azure SQL DB can now also use vCores as per MI, what was the previous “metric” used to determine available performance

Answer 23

DTUs - Database Transaction UNits

Question 24

What config variants of Azure SQL DB are there?

Answer 24

Azure SQL DB
Single Database - Simple and Original, deploy DB to logical server and connect to it. Each DB has its own resources that are managed individually.

Elastic Pools - Azure SQL DB equivalent of deploying multiple DBs to one instance

Hyperscale - Azure SQL DB is limited to 4TB, Hyperscale uses new horizontal scaling to allows DBs to be 100TB and beyond. Hyperscale is same cost as Single DB however you also pay per TB for storage

Serverless - Auto Scale and Auto Close Option - costs are higher per vCore yet billing pauses after a period of inactivity (typically 1 hr) and resumes when activity restarts

Question 25

What are the network options for Azure SQL DB

Answer 25

Azure SQL DB has a default public endpoint, with access being controlled via firewall rules OR limited to specific Azure networks using features like Virtual Network endpoints or Private link.

Question 26

Give an overview of Scaling an Elastic Pool

Answer 26

The elastic pool benefit is that resources are shared between a group of databases therefore management of the performance level does not need to occur at the DB level. Assign a Min and Max level for each DB in your pool with the ability to customise those settings for individuals or groups of DBs (with vCores it's down to 1/4 of a vCore up to all the vCores) You can also increase the maximum amount of resource (DTUs or vCores) to your pool however this action is not immediate and will incur a single short downtime as the process to increase the size performs a migration task.

Question 27

Give an overview of Scaling a Serverless Azure SQL DB

Answer 27

Define Min and Max vCores Scale options are mostly seemless however can incur short down time if required to move host App connection should be fault tolerant due to scaling operations and Startup operations.

Question 28

What are the Open Source Azure PaaS offerings for DB and what SQL PaaS offering are they similar too? Also what do they have tight integration with for ease of use

Answer 28

MySQL MariaDB PostgreSQL All similar principal to Azure SQL DB and have a tight integration with Azure WebApps

Question 29

What points are there around Network connectivity for the Open source Azure PaaS offerings for DB

Answer 29

Firewall can be set for IP Address or Range | Can config vNet endpoint to allow connections from Azure Virtual Network.

Question 30

Is Query Store avaialble for PostgreSQL

Answer 30

Yes

Question 31

What does Query Store for PostgreSQL

Answer 31

Tracks both execution runtime stats and wait stats

Question 32

Where is Query Store information stored for PostgreSQL

Answer 32

Stored within azure_sys in the query_store schema.

Question 33

What level of setting is "SQL Server Compatibility level" and what does it allow

Answer 33

Database Level setting - always has been Allows specific T-SQL Constructs and Keywords to be used Also determines certain query optimizer behaviours

Question 34

How long does a SQL Server release stay in primary support and what is the difference between primary and extended support

Answer 34

Primary Support = 5 years, during which time updates are released with enhanced capabilities, feature gaps are closed, performance/security/functional bugs are fixed Extended Support = Only Security Bugs are fixed

Question 35

Microsoft recommends using what level metric for application vendor support and why?

Answer 35

Microsoft recommends application vendors certify application to run at a specific compatibility level as opposed to a particular software version. This allows for an application to run in Azure MI or the latest release of SQL Server whilst maintaining it's vendor supported compatibility level

Question 36

What does Query Plan Shape protection mean?

Answer 36

Query Plan shape protection means that query execution plans and there performance should be nearly the same on the same (or similar hardware) and changes in the query plan shape at the same compatibility level will be treated as bugs. Thus removing one of the big risks when upgrading SQL Server,