Brainscape's Knowledge GenomeTM

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


See full index

Azure > Azure SQL Type Database > Flashcards

Azure SQL Type Database Flashcards

1
Q

What are the types of Azure SQL Databases (MS)

A

SQL on a Virtual Machine

Azure SQL Managed Instance
– General purpose
– Buisness critical

Azure SQL Database
– General purpose
– Buisness critical
– Elastic pool

Azure SQL Database (hyperscale)
– General purpose
– Buisness critical
– Elastic pool

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

When should I use Azure SQL Managed Instance?

A

Most compatible, suitable for lift and shift.

Azure SQL Managed Instance is an ideal choice when you need a fully managed SQL Server instance in the cloud that supports the following scenarios:

Easy Lift-and-Shift for On-Premises SQL Server Workloads: SQL Managed Instance offers near-full compatibility with on-premises SQL Server, making it an excellent option for migrating existing SQL Server workloads with minimal application or database refactoring.

Extended SQL Server Features: SQL Managed Instance includes features not available in Azure SQL Database, such as SQL Agent, cross-database queries, and Service Broker, which are useful for applications with dependencies on these features.

High Availability and Built-In Disaster Recovery: SQL Managed Instance supports built-in high availability and offers options for automated backups, point-in-time restore, and geo-redundancy, making it well-suited for applications with strict recovery time objectives (RTO) and recovery point objectives (RPO).

Network Isolation and Enhanced Security: If you need to isolate your database within a private network and integrate it with on-premises resources, Managed Instance can be deployed within a virtual network (VNet), enabling private IP access and additional security configurations.

Compliance with Enterprise Requirements: Managed Instance supports compliance requirements for businesses needing features like Azure Active Directory (Entra ID) authentication, Transparent Data Encryption (TDE), and Advanced Threat Protection (ATP).

Scaling for Enterprise Workloads: Managed Instance provides scaling options in terms of both compute and storage, which is beneficial for enterprise applications with demanding performance and storage requirements.

Hybrid Cloud and Application Modernization: If you’re looking to modernize a portion of an on-premises SQL Server setup while keeping certain components in the cloud, Managed Instance allows for seamless integration and hybrid setups with features like Linked Servers and cross-premises connectivity.

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

When should I use Azure SQL Database elastic pools?

A

Best fit for modern cloud applications where you can change the application code to account for not having full SQL server capabilities.

Also when having several databases thet wnat to spread the available resources across the databases because several databases are not all taking resources at once.

Azure SQL Database elastic pools are a great choice when you have multiple databases that:

Have variable and unpredictable usage patterns: If your databases have varying or unpredictable demand, elastic pools allow you to balance the performance cost across multiple databases, making it cost-effective.

Need resource sharing and cost savings: Elastic pools let multiple databases share a set of resources (DTUs or vCores) at a fixed price. This can be more economical than provisioning resources for each database individually, especially if some databases have low or intermittent usage.

Have databases with a single-tenant or multi-tenant SaaS model: In multi-tenant SaaS scenarios, where each tenant might have its database, elastic pools allow for predictable costs while supporting varying tenant usage.

Require scaling for numerous databases: Elastic pools allow you to manage and scale a group of databases easily without scaling each database individually. This can simplify operations if you have multiple databases with similar performance needs.

Operate under a fixed budget: By placing multiple databases into an elastic pool, you can control costs while providing enough resources to handle varying workload demands across the databases.

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

When is it best to use Azure SQL Virtual Machines?

A

Best fit when you need operating system level access and the most compatability.

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

Who is responsible for patching the operating system when using Azure SQL on virtual machines?

A

You are responsible for patching.

When using Azure SQL on virtual machines (SQL Server on Azure VMs), the customer is responsible for patching the operating system. Azure provides platform-managed services to assist with SQL Server updates and maintenance, but the underlying VM and OS are managed by the customer, including OS patches and updates.

In contrast, Azure SQL Database (a fully managed PaaS offering) handles both OS and database patching as part of the service, reducing the customer’s maintenance burden.

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

Who is responsible for patching when using Azure SQL Database?

A

When using Azure SQL Database, Microsoft is responsible for patching the operating system, database engine, and all underlying infrastructure. This is part of the fully managed platform-as-a-service (PaaS) model, which includes regular updates, patches, and backups to ensure availability, security, and compliance.

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

When using Azure SQL on virtual machines, what VM type should you be considering?

A

Consider a memory-optimized virtual machine.

Storage-optimized virtual machine.

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

How can you quickly back up the VM running your SQL on a virtual machine?

A

To quickly back up a VM running SQL Server on a virtual machine in Azure, you can follow these steps:

  1. Use Azure Backup for VM-level Backup
    Azure Backup can perform a backup of the entire VM, including the OS and data disks. This backup is typically file-system consistent (not application-consistent) by default, meaning it’s not aware of the SQL Server state.

Steps to back up a VM with Azure Backup:

Go to the Azure portal.
Navigate to Backup Center or search for Backup.
Create a Recovery Services Vault if you don’t have one.
In the vault, select Backup and then choose Azure Virtual Machine as the backup item.
Select the VM running SQL Server and configure the backup schedule.
Run an immediate backup or let the backup run on the schedule you’ve set.
Important Note: This backup will capture the VM at the disk level but is not SQL Server-aware (it’s file-system consistent).

  1. Use SQL Server Backup for Application-Consistent Backup
    If you need application-consistent backups, you should also back up the SQL Server databases while the VM is running. This ensures that the SQL Server database files (including transaction logs) are in a consistent state and recoverable.

Use SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL) commands to back up the databases. You can back up the database files using BACKUP DATABASE and include transaction logs (BACKUP LOG).
You can schedule these backups or trigger them manually as needed.
Example:

BACKUP DATABASE [YourDatabase] TO DISK = N’C:\Backup\YourDatabase.bak’
WITH INIT;

BACKUP LOG [YourDatabase] TO DISK = N’C:\Backup\YourDatabase_log.trn’;
3. Use Azure Backup with SQL Server Integration (Application-Consistent Backup)
For more seamless integration, you can enable SQL Server-aware backups through Azure Backup. This provides an application-consistent backup that takes SQL Server into account (ensuring that transaction logs and open transactions are handled).

In Azure Backup, enable SQL Server protection in the Recovery Services Vault.
Select the VM running SQL Server and configure SQL Server backup options.
Azure Backup will back up the SQL Server databases in an application-consistent manner, ensuring that the SQL Server transaction logs and database states are consistent.
4. Use VM Snapshot for Quick Backups (Not SQL Consistent)
A snapshot of the VM disk is another option for a quick backup, but keep in mind that snapshots are not application-consistent. It’s a quick way to back up the VM, but to ensure SQL Server data consistency, you need to ensure that SQL Server has flushed transaction logs before taking the snapshot.

To take a snapshot:

