Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

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

Module 2 - Plan and Implement Data Platform Resources Flashcards

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
Q

Give the Headlines on Azure Site Recovery when thinking of SQL on IaaS

A

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

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

Define the Azure Market place

A

Central location to create Azure resource via a template

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

What is the preferred storage type for SQL IaaS , what are is the SLA and Limit of this

A

Most cases use Azure Managed Disks (except FCI)
99.999% Availability
50,000 disks per instance

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

What Disk types are there in IaaS

A

Standard HDD
Standard SSD
Premium SSD
Ultra Disk

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

What are the best practices for storage on SQL IaaS

A

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

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

What latency should you get when using Premium SSD and what should you do if you need better performance

A

When configured correctly Premium SSD will see single digit Ms latency - if need better performance utilise Ultra Disk

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

What does PowerShell and CLI do for deployments

A

Powershell allows for programatical deployment with granular control over attributes
CLI is more simplistic and can achieve the same result with less code

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

Give the headlines for ARM templates

A

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

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

What is often the first type of service used when migrating from on-prem

A

IaaS

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

What does the Azure Migrate Server tool do?

A

Provides a central location to assess and migrate on-prem resources.

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

What types of servers (physical, virtual) can the Azure Migrate Server tool discover and assess.

A

Both physical and virtual (either VMWare or Hyper V)

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

What budget type info does the Azure Migrate Server tool provide

A

Suggests VM Sizes and estimates cost

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

What is the infrastructure of the Azure Migrate Server Tool?

A

Cloud based application that has a lightweight agent deployed on prem which reports back to the cloud tool

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

What is the MAP toolkit

A

Microsoft Assessment and Planning Toolkit

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

What does the Map toolkit do

A

Gathers inventory of SQL Servers on network (Version and Server info) and reports on it’s findings.

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

What is the Database Experimentation toolkit used for and what does it provide?

A

Used to evaluate version upgrades of SQL server by checking syntax compatibility
Provides a platform to evaluate query performance on target version

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

If the MAP Toolkit and the Database Experimentation toolkit help identify DBs and highlight any potential issues what does the Data Migration Assistant do?

A

Assess and identifies new features you might benefit from as well as performing the migration

42
Q

What is the Data Migration Assistant used to migrate between

A

SQL Server Versions On Prem, On-Prem to VM or to Azure SQL DB or Managed Instance

43
Q

The Data Migration Assistant can assess T-SQL queries from where?

A

