Azure SQL Type Database

Question 1

What are the types of Azure SQL Databases (MS)

Answer 1

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

Question 2

When should I use Azure SQL Managed Instance?

Answer 2

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.

Question 3

When should I use Azure SQL Database elastic pools?

Answer 3

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.

Question 4

When is it best to use Azure SQL Virtual Machines?

Answer 4

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

Question 5

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

Answer 5

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.

Question 6

Who is responsible for patching when using Azure SQL Database?

Answer 6

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.

Question 7

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

Answer 7

Consider a memory-optimized virtual machine.

Storage-optimized virtual machine.

Question 8

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

Answer 8

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).

2. 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.

Question 9

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

Answer 9

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

Question 10

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

Answer 10

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

Question 11

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

Answer 11

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

Question 12

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

Answer 12

Node local storage

Question 13

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

Answer 13

Azure Blog Storage

Question 14

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

Answer 14

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

Question 15

What the the tiers available in Azure SQL?

Answer 15

General-purpose
Buisness-critical
Hyperscale

Question 16

When would I use the General-Purpose Tier?

Answer 16

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.

Question 17

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

Answer 17

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

Question 18

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

Answer 18

1. Most general-purpose workloads
2. Moderate performance needs.
3. Budget-friendly: Provides cost-effective options.

Question 19

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

Answer 19

1. Most general-purpose workloads
2. Moderate performance needs.
3. Budget-friendly: Provides cost-effective options.

Question 20

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

Answer 20

1. Applications with standard OLTP workloads.
2. Moderate transaction rates and throughput.
3. Applications that need balanced performance at a lower

Question 21

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

Answer 21

1. Applications with standard OLTP workloads.
2. Moderate transaction rates and throughput.
3. Applications that need balanced performance at a lower

Question 22

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

Answer 22

1. vCore
2. DTU

Question 23

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

Answer 23

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.

Question 24

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

Answer 24

Azure Premium SSD for storage (Blob Storage)

Question 25

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

Answer 25

Azure Premium SSD for storage (Blob Storage)

Question 26

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

Answer 26

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

Question 27

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

Answer 27

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

Question 28

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

Answer 28

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.

Question 29

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

Answer 29

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.

Question 30

For Azure SQL Database, explain backups.

Answer 30

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.

Question 31

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

Answer 31

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.

Question 32

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

Answer 32

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

Question 33

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

Answer 33

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

Question 34

For Azure SQL Instance, explain backups?

Answer 34

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.

Question 35

For Azure SQL Instance, explain backup retention?

Answer 35

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.

Question 36

For Azure SQL Database, what pricing tiers are available?

Answer 36

1. vCore: General Purpose 1. vCore: Business Critical 1. vCore: Hyperscale 1. DTU: Basic 1. DTU: Standard 1. DTU: Premium

Question 37

Does Azure SQL Managed Instance support SQL Server Integration Services?

Answer 37

Yes, SQL Server Integration Services (SSIS) is supported

Question 38

Does Azure SQL Database support SQL Server Integration Services?

Answer 38

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.

Question 39

Does Azure SQL Instance support Cross Database transactions?

Answer 39

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.

Question 40

Does Azure SQL Instance support Linked Services?

Answer 40

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.

Question 41

Does Azure SQL Instance support CLR?

Answer 41

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.

Question 42

Does Azure SQL Instance support Service Broker?

Answer 42

No

Question 43

Does Azure SQL Instance support SQL Server Replication?

Answer 43

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

Question 44

Does Azure SQL Instance support Database Mail?

Answer 44

No

Question 45

Does Azure SQL Instance support Stretch Database?

Answer 45

No

Question 46

Does Azure SQL Instance support Data Quality Service?

Answer 46

No

Question 47

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

Answer 47

10year

Question 48

How are backups handled in Azure SQL Instance?

Answer 48

1. Backups are automatic 2. Full weekly backup 3. Diff backups every 12 - 24 hrs 4. Transaction log backups every 5 - 10 min

Question 49

How long are backups retained in Azure SQL Instance?

Answer 49

1. Default retention is 7 to 35 days 2. Long Term Retention up to 10 years

Question 50

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

Answer 50

1. Use point-in-time restore

Question 51

How are backups handled in Azure SQL Instance?

Answer 51

1. Backups are automatic 2. Full weekly backup 3. Diff backups every 12 - 24 hrs 4. Transaction log backups every 5 - 10 min

Question 52

How long are backups retained in Azure SQL Instance?

Answer 52

1. Default retention is 7 to 35 days 2. Long Term Retention up to 10 years

Question 53

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

Answer 53

Use point-in-time restore

Question 54

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

Answer 54

No it is automatic performance tuning

Question 55

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

Answer 55

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

Question 56

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

Answer 56

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

Question 57

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