In the Azure portal, navigate to your VM.
Go to the Disks section and select the disk you want to back up.
Click on Create snapshot.
Configure the snapshot and create it.
This is a quick method, but again, it’s not suitable for SQL Server database consistency unless done in conjunction with SQL Server-aware backup methods.

Key Points:
Azure Backup provides a simple method for VM-level backup but is not application-consistent by default for SQL Server.
SQL Server native backups (via SSMS or T-SQL) provide application-consistent backups for SQL databases.
You can combine SQL Server backup with Azure Backup for an application-consistent VM backup.
For mission-critical SQL Server applications, it’s important to ensure the backup is both file-system consistent and SQL Server application-consistent, typically achieved through SQL Server-aware backups.

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

What is Azure SQL Database latency when data stays in the same region as going to a secondary?

A

All data passing over the network in a region is less than 2ms.

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

Explain how availability is achieved for the general-purpose tier for Azure SQL Database and Azure SQL Instance?

A

Data from the primary is stored in Azure Blob Storage, and there are three replicas; if the primary node compute fails, there are spare nodes kept available in the regional and one will be selected and pointed to the storage and booted to replace the failed primary

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

Explain how availability is achieved for the business-critical tier for Azure SQL Database and Azure SQL Instance.

A

The business-critical data is stored on the node’s local disk, with several secondary nodes; data is sync replication and acknowledged from at least one node. This is called SQL always on

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

Where is data stored when using the business-critical tier for Azure SQL Database and Azure SQL Instance?

A

Node local storage

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

Where is data stored when using the general-purpose tier for Azure SQL Database and Azure SQL Instance?

A

Azure Blog Storage

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

Explain always-on the context of Azure SQL Database and Azure SQL Instance?

A

In always-on, data is replicated between primary and secondary replicas; data can be synced or async.

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

What the the tiers available in Azure SQL?

A

General-purpose
Buisness-critical
Hyperscale

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

When would I use the General-Purpose Tier?

A
  1. Most general-purpose workloads
  2. moderate performance needs.
  3. Budget-friendly: Provides cost-effective options.
  4. Storage: Uses Azure Premium SSD for storage, with up to 8 TB of database size.
  5. Availability: Provides high availability using the Always On availability groups with automated backups and replication.
  6. Sutable for: Applications with standard OLTP workloads.
  7. Sutable for: Moderate transaction rates and throughput.
  8. Sutable for: Applications that need balanced performance at a lower cost.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

How is High-Availability achieved for the General-Purpose Tier for Azure SQL Database?

A

Availability: Provides high availability using the Always On availability groups with automated backups and replication.

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

What workload is suitable for the General-Purpose Tier for Azure SQL Database?

A
  1. Most general-purpose workloads
  2. Moderate performance needs.
  3. Budget-friendly: Provides cost-effective options.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What workload is suitable for the General-Purpose Tier for Azure SQL Instance?

A
  1. Most general-purpose workloads
  2. Moderate performance needs.
  3. Budget-friendly: Provides cost-effective options.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What workload is suitable for the General-Purpose Tier for Azure SQL Instance?

A
  1. Applications with standard OLTP workloads.
  2. Moderate transaction rates and throughput.
  3. Applications that need balanced performance at a lower
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What workload is suitable for the General-Purpose Tier for Azure SQL Database?

A
  1. Applications with standard OLTP workloads.
  2. Moderate transaction rates and throughput.
  3. Applications that need balanced performance at a lower
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What are the purchasing models available for both Azure SQL Database and Instance?

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

For the purchasing model for both Azure SQL Database and Instance, what is the MS recommended model?

A

We recommend the vCore-based model because it allows you to independently select compute and storage resources. The DTU-based model is a bundled compute, storage, and I/O resource measure.

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

What SSD types are used with the General-Purpose Tier for Azure SQL Database?

A

Azure Premium SSD for storage (Blob Storage)

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

What SSD types are used with the General-Purpose Tier for Azure SQL Instance?

A

Azure Premium SSD for storage (Blob Storage)

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

What SSD types are used with the Buisness-Critical Tier for Azure SQL Database?

A

Uses local SSDs to offer low-latency reads and writes.

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

What SSD types are used with the Buisness-Critical Tier for Azure SQL Instance?

A

Uses local SSDs to offer low-latency reads and writes.

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

How is High-Availability acheived for the Business-Critical Tier for Azure SQL Database?

A

High availability: Three replicas in the primary region ensure higher availability. In this case, three replicas are in the same area with the same data, and data is synced using the sync process.

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

How is High-Availability acheived for the Business-Critical Tier for Azure SQL Instance?

A

High availability: Three replicas in the primary region ensure higher availability. In this case, three replicas are in the same area with the same data, and data is synced using the sync process.

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

For Azure SQL Database, explain backups.

A
  1. Azure SQL Database automatically creates
  2. Full Backup: Taken once a week.
  3. Differential Backup: Taken every 12 to 24 hours.
  4. Transaction Log Backup: Taken every 5 to 10 minutes.
  5. Stored in geo-redundant storage (GRS) by default, ensuring high availability and disaster recovery capabilities.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

For Azure SQL Database, what are the backup retention periods?

A
  1. Basic Tier: Backups are retained for seven days.
  2. Standard and Premium Tiers: Backups are retained for 35 days.
  3. Long-Term Retention (LTR): You can configure long-term retention for up to 10 years if needed, by moving backups to Azure Blob Storage.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

For Azure SQL Database, do you select the retention periods?

A
  1. Basic Tier: Backups are retained for 7 days.
  2. Standard and Premium Tiers: Backups are retained for 35 days by default.
  3. Custom retention period up to 10 year
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Can I restore the Azure SQL Database to any point in time? If so, please explain.

A

You can restore the database to any point in time within the retention period. This feature helps recover from accidental data changes or corruption.

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

For Azure SQL Instance, explain backups?

A
  1. Azure SQL Instance automatically creates
  2. Full Backup: Taken once a week.
  3. Differential Backup: Taken every 12 to 24 hours.
  4. Transaction Log Backup: Taken every 5 to 10 minutes.
  5. Stored in geo-redundant storage (GRS) by default, ensuring high availability and disaster recovery capabilities.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

For Azure SQL Instance, explain backup retention?

A
  1. Automated backups are retained for 7 to 35 days, depending on your configuration and service tier.

2.Long-Term Retention (LTR): Similar to Azure SQL Database, you can configure long-term retention for up to 10 years.

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

For Azure SQL Database, what pricing tiers are available?

A
  1. vCore: General Purpose
  2. vCore: Business Critical
  3. vCore: Hyperscale
  4. DTU: Basic
  5. DTU: Standard
  6. DTU: Premium
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Does Azure SQL Managed Instance support SQL Server Integration Services?

A

Yes, SQL Server Integration Services (SSIS) is supported

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

Does Azure SQL Database support SQL Server Integration Services?

A