Extended Events Trace and external applications (such as those written in C# etc)

44
Q

What does the Azure Database Migration Service (DMS) support

A

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
Q

What is a prerequisite of the Azure Database Migration Service (DMS)

A

vNet in Azure

If performing from on-prem either a VPN connection or ExpressRoute

46
Q

What are some of the other (more manual) ways of performing a migration to Azure

A

Backup/Restore
Log Shipping
AOAG
Replication

47
Q

What are the PaaS Service Tiers

A

DTUs (Basic, Standard, Premium)

vCores (General Purpose, Business Critical, HyperScale)

48
Q

What is the Hyperscale service tier used for

A

Databases over 4TBs

49
Q

What storage does the General Purpose tier use?

A

Azure Premium Storage (Blob)

50
Q

What storage and additional benefit does using the Business Critical tier bring for Azure SQL PaaS workloads?

A

Local SSD Storage

provides Read-only database replica

51
Q

How are PaaS Backups taken

A

Automatically,
Full - Weekly
Diff - 12 hourly
Log - 5/10 mins

52
Q

Where are PaaS backups stored

A

Azure Blob Storage (Geo-Redundant)

53
Q

What is the default retention period for PaaS backups and how can you extend this

A

Between 7 and 35 days dependant on storage tier

Extend using Long Term Retention

54
Q

What happens if you issue a T-SQL Backup or Restore command in Azure SQL DB and WHY

A

Access is denied as Manual backups are not permitted.

Restore commands will not work either

55
Q

What is the use of Read-Access Geo Redundant storage for PaaS Backups

A

To have a copy in a secondary region that you can read from.

56
Q

What are the limitations when restoring a DB in a PaaS environment

A

Cannot restore over an existing DB, need to

delete/rename the original

57
Q

What can cause fluctuations in restore times in a PaaS environment

A

Platform Service Tiers

58
Q

How can you restore a PaaS DB

A

Via the portal or scripting langages

59
Q

What is Active-Geo Replication

A

Business Continuity feature with async replication to up to 4 replicas

60
Q

How is a TX processed with Active Geo Replication.

A

Tx committed to primary and replicas in region

Tx replayed at secondaries async

61
Q

What are some of the benefits of Active Geo Replication

A

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
Q

What points around failover are there in Active Geo Replication

A

Failover can be initialted manually or via the application

Potentially need a connx string change in the application.

63
Q

Failover Groups are built on what??

A

Geo-Replication

64
Q

What is the benefit of Failover Groups ahead of Active Geo Replication

A

Single Endpoint for Connx, provides endpoints which can route traffic to appropriate endpoint.

65
Q

Where do you deploy a Serverless PaaS DB

A

At the logical server - same as a singleton

66
Q

What are the main benefits of Serverless

A

Compute Tier can scale up/down automatically and auto pause based on demand/usage
You are not charged whilst paused

67
Q

What resumes a paused serverless DB

A

A connection will resume the DB however it is not instant

68
Q

What can you specify a Min/Max to for serverless and what is allocated in proportion to that range

A

Specify a Min/Max number of vCores, Memory and IO is specified proportionately to the range.

69
Q

What features are serverless DBs not compatable with and why

A 
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
Q

What is the purpose of Azure SQL DB HyperScale

How does it work and what is the cost model

A

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
Q

How do you convert back from Azure SQL DB HyperScale

A

You cannot convert back once using Hyperscale

72
Q

What are elastic pools?

A

Deployment option to purchase Azure Compute resources to share among multiple DBs
Allows easy scalability for DBs up to a set limit

73
Q

What are elastic pools good for

A

Multi-Tenant where each has own DB with a similar workload

74
Q

When managing elastic pools you can do this via the portal what are some of the things you can see and do

A

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
Q

What is SQL MI similar to and Why

A

Similar to on prem or IaaS with access to TempDB and SQL Agent etc

76
Q

How does SQL MI differ from IaaS

A

No access to underlying OS

77
Q

What is the uptime SLA of MI

A

99.99%

78
Q

What does General Purpose provide in terms of HA for MI

A

Storage replication for availability

79
Q

What does Business Critical provide in terms of HA for MI

A

Multiple Replicas

80
Q

Where can you NOT restore MI Backups

A

Across different regions or subscriptions

81
Q

What can you take in terms of MI backups

A

A copy_only backup of a DB stored in Azure Storage

82
Q

At what level is MI backed up

A

At database level not the instance level

83
Q

Within MI you can restore via T-SQL what can and can’t you do with this

A

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
Q

What must you do before taking a copy only backup in MI

A

Turn off TDE.

85
Q

What are the headlines around DR for Azure SQL Managed Instance

A

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
Q

Why must each MI Primary and Secondary be in the same DNS zone, how can you facilitate this?

A

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
Q

What are the Open Source DB Offerings

A

MySQL, MariaDB, PostgreSQL

88
Q

What services do the Open Source DB Offerings have

A

Native HA
Auto Patch and Backup
Highest Security Potential
Fully Supported by Microsoft

89
Q

How do you upgrade to a new version of an Open Source DB offering

A

Backup and restore into a new version

90
Q

What are the MySQL and MariaDB headlines

A

Easy lift and shift from customer to cloud
Use existing frameworks and langauges to not disrupt business
Built in HA
Dynamic Scaling

91
Q

What are some of the PostgreSQL headlines inc PostgreSQL Hyperscale

A

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
Q

What are the open source DB service Tiers

A

Basic, General Purpose, Memory Optimized (bringing quicker processing and higher concurrency))

93
Q

How can you deploy PostgreSQL and what do you need to decide

A

Portal, CLI, ARM, Limited Powershell

Select Single Server or HyperScale

94
Q

PostgreSQL Hyperscale is different to Azure SQL DB Hyperscale yet both use what

A

Horizontal Scaling

95
Q

What does Hyperscale allow PostgreSQL nodes to do

A

Work together in shared nothing design.

96
Q

What is the architecture/infrastructure of PostgreSQL Hyperscale

A

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
Q

Hyperscale PostgreSQL DBs are sharded what does this mean and what does it allow

A

Data in a single table can be split across nodes using distributed tables - allows for paralellization of queries

98
Q

True or False - PostgreSQL Hyperscale Worker and Coordinator nodes can be scaled inderpendantly

A

True.

99
Q

How many PostgreSQL Hyperscale Worker nodes are there by default and how can you configure HA

A

20 worker nodes by default and HA configured for each node.

100
Q

For MariaDB and MySQL HA is built in - what is the SLA uptime

A

99.99%

101
Q

How does HA work for MariaDB and MySQL

A

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
Q

How do you scale out read workloads for Maria and MySQL

A

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.
© 2024 Bold Learning Solutions. Terms and Conditions