Answer 57

Use the vCore model

Question 58

Can I use Hybrid Benefits with Azure SQL?

Answer 58

Yes

Question 59

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?

Answer 59

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.

Question 60

Explain how RLS works in Azure SQL Database?

Answer 60

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.

Question 61

Explain how RLS is used in the Azure SQL Database?

Answer 61

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

Question 62

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

Answer 62

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.

Question 63

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

Answer 63

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.

Question 64

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

Answer 64

All databases belong to the server.

Question 65

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?

Answer 65

Use Database-level Firewall Rules

Question 66

What is Always-Encrypted Client-Side Encryption?

Answer 66

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

Question 67

What is Always-Encrypted Column-Level Encryption Encryption?

Answer 67

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.

Question 68

What is Always-Encrypted Separation of Roles ?

Answer 68

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.

Question 69

What is Always-Encrypted Encryption Types?

Answer 69

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.

Question 70

What is Always-Encrypted Transparent to Applications Encryption?

Answer 70

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.

Question 71

What is Always-Encrypted Transparent Keys and Key Management?

Answer 71

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).

Question 72

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

Answer 72

Yes, 5 seconds

Question 73

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

Answer 73

No, only read.

Question 74

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

Answer 74

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

Question 75

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

Answer 75

No, separate regions.

Question 76

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

Answer 76

Yes

Question 77

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

Answer 77

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

Question 78

Is Azure SQL Managed Instance a single-tenant environment?

Answer 78

Yes, Azure SQL Managed is a single-tenant environment

Question 79

Is Azure SQL Database a single-tenant environment?

Answer 79

No, it is a multi-tenant environment

Question 80

Are service-side transactions supported in Azure SQL Database?

Answer 80

No

Question 81

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

Answer 81

Yes

Question 82

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

Answer 82

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.

Question 83

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?

Answer 83

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

Question 84

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

Answer 84

Yes, both support dynamic masking.

Question 85

What makes up the DR for Azure DQL Database?

Answer 85

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

Question 86

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

Answer 86

Create secondary read replicas.

Question 87

Are failover groups used with Active Geo-replication?

Answer 87

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.

Question 88

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

Answer 88

Yes

Question 89

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

Answer 89

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

Question 90

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

Answer 90

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.

Question 91

Explain Azure SQL Database Point-in-Time Restore?

Answer 91

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.

Question 92

Explain Azure SQL Database Geo-Replication?

Answer 92

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.

Question 93

Explain Azure SQL Database Auto-Failover Groups?

Answer 93

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.

Question 94

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

Answer 94

Yes

Question 95

Explain Azure SQL Database Point-in-Time Restore?

Answer 95

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.

Question 96

Explain Azure SQL Database Geo-Restore?

Answer 96

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.

Question 97

Explain Azure SQL Database Zone Redundant Configuration?

Answer 97

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.

Question 98

Explain Azure SQL Database Zone Geo-Redundant Backups?

Answer 98

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.

Question 99

How do you perform an Azure SQL Database?

Answer 99

You do no, performance tuning is automatic

Question 100

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

Answer 100

Yes

Question 101

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

Answer 101

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

Question 102

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

Answer 102

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

Question 103

For Azure SQL Database, what is the SLA?

Answer 103

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

Question 104

For Azure SQL Database, what is the RPO?

Answer 104

0 (Out of the box by default)

Question 105

For Azure SQL Database, what is the RTO?

Answer 105

0 (Out of the box by default)

Question 106

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

Answer 106

Automated backups

Question 107

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

Answer 107

Standard applications with balanced cost and performance

Question 108

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

Answer 108

Yes, compute and storage are separated (remote storage)

Question 109

What is the IO performance like in General Purpose tier?

Answer 109

Moderate, using Azure Premium SSD-backed storage

Question 110

What is the max database size for General Purpose tier?

Answer 110

4 TB

Question 111

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

Answer 111

Zone-redundant with Always On availability groups using remote storage

Question 112

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

Answer 112

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

Question 113

What is the failover time for the General Purpose tier?

Answer 113

30 seconds to several minutes

Question 114

What kind of storage does the General Purpose tier use?

Answer 114

Azure Premium SSD with remote storage

Question 115

How does the General Purpose tier handle scaling?

Answer 115

Moderate scaling capabilities

Question 116

What workloads is the General Purpose tier best for?

Answer 116

Standard workloads with balanced requirements

Question 117

Does the General Purpose tier offer read replicas?

Answer 117

No read replicas available

Question 118

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

Answer 118

Applications requiring high availability and low-latency IO operations

Question 119

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

Answer 119

No, local SSD-based storage

Question 120

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

Answer 120

High, using local SSD with low-latency

Question 121

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

Answer 121

4 TB

Question 122

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

Answer 122

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

Question 123

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

