Module 2 - Plan and Implement Data Platform Resources Flashcards
What are the 2 types of IaaS licensing model, + explain
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 are VM resources split up based on workload type
VMs Split into family types with different sizes within each family
At a high level how can HA be achieved when using IaaS
HA built into Azure Platform + additonal features specfic to SQL Server which can be used
What are Availability Zones and what do they do
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%
What are availabily sets and what do they do?
When should you use and when should you not use?
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 many replicas can you implement SQL AOAG over
2 - 9
Where can you implement AOAG
Onprem or IaaS
What process does AOAG use to send TXs
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
What dictates the availability mode to choose with AOAG
Physical distance of servers (i.e. latency involved)
What availability mode should you use in AOAG if workload requires lowest latency and/or nodes are far apart
Async
What availability mode should you use in AOAG if workload can withstand some latency or nodes are close together
Sync
What does an AOAG provide
HA and DR as single AOAG can support both sync and async modes
What is the unit of failover in an AOAG
The availability group of Databases (i.e. not the instance)
What (and where) does a SQL FCI protect
An entire instance with HA in a single region
How could you build DR into a FCI
By combining with AOAG or log shipping as DR not part of FCI
What does SQL FCI require
Shared Storage (either in Azure Shared File storage or Using storage spaces direct on Windows Server)
Is AOAG or FCI preferred for new Azure Deployments - and why
AOAG as no complexity or shared storage
Why might you need to build a SQL FCI in Azure
For on-prem migrations requiring the use of FCI for App Support
DR is still required in Azure, what methods are there for DR
Azure Platform Options (Geo-Replicated Storage and Azure Site Recovery)
SQL Offered Solutions like AOAG and Backups
On a VM how can you control backups and where can you backup to
You have granular control and can backup to the IaaS disk or to Azure Blob Storage
What is the Azure option to use Geo-Redundant Storage or Read-Access geo-Redundant Storage
So you can ensure your backups are stpred safely across geographic landscape
What does the Azure SQL VM provider allow you to do for your backups
Allows SQL backups to be managed by the platform
Give the headlines on Azure Backup for SQL Server
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 can Availability Groups help with SQL Server on IaaS aside from HA
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
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
Define the Azure Market place
Central location to create Azure resource via a template
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
What Disk types are there in IaaS
Standard HDD
Standard SSD
Premium SSD
Ultra Disk
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
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
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
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
What is often the first type of service used when migrating from on-prem
IaaS
What does the Azure Migrate Server tool do?
Provides a central location to assess and migrate on-prem resources.
What types of servers (physical, virtual) can the Azure Migrate Server tool discover and assess.
Both physical and virtual (either VMWare or Hyper V)
What budget type info does the Azure Migrate Server tool provide
Suggests VM Sizes and estimates cost
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
What is the MAP toolkit
Microsoft Assessment and Planning Toolkit
What does the Map toolkit do
Gathers inventory of SQL Servers on network (Version and Server info) and reports on it’s findings.
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
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
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
The Data Migration Assistant can assess T-SQL queries from where?
Extended Events Trace and external applications (such as those written in C# etc)
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
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
What are some of the other (more manual) ways of performing a migration to Azure
Backup/Restore
Log Shipping
AOAG
Replication
What are the PaaS Service Tiers
DTUs (Basic, Standard, Premium)
vCores (General Purpose, Business Critical, HyperScale)
What is the Hyperscale service tier used for
Databases over 4TBs
What storage does the General Purpose tier use?
Azure Premium Storage (Blob)
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
How are PaaS Backups taken
Automatically,
Full - Weekly
Diff - 12 hourly
Log - 5/10 mins
Where are PaaS backups stored
Azure Blob Storage (Geo-Redundant)
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
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
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.
What are the limitations when restoring a DB in a PaaS environment
Cannot restore over an existing DB, need to
delete/rename the original
What can cause fluctuations in restore times in a PaaS environment
Platform Service Tiers
How can you restore a PaaS DB
Via the portal or scripting langages
What is Active-Geo Replication
Business Continuity feature with async replication to up to 4 replicas
How is a TX processed with Active Geo Replication.
Tx committed to primary and replicas in region
Tx replayed at secondaries async
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
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.
Failover Groups are built on what??
Geo-Replication
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.
Where do you deploy a Serverless PaaS DB
At the logical server - same as a singleton
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
What resumes a paused serverless DB
A connection will resume the DB however it is not instant
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.
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.
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
How do you convert back from Azure SQL DB HyperScale
You cannot convert back once using Hyperscale
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
What are elastic pools good for
Multi-Tenant where each has own DB with a similar workload
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
What is SQL MI similar to and Why
Similar to on prem or IaaS with access to TempDB and SQL Agent etc
How does SQL MI differ from IaaS
No access to underlying OS
What is the uptime SLA of MI
99.99%
What does General Purpose provide in terms of HA for MI
Storage replication for availability
What does Business Critical provide in terms of HA for MI
Multiple Replicas
Where can you NOT restore MI Backups
Across different regions or subscriptions
What can you take in terms of MI backups
A copy_only backup of a DB stored in Azure Storage
At what level is MI backed up
At database level not the instance level
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
What must you do before taking a copy only backup in MI
Turn off TDE.
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
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”
What are the Open Source DB Offerings
MySQL, MariaDB, PostgreSQL
What services do the Open Source DB Offerings have
Native HA
Auto Patch and Backup
Highest Security Potential
Fully Supported by Microsoft
How do you upgrade to a new version of an Open Source DB offering
Backup and restore into a new version
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
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.
What are the open source DB service Tiers
Basic, General Purpose, Memory Optimized (bringing quicker processing and higher concurrency))
How can you deploy PostgreSQL and what do you need to decide
Portal, CLI, ARM, Limited Powershell
Select Single Server or HyperScale
PostgreSQL Hyperscale is different to Azure SQL DB Hyperscale yet both use what
Horizontal Scaling
What does Hyperscale allow PostgreSQL nodes to do
Work together in shared nothing design.
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.
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
True or False - PostgreSQL Hyperscale Worker and Coordinator nodes can be scaled inderpendantly
True.
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.
For MariaDB and MySQL HA is built in - what is the SLA uptime
99.99%
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
How do you scale out read workloads for Maria and MySQL
With “Replication” Setting to simply add a read only replica.
