Free DP-300 Exam Braindumps
Administering Microsoft Azure SQL Solutions Questions 196–200 (page: 41)

Updated On: 10-Feb-2026

HOTSPOT (Drag and Drop is not supported)

You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines named SQLVM1 and a user named User1. SQLVM1 hosts a database named DB1.

You need to ensure that User1 can perform the following tasks on DB1:

Create jobs.

View all jobs.

Modify, delete, and disable the jobs the user created.

The solution must use the principle of least privilege.

Which built-in database role should you assign to User1, and where is the role defined? To answer, select the appropriate options in the answer area.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:





Box 1: msdb
SQL Server has the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:

SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole

Box 2: SQLAgentReaderRole
SQLAgentReaderRole Permissions
SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole.

Incorrect:
* SQLAgentUserRole Permissions (too few)
SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. They cannot use multiserver jobs (master and target server jobs), and they cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of SQLAgentUserRole.

* SQLAgentOperatorRole Permissions (too much)
SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server.

SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server. They can also enable or disable all local jobs and schedules on the server.


Reference:

https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles



You have an Azure subscription that contains an Azure SQL managed instance named SQLMI1 and a Log Analytics workspace named Workspace1.

You need to collect performance metrics for SQLMI1 and stream the metrics to Workspace1.

What should you do first?

  1. Create a private endpoint connection on SQLMI1.
  2. Configure Azure SQL Analytics to use Workspace1.
  3. Modify the Compute + storage settings for SQLMI1.
  4. Modify the diagnostic settings for SQLMI1.

Answer(s): D

Explanation:

Instances in Azure SQL Managed Instance
You can set up a managed instance resource to collect the following diagnostic telemetry:
ResourceUsageStats contains vCores count, average CPU percentage, IO requests, bytes read/written, reserved storage space, and used storage space.
To configure streaming of diagnostic telemetry for managed instance and instance databases, you will need to separately configure each:
Enable streaming of diagnostic telemetry for managed instance Enable streaming of diagnostic telemetry for each instance database The managed instance container has its own telemetry separate from each instance database's telemetry.
To enable streaming of diagnostic telemetry for a managed instance resource, follow these steps:
1. Go to the managed instance resource in Azure portal.
2. Select Diagnostics settings.
3. Select Turn on diagnostics if no previous settings exist, or select Edit setting to edit a previous setting.
4. Enter a setting name for your own reference.
5. Select a destination resource for the streaming diagnostics data: Archive to storage account, Stream to an event hub, or Send to Log Analytics.
6. For log analytics, select Configure and create a new workspace by selecting +Create New Workspace, or use an existing workspace.
7. Select the check box for instance diagnostic telemetry: ResourceUsageStats.
8. Select Save.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming- export-configure



HOTSPOT (Drag and Drop is not supported)

You have an Azure SQL database named DB1 in the General Purpose service tier.

You need to monitor DB1 by using SQL Insights.

What should you include in the solution? To answer, select the appropriate options in the answer area.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:





Box 1: The Azure Monitor agent
SQL Insights performs all monitoring remotely. Monitoring agents on dedicated virtual machines connect to your SQL resources and remotely gather data. The gathered data is stored in Azure Monitor Logs to enable easy aggregation, filtering, and trend analysis. You can view the collected data from the SQL Insights workbook template, or you can delve directly into the data by using log queries.

The following diagram details the steps taken by information from the database engine and Azure resource logs, and how they can be surfaced.



Box 2: A Log Analytics workspace
Log Analytics workspaces
For the Log Analytics workspaces, you're charged based on the pricing published on the Azure Monitor pricing page. The Log Analytics workspaces that SQL Insights uses will incur costs for data ingestion, data retention, and (optionally) data export.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-insights-overview



You have an Azure SQL database named DB1 in the General Purpose service tier.

The performance metrics for DB1 are shown in the following exhibit.



You need to reduce the Log IO percentage. The solution must minimize costs.

What should you do?

  1. Change Service tier to Business Critical.
  2. Increase the number of vCores.
  3. Perform a checkpoint operation.
  4. Change Recovery model to Simple.

Answer(s): A

Explanation:

High Log IO is usually seen when you hit the IOPS/throughput limits for the respective pricing tier.
* Data/Log IOPS (approximate)
General Purpose service tier: 500 - 7500 per file
Increase file size to get more IOPS
Business Critical:
16 K - 320 K (4000 IOPS/vCore)
Add more vCores to get better IO performance.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits



You have an Azure SQL database named DB1 that contains a nonclustered index named index1.

End users report slow queries when they use index1.

You need to identify the operations that are being performed on the index.

Which dynamic management view should you use?

  1. Sys.dm_exec_query_plan_stats
  2. Sys.dm_db_index_physical_stats
  3. Sys.dm_db_index_operational_stats
  4. Sys.dm_db_index_useage_stats

Answer(s): C



Viewing page 41 of 85
Viewing questions 201 - 205 out of 470 questions


Post your Comments and Discuss Microsoft DP-300 exam prep with other Community members:

AI Tutor AI Tutor 👋 I’m here to help!