Answer 123

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

Question 124

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

Answer 124

3-10 seconds (fast failover with SSD)

Question 125

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

Answer 125

Local SSD storage with fast local access

Question 126

How does the Business-Critical tier handle scaling?

Answer 126

Moderate scaling capabilities

Question 127

What workloads is the Business-Critical tier best for?

Answer 127

Mission-critical workloads needing low-latency and high availability

Question 128

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

Answer 128

Up to 4 read replicas

Question 129

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

Answer 129

Applications needing high scalability and large database size

Question 130

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

Answer 130

Yes, highly scalable storage and compute

Question 131

What is the IO performance like in Hyperscale tier?

Answer 131

Scalable based on workload demand

Question 132

What is the max database size for Hyperscale tier?

Answer 132

Up to 100 TB

Question 133

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

Answer 133

Storage-level redundancy with highly distributed architecture

Question 134

How does backup and restore work in the Hyperscale tier?

Answer 134

Automated backups with fast snapshot-based restores

Question 135

What is the failover time for the Hyperscale tier?

Answer 135

Minutes, depending on scaling needs

Question 136

What kind of storage does the Hyperscale tier use?

Answer 136

Remote object storage, highly scalable

Question 137

How does the Hyperscale tier handle scaling?

Answer 137

Rapid horizontal scaling with no downtime

Question 138

What workloads is the Hyperscale tier best for?

Answer 138

Large-scale, rapidly growing, read-heavy workloads

Question 139

How many read replicas are available in the Hyperscale tier?

Answer 139

Unlimited replicas for read scaling

Question 140

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

Answer 140

Hyperscale tier as it has upto 100TB of storage

Question 141

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

Answer 141

Unlimited

Question 142

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

Answer 142

Scalable based on workload demand

Question 143

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

Question 144

When using Azure SQL Database standard

Question 145

Has Azure SQL Database got partitioning and shading?

Answer 145

Yes, Azure SQL Database has both capabilities.

Question 146

What is an Azure SQL - Server Lever Firewall?

Answer 146

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.

Question 147

What is an Azure SQL - DatabaseLever Firewall?

Answer 147

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

Question 148

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

Answer 148

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

Question 149

Explain writing audit in Azure SQL server.

Answer 149

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.

Question 150

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

Answer 150

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.

Question 151

Explain dynamic masking?

Answer 151

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.

Question 152

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

Answer 152

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

Question 153

Explain the security baseline for Azure SQL.

Answer 153

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

Question 154

Why is Azure always encrypted?

Answer 154

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

Question 155

What is Always Encrypted with secure enclaves?

Answer 155

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.

Question 156

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

Answer 156

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.

Question 157

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

Answer 157

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.

Question 158

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

Answer 158

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

Question 159

What is an always-on availability group?

Answer 159

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.

Question 160

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

Answer 160

Azure SQL on VM only: No

Question 161

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

Answer 161

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

Question 162

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

Answer 162

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

Question 163

Explain Always Encrypted?

Answer 163

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

Question 164

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

Answer 164

It's stored on premium Azure Storage

Question 165

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

Answer 165

Azure Managed Customer managed keys

Question 166

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

Answer 166

It's stored on LRS

Question 167

What is the difference between Transparent encryption and Always encrypt?

Answer 167

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

Question 168

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

Answer 168

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.

Question 169

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

Answer 169

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.

Question 170

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

Answer 170

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.

Question 171

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

Answer 171

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).

Question 172

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

Answer 172

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

Question 173

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

Answer 173

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.

Question 174

What pricing tiers are available for the Azure SQL Database?

Answer 174

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. 2. 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.

Question 175

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

Answer 175

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

Question 176

Does Azure SQL Database support the CLR?

Answer 176

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.

Question 177

Does Azure SQL on a VM support the CLR?

Answer 177

Yes

Question 178

Does Azure SQL Manage Instances support the CLR?

Answer 178

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:

Question 179

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

Answer 179

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.

Question 180

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?

Answer 180

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.

Question 181

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

Answer 181

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.

Question 182

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

Answer 182

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.

Question 183

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.

Answer 183

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.

Question 184

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

Answer 184

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.

Question 185

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?

Answer 185

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.

Question 186

Is the Always On feature available for Azure SQL Database?

Answer 186

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.

Question 187

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

Answer 187

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.

Question 188

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

Answer 188

No

Question 189

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

Answer 189

yes

Question 190

Can you use geo-replication with managed instances?

Answer 190

No

Question 191

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

Answer 191

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.

Question 192

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

Answer 192

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

Question 193

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

Answer 193

You cna use the geo-replication capability

Question 194

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

Answer 194

Yes, you select the replica and select force failure

Question 195

Can Azure SQL Migration service migrate Opensouce Databases

Answer 195

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.