Azure SQL Database does not support SQL Server Integration Services (SSIS) directly. SSIS is not natively hosted on Azure SQL Database, which means you cannot run SSIS packages directly within Azure SQL Database itself. However, there are alternative approaches to using SSIS with Azure SQL Database:

  1. Azure Data Factory: You can use Azure Data Factory (ADF) with the SSIS integration runtime to run SSIS packages in the cloud. ADF provides a managed SSIS service that enables you to host, execute, and manage SSIS packages, allowing you to connect to Azure SQL Database as part of your ETL (Extract, Transform, Load) workflows.
  2. On-premises SSIS: If you have SSIS running on-premises, you can still connect to Azure SQL Database as a data source or destination by configuring the connection in your SSIS packages to point to your Azure SQL Database instance.
  3. SQL Managed Instance: If you need native support for SSIS, you might consider Azure SQL Managed Instance, which offers a broader range of SQL Server features, including the ability to host SQL Agent jobs to run SSIS packages.

In short, while Azure SQL Database does not natively support SSIS, you can use Azure Data Factory or host SSIS packages on-premises or in a managed instance to interact with Azure SQL Database.

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

Does Azure SQL Instance support Cross Database transactions?

A

Yes, Azure SQL Managed Instance supports cross-database transactions. This feature allows you to perform operations across multiple databases within the same managed instance using traditional Distributed Transaction Coordinator (DTC) support. This capability is useful for complex applications that require transactional consistency across several databases.

However, for cross-instance or cross-server database transactions (e.g., between different Azure SQL Managed Instances or an on-premises SQL Server and Azure SQL Managed Instance), you would need to configure additional components, such as Azure SQL Database elastic transactions, or use alternative methods, as DTC is only supported within the boundaries of a single managed instance.

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

Does Azure SQL Instance support Linked Services?

A

Yes, Azure SQL Managed Instance supports linked servers. Linked servers in Azure SQL Managed Instance allow you to connect to and query external data sources directly from the SQL Managed Instance, just as you would in an on-premises SQL Server.

With linked servers, Azure SQL Managed Instance can connect to other SQL Servers, Azure SQL Databases, or even non-SQL data sources, enabling distributed queries and data integration across different environments.

For setting up linked servers in Azure SQL Managed Instance, you typically need:

Credentials for authentication.
Network configurations that permit connectivity to the external source.
Transact-SQL (T-SQL) configuration steps to define and manage the linked server settings.
This feature makes Azure SQL Managed Instance a flexible choice for hybrid and multi-cloud database scenarios where integration with external databases is required.

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

Does Azure SQL Instance support CLR?

A

Yes, Azure SQL Managed Instance supports the CLR (Common Language Runtime) integration. This feature allows you to create user-defined functions, stored procedures, triggers, and aggregates in managed code, such as C# or VB.NET, which can then be executed within SQL Managed Instance. It’s especially useful for more complex logic and custom functions that may be difficult to achieve with T-SQL alone.

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

Does Azure SQL Instance support Service Broker?

A

No

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

Does Azure SQL Instance support SQL Server Replication?

A

No, Azure SQL Managed Instance does not support Service Broker. While Service Broker is available in SQL Server on-premises for managing asynchronous messaging and processing, this feature is not supported in Azure SQL Managed Instance or Azure SQL Database.

For similar functionality, you may consider using alternatives like Azure Service Bus or Azure Queue Storage, which provide reliable messaging and queueing capabilities suitable for cloud applications

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

Does Azure SQL Instance support Database Mail?

A

No

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

Does Azure SQL Instance support Stretch Database?

A

No

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

Does Azure SQL Instance support Data Quality Service?

A

No

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

What is the upper limit to retain automatic backups for Azure SQL Database and Azure SQL Instance?

A

10year

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

How are backups handled in Azure SQL Instance?

A
  1. Backups are automatic
  2. Full weekly backup
  3. Diff backups every 12 - 24 hrs
  4. Transaction log backups every 5 - 10 min
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

How long are backups retained in Azure SQL Instance?

A
  1. Default retention is 7 to 35 days
  2. Long Term Retention up to 10 years
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

Explain how to recover a recent backup in Azure SQL Instance?

A
  1. Use point-in-time restore
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

How are backups handled in Azure SQL Instance?

A
  1. Backups are automatic
  2. Full weekly backup
  3. Diff backups every 12 - 24 hrs
  4. Transaction log backups every 5 - 10 min
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

How long are backups retained in Azure SQL Instance?

A
  1. Default retention is 7 to 35 days
  2. Long Term Retention up to 10 years
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

Explain how to recover a recent backup in Azure SQL Instance?

A

Use point-in-time restore

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

Do I manually tune for performance for the Azure SQL Instance and Database performance?

A

No it is automatic performance tuning

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

Should I be concerned about the database version for the Azure SQL Instance?

A

No, the Azure SQL Instance is updated by MS to the latest release to fix issues.
1. Latest version
2. Version less

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

Should I be concerned about the database version of the Azure SQL Database?

A

No, the Azure SQL Database is updated by MS to the latest release to fix issues.
1. Latest version
2. Version less

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

I wnat to use the Azure Hybrid Benefit model with Azure SQL Database and Instance; what purchasing model should I use?

A

Use the vCore model

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

Can I use Hybrid Benefits with Azure SQL?

A

Yes

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

You have a web application that uses Azure SQL Database as its backend. The database contains sensitive customer data that must be protected from unauthorized access. It would be best to recommend a solution implementing row-level security (RLS) in the database. The solution must meet the following requirements: * Allow users to access only the data that is relevant to them. * Ensure that users cannot access data that is not relevant to them. * Minimize the amount of administration required to manage the security. What should you include in the recommendation?

A

RLS is a feature in Azure SQL Database that restricts data access for specific rows in a table, based on the characteristics of the user executing the query. RLS ensures that users can only view and manipulate rows of data they are authorized to access, which aligns perfectly with the requirement of allowing users to access only the relevant data.

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

Explain how RLS works in Azure SQL Database?

A
  1. Security Policies: You define a security policy that enforces the restrictions for each query executed by a user. This policy can include a predicate that is evaluated for each query.
  2. Filter Predicate: A function returns a boolean value to decide which rows the user can access. This predicate is applied every time a query runs on a table with RLS enabled.
  3. Block Unauthorized Access: Users will automatically be blocked from accessing rows that do not match the filter predicate.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
61
Q

Explain how RLS is used in the Azure SQL Database?

A

Query data is filtered based on user, so the users only return a subset of the database data.

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

Explain an Azure SQL Instance SQL Server-level Firewall Rule?

A

Controls access to the entire network ranges or specific IP addresses across all databases under the server. This is beneficial for managing multiple applications or clients accessing different databases but sharing the same security policies.

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

Using SQL Server-level Firewall Rule, can you specify a database to an IP?

A

No, SQL Server-level firewall rules in Azure SQL cannot be used to specify access to a single database for a particular IP address. These rules operate at the server level, meaning they control access for all databases under the server. Once an IP address is allowed at the server level, that IP can access any database on that server, provided the user has the necessary authentication and permissions.

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

When using Azure SQL Service Instance SQL Server-level Firewall Rules, what is the scope of the rules?

A

All databases belong to the server.

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

I have three databases running on an Azure SQL server instance; how can I ensure that IP1 for DB1 and IP2 for DB2, and IP3 for DB3?

A

Use Database-level Firewall Rules

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

What is Always-Encrypted Client-Side Encryption?

A

The encryption and decryption process happens on the client side. Sensitive data is never visible to the database or database administrators in plaintext.

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

What is Always-Encrypted Column-Level Encryption Encryption?

A

You can encrypt specific sensitive columns (e.g., credit card numbers, social security numbers) in a table while leaving the rest of the data unencrypted.

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

What is Always-Encrypted Separation of Roles ?

A

Always Encrypted helps to separate the roles between the application developers or administrators and the database administrators. Database administrators have access to the database but do not have access to the encryption keys and thus cannot view sensitive data in plaintext.

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

What is Always-Encrypted Encryption Types?

A

Deterministic Encryption: Always produces the same encrypted value for the same plaintext value. This allows equality searches but may reveal patterns.
Randomized Encryption: Produces different encrypted values for the same plaintext value, providing better security but does not support equality searches.

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

What is Always-Encrypted Transparent to Applications Encryption?

A

Applications can interact with the data without needing to be aware of the encryption process. The client driver (e.g., ADO.NET) transparently handles encryption and decryption when configured with the necessary keys.

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

What is Always-Encrypted Transparent Keys and Key Management?

A

There are two key types used:
Column Encryption Key (CEK): Encrypts the actual data.
Column Master Key (CMK): Encrypts the CEK. The CMK is stored in an external key store (e.g., Azure Key Vault).

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

When using an auto-failover group, can you have a data loss?

A

Yes, 5 seconds

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

When using an auto-failover group, can you use the secondary for both read and write?

A

No, only read.

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

When using an auto-failover group, should the secondary be in a separate resource group?

A

No, its a requirment to have both primary and secondary in same resource group.

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

When using an auto-failover group, should you locate both primary and secondary in the same region?

A

No, separate regions.

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

Are server-side transaction supported for Azure SQL Managed Instance?

A

Yes

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

Can you have a private vNET and deploy Azure SQL Database?

A

No, deploying Azure SQL is supported, but you could have a private or service endpoint to the SQL service or the database.

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

Is Azure SQL Managed Instance a single-tenant environment?

A

Yes, Azure SQL Managed is a single-tenant environment

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

Is Azure SQL Database a single-tenant environment?

A

No, it is a multi-tenant environment

80
Q

Are service-side transactions supported in Azure SQL Database?

A

No

81
Q

Are service-side transactions supported in Azure SQL Managed Instance?

A

Yes

82
Q

For Azure Managed Instance and Azure SQL Database, explain service-side dynamic masking?

A

Dynamic Data Masking automatically masks the data when it is queried, but the underlying data in the database remains unchanged. This means users with the appropriate permissions can still see and work with the actual data, while others only see masked data based on predefined rules.

83
Q

For Azure Managed Instance, how could you ensure compliance so that when user A queries the database, credit card nu members are not returned masked, but for user B, they are returned as numbers?

A

Use dynamic masking to assign user A masked data and user B unmasked.

84
Q

When using dynamic masking of data, is it supported for Azure SQL Managed instance and Azure SQL database?

A

Yes, both support dynamic masking.

85
Q

What makes up the DR for Azure DQL Database?

A
  1. Active Geo-Replication
  2. Auto-Failover Groups
  3. Point-in-Time Restore:
  4. Geo-Restore
  5. Zone Redundant Configuration
  6. Geo-redundant backups
86
Q

How can you provide read-only replicas of an Azure SQL Database DB in a separate region?

A

Create secondary read replicas.

87
Q

Are failover groups used with Active Geo-replication?

A

No, Failover Groups and Active Geo-Replication are separate features in Azure SQL Database. They serve similar but distinct purposes. While they provide disaster recovery and high availability across regions, they are typically used independently, not together.

88
Q

Are they using Azure SQL Database DB with a secondary read replica in a separate region, can you manually failover?

A

Yes

89
Q

For Geo-Replication, when you manually failover to the secondary database, is the DNS used to connect to the database the same?

A

No, your apps will have to consider DNS name change in failover.

90
Q

I have an Azure SQL Database with a secondary for DR; how can I configure the system for automatic failover?

A

Auto-Failover Groups:

Auto-Failover Groups provide automatic failover capabilities for geo-replicated databases without needing manual intervention.

It supports automatic failover in the event of a regional outage, ensuring continuous availability.

This feature enables seamless management of multiple databases and maintains read-write access to the database after the failover event.

With Auto-Failover Groups, you can configure policies for automatic or manual failover between the primary and secondary regions.

91
Q

Explain Azure SQL Database Point-in-Time Restore?

A
  1. Azure SQL Database automatically performs continuous backups for databases, allowing you to restore the database to any point within the backup retention period (up to 35 days for Standard and Premium tiers).
  2. Point-in-Time Restore is valuable for recovering from accidental data loss, corruption, or malicious activity without relying on regional failover.
  3. You can restore databases within the same region or even across regions using geo-redundant backups.
92
Q

Explain Azure SQL Database Geo-Replication?

A
  1. Active Geo-Replication allows you to create up to four readable secondary replicas of your database in different Azure regions.
  2. These replicas are synchronized asynchronously, meaning they can serve as failover databases if the primary database becomes unavailable.
  3. In the event of a failure, you can manually fail over to a secondary replica to restore service with minimal data loss (dependent on replication lag).
  4. The secondary replicas are also available for read-only queries, improving availability and performance in global applications.
93
Q

Explain Azure SQL Database Auto-Failover Groups?

A
  1. Auto-Failover Groups provide automatic failover capabilities for geo-replicated databases without needing manual intervention.
  2. It supports automatic failover in the event of a regional outage, ensuring continuous availability.
  3. This feature enables seamless management of multiple databases and maintains read-write access to the database after the failover event.
  4. With Auto-Failover Groups, you can configure policies for automatic or manual failover between the primary and secondary regions.
94
Q

Can you use auto-failover groups with several DB’s

A

Yes

95
Q

Explain Azure SQL Database Point-in-Time Restore?

A

Azure SQL Database automatically performs continuous backups for databases, allowing you to restore the database to any point within the backup retention period (up to 35 days for Standard and Premium tiers).

Point-in-Time Restore is valuable for recovering from accidental data loss, corruption, or malicious activity without relying on regional failover.

You can restore databases within the same region or even across regions using geo-redundant backups.

96
Q

Explain Azure SQL Database Geo-Restore?

A

Geo-Restore allows you to recover your database from a geo-replicated backup to any Azure region in the event of a major regional outage or disaster.

This feature ensures that even if your primary region is inaccessible, you can restore your database from backups in a secondary region.

Geo-restore uses geo-redundant backups stored in different regions, providing protection against region-wide failures.

The recovery time is longer compared to Active Geo-Replication or Failover Groups, but it provides a last-resort recovery option.

97
Q

Explain Azure SQL Database Zone Redundant Configuration?

A

For Premium and Business Critical tiers, Azure SQL Database supports Zone Redundant Databases, which replicate databases across different availability zones within a region.

This ensures that the database remains available through replicas in other zones even if a failure occurs in one availability zone.

Zone redundancy enhances high availability and protects against localized failures within a region.

98
Q

Explain Azure SQL Database Zone Geo-Redundant Backups?

A

By default, geo-redundant backups are enabled for Azure SQL Database, meaning backups are stored in both the primary and secondary regions.

These backups ensure that even if your primary region is down, you can restore your database using backups stored in another region.

99
Q

How do you perform an Azure SQL Database?

A

You do no, performance tuning is automatic

100
Q

Is performance tuning also available for Azure SQL-managed instances?

A

Yes

101
Q

You have an Azure SQL-managed instance, and the application team asked about recommending an easy way to store NoSQL with the least effort?

A

Azure SQL-managed instance is multi-model; you can store and work with NoSQL data.

102
Q

You have an Azure SQL Database database, and the application team asked about recommending an easy way to store NoSQL with the least effort?

A

Azure SQL-managed database is multi-model; you can store and work with NoSQL data.

103
Q

For Azure SQL Database, what is the SLA?

A

99.99% (4 min in a month) (Out of the box by default)

104
Q

For Azure SQL Database, what is the RPO?

A

0 (Out of the box by default)

105
Q

For Azure SQL Database, what is the RTO?

A

0 (Out of the box by default)

106
Q

What type of protection do I require from corruption when using Azure SQL Database?

A

Automated backups

107
Q

What is the target use case for General Purpose tier in Azure SQL Database?

A

Standard applications with balanced cost and performance

108
Q

Does the General Purpose tier in Azure SQL Database have compute and storage separation?

A

Yes, compute and storage are separated (remote storage)

109
Q

What is the IO performance like in General Purpose tier?

A

Moderate, using Azure Premium SSD-backed storage

110
Q

What is the max database size for General Purpose tier?

A

4 TB

111
Q

What kind of high availability (HA) does the General Purpose tier offer?

A

Zone-redundant with Always On availability groups using remote storage

112
Q

How does backup and restore work in the General Purpose tier?

A

Automated backups, geo-redundant, and supports point-in-time restore

113
Q

What is the failover time for the General Purpose tier?

A

30 seconds to several minutes

114
Q

What kind of storage does the General Purpose tier use?

A

Azure Premium SSD with remote storage

115
Q

How does the General Purpose tier handle scaling?

A

Moderate scaling capabilities

116
Q

What workloads is the General Purpose tier best for?

A

Standard workloads with balanced requirements

117
Q

Does the General Purpose tier offer read replicas?

A

No read replicas available

118
Q

What is the target use case for Business-Critical tier in Azure SQL Database?

A

Applications requiring high availability and low-latency IO operations

119
Q

Does the Business-Critical tier in Azure SQL Database have compute and storage separation?

A

No, local SSD-based storage

120
Q

What is the IO performance like in Business-Critical tier?

A

High, using local SSD with low-latency

121
Q

What is the max database size for Business-Critical tier?

A

4 TB

122
Q

What kind of high availability (HA) does the Business-Critical tier offer?

A

Zone-redundant using Always On availability groups with local SSD storage

123
Q

How does backup and restore work in the Business-Critical tier?

A

Automated backups, geo-redundant, and supports point-in-time restore

124
Q

What is the failover time for the Business-Critical tier?

A

3-10 seconds (fast failover with SSD)

125
Q

What kind of storage does the Business-Critical tier use?

A

Local SSD storage with fast local access

126
Q

How does the Business-Critical tier handle scaling?

A

Moderate scaling capabilities

127
Q

What workloads is the Business-Critical tier best for?

A

Mission-critical workloads needing low-latency and high availability

128
Q

How many read replicas are available in the Business-Critical tier?

A

Up to 4 read replicas

129
Q

What is the target use case for Hyperscale tier in Azure SQL Database?

A

Applications needing high scalability and large database size

130
Q

Does the Hyperscale tier in Azure SQL Database have compute and storage separation?

A

Yes, highly scalable storage and compute

131
Q

What is the IO performance like in Hyperscale tier?

A

Scalable based on workload demand

132
Q

What is the max database size for Hyperscale tier?

A

Up to 100 TB

133
Q

What kind of high availability (HA) does the Hyperscale tier offer?

A

Storage-level redundancy with highly distributed architecture

134
Q

How does backup and restore work in the Hyperscale tier?

A

Automated backups with fast snapshot-based restores

135
Q

What is the failover time for the Hyperscale tier?

A

Minutes, depending on scaling needs

136
Q

What kind of storage does the Hyperscale tier use?

A

Remote object storage, highly scalable

137
Q

How does the Hyperscale tier handle scaling?

A

Rapid horizontal scaling with no downtime

138
Q

What workloads is the Hyperscale tier best for?

A

Large-scale, rapidly growing, read-heavy workloads

139
Q

How many read replicas are available in the Hyperscale tier?

A

Unlimited replicas for read scaling

140
Q

I require a Azure SQL Database for 50TB of data and many read nodes, what service tier best suites these requirements?

A

Hyperscale tier as it has upto 100TB of storage

141
Q

For Azure SQL Database Hyperscale how many read replicas can i have?

A

Unlimited

142
Q

For Azure SQL Database Hyperscale how is IO performance managed, is it fixed, manual, scale on demand?

A

Scalable based on workload demand

143
Q

I have a web app that uses SQL, the web app is only used by 20 people during business hours and dose not use

A
144
Q

When using Azure SQL Database standard

A
145
Q

Has Azure SQL Database got partitioning and shading?

A

Yes, Azure SQL Database has both capabilities.

146
Q

What is an Azure SQL - Server Lever Firewall?

A

A server lever firewall is an IP firewall created at the single server level or the pool level and blocks connection from IP addresses with no permissions.

147
Q

What is an Azure SQL - DatabaseLever Firewall?

A

A database firewall rule is a rule to block access to the database.

148
Q

By default, does Azure SQL - Database Level Firewall block traffic?

A

No, by default, it opens and relies on the server-level rule to block traffic.

149
Q

Explain writing audit in Azure SQL server.

A

In Azure SQL, to securely write audit logs to a storage account behind a VNet and firewall, configure Azure SQL auditing to target a storage account restricted to specific networks. Set up firewall rules and VNet integration on the storage account to limit access, and use a managed identity for secure permissions. This approach enhances data protection by isolating audit logs within trusted network boundaries.

150
Q

Explain Azure SQL server Advanced Threat Protection for Azure SQL Database?

