DP-300 Online Practice Questions and Answers

Correct Answer: B

Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region to protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates a failover from the primary to secondary region. When you enable read access to the secondary region, your data is available to be read if the primary region becomes unavailable. For read access to the secondary region, enable read-access geo-redundant storage (RA- GRS) or read-access geo-zone-redundant storage (RA-GZRS).

Incorrect Answers:

A: Locally redundant storage (LRS) copies your data synchronously three times within a single physical location in the primary region. LRS is the least expensive replication option, but is not recommended for applications requiring high availability.

C: Zone-redundant storage (ZRS) copies your data synchronously across three Azure availability zones in the primary region.

D: Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region to protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates a failover from the primary to secondary region.

Reference: https://docs.microsoft.com/en-us/azure/storage/common/storage-redundancy

Correct Answer: D

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

1.

The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

2.

The SNAPSHOT isolation level.

If READ_COMMITTED_SNAPSHOT is set to ON (the default on SQL Azure Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

Incorrect Answers:

A: When the PARAMETERIZATION database option is set to SIMPLE, the SQL Server query optimizer may choose to parameterize the queries. This means that any literal values that are contained in a query are substituted with parameters. This process is referred to as simple parameterization. When SIMPLE parameterization is in effect, you cannot control which queries are parameterized and which queries are not.

B: You can specify that all queries in a database be parameterized by setting the PARAMETERIZATION database option to FORCED. This process is referred to as forced parameterization.

C: Delayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. Delayed transaction durability reduces both latency and contention within the system.

Some of the cases in which you could benefit from using delayed transaction durability are:

1.

You can tolerate some data loss.

2.

You are experiencing a bottleneck on transaction log writes.

3.

Your workloads have a high contention rate.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

Correct Answer: AC

An auditing policy can be defined for a specific database or as a default server policy in Azure (which hosts SQL Database or Azure Synapse):

1.

A server policy applies to all existing and newly created databases on the server.

2.

If server auditing is enabled, it always applies to the database. The database will be audited, regardless of the database auditing settings.

3.

Enabling auditing on the database, in addition to enabling it on the server, does not override or change any of the settings of the server auditing. Both audits will exist side by side.

Note:

The Server Audit Specification object belongs to an audit. A Database Audit Specification defines which Audit Action Groups will be audited for the specific database in which the specification is created.

Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/auditing-overview

Correct Answer: ACD

C: Advanced Data Security (ADS) is a unified package for advanced SQL security capabilities. ADS is available for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. It includes functionality for discovering and classifying sensitive data

D: You can apply sensitivity-classification labels persistently to columns by using new metadata attributes that have been added to the SQL Server database engine. This metadata can then be used for advanced, sensitivity-based auditing and protection scenarios.

A: An important aspect of the information-protection paradigm is the ability to monitor access to sensitive data. Azure SQL Auditing has been enhanced to include a new field in the audit log called data_sensitivity_information. This field logs the sensitivity classifications (labels) of the data that was returned by a query. Here's an example:

Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview

Correct Answer: D

Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure?tabs=azure-portal#configure-the-streaming-export-of-diagnostic-telemetry

Correct Answer: B

Correct answer is B, because you should have 8 data files for 8vCPU in total, but you already have some data files and you just need to add/configure extra secondary files

Correct Answer: AE

A. a storage account

E. a Log Analytics workspace Azure SQL insights is a feature of Azure Monitor that collects, aggregates, and serves SQL performance metrics. It uses a Log Analytics workspace to store and provide advanced analytics on the performance data. You also need a storage account to maintain the SQL assessment data over time. These two resources are essential for setting up SQL insights. A virtual machine, an Azure logic app, and an Azure function are not necessary for this particular task.

Correct Answer:

Box 1: No

By default CREATE INDEX is disabled. It is here configured as INHERIT so it is disabled.

Box 2: No

By default DROP INDEX is disabled.

Box 3: Yes

FORCE LAST GOOD PLAN (automatic plan correction) - Identifies Azure SQL queries using an execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan.

Reference:

https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable

Correct Answer:

Box 1: "SystemAssigned"

managedInstances, property value: identity

The Azure Active Directory identity of the managed instance.

ResourceIdentity

The identity type. Set this to 'SystemAssigned' in order to automatically create and assign an Azure Active Directory principal for the resource.

Possible values:

'None'

'SystemAssigned'

'SystemAssigned,UserAssigned'

'UserAssigned'

Box 2: "zoneRedundant"

ManagedInstanceProperties include:

* zoneRedundant

Whether or not the multi-az is enabled.

Zone-redundant storage (ZRS) replicates your storage account synchronously across three Azure availability zones in the primary region. Each availability zone is a separate physical location with independent power, cooling, and networking.

Incorrect:

* dnsZonePartner

The resource id of another managed instance whose DNS zone this managed instance will share after creation.

Reference: https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/managedinstances?pivots=deployment-language-bicep

https://learn.microsoft.com/en-us/azure/storage/common/storage-redundancy

Correct Answer: