DP-300 Online Practice Questions and Answers

Correct Answer: D

Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set number of resources at a set price.

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

Correct Answer: A

Azure Data Factory annotations help you easily filter different Azure Data Factory objects based on a tag. You can define tags so you can see their performance or find errors faster.

Reference: https://www.techtalkcorner.com/monitor-azure-data-factory-annotations/

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: 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: A

Correct Answer: B

You can connect to an Azure SQL server using an Azure Private Endpoint.

Azure Private endpoint is the fundamental building block for Private Link in Azure. It enables Azure resources, like virtual machines (VMs), to privately and securely communicate with Private Link resources such as Azure SQL server.

Reference:

https://learn.microsoft.com/en-us/azure/private-link/tutorial-private-endpoint-sql-portal

Correct Answer: A

The correct answer is (A).

The Query Store option is a cost-effective way to reduce memory pressure on a managed Azure SQL instance. The Query Store stores temporary data used by queries in the cloud, reducing the memory requirements of the managed Azure

SQL instance.

Correct Answer:

Box 1: CSV files that have file named beginning with "tripdata_2020"

Box 2: a header

FIRSTROW = 'first_row'

Specifies the number of the first row to load. The default is 1 and indicates the first row in the specified data file. The row numbers are determined by counting the row terminators. FIRSTROW is 1-based.

Reference:

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset

Correct Answer:

Box 1: Store the infrastructure logs in the Cool access tier the application logs in the Archive access tier

Hot - Optimized for storing data that is accessed frequently.

Cool - Optimized for storing data that is infrequently accessed and stored for at least 30 days.

Archive - Optimized for storing data that is rarely accessed and stored for at least 180 days with flexible latency requirements, on the order of hours.

Box 2: Azure Blob storage lifecycle management rules

Blob storage lifecycle management offers a rich, rule-based policy that you can use to transition your data to the best access tier and to expire data at the end of its lifecycle.

Reference:

https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-storage-tiers

Correct Answer:

Step 1: Create a Database Mail account

You would need to setup email account information that contain address of the e-mail server that will actually send the emails, with login/password information required to access e-mail server. In the following script set $(mailserver),

$(loginEmail), and $(password) information, change titles/description shown as '...' and run it:

-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = '...',

@description = '...',

@email_address = '$(loginEmail)',

@display_name = '...',

@mailserver_name = '$(mailserver)' ,

@username = '$(loginEmail)' ,

@password = '$(password)'

Step 2: Create a profile named AzureManagedInstance_dbmail_profile

If you want to send e-mail using SQL Agent jobs, there should be a profile that must be called 'AzureManagedInstance_dbmail_profile'. Otherwise, Managed Instance will be unable to send emails via SQL Agent.

Step 3: Enable email notifications upon failure.

Sending email notifications to operators

You can also define the operators with assigned email addresses and send emails to them using sp_notify_operator procedure. First, you need to add an operator and specify his email address:

EXEC msdb.dbo.sp_add_operator

@name = N'SQL DevOp',

@enabled = 1,

@email_address = N'$(email)',

@weekday_pager_start_time = 080000,

@weekday_pager_end_time = 170000,

@pager_days = 62;

Then, you can send an email notification to the operator:

DECLARE @body VARCHAR(4000) = 'The email is sent using sp_notify_operator from ' + @@SERVERNAME;

EXEC msdb.dbo.sp_notify_operator

@profile_name = N'AzureManagedInstance_dbmail_profile',

@name = N'SQL DevOp',

@subject = N'Azure SQL Instance - Test Notification',

@body = @body;

Again, the important thing here is that you need to specify AzureManagedInstance_dbmail_profile as the email profile that will be used to send the notifications.

Job notifications

Reference: https://techcommunity.microsoft.com/t5/azure-sql-blog/sending-emails-in-azure-sql-managed-instance/ba-p/386235