1 Plan and Implement Data Platform Resources Flashcards
1
Q
Deploying a SQL Server Virtual Machine
A
- Create, SQL Database
- Server name needs to be unique throughout Azure
- Admin account password must be at least 12 characters long
- If adding existing data, select a backup or sample data
- You should use Managed Service Accounts (MSA) for a single computer running a service
2
Q
What is a gMSA
A
Group Managed Service Account, used for assigning the MSA to multiple servers
3
Q
Deploying Azure SQL Managed Instance
A
- Create, SQL Managed Instance
- You need
- Subscription and resource group
- Managed instance name
- Region
- Managed instance admin login and password
4
Q
Deploying VM with SQL Server
A
- Create a Virtual Network - In IP Addresses –default(subnet)– Edit subnet, add the Service Endpoint “Microsoft.SQL”
- Create the Azure VM - In Networking, select “Private endpoint”, then “+Add Private Endpoint” and select the subnet from above.
- When created, in “Firewalls and virtual networks”, click “+Add client IP”, and “Allow Azure services and resources to access this server”.
5
Q
Patching + Updates for Azure SQL and Managed Instance
A
- Built in patching
- Both will use the latest stable database engine version
6
Q
OS Updates
A
- As per standard Microsoft Windows Update
- Windows may do some updates automatically
7
Q
Patching SQL Server - IaaS
Enabling Automated Patching
A
- Requires SQL 2008R2 or above, and Windows Server 2008 R2 or above
- In SQL Server settings which created the VM OR In the Azure resource, go to Settings > SQL Server Configuration > Patching.
- May take several minutes
- Settings include day, start hour, window duration (# of minutes to download and install)
8
Q
When can you configure Automated Patching
A
- When creating a new VM
- For existing VMs, by going to the Azure Portal > the relevant VM > Settings > SQL Server Configuration > Patching
- By using Powershell
9
Q
What are the Powershell commands for enabling Automated Patching?
A
New-AzVMSqlServerAutoPatchingConfig -Enable (setting the schedule)
Set-AzVmSqlServerExtension (installing the extension with the schedule)
10
Q
What is Automatic Registration?
A
- This daily checks whether there are any unregistered VMs in the subscription, and if so, registers them in lightweight mode.
- To take advantage of all the features, you would still need to manually upgrade.
- To enable, go to Azure Portal > SQL virtual machines (plural) > At the top, click “Automatic SQL Server VM registration”
11
Q
What are the requirements for deploying Azure SQL Database?
A
- PaaS
- Azure manages the database
- Resources are always running unless dropped (apart from serverless, when paused)
- Best for modern cloud applications, and fast time-to-market for new solutions are needed.
- Can use Azure Hybrid Benefit (Windows Server for VMs only, and SQL Server licenses with Software Assurance) and reserved capacity to reduce cost.
- Hybrid Benefit doesn’t apply to serverless
12
Q
What are the requirements for deploying Azure SQL Managed Instance?
A
- PaaS
- Azure manages the database
- Resources are always running unless dropped
- Best for most migrations to the cloud. May need some changes
- Best for new applications or existing on-prem applications for use in the cloud.
- Can use Azure Hybrid Benefit (Windows Server for VMs only, and SQL Server licenses with Software Assurance) and reserved capacity to reduce cost.
13
Q
What are the requirements for deploying SQL Server on VMs
A
- IaaS
- You need to manage the VM, and gives you control of the database
- You can shut down resources when not in use
- Lift-and-shift. As easy as moving from one on-prem server to another.
- Higher cost than PaaS
- Best when you don’t want any database changes, or when you require OS-level access.
- Can use Azure Hybrid Benefit (Windows Server for VMs only, and SQL Server licenses with Software Assurance) and reserved capacity to reduce cost.
14
Q
What are the functional benefits/impact of Azure SQL Database?
A
- Most commonly used SQL Server features
- Trace flags not supported
- Built-in backups, patching, and recovery
- Databases up to 100 TB
- Supports server less compute [the alternative is “provisioned”]
- CLR not supported
- Based on the latest stable Enterprise Edition
- Can use Elastic Job Agent service
15
Q
What are the functional benefits/impact of Azure SQL Managed Instance?
A
- High compatibility with SQL Server
- Only a limited number of (global) trace flags are supported
- Built-on backups, patching, and recovery.
- Up to currently available instance size, subject to # of vCores
- CLR supported
- Based on latest stable Enterprise Edition
- Can use SQL Agent jobs
16
Q
What are the functional benefits/impacts of SQL Server on Azure VM?
A
- All on-premises capabilities
- Trace flags supported
- You manage backup and patches.
- Instances up to 256 TB (Databases of up to edition size)
- Lift-and-shift
- Choose which version, edition, and OS you use.
- Can use SQL Agent jobs
17
Q
What is the HA/DR for Azure SQL Database?
A
- Up to 99.995% availability
- Minimum SQL is 99.99% availability, except for Hyperscale, which is 99.9% - 99.95
- At the Basic, Standard and General Purpose level, you can use locally redundant availability.
- At the Premium and Business Critical level or elastic pools, can use a 3 to 4 node cluster with Locally or Zone Redundant Availability. You can also add read-only replicas (“Read-Scale-out” feature)
- At the General Purpose level using Gen5 compute hardware in certain regions, can use Zone redundant configuration (preview).
18
Q
What is HA/DR for Azure SQL Managed Instance?
A
- 99.99% availability
- At the Basic, Standard, and General Purpose level, can use locally redundant availability. At the Premium and Business Critical level, or elastic pools, an use a 3 to 4 node cluster with Locally Redundant Availability. You can also add read-only replicas (the “Read Scale-out” feature).
19
Q
What is HA/DR for SQL Server on Azure VM?
A
- Up to 99.995 availability. However, this requires a second VM, and using AlwaysOn availability groups. Minimum SLA is 95% for the VM.
- Can configure availability replicas, using a domain controller VM.
20
Q
What are the backup, restore, and redundancy options for Azure SQL database?
A
- Automatic backups, including full, differential, and transaction log, for 7-35 days.
- Can configure full database backups to Azure Storage for long-term backup retention.
- Point-in-time restores
- Can configure Active geo-replication (up to 4 readable secondary databases)
- Can configure auto-failover groups
21
Q
What are the backup, restore, and redundancy options for Azure SQL Managed Instance?
A
- Automatic backups, including full, differential, and transaction log, for 7-35 days.
- Can perform copy-only backups for long-term retention (preview).
- Point-in-time restores
- No Active geo-replication
- Can configure auto-failover groups.
22
Q
What are the backup, restore, and redundancy options for SQL Server on Azure VM?
A
- Can configure backups
- With appropriate backups, can do point-in-time restores
- Can configure geo-replication storage (asynchronously). Data file and log file needs to be on the same disk.
- Can configure Azure failover cluster instances using shared storage.
23
Q
What are the security aspects of Azure SQL Database?
A
- Auditing works at the database level
- .xel log files are stored in Azure Blob storage
- Can use Azure Defender for SQL, which includes: Vulnerability assessment and Thread detection (costs around .02/instance/hour)
- Data encryption, using Transport Layer Security (TLS), Transparent Data Encryption (TDE), and Always Encrypted. Firewalls
- SQL authentication or Azure Active Directory (Microsoft Entra ID)
24
Q
What are the security aspects of SQL Managed Instance?
A
- Auditing works at the server level.
- .xel log files are stored in Azure Blob storage
- Can use Azure Defender for SQL, which includes: Vulnerability assessment and threat detection (costs around .02/instance/hour)
- Data encryption, sing Transport Layer Security (TLS), Transparent Data Encryption (TDE), and Always Encrypted. Firewalls
- SQL authentication or Azure Active Directory authentication (Microsoft Entra ID)
25
What are the security aspects of SQL Server on Azure VM?
- Auditing works at the server level
- Events are stored in the file system or Windows event logs.
- Can use Azure Defender for SQL, which includes: Vulnerability assessment and threat detection (costs around .02/instance/hour)
- Data encryption, sing Transport Layer Security (TLS), Transparent Data Encryption (TDE), and Always Encrypted. Firewalls
- Windows or SQL Server authentication
26
What are reasons to partition data?
- Scalability - There hardware limits, but if you divide data into partitions on separate servers, it can be scaled out.
- Performance - Smaller amounts of data in a single partition, and multiple data stores can be accessed at the same time.
- Security - Apply different security to sensitive and non-sensitive partitions.
- Administration - Different strategies of monitoring, management, and backup per partition. Backups for a single partition can be quicker than the entire DB.
- Different hardware or services - Premium or Standard where needed
- Availability - If once instance fails, only that partition is unavailable.
27
What is Horizontal partitioning?
- All partitions have the same schema
- Each partition, or shard, holds a subset of the data (rows)
- If some data is fairly static or small, consider replicating it in all partitions to reduce cross-partition access.
28
What is vertical partitioning?
- Each partition holds a subset of the columns
Some columns may be needed less often, and they could be separated away, and used only when needed.
- Some columns may also be more sensitive, and could be separated away.
- All partitions would need to be capable of being joined - for instance, by the same primary key in each.
29
What is functional partitioning?
- Different tables in each partition relating to function
- Store data could be in one partition and employee data in another.
- Some tables could be more sensitive, and could be separated away into another partition.
30
What are other recommendations for partitioning?
- Horizontal, vertical, and functional partitioning can be combined
- Keep the data, where possible, geographically close to the users.
- Consider the backup, archiving (including deleting) and HA/DR requirements for each partition.
31
Why should you partition?
- Storage space limitations - Maximum storage capacity can be reached on a server
- Computing resource limits - Exceeding this may result in time outs
- Network bandwidth- Exceeding this can result in failed requests.
32
How do you scale vertically?
- Add disk capacity, processing power, memory, and network connections
- You may reach the same problem later.
33
How do you partition the data store horizontally into shards?
- Each shard has its own subset of data
- It runs on a server acting as a storage node
34
How do you partition horizontally?
- Scale out by adding additional shards
- Use off-the-shelf hardware for each storage node
- Balance the workload across shards
- Locate shards near to the users who will use it.
35
How do you implement a lookup strategy for horizontal partitioning?
- Have a shard key (an ID), an a map which shows where the data is stored.
- Offers more control
- Requires additional overhead.
36
How do you implement a range strategy for horizontal partitioning?
- Use sequential shard keys in ranges (e.g. one per month)
- Similar data is kept on the same storage node, so it can retrieve multiple items in a single operation.
- Doesn’t necessarily provide optimal balancing
- Rebalancing shares is difficult
37
How do you implement a hash strategy for horizontal partitioning?
- Data distributed evenly among the shards. Reduces hotspots (high loads for an individual server) by using some random element for distribution.
- More even data and load distribution
- Computing the hash might increase the overall requirements
- Rebalancing shards is difficult
38
What are the scale and performance options for Azure SQL Database?
- Single database
- Elastic pool
- Compute tier
39
What are the compute tiers for Azure SQL Database?
- Provisioned - For regular usage patterns, or multiple databases with elastic pools
- Serverless compute - on/off usage with a relative low average compute. This supports automatic pausing and resuming. When paused, you only pay for storage
40
What is the vCore purchasing model?
- Specify separate amount of Number of vCores, memory, and amount/speed of storage
- Look at
* Data maximum memory size
* Log size (30% of the data max size)
* IOPs
* Concurrent workers
* Backup retention
41
What are the maximum vCore configurations?
- 80 vCores at Gen5
- 4 TB memory
- 4 TB database size
- Hyperscale has 100TB database size
42
What is the purpose of using Azure Hybrid benefit and/or reserved capacity?
- Azure Hybrid benefit allowed you to bring in your existing on-prem licenses to the cloud
- Reserved capacity is paying in advance at a discount
43
What drives are used for vCore purchasing models?
Local SSDs provisioned in 1 GB increments
44
What is the General Purpose service tier?
- For most business workloads
- Storage latency of 5-10ms (about the same as SQL Server on a VM)
45
What is the Business Critical service tier?
- When you need low latency I/O (1-2 ms) or frequent communications between app and database
- Large number of updates, or long running transactions that modify data
- Higher resiliency, availability, and fast geo-recovery and recovery from failures, and advanced data corruption protection.
- Free-of-charge secondary read-only replica
46
What is the Hyperscale service tier?
- On-demand scalable storage
- Only for Azure SQL database
- You cannot subsequently change out of Hyperscale. Costs the same as Azure SQL Database
47
What are the max data sizes per hardware type?
- GP_S_Gen5_1 - 512 GB
- GP_S_Gen5_2, 4, 6 - 102 GB
- GP_S_Gen5_10 - 1536 GB
- GP_S_Gen5_12, 14, 16, 18, 20 - 3072 GB
- GP_S_Gen5_24, 32, 40 - 4096 GB
48
How is tempdb configured for Azure SQL Database?
- Creates 1 file per vCore with 32GB per file
- Caps of up to 32 files for serverless computing only
49
What is the DTU-based purchasing model?
- For light to heavy workloads
- Offers bundles of maximum number of compute, memory, and I/O (reads/writes) resources for each class. You cannot separate them.
- Uses Azure Premium disks. Provisioned in increments of 250 GB to 1GB, and 256 GB thereafter.
50
What are the service tiers for the DTU purchasing model?
- Basic - For less demanding workloads
- Standard - For typical performance
- Premium - For I/O intensive workloads
- Note - Basic and Standard S0, S1, and S2 have less than 1 vCore, and cannot use “change data capture”
- Consider Basic, S0, and S1, where database files are stored in Azure Standard Storage (HDD) for development, testing, and infrequently accessed workloads.
51
When should you consider changing to VCores from DTUs?
When you need to use more than 300 DTUs
Might reduce costs and there’s no downtime to converting
52
What is the URL for the DTU calculator
https://dtucalculator.azurewebsites.net
53
What are the considerations for changing service tiers on demand (but not out of Hyperscale)?
- Don’t do it when there’s a long job running
- For the DMVs to have accurate figures, you may need to flush the Query Store after rescaling
- EXEC sp_query_store_flush_db
54
What are the considerations for choosing between server and serverless models?
- A server is a logical server, which includes logins, firewall and auditing rules, policies, and failover groups.
- The server name must be unique in Azure
- Don’t need to manage the instance
- Cannot use “USE” to change database context
Vs
- Serverless bills for compute per second
- Pauses databases and billing in inactive periods
55
What are network configuration settings?
- No access
- Public/private endpoint. You can add current client IP address
- Choose whether to "Allow Azure services and resources to access this server" (for other Azure services)
56
What are the connection policies?
- Proxy - Uses Azure SQL Database gateways
- Redirect - Establish directly to the database node
- Default - Redirect if connection originates inside Azure, and Proxy if outside Azure
57
How can you seed data in Azure SQL Database?
You can have sample data, or data based on the restore from a geo-replicated backup
58
What are the database collation options?
CS - Case Sensitive
CI - Case Insensitive
AS - Accent Sensitive
AI - Accent Insensitive
59
What is Azure Defender?
Identifies and mitigate potential database vulnerabilities and threat detection
60
What recovery model does Azure SQL Database use?
Full and it should not be changed
61
What are the service tiers for Azure SQL?
General Purpose
Business Critical
Hyperscale
62
What is the best use case for the General Purpose service tier? (Azure SQL DB)
Budget oriented balanced compute and storage options
63
What is the best use case for the Business Critical service tier? (Azure SQL DB)
OLTP applications with high transaction rate and low IO latency. Offers highest resilience to failures and fast failovers using multiple synchronously updated replicas.
64
What is the best use case for the Hyperscale service tier? (Azure SQL DB)
Not for Managed Instance
Most business workloads. Auto-scaling storage size up to 100 TB, fluid vertical and horizontal compute scaling, fast database restore.
65
What is the compute size for the General Purpose service tier? (Azure SQL DB)
1 to 80 vCores
66
What is the compute size for the Business Critical service tier? (Azure SQL DB)
1 to 128 vCores
67
What is the compute size for the Hyperscale service tier? (Azure SQL DB)
1 to 80 vCores
68
What is the storage type for the General Purpose service tier? (Azure SQL DB)
Remote storage
69
What is the storage type for the Business Critical service tier? (Azure SQL DB)
Local SSD storage
70
What is the storage type for the Hyperscale service tier? (Azure SQL DB)
Tiered remote and local SSD storage
71
What is the log write throughput for the General Purpose tier? (Azure SQL DB)
Single databases: 4.5 MB/s per vCore (Max 50 MB/s)
Elastic pool: 6 MB/s per vCore (max 62.5 MB/s)
72
What is the log write throughput for the Business Critical service tier? (Azure SQL DB)
Single databases: 12 MB/s per vCore (max 96 MB/s)
Elastic pools: 15 MB/s per vCore (max 120 MB/s)
73
What is the log write throughput for the Hyperscale service tier? (Azure SQL DB)
100 MB/s
74
What is the availability for the General Purpose service tier? (Azure SQL DB)
99.99%
75
What is the availability for the Business Critical service tier? (Azure SQL DB)
99.99%
99.995% with zone redundant single database
76
What is the availability for the Hyperscale service tier? (Azure SQL DB)
99.95% with one secondary replica
99.99% with more replicas
77
Where is in-memory OLTP available? (Azure SQL DB)
- Available in Business Critical
- Partially supported in Hyperscale. Memory-optimized table types, table variables, and natively compiled modules are supported
- Not available in General Purpose
78
How many ready only replicas are available? (Azure SQL DB)
- General Purpose - 0 built-in; 0-4 using geo-replication
- Business Critical - 1 built-in, included in price; 0-4 using geo-replication
- Hyperscale - 0-4 built-in
79
What is the database + storage size for General Purpose and Business Critical service tiers? (Azure SQL DB)
1 GB - 4 TB
80
What is the database + storage size for the Hyperscale service tier? (Azure SQL DB)
40 GB - 100 TB
81
What is the tempdb size for General Purpose and Business Critical service tiers? (Azure SQL DB)
32 GB per vCore at no extra cost
82
What is the tempdb size for the Hyperscale tier? (Azure SQL DB)
32 GB per vCore
83
What is the backup policy for General Purpose and Business Critical service tiers? (Azure SQL DB)
RA-GRS (read-access geo-redundant storage), 1-35 days (7 days by default)
84
What is the backup policy for the Hyperscale service tier? (Azure SQL DB)
RA-GRS (read-access geo-redundant storage), 7 days, fast point-in-time recovery (PITR)
85
What is the pricing/billing for General Purpose and Business Critical service tiers? (Azure SQL DB)
- vCore, reserved storage, and backup storage are charged
- IOPS is not charged
86
What is the pricing/billing for the Hyperscale service tier? (Azure SQL DB)
- vCore for reach replica and used storage are charged
- IOPS not yet charged
87
What are the discount models for General Purpose and Business Critical service tiers? (Azure SQL DB)
- Reserved instances
- Azure Hybrid Benefit (not available on dev/test subscriptions)
- Enterprise and Pay-As-You-Go Dev/Test subscriptions
88
What are the discount models for the Hyperscale service tier? (Azure SQL DB)
- Azure Hybrid Benefit (not available on dev/test subscriptions)
- Enterprise and Pay-As-You-Go Dev/Test subscriptions
89
What are the service tiers for Azure SQL Managed Instance?
- General purpose - Most workloads (default option)
- Business Critical
** Low-latency workloads
** High resiliency to failures
** Fast Failovers
90
What is the hardware generation for Azure SQL Managed Instance
- Compute and memory limits
- Up to 80 vCores
- 400 GB Memory
- Up to 100 databases
- Up to 16 TB database size
91
What are features that are in Azure SQL Managed Instance that are not in Azure SQL Database?
- Cross-database queries
- CLR (common language runtime
- SQL Agent
- msdb database
- You can manually make a copy-only backup of a database
92
What is the vCore compute model for Azure SQL Managed Instance?
- SQL Managed Instances does not support the DTU-based purchased model
- Cannot use Hyperscale in Azure SQL MI
93
What is a virtual cluster for Azure SQL Managed Instance?
A dedicated ring for data
94
How many tempdb files are created for Azure SQL Managed Instance?
12 regardless of the number of vCores
95
What is the best use of the General Purpose service tier for Azure SQL Managed Instance?
Budget oriented balanced compute and storage options
96
What is the best use of the Business Critical service tier for Azure SQL Managed Instance?
OLTP applications with high transaction rate and low IO latency. Offers highest resilience to failures and fast failovers using multiple synchronously updated replicas
97
What is the compute size for the General Purpose service tier for Azure SQL Managed Instance?
4, 8, 16, 24, 32, 40, 64, 80 vCores
2 vCores also available in pools
98
What is the compute size for the Business Critical service tier for Azure SQL Managed Instance?
4, 8, 16, 24, 32, 40, 64, 80 vCores
99
What kinds of storage type is used by the General Purpose service tier for Azure SQL Managed Instance?
Remote storage
100
What is the storage type for the Business Critical service tier in Azure SQL Managed Instance?
Local SSD storage
101
What is the database + storage size for the General Purpose service tier in Azure SQL Managed Instance?
32 GB - 8 TB
102
What is the database + storage size for the Business Critical service tier for Azure SQL Managed Instance?
32 GB - 4 TB
103
What is the tempdb size for the General Purpose service tier in Azure SQL Managed Instance?
24 GB per vCore (max about 2 TB)
104
What is the tempdb size for the Business Critical service tier for Azure SQL Managed Instance?
Up to 4 TB - limited by storage size
105
What is the log write throughput for the General Purpose tier in Azure SQL Managed Instance?
3 MB/s per vCore (max 22 MB/s)
106
What is the log write throughput for the Business Critical service tier of Azure SQL Managed Instance?
4 MB/s per vCore (max 48 MB/s)
107
What is the availability for the General Purpose service tier for Azure SQL Managed Instance?
99.99%
108
What is the availability for the Business Critical service tier for Azure SQL Managed Instance?
99.99%
99.995% with zone redundant single database
109
110
What service tier allows for in-memory OLTP in Azure SQL Managed Instance?
Business Critical only
111
What are the read only replica options for the General Purpose service tier in Azure SQL Managed Instance?
0 built-in
0-4 using geo replication
112
What are the read only replica options for the Business Critical service tier in Azure SQL Managed Instance?
1 built-in, included in price
0-4 using geo replication
113
What is the backup policy for Azure SQL Managed Instance?
RA-GRS (read access geo-redundant storage), 1035 days, 7 days by default
114
What is the pricing/billing for Azure SQL Managed Instance?
- vCore, reserved storage, and backup storage is charged
- IOPS is not charged
115
What are the discount models for Azure SQL Managed Instance?
- Reserved instances
- Azure Hybrid Benefit (not available on dev/test subscriptions)
- Enterprise and Pay-As-You-Go Dev/Test subscriptions
116
What are the SLAs for SQL Server in Azure VMs?
- 95% (18 days) - Standard HDD Managed Disks
- 99.5% (1.8 days) - Standard SSD Managed Disks
- 99.9% (8 hours) - Premium SSD or Ultra Disks
- 99.95% - 2+ instances in the same Availability Set (Different computers in the same datacenter)
- 99.99% - 2+ instances in 2+ Availability Zones in the same Azure region (different physical datacenters with independent power, cooling, and networking)
117
When should you use SQL Server in Azure VMs?
- When you need an older version of SQL Server or access to a Windows Operating System
- When you need SSAS, SSIS, or SSRS
- When you need features not available in Azure SQL Database or Azure SQL Managed Instance
118
What are the best practices for VM size for SQL Server in Azure VMs?
- At least 4 vCPUs
- Memory optimized VMs are best for SQL Server workloads
- Higher memory-to-vCore ratio for mission critical or data warehouses.
- Azure VM marketplace images are configured for optimal SQL Server performance.
119
What are the best practices for Storage for SQL Server in Azure VMs for placing data files?
- Place data, log, and tempdb files on separate drives
- Data drives should be on Premium P30 and P40 disks for cache support
- Log drive should be on Premium P3o to P80 disks, or Ultra disks for sub millisecond latency.
- Tempdb should be placed on the D drive on the SSD
120
What are the best practices for Storage for SQL Server in Azure VMs for striping?
- Stripe using Storage Spaces to increase I/O bandwidth. 3+ drives form a storage pool. Which is done by
1. Creating the individual disks
2. Creating the storage pool
3. Creating a single virtual disk from 3 resiliency types - Simple, Mirror, or Parity
4. Creating a volume
121
What is the Simple resiliency type for striping?
- Needs at least 1 physical disk
- Stripes data on physical disks
- Maximizes disk capacity and increases throughput
- No resiliency (does not protect from disk failure)
- Use for high-performance where resilenccy is not required by striping
122
What is the Mirror resiliency type for striping?
- Needs at least 2 physical disks to protect from single disk failure
- 2-3 copies of the data
- Increases the reliability, but reduces capacity
- Greater data throughput and lower access latency
- Use for most deployments
123
What is the Parity resiliency type for striping?
- Needs at least 3 physical disks to protect from single disk failure
- Stripes data and parity information across disks
- Increases reliability, but reduced capacity
- Increases resiliency
- Use for archive and backups
124
What configuration should you use for the storage account used by SQL Server in Azure VMs?
Use Local Redundant Storage, not Geo-redundant storage
125
What are the 6 different series of VMs?
- General Purpose - Balanced CPU-to-memory; good for dev/test, small to medium DBs or traffic web servers
- Compute Optimized - High CPU; good for medium traffic web servers, network appliances, batch processes, and application servers
- Memory Optimized - High Memory (up to 4 TB); Good for relational database servers, medium to large caches, and in-memory analytics.
- Storage Optimized - High disk throughput; Good for Big Data, SQL, NoSQL DBs, data warehousing and large transactional databases.
- GPU - Specialized VMs; Heavy graphic rendering and video editing, as well as model training and inferencing (ND) with deep learning.
- High performance compute - Fastest VMs; Most powerful CPU machines
126
What are the best practices for SQL Server in Azure VMs
- Enable database page compression where appropriate
- Enable backup compression and instant file initialization
- Limit auto growth and disable auto shrink
- Use one tempdb data file per core, up to 8 files
- Apply any CUs for your version of SQL Server
- Register with the SQL IaaS Agent extension for automated backup, patching, Key Vault integration, Portal reporting, and more.
- Enable auto shutdown for dev/test environments
127
How do you configure storage and infrastructure resources for VMs?
- When creating the VM, the "SQL Server settings - Change configuration" show the storage
- Under "Configure storage" - Select "transactional processing" or "data warehousing". This will change your stripe configuration, optimizing it for OLTP or analytic/reporting workloads
- After setting the VM, when using disk caching for Premium SSD, you can select the disk caching level (Settings > Disks)
128
What are the settings for disk caching when using Premium SSDs for SQL Server in Azure VMs?
- ReadOnly for SQL Server data files, as this improves reads from cache (VM memory and local SSD), which is much faster than from disk (Azure Blob storage)
- None for SQL Server log files, as the data is written sequentially
- ReadWrite caching shouldn't be used for SQL Server files as SQL doesn't support data consistency with this cache type. But it could be used for the OS drive, but isn't recommended to change the OS caching level.
129
How do you configure storage and infrastructure resources for Azure SQL database and Azure SQL Managed Instance?
Go to Settings > Compute + storage
130
What is the DTU purchasing model?
- A package of compute, storage, and IO resources
- Simple, pre-configured
131
What is the vCore-based purchasing model?
- Independent scaling of compute, storage, and IO resources
- Flexible, control, transparency
- Use with Azure Hybrid Benefit for cost savings
- Business Critical service tier includes 3 replicas, but is 2.7x the price
132
What are the rules for both purchasing models for single databases?
- They can be moved in and out of elastic pools
- They are isolated from others and are portable
- They can be dynamically (aka manually) scaled (but not auto scaled) up and down.
133
What are the rule for both purchasing models for Elastic Pools?
- You assign resources which are shared by all pool databases
- You can dynamically scale or auto scale resources up and down.
- This is for multiple databases, good when they have variable usage patterns
- Can add databases by going to the pool and clicking on "+Add databases"
134
What are the storage costs for both purchasing models?
Based on the amount provisioned, that's the maximum database size
135
How do you calculate costs
https://azure.microsoft.com/en-us/pricing/details/azure-sql-database/single
136
What factors should you consider when determining how many DTUs would be needed?
- Max storage bytes (for all databases in the pool)
- Average DTU utilization x number of databases
- Number of concurrently peaking databases x peak DTU utilization per database
- Unit price for eDTU pools is 1.5x the DTU unit price for a single database
- Unit price for vCore pools is the same unit price for single databases.
137
Why should you use compression?
- Reduced space. Useful for data which is infrequently used
- You can compress at the row level, page level, or none
- Compression doesn't affect backup and restore
- Requires extra time and CPU to compress and retrieve data
138
How does row compression work?
- Numeric types will be reduced, maybe down to 1 byte.
- Some data types will be compress: datetime, datetime2, and datetimeoffset. Others do not benefit from compression
- char and nchar will be compressed, up to 50% in English, German, Hindi, and Turkish, but only up to 40% in Vietnamese and 15% Japanese.
- varchar and nvarchar do not benefit.
139
How does page compression work?
Page compression is 3 operations in order
- Row compression
- Prefix compression
- Dictionary compression
- Pages are uncompressed at first. Page compression is only used when additional rows can be fit on a full page.
140
How does prefix compression work?
- If values in the same column start with the same characters, this can be optimized.
- A common prefix per column is moved to the Compression information structure immediately after the page header.
- A reference is made in that value to the prefix, and the number of characters which are in common.
141
How does dictionary compression work?
- If values after prefix compression in ANY column are the same, this can be optimized.
- Common values are moved to the Compression Information structure and a reference is made.
142
In what editions/versions of SQL server is compression available in?
- Azure SQL Database
- Azure SQL Managed Instance
- SQL Server on VMs
**from SQL Server 2016 SP1 in all editions
**Enterprise only before
143
What can you compress?
- Tables stored with a clustered index or without
- A complete nonclustered index
- A complete indexed view
144
What can you not compress?
System tables
145
Can you use different compression on different partitions?
Yes
146
What can you run to estimate compression benefits?
sp_estimate_data_compression_savings
(Not available in Azure SQL Database)
- 'SchemaName'
- 'TableName'
- Index_ID - either 0 for a heap, 1 for a clustered index, or >1 for nonclustered index. NULL if a table
- PartitionNumber (Or NULL)
- 'ROW' (or 'PAGE', 'NONE')
147
How do you enable compression in SSMS?
1. Right click on table/index, and go to Storage > Manage Compression
2. Click next, and select the compression type for each partition.
* You can also click "User same compression type for all partitions"
* You can also click on "Calculate" to calculate space requirements (not in Azure SQL DB)
3. Select whether to run immediately or create a script (to file, clipboard, or new query window)
**If running on VM, you may also be able to schedule
148
How do you enable compression for a table with TSQL?
ALTER TABLE schema.tablename
REBUILD PARTITION = 1 | ALL WITH (DATA_COMPRESSION = PAGE | ROW | NONE);
149
How do you enable compression for an index with TSQL
ALTER INDEX IndexName ON schema.tablename
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE | ROW | NONE);
150
What are considerations when using compression for columnstore objects?
- Initially used in data warehouses, but then expanded
- Columns are always compressed
- Indexes work best when you scan large amounts of data, like fact tables in DWs
- Generally clustered. Nonclustered only used when you have a data type not supported by a clustered index.
- Can be further compressed using archival compression
151
When should you use archival compression?
- When the data is not often read, but needed to be retained for regulatory or business reasons.
- Saves space, but there is a high CPU cost to uncompressing it, which is more than any IO saving.
152
How do you evaluate the requirements for migration?
- What workloads you intend to migrate?
- Actual resource requirements.
- Downtime allowances
- Dependencies between databases, and between databases and applications
- Security requirements
- Backup and restore requirements
- Current and future limitations
- Location for data storage (GDPR, California Consumer Privacy Act, or similar)
153
What is the strategy to lift-and-shift SQL Server to a VM?
- Use Azure Migrate
- It can discover and assess SQL data estate at scale (across your data center)
- Get Azure SQL deployment recommendations, target sizing, and monthly estimates
154
What is the strategy to migrate non-SQL objects, such as Access, DB2, MySQL, Oracle, and SAP ASE database to SQL Server or Azure SQL?
Use SQL Server Migration Assistant
155
What is the strategy to migrate SQL Server objects to SQL Database/Managed Instance if you need to migrate and/or upgrade SQL Server?
- Use Data Migration Assistant (DMA)
- Can help migrate to Azure SQL Database, or to a VM or to another on-prem server
- Can also discover and assess SQL data estate, and recommend performance and reliability improvements for your target environment
- Detect compatibility issues between your current DB and a target version of SQL Server or Azure SQL.
- Move you schema, data, and uncontained objects.
156
What is the strategy to migrate SQL Server objects to SQL Database/Managed Instance if you need to compare workloads between the source and target SQL Server?
- Use Database Experimentation Assistant (DEA)
- Capture the workload of a source SQL Server environment
- Identify compatibility issues
157
What is the strategy to migrate SQL Server objects to SQL Database/Managed Instance if you need to migrate open source databases, such as MySQL, PostgreSQL, or MariaDB?
- Use Azure Database Migration Services (DMS)
- Minimal downtime (especially if online using the Premium pricing tier). Good for large migrations.
158
What are the requirements for using the Azure Database Migration Service
- Allow outbound port 443 (HTTPS), possibly 1434 (UDP)
- Enable TCP/IP protocol
- Create an Azure SQL Database instance, have a server-level firewall rule to allow DMS access, and have CONTROL DATABASE permission on the target database.
- Have CONTROL SERVER permissions on the source.
- This doesn't initiate any backups, and uses existing full and log backups (not diffs)
159
What migration options can be online (continuous sync)?
SQL Server to Azure SQL MI
MongoDB to Azure Cosmos DB
PostgreSQL to Azure DB for PostgreSQL
160
What migration options can be offline (one-time)?
SQL Server to Azure SQL MI
SQL Server to Azure DB
SQL Server to Azure SQL VM
MongoDB to Azure Cosmos DB
MySQL to Azure DB for MySQL
161
What are the prerequisites for Migrating SQL Server to Azure SQL Database?
- Enable TCP/IP on your SQL Server instance
- Download the Data Migration Assistant
- Create a Virtual Network for the Azure Database Migration Service using either ExpressRoute
or VPN.
- Enable outbound port 443 of ServiceTag for ServiceBus, Storage and AzureMonitor.
- Allow database engine access in Windows firewall, and open the Windows firewall to TCP
port 1433 (unless you have changed it). You may also need to have UDP port 1434.
- Create a server-level IP firewall rule to allow Azure Database Migration Service access.
- Your credentials need CONTROL SERVER on the SQL Server instance, and CONTROL DATABASE on Azure SQL.
162
How do you check for issues that may block a migration?
- In Data Migration Assistant, select +New and Assessment, and enter a project name.
- Select Database Engine, SQL Server and Azure SQL Database, and either/both:
▪ Check database compatibility – identifies partially supported or unsupported features which may block migration. There will be recommendations.
▪ Check feature parity – recommendations, different approached, and mitigating steps.
- Click Next and provide connection details to SQL Server.
- Select databases, click Add and Start Assessment.
163
How do you migrate a sample schema?
- In the DMA, click +New, Project Type, Migration.
- Add a project name, SQL Server, and Azure SQL Database
- Select “Schema only” in “Migration scope”.
- Click Create, and enter your SQL Server details.
- Click Next, then enter your Azure SQL details.
- Click Connect, and select the relevant database.
- Click Next, and specify the schema objects to be deployed. By default, all of them are selected.
- Click ”Generate SQL script” then “Deploy schema”.
164
How do you register the Microsoft.DataMigration resource provider?
- In the Azure portal, go to Subscriptions.
- In the Azure Database Migration Service subscription, click on Resource providers.
- Find “Microsoft.DataMigration” and Register it.
165
How do you create an instance of Azure Database Migration Service?
- In the Azure portal, go to this service and click Create, and select:
▪ Subscription, Resource Group, Name
▪ Location, ”Azure” as Service mode, and
▪ Pricing tier (Standard is free)
- On ”Create Migration Service”, select an existing VN or create a new one.
166
What are the pricing tiers for Azure Database Migration Serivce?
- Standard is free for offline (one-off) migration only.
- Premium is about $1 for 3 hours. Allows for online (continuous migration) and offline migrations, and faster speeds.
- You can have the 4 vCore Premium DMS free for 6 months. You can use it for a total of 1 year, and create 2 DMS services per subscription.
167
How do you create a migration project?
- In the Azure portal, go to “Azure Database Migration Services”, select the relevant instance, and select ”New Migration Project”.
- Add a project name, SQL Server, Azure SQL Database, and Data migration.
- Click on “Create and run activity”.
168
What are the other options for migration?
- Bulk Copy Program (BCP) can be used from on-prem or a VM to Azure SQL
- BULK INSERT - loading data from Azure Blob storage
- SSIS packages - ETL
- Spark or Azure Data Factory
169
What are the steps for an offline migration strategy?
1. Check for blocking issues
2. Migrate sample schema if necessary
3. Register the Microsoft.DataMigration resource provider
4. Create an instance of the Azure Database Migration Service
5. Create a migration project
6. Enter source settings
7. Enter target settings
8. Run the migration
170
How should you validate a migration?
1. Create validation tests using SQL queries against source and target DBs
2. Create performance tests and compare between source and target DBs
3. Check applications (e.g. references to target DB)
4. Investigate what effect the DB compatibility level may have had
5. Check for missing features.
171
What are the effects of a change in DB compatibility level?
- Azure SQL DB and Azure SQL MI will always use the latest version
- Are existing queries using the best plan under the new compatibility level?
- Is there query performance regression? Force the last known good plan if there is
- Is there parameter sniffing? Do stored procedures need to be recompiled?
- Look for features which work better in the source DB but not the target DB
- Look for missing indexes
- Look for new features in the latest DB compatibility level.
172
What is SQL Data Sync?
- Allows you to synchronize data across multiple databases
- Tables need to have a primary key, which cannot be changed (rows can be deleted/recreated instead)
- Doesn't work with Azure SQL MI
- Azure SQL DB must be the Hub, but VMs can be members
173
What is a Hub DB?
- An Azure SQL DB
- One is defined
174
What is the Sync Metadata Database?
- Contains the metadata and log for Data Synch.
- Azure SQL DB in the same region as the Hub DB
- It should be empty. Data Synch creates tables and runs a frequent workload
175
What are Member DBs in Data Sync?
- Either Azure SQL or on-prem/VM (not MI)
- You must install a local sync agent
176
What are the properties of the synch group?
- Sync Schema data shows what data is synchronized
- Sync Direction can be both ways or only one direction
- Sync Interval is how often synchronization happens
- Conflict Resolution Policy is "Hub wins" or "Member wins", but whichever member syncs first wins
177
When should you use SQL Data Sync?
- Hybrid Data Synchronization
- Distributed Applications, including Globally Distributed Applications
178
What are the steps to setup a database and Sync Metadata Database for SQL Data Sync?
1. Go to Azure portal, SQL Databases
2. Go to the Hub database
3. Go to "Sync to other databases"
4. Go to "New Synch Group"
5. Add Sync members
179
How do you add Azure SQL DB as a sync member?
1. Open the sync group - Database
2. Click on "Add an Azure Database"
3. Select the Sync Member Name, subscription, Azure SQL Server and DB, Sync directions, existing username and password for the member db, and use private link
180
How do you add an on-prem SQL Server database as a sync member?
1. Select "Choose the Synch Agent Gateway"
2. Select "Existing Agent" or "Create new agent"
- If new, download the Azure SQL Data Sync Agent, enter an Agent Name, select Create and Generate key, and copy the key to the clipboard
3. On the on-prem SQL Server, run the Client Sync Agent app
4. Click "Submit Agent Key"
5. In the "Sync Metadata Database Configuration", enter credentials for the metadata db server. If automatically created, this will be the same server as the hub db.
6. Click Register.
7. Select either SQL Server or Windows authentication.
8. Click test connection and Save.
9. In the portal, in "Configure On-Premises page", select "Select the Database", and provide a name for the new sync member and the sync directions.
181
How do you validate that SQL Data Sync is working?
Go to the Sync Group page, tables, and click refresh schema. It may take a while for data to be refreshed.
182
How do you migrate from SQL on Azure VM to Azure SQL DB or Azure SQL MI?
Exactly the same way as migrating from SQL on Prem
183
How do you migrate from Azure SQL DB or Azure SQL MI?
- Export the data
- Use SSMS Data tier applications (DAC)
- Use SQLPackage CLI, which will also create a backpac
- Use the Azure Portal
- Use Powershell
- Azure CLI
184
How do you export data to migrate from Azure SQL DB or Azure SQL MI?
1. Right click on the DB in SSMS. Tasks > Export Data.
2. Select source and destination.
3. This will copy data, but not objects
185
How do you use SSMS Data-tier applications to migrate data from Azure SQL DB or Azure SQL MI?
1. Right click the source DB and got to Tasks > Export Data-tier application. This will create a bacpac, an archive containing schema and data.
2. On the destination server in SSMS, right click the word "Databases" and select tasks > import data-tier application.
186
How do you use SQL Package CLI utility to migrate data from Azure SQL DB or Azure SQL MI?
1. Open a command prompt and run
cd C:\Program Files\Microsoft SQL Server\160\DAC\bin
2. Execute this command sqlpackage.exe /a:Export /SourceServerName:servername.database.windows.net /SourceDatabaseName:dbname /SourceUser:username /SourcePassword:password /TargetFile:C:\Users\backup.bacpac
3. To upload it, assuming you're still in the command prompt, run sqlpackage.exe /a:Import /TargetServerName:MIname.appname.database.windows.net /TargetDatabaseName:dbname /TargetUser:username /TargetPassword:password /SourceFile:C:\Users\backup.bacpac
187
How do you use the Azure Portal to migrate data from Azure SQL DB or Azure SQL MI?
- Azure SQL MI is not supported
- Uses bacpac
- Go to the DB, and click on export database
- Select a previously created standard storage
- You can check the export status by going to the Azure SQL Server (not the DB) and go to Import/Export history
- When complete, you can use this for importing into MI using SSMS, or creating a new Azure SQL DB using the portal
188
How do you use Powershell to export data from Azure SQL DB or Azure SQL MI for migration?
Use the New-AzSqlDatabaseExport cmdlet
- $exportRequest = New-AzSqlDatabaseExport -ResourceGroupName
$ResourceGroupName -ServerName $ServerName '
- -DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -
StorageKey $StorageKey -StorageUri $BacpacUri `
- -AdministratorLogin $creds.UserName -AdministratorLoginPassword
$creds.Password
189
How do you use PowerShell to import data from Azure SQL DB or Azure SQL MI for migration?
Use the New-AzSqlDatabaseImport cmdlet
- $importRequest = New-AzSqlDatabaseImport -ResourceGroupName
"" `
- -ServerName "" -DatabaseName "" `
- -DatabaseMaxSizeBytes "" -StorageKeyType
"StorageAccessKey" `
-StorageKey $(Get-AzStorageAccountKey `
- -ResourceGroupName "" -
StorageAccountName "").Value[0] `
- -StorageUri
"https://myStorageAccount.blob.core.windows.net/importsample/sample.
bacpac" `
- -Edition "Standard" -ServiceObjectiveName "P6" `
- -AdministratorLogin "" `
- -AdministratorLoginPassword $(ConvertTo-SecureString -String
"" -AsPlainText -Force)
190
How do you use Azure CLI to import data from Azure SQL DB or Azure SQL MI for migration?
Use az sql db import
# get the storage account key
az storage account keys list --resource-group "" --accountname ""
az sql db import --resource-group "" --server "" --name "" `
--storage-key-type "StorageAccessKey" --storage-key "" `
--storage-uri "https://myStorageAccount.blob.core.windows.net/importsample/sample.bacpac" `
-u "" -p ""
191
When is it useful to move one or more databases from one Azure SQL MI to another?
- You want to manage database size and performance
- Balance workloads and resources across several Managed Instances
- Copy/move databases between dev, test, and prod environments.
- Combine databases from multiple instances
192
How do you copy a Managed Instance?
1. Go to the Managed Instance.
2. Go to Data managaedment > Databases.
3. Select the database(s), and click Move or Copy at the top.
4. In the source details pane, select the source database(s) and Managed Instance.
5. In the destination details pane, select the destination Managed Instance.
6. Click Review + Start, and then Start
193
Where do you view the status of the copy/move/
- It will say “Copy/Move in progress” in the “operations details” column
- You cancel it
- It will say “Copy/Move ready for completion” when the data is transferred. Click “ready for completion” and “complete” to complete
- If it takes more than 24 hours, the copy/move is cancelled and the destination DB is dropped