A

Advanced Threat Protection for Azure SQL Database detects anomalous activities indicating unusual and potentially harmful attempts to access or exploit databases. Advanced Threat Protection can identify Potential SQL injection, Access from unusual location or data center, Access from unfamiliar principal or potentially harmful application, and Brute force SQL credentials - see more details in Advanced Threat Protection alerts.

151
Q

Explain dynamic masking?

A

Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal effect on the application layer. It’s a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database isn’t changed.

For example, a service representative at a call center might identify a caller by confirming several characters of their email address, but the complete email address shouldn’t be revealed to the service representative. A masking rule can be defined that masks all the email address in the result set of any query. As another example, an appropriate data mask can be defined to protect personal data, so that a developer can query production environments for troubleshooting purposes without violating compliance regulations.

152
Q

Can you use Azure Policies with Azure SQL Instance and Database to enforce regulatory compliance?

A

Yes, Azure provides a number of out-of-the-box compliance profiles for Azure SQL Instance and Database:

153
Q

Explain the security baseline for Azure SQL.

A

Using Azure Policy, this security baseline applies guidance from the Microsoft cloud security benchmark version 1.0 to Azure SQL.

154
Q

Why is Azure always encrypted?

A

Azure SQL Always Encrypted is a feature that protects sensitive data by encrypting it on the client side before storing it in the database. Only clients with the correct encryption keys can decrypt and read the data, ensuring that even database administrators cannot access it. This provides enhanced security for confidential information like social security numbers or credit card detail

155
Q

What is Always Encrypted with secure enclaves?

A

Azure Always Encrypted with secure enclaves enhances data security by protecting sensitive data in use, ensuring it remains encrypted during query execution. This feature leverages secure enclaves, isolated and hardware-protected memory regions, allowing computations on encrypted data without exposing it. It ensures higher security for data operations, reducing risk even for administrators with elevated privileges.

156
Q

What is Azure SQL Database and Instance Data Discovery & Classification?

A

Data Discovery & Classification is built into Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. It provides basic capabilities for discovering, classifying, labeling, and reporting the sensitive data in your databases.

157
Q

Explain Transparent data encryption for SQL Database, SQL Managed Instance, and Azure Synapse Analytics?

A

Transparent data encryption (TDE) helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics against the threat of malicious offline activity by encrypting data at rest. It performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application. By default, TDE is enabled for all newly deployed Azure SQL Databases and must be manually enabled for older databases of Azure SQL Database. For Azure SQL Managed Instance, TDE and freshly created databases are enabled at the instance level. TDE must be manually enabled for Azure Synapse Analytics.

158
Q

Can you bring your key when using Transparent data encryption for SQL Database, SQL Managed Instance?

A

Yes, you can use your key or a managed key.

159
Q

What is an always-on availability group?

A

Azure SQL on VM only: It is a one-to-eight replication for high availability and disaster recovery, an alternative to mirroring. It groups one or more databases on the primary and replicates to eight instances per primary database.

160
Q

When using an always-on availability group, can the secondary databases be written?

A

Azure SQL on VM only: No

161
Q

When using an always-on availability group, can the secondary databases be read?

A

Azure SQL on VM only: Yes, this is optional,

162
Q

When using an always-on availability group, is the secondary databases a backup?

A

Azure SQL on VM only: No, because in a disaster situation, insufficient data could corrupt the primary and secondary databases.

163
Q

Explain Always Encrypted?

A

It uses client-side keys to encrypt the data in the SQL database.

164
Q

For Azure SQL Managed Instance, where is database data stored?

A

It’s stored on premium Azure Storage

165
Q

When using Transparent encryption, what key type options do you have available?

A

Azure Managed
Customer managed keys

166
Q

For Azure SQL Managed Instance, is database data stored in LRS, ZRS or GRS?

A

It’s stored on LRS

167
Q

What is the difference between Transparent encryption and Always encrypt?

A

Transparent encryption, encrypts transparently the database data and the logs.
Always encrypt uses client-side keys to encrypt data,

168
Q

When using SQL Database dynamic data masking can a admin see the un masked data?

A

Yes, in Azure SQL Database, administrators with the necessary permissions, such as the SQL Server admin or members of the db_owner role, can see unmasked data, even if dynamic data masking is applied. Dynamic data masking is primarily designed to limit access to sensitive information for non-privileged users. It helps protect sensitive data by hiding it in query results for users who don’t have permissions to see unmasked data, but it doesn’t prevent admins or users with elevated privileges from viewing the full data.

169
Q

What is the primary purpose of Dynamic Data Masking in Azure SQL Database?

A

Dynamic Data Masking helps limit access to sensitive data by masking it at the database level, providing obfuscated data to unauthorized users while allowing authorized users full access.

170
Q

How does Azure SQL Database Dynamic Data Masking work without affecting the actual data stored?

A

Dynamic Data Masking applies real-time obfuscation to query results based on user roles and permissions, so the underlying data remains unchanged in the database.

171
Q

What are the key masking types provided by Azure SQL Database Dynamic Data Masking?

A

The main masking types include:

Default (fully masked)

Email (masks part of email addresses)

Random (masks numeric ranges)

Custom String (masks with a specified pattern).

172
Q

Can Azure SQL Database Dynamic Data Masking be customized for specific user roles?

A

Yes, administrators can define which user roles have access to unmasked data, allowing greater control over who can view sensitive information.

173
Q

How can Dynamic Data Masking be enabled in Azure SQL Database, and what are the setup requirements?

A

Dynamic Data Masking can be enabled through the Azure portal, PowerShell, or T-SQL commands, and requires setting up masking rules for each sensitive column to specify the type of masking to apply.

174
Q

What pricing tiers are available for the Azure SQL Database?

A
  1. vCore-Based Purchasing Model: This model provides flexibility by allowing you to select the number of virtual cores (vCores), memory, and storage independently, aligning resources with your specific workload needs. The vCore-based model includes the following service tiers:

General Purpose: Designed for most business workloads, offering balanced and scalable compute and storage options at an affordable price point.
MICROSOFT LEARN

Business Critical: Tailored for applications requiring low-latency responses and high resilience to failures, utilizing high-performance local SSD storage.
MICROSOFT LEARN

Hyperscale: Ideal for applications with highly scalable storage and read-scale requirements, supporting rapid scaling up to 100 TB of storage.

Within the vCore-based model, you can choose between two compute tiers:

Provisioned Compute: Resources are allocated continuously, suitable for workloads with consistent usage patterns.

Serverless Compute: Resources auto-scale based on workload demand, optimizing cost for intermittent or unpredictable usage patterns.

  1. DTU-Based Purchasing Model: The Database Transaction Unit (DTU) model offers pre-configured bundles of compute, storage, and I/O resources, simplifying the selection process for common workloads. The DTU-based model includes the following service tiers:

Basic: Suitable for small databases with light workloads, providing up to 5 DTUs and 2 GB of storage.

Standard: Designed for moderate workloads, offering a range of DTUs (from 10 to 3,000) and up to 1 TB of storage.

Premium: Intended for high-performance workloads, providing between 125 and 4,000 DTUs and up to 4 TB of storage.

175
Q

For each pricing tier for Azure SQL Database, what are the max resource sizes?

A

vCore-Based Purchasing Model:

General Purpose Tier:
Max vCores: 80
Max Storage: 4 TB
Max Concurrent Workers: 1,600
Max Concurrent Sessions: 32,767

Business Critical Tier:
Max vCores: 80
Max Storage: 16 TB (was at 4TB went to 8TB and now is 16TB)
Max Concurrent Workers: 1,600
Max Concurrent Sessions: 32,767
Hyperscale Tier:

Hyperscale:
Max vCores: 80
Max Storage: 100 TB
Max Concurrent Workers: 1,600
Max Concurrent Sessions: 32,767

176
Q

Does Azure SQL Database support the CLR?

A

Azure SQL Database does not support the creation or execution of user-defined Common Language Runtime (CLR) assemblies. While the clr enabled configuration option is present and set to 1 by default, this setting pertains to internal functionalities that rely on CLR and does not permit user-defined CLR integration.
Microsoft Learn

For scenarios requiring CLR integration, Azure SQL Managed Instance offers support for CLR assemblies, allowing you to author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code.
Microsoft Learn

If migrating an application that utilizes CLR assemblies to Azure SQL Database, you would need to refactor the application to remove CLR dependencies or consider alternative Azure services that support CLR integration.
SQL Server Central

For a detailed overview of CLR integration in SQL Server, you can refer to Microsoft’s documentation.

177
Q

Does Azure SQL on a VM support the CLR?

A

Yes

178
Q

Does Azure SQL Manage Instances support the CLR?

A

Yes, Azure SQL Managed Instance supports Common Language Runtime (CLR) integration, allowing you to create and execute user-defined CLR assemblies such as stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code.
Microsoft Learn

To enable CLR integration in Azure SQL Managed Instance, you need to set the clr enabled option to 1 using the sp_configure stored procedure:

179
Q

For Azure SQL Database, is OLTP available on the general standard tier?

A

No

In Azure SQL Database, In-Memory OLTP is not available in the General Purpose or Standard service tiers. This feature is supported in the Premium (DTU) and Business Critical (vCore) service tiers. The Hyperscale service tier supports a subset of In-Memory OLTP objects but does not include memory-optimized tables.
Microsoft Learn

If your workload requires In-Memory OLTP capabilities, you should consider using the Premium or Business Critical service tiers. These tiers are designed to handle high-performance transaction processing workloads and provide the necessary support for In-Memory OLTP features.

180
Q

Can you scale the read node for Azure SQL Database - Single Database using the general-purpose pricing tier, and if so, what is the maximum number of nodes?

A

The read scale-out feature is unavailable in Azure SQL Database’s General Purpose service tier. This feature, which allows offloading read-only workloads to secondary replicas, is exclusive to the Premium, Business Critical, and Hyperscale service tiers. Therefore, you cannot scale out read nodes in the General Purpose tier.
Microsoft Learn

For scenarios requiring read scale-out capabilities, consider upgrading to one of the supported service tiers:

Premium/Business Critical: These tiers provide high availability with multiple replicas and support read scale-out by redirecting read-only queries to secondary replicas.
Microsoft Learn

Hyperscale: This tier offers rapid scale-out by provisioning additional read replicas as needed, supporting up to four high-availability replicas and up to 30 named replicas for read workloads.
Microsoft Learn

You can effectively manage and scale your read workloads in Azure SQL Database by selecting an appropriate service tier.

181
Q

For Azure SQL Database, will it remain highly available in a regional outage when using a general purpose tier?

A

Azure SQL Database’s General Purpose tier offers high availability within a single region by separating compute and storage layers. However, in the event of a regional outage, this configuration alone doesn’t ensure database availability.

To maintain high availability during regional outages, it’s recommended to implement disaster recovery strategies that span multiple regions. Options include:

Active Geo-Replication: This feature allows you to create continuously synchronized readable secondary databases in different regions. In the event of a regional outage, you can manually fail over to a secondary database to maintain availability.

Auto-Failover Groups: This feature enables automatic failover of multiple databases to a secondary region. It provides read-write and read-only listener endpoints that remain unchanged during failover, simplifying application connectivity.

Implementing these strategies ensures that your Azure SQL Database remains highly available even during regional outages.

182
Q

For Azure SQL Database, will it remain highly available in a regional outage when using a business premium tier?

A

Azure SQL Database’s Business Critical service tier offers high availability within a single region by maintaining multiple replicas of your database. However, in the event of a regional outage, this configuration alone does not ensure database availability. To achieve resilience against regional failures, it’s recommended to implement disaster recovery strategies such as active geo-replication or failover groups, which replicate your database to a secondary region. These approaches enable quick recovery and continuity of service during regional disruptions.

183
Q

You are the IT administrator for a large organization that uses a variety of on-premises and cloud-based services. One of the services used by the organization is a SQL Server instance running on an Azure virtual machine. You need to recommend a disaster recovery solution that meets the following requirements: Provides near real-time data replication to a secondary location in a different Azure region. Supports an RTO of 10 minutes. Supports an RPO of 5 minutes. Minimizes costs while providing the necessary level of protection. What solution should you recommend?

A. Use Azure Site Recovery to replicate the SQL Server virtual machine to a secondary region.

B. Set up SQL Server Always On availability groups with a secondary replica in a different Azure region.

C. Implement SQL Server transactional replication to a SQL Server instance in a different Azure region.

D. Use Azure Backup to take frequent backups and restore to a secondary location in the event of a disaster.

A

B is the answer but it could also be A because of the need to minimize cost.

Further Info:

RPO and RTO Guarantees: While Azure Site Recovery can replicate virtual machines to a secondary region, its RPO and RTO depend on VM replication intervals, which may not consistently achieve the 5-minute RPO required for databases with high transactional requirements. SQL Server Always On availability groups, on the other hand, are optimized to maintain a near real-time secondary copy of the data, making it more suitable for applications requiring strict RPOs.

Application-Level Consistency: Azure Site Recovery is not application-aware. It replicates the entire VM, meaning that for SQL Server, any transactional integrity relies on the VM state, not the SQL Server instance itself. SQL Server Always On availability groups provide transactional consistency and failover at the SQL Server level, ensuring that the database state is preserved accurately in near real-time, which is critical for databases with low RPO and RTO requirements.

Cost-Effectiveness: For databases, Always On availability groups offer an efficient mechanism to replicate data changes, rather than replicating the entire VM state, which can be more costly and less efficient for disaster recovery purposes focused specifically on the SQL Server.

184
Q

Describe the Always On feature when using SQL on a virtual machine?

A

