Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

DP-300 > Module 2 - Plan and Implement Data Platform Resources > Flashcards

Module 2 - Plan and Implement Data Platform Resources Flashcards

(102 cards)

Start Studying
1
Q

What are the 2 types of IaaS licensing model, + explain

A

Pay as you go - SQL Licence included with machine cost

Bring your Own Licence - apply an existing license which must be reported by licence mobility verification in 10 days.

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

How are VM resources split up based on workload type

A

VMs Split into family types with different sizes within each family

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

At a high level how can HA be achieved when using IaaS

A

HA built into Azure Platform + additonal features specfic to SQL Server which can be used

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

What are Availability Zones and what do they do

A

Unique physical locations within a region
Each zone made up of one or more DCs with its own power, cooling, network.
Provides HA against data center failure when multiple VMs deployed to different zones
VM eco system can be spread across 3 zones in region
Increases Uptime SLA to 4-9s 99.99%

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

What are availabily sets and what do they do?

When should you use and when should you not use?

A

Similar to Availabilty zones yet do not spread workload over different data centers instead spreading across different servers and racks within a data center
Use Availability Sets to Spread AOAG workloads across different physical hardware
Use when Region doesn’t allow availability zones OR when an app cannot tolerate interzone latency

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

How many replicas can you implement SQL AOAG over

A

2 - 9

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

Where can you implement AOAG

A

Onprem or IaaS

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

What process does AOAG use to send TXs

A

TXs committed to primary then sent to sync or async replicas.
Sync ensures Tx is commtied to one or more secondaries before application can continue

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

What dictates the availability mode to choose with AOAG

A

Physical distance of servers (i.e. latency involved)

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

What availability mode should you use in AOAG if workload requires lowest latency and/or nodes are far apart

A

Async

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

What availability mode should you use in AOAG if workload can withstand some latency or nodes are close together

A

Sync

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

What does an AOAG provide

A

HA and DR as single AOAG can support both sync and async modes

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

What is the unit of failover in an AOAG

A

The availability group of Databases (i.e. not the instance)

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

What (and where) does a SQL FCI protect

A

An entire instance with HA in a single region

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

How could you build DR into a FCI

A

By combining with AOAG or log shipping as DR not part of FCI

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

What does SQL FCI require

A

Shared Storage (either in Azure Shared File storage or Using storage spaces direct on Windows Server)

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

Is AOAG or FCI preferred for new Azure Deployments - and why

A

AOAG as no complexity or shared storage

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

Why might you need to build a SQL FCI in Azure

A

For on-prem migrations requiring the use of FCI for App Support

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

DR is still required in Azure, what methods are there for DR

A

Azure Platform Options (Geo-Replicated Storage and Azure Site Recovery)
SQL Offered Solutions like AOAG and Backups

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

On a VM how can you control backups and where can you backup to

A

You have granular control and can backup to the IaaS disk or to Azure Blob Storage

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

What is the Azure option to use Geo-Redundant Storage or Read-Access geo-Redundant Storage

A

So you can ensure your backups are stpred safely across geographic landscape

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

What does the Azure SQL VM provider allow you to do for your backups

A

Allows SQL backups to be managed by the platform

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

Give the headlines on Azure Backup for SQL Server

A

Requires Agent on VM that communicates with the Azure Service.
Provides central location to monitor backups to ensure meeting any specified RPO/RTO metrics
Comprehensive enterprise backup solution providing - Longterm backup rentention, Auto management and Added data protection
However there is an additional cost

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

How can Availability Groups help with SQL Server on IaaS aside from HA

A

