Module 1 - Role of Azure DBA Flashcards

1
Q

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

A

Less management required
Automated Patching
Automated Backups
More scaling options

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Which has the easier migration path, IaaS or PaaS?

A

IaaS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What does the SQL IaaS extension allow for?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

ARM Templates are stored as JSON files

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What does the use of ARM templates allow

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

Azure DevOps Pipe line

Custom Deployment via the Azure Portal

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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%

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

Allowing restores from On-Prem backups

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Azure SQL MI Provides a SQL instance allowing how many DBs

A

Up to 100DBs + System databases

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What levels of offerings does azure SQL MI have

A

General Purpose

Business Critical

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

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

17
Q

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

A

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

18
Q

What is the headlines of the PaaS connectivity architecture

A

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

19
Q

What are the headlines of PaaS Backup and Restore

A

Fully managed Full, Diff and Log Backups taken regularly

Copy only backups can be taken to Azure Storage

20
Q

What are the auto tuning capabilites for Azure PaaS SQL

A

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)

21
Q

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

A

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

22
Q

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

A

New Application

23
Q

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

A

DTUs - Database Transaction UNits

24
Q

What config variants of Azure SQL DB are there?

A

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

25
Q

What are the network options for Azure SQL DB

A

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.

26
Q

Give an overview of Scaling an Elastic Pool

A

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.

27
Q

Give an overview of Scaling a Serverless Azure SQL DB

A

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.

28
Q

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

A

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

29
Q

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

A

Firewall can be set for IP Address or Range

Can config vNet endpoint to allow connections from Azure Virtual Network.

30
Q

Is Query Store avaialble for PostgreSQL

A

Yes

31
Q

What does Query Store for PostgreSQL

A

Tracks both execution runtime stats and wait stats

32
Q

Where is Query Store information stored for PostgreSQL

A

Stored within azure_sys in the query_store schema.

33
Q

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

A

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

34
Q

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

A

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

35
Q

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

A

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

36
Q

What does Query Plan Shape protection mean?

A

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,