The Always On feature in SQL Server on a virtual machine (VM) in Azure is a high-availability and disaster recovery solution. It provides redundancy and failover support for SQL databases by leveraging Availability Groups or Failover Cluster Instances.

Here’s a breakdown:

Always On Availability Groups (AG): This feature allows multiple databases to be grouped together and synchronized across multiple SQL Server instances on different VMs. Each Availability Group can contain a primary replica and multiple secondary replicas, enabling:

High availability through automatic failover to a secondary replica.
Disaster recovery by deploying replicas across different regions or zones.
Read scale-out with the ability to offload read-only queries to secondary replicas.
Always On Failover Cluster Instances (FCI): This feature uses shared storage across multiple nodes in a failover cluster. It ensures that SQL Server remains available by failing over to another VM in case the primary instance fails. FCI is often combined with Azure Premium File Shares or Azure Shared Disks for storage, supporting failover without data replication.

Load Balancer: When configuring Always On in Azure VMs, an Azure Load Balancer may be used to route traffic to the active SQL instance, especially during failovers.

Benefits of Always On in Azure VMs:

Increases availability and resilience for mission-critical databases.
Supports geo-disaster recovery scenarios with geographically distributed replicas.
Allows cost optimization by running active replicas in less expensive regions while still ensuring failover capabilities.
In summary, SQL Always On for Azure VMs is ideal for applications needing high uptime and disaster recovery while providing the flexibility to manage costs and resources across regions.

185
Q

You are planning to migrate a large-scale Oracle database to Azure. You need to recommend a solution for the Azure SQL Database configuration that meets the following requirements: The database must support high-performance OLTP workloads. The solution must provide automatic failover to a secondary region in case of a disaster. Which service tier should you recommend for the Azure SQL Database and why?

A

For a large-scale Oracle database migration that requires high-performance OLTP workloads with automatic failover to a secondary region, the Business Critical service tier in Azure SQL Database is the recommended choice. Here’s why:

High Performance: The Business Critical tier provides high IOPS and low-latency I/O, which is essential for handling high-performance OLTP (Online Transaction Processing) workloads.

Automatic Failover: The Business Critical tier offers geo-replication capabilities, enabling a secondary replica in a different region. In the event of a disaster, failover to this secondary region is automatic, meeting your requirement for disaster recovery.

High Availability and Redundancy: This tier uses a Premium Availability model with multiple replicas and automatic failover, providing built-in high availability within the primary region, which enhances reliability and durability for mission-critical applications.

Read Scale-Out: The Business Critical tier supports read scale-out to additional replicas, allowing you to offload read operations, which is beneficial for performance optimization.

186
Q

Is the Always On feature available for Azure SQL Database?

A

No

Azure SQL Database, as a fully managed Platform as a Service (PaaS) offering, inherently provides high availability and disaster recovery features without the need for manual configuration of the Always On feature found in SQL Server on virtual machines. By default, Azure SQL Database achieves availability through local redundancy, ensuring your database remains accessible during maintenance operations and local hardware failures.

For enhanced resilience, Azure SQL Database offers zone-redundant configurations, which protect against datacenter-level failures by replicating data across multiple availability zones within a region. This setup provides higher availability and fault tolerance compared to traditional Always On configurations.
Microsoft Learn

In summary, while Azure SQL Database doesn’t utilize the Always On feature as implemented in SQL Server on VMs, it delivers equivalent or superior high availability and disaster recovery capabilities through its built-in redundancy options.

187
Q

What is the purpose of the active geo-replication feature in Azure SQL Database, and where can the readable secondary database be located?

A

The active geo-replication feature in Azure SQL Database allows continuous data replication from a primary database to a readable secondary database, also known as a geo-secondary or geo-replica. The readable secondary database can be located in the same Azure region as the primary database or, more commonly, in a different region.

188
Q

When using active geo-replication, do you get a stable endpoint?

A

No

189
Q

When using auto-failover, do you get a stable endpoint?

A

yes

190
Q

Can you use geo-replication with managed instances?

A

No

191
Q

When creating you Azure SQL, do you get to decide on how you point-in-time and long-term-backups storage is replicated?

A

n Azure SQL Database, Point-in-Time Restore (PITR) and Long-Term Retention (LTR) backups are stored in Azure storage. The level of redundancy you choose for the backup storage determines your ability to recover from certain types of failures, such as a regional outage.

Key Concepts:
Replication Options for Backups:

Locally Redundant Storage (LRS): Backups are stored within a single Azure datacenter. This is cost-effective but does not protect against regional outages.
Geo-Redundant Storage (GRS): Backups are replicated across geographically separated Azure regions. This ensures data availability and recovery even if the primary region experiences an outage.
Point-in-Time Restore (PITR):

Allows you to restore a database to any point within a retention period (e.g., 7–35 days).
PITR is affected by the redundancy option:
With LRS, you can restore within the same region only.
With GRS, you can also restore from the geo-secondary region in case of a regional failure.
Long-Term Retention (LTR):

Allows you to store full backups for an extended period (weeks, months, or years) for compliance or business needs.
The replication setting applies to LTR backups as well. With GRS, the backups are stored in both the primary and secondary regions.
Geo Restore:

Geo restore is a feature available when geo-redundant storage is enabled.
It allows you to recover your database in a different region if the primary region is unavailable.
This is critical for disaster recovery scenarios.
Why Choose Geo-Redundant Storage?
Protection Against Regional Outages: If the primary region experiences a failure, GRS ensures your PITR and LTR backups are available in another region for recovery.
Business Continuity: Geo-redundancy supports recovery objectives aligned with high availability and disaster recovery strategies.

192
Q

In Azure SQL, when you select point-in-time and long-term backups and storage to be geo-replicated, what are you doing?

A

You are ensuring that you can recover in a separate region.

193
Q

How can you create a read only replica in the same or different region?

A

You cna use the geo-replication capability

194
Q

Can you perform a failover manually when using a read replica of a database?

A

Yes, you select the replica and select force failure

195
Q

Can Azure SQL Migration service migrate Opensouce Databases

A

Yes, Azure Database Migration Service (DMS) supports the migration of open-source databases to Azure. It facilitates seamless migrations from multiple database sources, including MySQL and PostgreSQL, to Azure’s managed database services with minimal downtime.

For MySQL databases, DMS enables migration to Azure Database for MySQL. Similarly, PostgreSQL databases can be migrated to Azure Database for PostgreSQL. The service supports both offline (one-time) and online (continuous sync) migration modes, allowing you to choose the approach that best fits your application’s downtime tolerance.

To assist with the migration process, Microsoft provides comprehensive learning paths and tutorials. These resources cover planning, executing, and validating migrations of open-source databases to Azure.

Before initiating a migration, it’s advisable to assess your source databases for compatibility and performance considerations. Tools like the Data Migration Assistant can help identify potential issues and provide recommendations to ensure a smooth transition to Azure’s managed database services.

Azure (49 decks)

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