Can be used for DR as well as HA
Implement replicas and stretch this further with Distributed AOAGs
Ensures viable copy of data is at another location from the primary to protect against Human and Natural disasters

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Give the Headlines on Azure Site Recovery when thinking of SQL on IaaS
Low cost, providing block level replication of your VM. Offers test and verify ability for DR strategy Best utilized for stateless environment s as opposed to Transactional VMs Relatively high RPO/RTO
26
Define the Azure Market place
Central location to create Azure resource via a template
27
What is the preferred storage type for SQL IaaS , what are is the SLA and Limit of this
Most cases use Azure Managed Disks (except FCI) 99.999% Availability 50,000 disks per instance
28
What Disk types are there in IaaS
Standard HDD Standard SSD Premium SSD Ultra Disk
29
What are the best practices for storage on SQL IaaS
Use Premium Disks, pooled for best IOPS and storage capacity Put data files in own pool with read caching Log file get no benefit from read caching Add TempDB on D:\ Drive
30
What latency should you get when using Premium SSD and what should you do if you need better performance
When configured correctly Premium SSD will see single digit Ms latency - if need better performance utilise Ultra Disk
31
What does PowerShell and CLI do for deployments
Powershell allows for programatical deployment with granular control over attributes CLI is more simplistic and can achieve the same result with less code
32
Give the headlines for ARM templates
Deploy full set of resources in a single declarative template Parse in parameters at time of deployment Deploy via Azure DevOps Pipeline or Custom Deployment in Portal Export ARM Templates in JSON format
33
What is often the first type of service used when migrating from on-prem
IaaS
34
What does the Azure Migrate Server tool do?
Provides a central location to assess and migrate on-prem resources.
35
What types of servers (physical, virtual) can the Azure Migrate Server tool discover and assess.
Both physical and virtual (either VMWare or Hyper V)
36
What budget type info does the Azure Migrate Server tool provide
Suggests VM Sizes and estimates cost
37
What is the infrastructure of the Azure Migrate Server Tool?
Cloud based application that has a lightweight agent deployed on prem which reports back to the cloud tool
38
What is the MAP toolkit
Microsoft Assessment and Planning Toolkit
39
What does the Map toolkit do
Gathers inventory of SQL Servers on network (Version and Server info) and reports on it's findings.
40
What is the Database Experimentation toolkit used for and what does it provide?
Used to evaluate version upgrades of SQL server by checking syntax compatibility Provides a platform to evaluate query performance on target version
41
If the MAP Toolkit and the Database Experimentation toolkit help identify DBs and highlight any potential issues what does the Data Migration Assistant do?
Assess and identifies new features you might benefit from as well as performing the migration
42
What is the Data Migration Assistant used to migrate between
SQL Server Versions On Prem, On-Prem to VM or to Azure SQL DB or Managed Instance
43
The Data Migration Assistant can assess T-SQL queries from where?
Extended Events Trace and external applications (such as those written in C# etc)
44
What does the Azure Database Migration Service (DMS) support
A mix of Sources and Targets. Offline (one time) and Online (Continious Datasync) migrations. Supports SQL Server, Azure SQL PaaS as well as Oracle and PostgreSQL offerings etc
45
What is a prerequisite of the Azure Database Migration Service (DMS)
vNet in Azure | If performing from on-prem either a VPN connection or ExpressRoute
46
What are some of the other (more manual) ways of performing a migration to Azure
Backup/Restore Log Shipping AOAG Replication
47
What are the PaaS Service Tiers
DTUs (Basic, Standard, Premium) | vCores (General Purpose, Business Critical, HyperScale)
48
What is the Hyperscale service tier used for
Databases over 4TBs
49
What storage does the General Purpose tier use?
Azure Premium Storage (Blob)
50
What storage and additional benefit does using the Business Critical tier bring for Azure SQL PaaS workloads?
Local SSD Storage | provides Read-only database replica
51
How are PaaS Backups taken
Automatically, Full - Weekly Diff - 12 hourly Log - 5/10 mins
52
Where are PaaS backups stored
Azure Blob Storage (Geo-Redundant)
53
What is the default retention period for PaaS backups and how can you extend this
Between 7 and 35 days dependant on storage tier | Extend using Long Term Retention
54
What happens if you issue a T-SQL Backup or Restore command in Azure SQL DB and WHY
Access is denied as Manual backups are not permitted. | Restore commands will not work either
55
What is the use of Read-Access Geo Redundant storage for PaaS Backups
To have a copy in a secondary region that you can read from.
56
What are the limitations when restoring a DB in a PaaS environment
Cannot restore over an existing DB, need to | delete/rename the original
57
What can cause fluctuations in restore times in a PaaS environment
Platform Service Tiers
58
How can you restore a PaaS DB
Via the portal or scripting langages
59
What is Active-Geo Replication
Business Continuity feature with async replication to up to 4 replicas
60
How is a TX processed with Active Geo Replication.
Tx committed to primary and replicas in region | Tx replayed at secondaries async
61
What are some of the benefits of Active Geo Replication
Calling application does not need to wait for secondary commit Free up transactional resource or place data closer to end users Secondary can be in the same or different region to the primary
62
What points around failover are there in Active Geo Replication
Failover can be initialted manually or via the application | Potentially need a connx string change in the application.
63
Failover Groups are built on what??
Geo-Replication
64
What is the benefit of Failover Groups ahead of Active Geo Replication
Single Endpoint for Connx, provides endpoints which can route traffic to appropriate endpoint.
65
Where do you deploy a Serverless PaaS DB
At the logical server - same as a singleton
66
What are the main benefits of Serverless
Compute Tier can scale up/down automatically and auto pause based on demand/usage You are not charged whilst paused
67
What resumes a paused serverless DB
A connection will resume the DB however it is not instant
68
What can you specify a Min/Max to for serverless and what is allocated in proportion to that range
Specify a Min/Max number of vCores, Memory and IO is specified proportionately to the range.
69
What features are serverless DBs not compatable with and why
``` Geo-Replication Long term retention for Backups Job DB in elastic Jobs Sync DB in SQL Data Sync As these require the DB not to auto pause. ```
70
What is the purpose of Azure SQL DB HyperScale | How does it work and what is the cost model
Removes 4TB size limit allowing DBs in excess of 100TB. Does this via Horizontal Scaling to add compute nodes as data sizes grow Same Cost as Azure SQL DB + a per TB cost
71
How do you convert back from Azure SQL DB HyperScale
You cannot convert back once using Hyperscale
72
What are elastic pools?
Deployment option to purchase Azure Compute resources to share among multiple DBs Allows easy scalability for DBs up to a set limit
73
What are elastic pools good for
Multi-Tenant where each has own DB with a similar workload
74
When managing elastic pools you can do this via the portal what are some of the things you can see and do
See resource utilization (i.e. which DB consumes most resource) Manage the pool with the management blade - allowing control of resource quantity, pool size, service tier, resources per DB and DBs in the pool
75
What is SQL MI similar to and Why
Similar to on prem or IaaS with access to TempDB and SQL Agent etc
76
How does SQL MI differ from IaaS
No access to underlying OS
77
What is the uptime SLA of MI
99.99%
78
What does General Purpose provide in terms of HA for MI
Storage replication for availability
79
What does Business Critical provide in terms of HA for MI
Multiple Replicas
80
Where can you NOT restore MI Backups
Across different regions or subscriptions
81
What can you take in terms of MI backups
A copy_only backup of a DB stored in Azure Storage
82
At what level is MI backed up
At database level not the instance level
83
Within MI you can restore via T-SQL what can and can't you do with this
Must use a URL endpoint not local storage Can use - FilelistOnly, HeaderOnly, LabelOnly, VerifyOnly Cannot resotre backup containing multiple log files Cannot resotre backup containing multiple backup sets Cannot restore backup consisting of In-Memory or File stream
84
What must you do before taking a copy only backup in MI
Turn off TDE.
85
What are the headlines around DR for Azure SQL Managed Instance
MI Offers Auto Failover Groups (whole instance not just DBs) Read Write + Read Only endpoints with easy connection string management In event of failover connection strings routed to appropriate instance Each MI Primary and Secondary must be in same DNS Zone
86
Why must each MI Primary and Secondary be in the same DNS zone, how can you facilitate this?
To ensure same multi-domain cert can be used for Auth between either to instances in same failover group Facilitate with "DNS Zone Partner"
87
What are the Open Source DB Offerings
MySQL, MariaDB, PostgreSQL
88
What services do the Open Source DB Offerings have
Native HA Auto Patch and Backup Highest Security Potential Fully Supported by Microsoft
89
How do you upgrade to a new version of an Open Source DB offering
Backup and restore into a new version
90
What are the MySQL and MariaDB headlines
Easy lift and shift from customer to cloud Use existing frameworks and langauges to not disrupt business Built in HA Dynamic Scaling
91
What are some of the PostgreSQL headlines inc PostgreSQL Hyperscale
Helps customers build large and scalable apps Allows for Horizontal scaling with hyperscalre Intergrates with GeoSpatial Support, Rich Indexing and JSONB PostrgeSQL Hyperscale ideal for multi tenant Apps with minmal changes required for data sharding.
92
What are the open source DB service Tiers
Basic, General Purpose, Memory Optimized (bringing quicker processing and higher concurrency))
93
How can you deploy PostgreSQL and what do you need to decide
Portal, CLI, ARM, Limited Powershell | Select Single Server or HyperScale
94
PostgreSQL Hyperscale is different to Azure SQL DB Hyperscale yet both use what
Horizontal Scaling
95
What does Hyperscale allow PostgreSQL nodes to do
Work together in shared nothing design.
96
What is the architecture/infrastructure of PostgreSQL Hyperscale
Nodes added to server group Each group has co-ordinator and multiple workers App sends query to co-ordinator which sends on to the relevant workers and gathers results.
97
Hyperscale PostgreSQL DBs are sharded what does this mean and what does it allow
Data in a single table can be split across nodes using distributed tables - allows for paralellization of queries
98
True or False - PostgreSQL Hyperscale Worker and Coordinator nodes can be scaled inderpendantly
True.
99
How many PostgreSQL Hyperscale Worker nodes are there by default and how can you configure HA
20 worker nodes by default and HA configured for each node.
100
For MariaDB and MySQL HA is built in - what is the SLA uptime
99.99%
101
How does HA work for MariaDB and MySQL
Tx written synchronously to storage, if node interruption occurs DB Server will create a new node and add existing storage. Tx in flight and active connections dropped so App Retry logic (aka Connection Resiliency) is important
102
How do you scale out read workloads for Maria and MySQL
With "Replication" Setting to simply add a read only replica.

DP-300 (4 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions